Solved

Subquery limitation

Posted on 1997-12-12
11
681 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
  • 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
 

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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now