Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 726
  • Last Modified:

Subquery limitation

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
myocca
Asked:
myocca
  • 6
  • 2
  • 2
  • +1
1 Solution
 
TMSCommented:
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
 
myoccaAuthor 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?

0
 
bretCommented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
myoccaAuthor 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

0
 
TMSCommented:
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
 
myoccaAuthor 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?
0
 
Pegasus100397Commented:
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
 
myoccaAuthor 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


0
 
myoccaAuthor 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.....
0
 
Pegasus100397Commented:
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
 
myoccaAuthor 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.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 6
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now