• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1551
  • Last Modified:

Exceeds 256 table limit

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
houmerlin
Asked:
houmerlin
  • 5
  • 3
1 Solution
 
JamesTCommented:
Can you post the query?
0
 
JamesTCommented:
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
 
houmerlinAuthor Commented:
is there any way increase the system value for 256 tables allowed?
0
Industry Leaders: 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!

 
JamesTCommented:
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
 
houmerlinAuthor Commented:
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
 
JamesTCommented:
No problem Wish I was of more help.
0
 
houmerlinAuthor Commented:
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
 
JamesTCommented:
So the view had more than 256 columns? Glad to help.
0
 
CleanupPingCommented:
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now