We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Subquery limitation

myocca
myocca asked
on
Medium Priority
751 Views
Last Modified: 2008-02-26
I need a way around the 16 subquery limitation in a single select statement for use in a view.  Are there any work arounds?
Comment
Watch Question

TMS

Commented:
Is oracle subject to this limitation :) ?

With so few information, it hard to give relevant answer...
If you can wrap your query around a stored procedure, you can use a temp table and populate the rows with different queries and then select * from the temp table...
If you give more information, you might get a better answer.

Author

Commented:
I'm fully aware of what can be done in a stored procedure, but we have a serious need for a view, but this view must be able to do more than 16 subqueries.  Although it is discouraged, is there anything that can be done to the system tables to permit T-SQL to allow more than 16 subqueries?  Or is there another work around.

btw - Does Oracle have this limit?

Commented:
Some kinds of subqueries can be converted to joins (but sadly, some cannot).  If you post the view that you are trying to write, we can see if it can be rewritten with joins instead.

Author

Commented:
Basically attempting to display a denormalized view of normalized tables.....

select   ID,
  'Child01'=(select ChildField from Child C
     where C.ID = P.ID and C.Sequence = 1),
  'Child02'=(select ChildField from Child C
     where C.ID = P.ID and C.Sequence = 2),
.....
  'Child99'=(select ChildField from Child C
     where C.ID = P.ID and C.Sequence = 99)
From Parent P

TMS

Commented:
Never worked with Oracle (who has its own problems anyway :)
I think, but I'm  not sure that joins are also limited to some arbitrary (low) number...

Author

Commented:
Ok, suppose I resign myself to the concept that I cannot achieve this in SQL 6.5.... Will SQL 7.0 have the same limitation?
Whoa!

Hold the phone!

I ran into this same limitation awhile back and solved it by assigning the results from the "repeating" (redundant) SQL statements into variables and then using those variables in my "main" SQL statement.

In your case your table containing "Child01..99" is extremely denormalized but of course you know that as you mentioned it. Anyways, have a gander at this:

Declare @Child01 Integer, @Child02 Integer........@Child99 Integer

Select @Child01'=(select ChildField from Child C
where C.ID = P.ID and C.Sequence = 1)

Select @Child02'=(select ChildField from Child C
where C.ID = P.ID and C.Sequence = 2)

.........
Select @Child99'=(select ChildField from Child C
where C.ID = P.ID and C.Sequence = 99)

Whew!!!! Finally...

Select @Child01, @Child02, @Child03.... @Child99

Nothing says you can't have more than SQL Statement in a view, I use this quite a bit to get around the 16 SubQuery limitation by doing what? Not Using subqueries! Each Select statement above is a "top-level" statement, not a subquery, so that SQL Server limitation does not come into effect.

Anyways, Hope this answer is relevant and Good Luck with your project!

Pegasus

Author

Commented:
Did you actually try this?

Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'declare'.
Msg 127, Level 15, State 1
This CREATE may only contain 1 statement.

create view vwTestC as

declare
 @c01 varchar(30),
 @c02 varchar(30),
 @c03 varchar(30)


select @c01 = name from sysobjects where name like 'sysob%'
select @c02 = name from sysobjects where name like 'sysrefer%'
select @c03 = name from sysobjects where name like 'sysind%'

select @c01, @c02, @c03


Author

Commented:
This error makes sense for a number of reasons the foremost being the limitation of 16 fields on the sysreferences table.

I surmise this question cannot be answered.....
Myocca,

   I was not aware that you were attempting to use the SQL in a CREATE VIEW statement, I must have not read the question well. Anyways...

I'd probably create a temporary table with all 99 fields in it, then use a stored procedure with multiple INSERT statements to populate the table, then construct the VIEW off of the temp table. If data concurrency is of primary importance then you can create Insert triggers on the "base" table to update the "view" table each time a record is added, updated or deleted.

Hopefully a "different" way of approaching the same problem. if this answer does not help then I'll pipe down and let the other experts have a gander at it. My curiousity is up on this one :)

Good luck with your project!
Pegasus

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
A temporary table is not an option because this view is expected to return the current data.  We are exploring stored procedure alternatives and have found a way of putting the sp syntax into a pass through query in Access to solve the problem.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.