Solved

Subquery limitation

Posted on 1997-12-12
11
697 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

821 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