Solved

Exceeds 256 table limit

Posted on 2002-03-27
10
1,519 Views
Last Modified: 2012-06-21
When i am trying to run query i get the following error:

Server: Msg 4408, Level 16, State 1, Line 1
The query and the views or functions in it exceed the limit of 256 tables.

the query command is long, but i have not had this problem before running the exact same query.  any possible solutions?


here is the query, like i said it is pretty long.  it is pretty repetitive, actually it is the exact same query, just repeated for as many months as the user selects.  the max # of months is 12 months, the code below shows the query for 12 months.  oh one more thing, if i run the query for 11 months instead of 12, i get no error.  

thanks for any help you can give.

select subdiv,g00, (g00-b00) b00, g01, (g01-b01) b01, g02, (g02-b02) b02, g03,
(g03-b03) b03, g04, (g04-b04) b04, g05, (g05-b05) b05, g06, (g06-b06) b06, g07,
(g07-b07) b07, g08, (g08-b08) b08, g09, (g09-b09) b09, g10, (g10-b10) b10, g11,
(g11-b11) b11, (g00+g01+g02+g03+g04+g05+g06+g07+g08+g09+g10+g11) as gTotal,
((g00-b00)+(g01-b01)+(g02-b02)+(g03-b03)+(g04-b04)+(g05-b05)+(g06-b06)+(g07-b07)+
(g08-b08)+(g09-b09)+(g10-b10)+(g11-b11)) as bTotal,
subabv,subnam, regn#, regnam
from
(select s.subdiv, isnull(g00.stat,0) as g00, isnull(b00.stat,0) as b00, isnull(g01.stat,0) as g01,
 isnull(b01.stat,0) as b01, isnull(g02.stat,0) as g02, isnull(b02.stat,0) as b02, isnull(g03.stat,0) as g03,
isnull(b03.stat,0) as b03, isnull(g04.stat,0) as g04, isnull(b04.stat,0) as b04, isnull(g05.stat,0) as g05,
 isnull(b05.stat,0) as b05, isnull(g06.stat,0) as g06, isnull(b06.stat,0) as b06, isnull(g07.stat,0) as g07,
isnull(b07.stat,0) as b07, isnull(g08.stat,0) as g08, isnull(b08.stat,0) as b08, isnull(g09.stat,0) as g09,
isnull(b09.stat,0) as b09, isnull(g10.stat,0) as g10, isnull(b10.stat,0) as b10, isnull(g11.stat,0) as g11,
isnull(b11.stat,0) as b11, s.subabv, s.subnam, r.regn#, r.regnam from
(select distinct subdiv,subnam,subabv, regn# from SBLDRRHI.dbo.v_sales_data
WHERE (slsdte <= '20021231' and slsdte >='20020101') OR (candte <='20021231' AND candte >='20021202')) s
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20020101' and slsdte <= '20020127' AND trnfdt = '' group by subdiv, regn#) g00
on s.subdiv = g00.subdiv and s.regn# = g00.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data w
here candte >= '20020101' and candte <= '20020127' AND trtodt = '' group by subdiv, regn#) b00
on s.subdiv = b00.subdiv and s.regn# = b00.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20020128' and slsdte <= '20020224' AND trnfdt = '' group by subdiv, regn#) g01
on s.subdiv = g01.subdiv and s.regn# = g01.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20020128' and candte <= '20020224' AND trtodt = '' group by subdiv, regn#) b01
on s.subdiv = b01.subdiv and s.regn# = b01.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20020225' and slsdte <= '20020331' AND trnfdt = '' group by subdiv, regn#) g02
on s.subdiv = g02.subdiv and s.regn# = g02.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20020225' and candte <= '20020331' AND trtodt = '' group by subdiv, regn#) b02
on s.subdiv = b02.subdiv and s.regn# = b02.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20020401' and slsdte <= '20020428' AND trnfdt = '' group by subdiv, regn#) g03
on s.subdiv = g03.subdiv and s.regn# = g03.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20020401' and candte <= '20020428' AND trtodt = '' group by subdiv, regn#) b03
on s.subdiv = b03.subdiv and s.regn# = b03.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20020429' and slsdte <= '20020526' AND trnfdt = '' group by subdiv, regn#) g04
on s.subdiv = g04.subdiv and s.regn# = g04.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20020429' and candte <= '20020526' AND trtodt = '' group by subdiv, regn#) b04
on s.subdiv = b04.subdiv and s.regn# = b04.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20020527' and slsdte <= '20020630' AND trnfdt = '' group by subdiv, regn#) g05
on s.subdiv = g05.subdiv and s.regn# = g05.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20020527' and candte <= '20020630' AND trtodt = '' group by subdiv, regn#) b05
on s.subdiv = b05.subdiv and s.regn# = b05.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20020701' and slsdte <= '20020728' AND trnfdt = '' group by subdiv, regn#) g06
on s.subdiv = g06.subdiv and s.regn# = g06.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20020701' and candte <= '20020728' AND trtodt = '' group by subdiv, regn#) b06
on s.subdiv = b06.subdiv and s.regn# = b06.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20020729' and slsdte <= '20020901' AND trnfdt = '' group by subdiv, regn#) g07
on s.subdiv = g07.subdiv and s.regn# = g07.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20020729' and candte <= '20020901' AND trtodt = '' group by subdiv, regn#) b07
on s.subdiv = b07.subdiv and s.regn# = b07.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20020902' and slsdte <= '20020929' AND trnfdt = '' group by subdiv, regn#) g08
on s.subdiv = g08.subdiv and s.regn# = g08.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20020902' and candte <= '20020929' AND trtodt = '' group by subdiv, regn#) b08
on s.subdiv = b08.subdiv and s.regn# = b08.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20020930' and slsdte <= '20021103' AND trnfdt = '' group by subdiv, regn#) g09
on s.subdiv = g09.subdiv and s.regn# = g09.regn#
 left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20020930' and candte <= '20021103' AND trtodt = '' group by subdiv, regn#) b09
on s.subdiv = b09.subdiv and s.regn# = b09.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20021104' and slsdte <= '20021201' AND trnfdt = '' group by subdiv, regn#) g10
on s.subdiv = g10.subdiv and s.regn# = g10.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20021104' and candte <= '20021201' AND trtodt = '' group by subdiv, regn#) b10
on s.subdiv = b10.subdiv and s.regn# = b10.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20021202' and slsdte <= '20021231' AND trnfdt = '' group by subdiv, regn#) g11
on s.subdiv = g11.subdiv and s.regn# = g11.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20021202' and candte <= '20021231' AND trtodt = '' group by subdiv, regn#) b11
on s.subdiv = b11.subdiv and s.regn# = b11.regn# left outer join SBLDRRHI.dbo.region r
on r.regn# = s.regn#) x
0
Comment
Question by:houmerlin
  • 5
  • 3
10 Comments
 
LVL 2

Expert Comment

by:JamesT
ID: 6899392
Can you post the query?
0
 
LVL 2

Accepted Solution

by:
JamesT earned 100 total points
ID: 6899845
From Books On Line:

Maximum Tables per SELECT statement 256

In your select statement you have

Select ....
from (select ......)

In the second select statement, for each value you receive back must count as one table. You must be getting back more than 256 values and hence count as more than 256 tables. To test this, take the second select out and run it by itself. If you get back more than 256 results this confirms. If not, I have no idea.

Just a thought.
0
 

Author Comment

by:houmerlin
ID: 6900088
is there any way increase the system value for 256 tables allowed?
0
 
LVL 2

Expert Comment

by:JamesT
ID: 6900318
No. That is a limit of SQL Server. Sorry. Did you copy just the second select out and run it? Did it return more than 256 results?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:houmerlin
ID: 6900334
yes i copied the second select and ran it and i get the same results.  

i have found that if i comment out the last two left outer joins everything works.  it is very frustrating.  i will figure something out.

thanks for your help.

-paul
0
 
LVL 2

Expert Comment

by:JamesT
ID: 6900391
No problem Wish I was of more help.
0
 

Author Comment

by:houmerlin
ID: 6900756
james,

i found the source of the problem.  the view had a large number of columns in the view.  i created a new view which only contained the columns we needed and it seems everything it working perfectly.  

thanks again for your help.

paul
0
 
LVL 2

Expert Comment

by:JamesT
ID: 6900790
So the view had more than 256 columns? Glad to help.
0
 

Expert Comment

by:CleanupPing
ID: 9280686
houmerlin:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

757 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

19 Experts available now in Live!

Get 1:1 Help Now