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

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

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!

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.

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

Your question, your audience. Choose who sees your identityâ€”and your questionâ€”with question security.