Solved

# Exceeds 256 table limit

Posted on 2002-03-27

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