Solved

Subquery limitation

Posted on 1997-12-12
11
711 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?
0
Comment
Question by:myocca
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +1
11 Comments
 

Expert Comment

by:TMS
ID: 1089605
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.
0
 

Author Comment

by:myocca
ID: 1089606
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?

0
 
LVL 10

Expert Comment

by:bret
ID: 1089607
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.
0
Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

 

Author Comment

by:myocca
ID: 1089608
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

0
 

Expert Comment

by:TMS
ID: 1089609
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...
0
 

Author Comment

by:myocca
ID: 1089610
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?
0
 
LVL 3

Expert Comment

by:Pegasus100397
ID: 1089611
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

0
 

Author Comment

by:myocca
ID: 1089612
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


0
 

Author Comment

by:myocca
ID: 1089613
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.....
0
 
LVL 3

Accepted Solution

by:
Pegasus100397 earned 100 total points
ID: 1089614
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
0
 

Author Comment

by:myocca
ID: 1089615
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.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question