Solved

Exceeds 256 table limit

Posted on 2002-03-27
10
1,528 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

679 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