我执行一个存储过程查询,
其中一个传入条件是仓库id的字符串,如'298,290,314,323,293,326,334,354,363,404,405,406,407,408,357,377,389,392',共17个仓库的列表,要求返回指定这17个仓库的横向库存情况(数量和金额),纵向显示商品数据,17个仓库时查询正常
当给定18个仓库列表字符串后,就会报以下的错误
‘未能为视图或函数解析分配辅助表。超过了查询中表的最大数目(260)。’我用的是varchar(8000)的字符串来执行查询,打出的字符长度为7132,并没有超过8000呀
这是什么原因呢?
其中一个传入条件是仓库id的字符串,如'298,290,314,323,293,326,334,354,363,404,405,406,407,408,357,377,389,392',共17个仓库的列表,要求返回指定这17个仓库的横向库存情况(数量和金额),纵向显示商品数据,17个仓库时查询正常
当给定18个仓库列表字符串后,就会报以下的错误
‘未能为视图或函数解析分配辅助表。超过了查询中表的最大数目(260)。’我用的是varchar(8000)的字符串来执行查询,打出的字符长度为7132,并没有超过8000呀
这是什么原因呢?
PS,SQl算这个个数的时候有bug,比如,你查询涉及的2个视图里面都有同样的2个表,SQL算得时候会算成6个!!2个视图+第一个视图的2个表+第二个视图的2个表。所以,检查一下你的视图定义吧
s_SonAll, u_Code,ProdStop,
u_Name,Back1Name,Back2Name,Back3Name,
u_Alias, ProdSpec, ProdType, ProdArea, BarCode,
case
when 0=0 then BaseUnitName
when 0=1 and Assitunit1Name is not null then Assitunit1Name
when 0=1 and Assitunit1Name is null then BaseUnitName
when 0=2 and Assitunit2Name is not null then Assitunit2Name
when 0=2 and Assitunit2Name is null then BaseUnitName
end SelectUnitName,
case
when 0=0 then '1'+BaseUnitName+'=1'+BaseUnitName
when 0=1 and Assitunit1Name is not null then
'1'+Assitunit1Name+'='+case when patindex('%[^0]%.%',reverse(AssitUnit1Rate))>0
then left(AssitUnit1Rate,len(AssitUnit1Rate)-patindex('%[^0]%.%',reverse(AssitUnit1Rate))+1)
else
rtrim(cast(cast(AssitUnit1Rate as float) as char))
end+BaseUnitName
when 0=1 and Assitunit1Name is null then '1'+BaseUnitName+'=1'+BaseUnitName
when 0=2 and Assitunit2Name is not null then
'1'+Assitunit2Name+'='+case when patindex('%[^0]%.%',reverse(AssitUnit2Rate))>0
then left(AssitUnit2Rate,len(AssitUnit2Rate)-patindex('%[^0]%.%',reverse(AssitUnit2Rate))+1)
else
rtrim(cast(cast(AssitUnit2Rate as float) as char))
end+BaseUnitName
when 0=2 and Assitunit2Name is null then '1'+BaseUnitName+'=1'+BaseUnitName
end as DWRatio , (select sum(a.P) from b_vw_C as a
where a.Stor_ID in (298,290,314,323,293,326,334,354,363,404,405,406,407,408,357,377,389,392) and a.R =b.s_id)/( case when 0=1 and Assitunit1Name is not null then AssitUnit1Rate when 0=2 and Assitunit2Name is not null then AssitUnit2Rate else 1 end)
as SumNumber , (select sum(a.M) from b_vw_C as a
where a.Stor_ID in (298,290,314,323,293,326,334,354,363,404,405,406,407,408,357,377,389,392) and a.R =b.s_id ) as SumMoney , (select a.P from b_vw_C as a
where a.Stor_ID=290 and a.R=b.s_id)/( case when 0=1 and Assitunit1Name is not null then AssitUnit1Rate when 0=2 and Assitunit2Name is not null then AssitUnit2Rate else 1 end)
as Stor1Number , (select a.M from b_vw_C as a
where a.Stor_ID=290 and a.R=b.s_id ) as Stor1Money , (select a.P from b_vw_C as a
where a.Stor_ID=293 and a.R=b.s_id)/( case when 0=1 and Assitunit1Name is not null then AssitUnit1Rate when 0=2 and Assitunit2Name is not null then AssitUnit2Rate else 1 end)
as Stor2Number , (select a.M from b_vw_C as a
where a.Stor_ID=293 and a.R=b.s_id ) as Stor2Money , (select a.P from b_vw_C as a
where a.Stor_ID=298 and a.R=b.s_id)/( case when 0=1 and Assitunit1Name is not null then AssitUnit1Rate when 0=2 and Assitunit2Name is not null then AssitUnit2Rate else 1 end)
as Stor3Number , (select a.M from b_vw_C as a
where a.Stor_ID=298 and a.R=b.s_id ) as Stor3Money , (select a.P from b_vw_C as a
where a.Stor_ID=314 and a.R=b.s_id)/( case when 0=1 and Assitunit1Name is not null then AssitUnit1Rate when 0=2 and Assitunit2Name is not null then AssitUnit2Rate else 1 end)
as Stor4Number , (select a.M from b_vw_C as a
where a.Stor_ID=314 and a.R=b.s_id ) as Stor4Money , (select a.P from b_vw_C as a
where a.Stor_ID=323 and a.R=b.s_id)/( case when 0=1 and Assitunit1Name is not null then AssitUnit1Rate when 0=2 and Assitunit2Name is not null then AssitUnit2Rate else 1 end)
as Stor5Number , (select a.M from b_vw_C as a
where a.Stor_ID=323 and a.R=b.s_id ) as Stor5Money , (select a.P from b_vw_C as a
where a.Stor_ID=326 and a.R=b.s_id)/( case when 0=1 and Assitunit1Name is not null then AssitUnit1Rate when 0=2 and Assitunit2Name is not null then AssitUnit2Rate else 1 end)
as Stor6Number , (select a.M from b_vw_C as a
where a.Stor_ID=326 and a.R=b.s_id ) as Stor6Money , (select a.P from b_vw_C as a
where a.Stor_ID=334 and a.R=b.s_id)/( case when 0=1 and Assitunit1Name is not null then AssitUnit1Rate when 0=2 and Assitunit2Name is not null then AssitUnit2Rate else 1 end)
as Stor7Number , (select a.M from b_vw_C as a
where a.Stor_ID=334 and a.R=b.s_id ) as Stor7Money , (select a.P from b_vw_C as a
where a.Stor_ID=354 and a.R=b.s_id)/( case when 0=1 and Assitunit1Name is not null then AssitUnit1Rate when 0=2 and Assitunit2Name is not null then AssitUnit2Rate else 1 end)
as Stor8Number , (select a.M from b_vw_C as a
where a.Stor_ID=354 and a.R=b.s_id ) as Stor8Money , (select a.P from b_vw_C as a
where a.Stor_ID=357 and a.R=b.s_id)/( case when 0=1 and Assitunit1Name is not null then AssitUnit1Rate when 0=2 and Assitunit2Name is not null then AssitUnit2Rate else 1 end)
as Stor9Number , (select a.M from b_vw_C as a
where a.Stor_ID=357 and a.R=b.s_id ) as Stor9Money , (select a.P from b_vw_C as a
where a.Stor_ID=363 and a.R=b.s_id)/( case when 0=1 and Assitunit1Name is not null then AssitUnit1Rate when 0=2 and Assitunit2Name is not null then AssitUnit2Rate else 1 end)
as Stor10Number , (select a.M from b_vw_C as a
where a.Stor_ID=363 and a.R=b.s_id ) as Stor10Money , (select a.P from b_vw_C as a
where a.Stor_ID=377 and a.R=b.s_id)/( case when 0=1 and Assitunit1Name is not null then AssitUnit1Rate when 0=2 and Assitunit2Name is not null then AssitUnit2Rate else 1 end)
as Stor11Number , (select a.M from b_vw_C as a
where a.Stor_ID=377 and a.R=b.s_id ) as Stor11Money , (select a.P from b_vw_C as a
where a.Stor_ID=389 and a.R=b.s_id)/( case when 0=1 and Assitunit1Name is not null then AssitUnit1Rate when 0=2 and Assitunit2Name is not null then AssitUnit2Rate else 1 end)
as Stor12Number , (select a.M from b_vw_C as a
where a.Stor_ID=389 and a.R=b.s_id ) as Stor12Money , (select a.P from b_vw_C as a
where a.Stor_ID=392 and a.R=b.s_id)/( case when 0=1 and Assitunit1Name is not null then AssitUnit1Rate when 0=2 and Assitunit2Name is not null then AssitUnit2Rate else 1 end)
as Stor13Number , (select a.M from b_vw_C as a
where a.Stor_ID=392 and a.R=b.s_id ) as Stor13Money , (select a.P from b_vw_C as a
where a.Stor_ID=404 and a.R=b.s_id)/( case when 0=1 and Assitunit1Name is not null then AssitUnit1Rate when 0=2 and Assitunit2Name is not null then AssitUnit2Rate else 1 end)
as Stor14Number , (select a.M from b_vw_C as a
where a.Stor_ID=404 and a.R=b.s_id ) as Stor14Money , (select a.P from b_vw_C as a
where a.Stor_ID=405 and a.R=b.s_id)/( case when 0=1 and Assitunit1Name is not null then AssitUnit1Rate when 0=2 and Assitunit2Name is not null then AssitUnit2Rate else 1 end)
as Stor15Number , (select a.M from b_vw_C as a
where a.Stor_ID=405 and a.R=b.s_id ) as Stor15Money , (select a.P from b_vw_C as a
where a.Stor_ID=406 and a.R=b.s_id)/( case when 0=1 and Assitunit1Name is not null then AssitUnit1Rate when 0=2 and Assitunit2Name is not null then AssitUnit2Rate else 1 end)
as Stor16Number , (select a.M from b_vw_C as a
where a.Stor_ID=406 and a.R=b.s_id ) as Stor16Money , (select a.P from b_vw_C as a
where a.Stor_ID=407 and a.R=b.s_id)/( case when 0=1 and Assitunit1Name is not null then AssitUnit1Rate when 0=2 and Assitunit2Name is not null then AssitUnit2Rate else 1 end)
as Stor17Number , (select a.M from b_vw_C as a
where a.Stor_ID=407 and a.R=b.s_id ) as Stor17Money
FROM a_vw_ProductBack b Where s_Syb>0 and s_SonCount=0 order by s_fullid