Skip to Content
0
Former Member
Apr 22, 2014 at 08:21 AM

very large number of tables or partitions. please simplify the query

43 Views

i am getting this error while executing this script in microsoft visual studio for ssrs report.

with cte as(

select

e.FirmName

,a.WhsCode,

CASE

when WhsName like 'wp1-%' then 'wp1'

when WhsName like 'wp2-%' then 'wp2'

when WhsName like 'wp3%' then 'wp3'

when WhsName like 'wp4%' then 'wp4'

when WhsName like 'wp5%' then 'wp5'

when WhsName like 'wp6%' then 'wp6'

when WhsName like 'wp7%' then 'wp7'

when WhsName like 'wp8%' then 'wp8'

when WhsName like 'wp9%' then 'wp9'

when WhsName like 'wp10%' then 'wp10'

when WhsName like 'wp11%' then 'wp11'

when WhsName like 'wp12%' then 'wp12'

when WhsName like 'wp13%' then 'wp13'

when WhsName like 'wp14%' then 'wp14'

when WhsName like 'wp15%' then 'wp15'

when WhsName like 'wp16%' then 'wp16'

when WhsName like 'wp17%' then 'wp17'

when WhsName like 'wp18%' then 'wp18'

when WhsName like 'wp19%' then 'wp19'

when WhsName like 'wp20%' then 'wp20'

when WhsName like 'wp21%' then 'wp21'

when WhsName like 'wp22%' then 'wp22'

when WhsName like 'wpdfs%' then 'WPDFS sales warehouse'

else whsname

end WhsName

,a.ItemCode

,b.ItemName

,b.SuppCatNum

,b.U_mrp,b.U_rpro

,a.OnHand

from OITW a

join OITM b on a.ItemCode=b.ItemCode

left join OMRC e on b.FirmCode=e.FirmCode

left join OWHS c on a.WhsCode=c.WhsCode

where b.FirmCode in (@Brand) and a.ItemCode in (@Itemcode)

and a.WhsCode in (@Warehouse)

),

cte2 as(

select 'Onhand' ID,FirmName,WhsCode,WhsName,ItemCode,ItemName,SuppCatNum,U_mrp,U_rpro,SUM(onhand)onhand from cte

group by FirmName,WhsCode,WhsName,ItemCode,ItemName,SuppCatNum,U_mrp,U_rpro

union all

select '[SALES]',null,null,x.Customername cardname,x.Item itemcode

,null ItemName,null ,0,null

,sum(x.SoldQty)qyt from

(

select

oinv.cardname Customername

,inv1.itemcode Item

,inv1.Quantity 'SoldQty'

from OINV join inv1 on oinv.DocEntry=inv1.DocEntry

left join OCRD on oinv.cardcode=ocrd.cardcode

left join OCRG on ocrd.GroupCode=ocrg.GroupCode

left join OITM on inv1.itemcode=oitm.itemcode

left join OMRC on oitm.FirmCode=omrc.FirmCode

where

oinv.docdate>=@from and oinv.docdate<=@to and ocrd.GroupCode in (@Group) and oinv.CardCode in (@Store)

and oitm.FirmCode in (@Brand) and INV1.ItemCode in (@Itemcode) and oinv.DocType='i'

union all

select

oinv.cardname Customername

,inv1.itemcode Item

,-inv1.Quantity 'SoldQty'

from ORIN OINV join RIN1 inv1 on oinv.DocEntry=inv1.DocEntry

left join OCRD on oinv.cardcode=ocrd.cardcode

left join OCRG on ocrd.GroupCode=ocrg.GroupCode

left join OITM on inv1.itemcode=oitm.itemcode

left join OMRC on oitm.FirmCode=omrc.FirmCode

where

oinv.docdate>=@from and oinv.docdate<=@to and ocrd.GroupCode in (@Group) and oinv.CardCode in (@Store)

and oitm.FirmCode in (@Brand) and inv1.ItemCode in (@Itemcode) and oinv.DocType='i'

)x group by x.Item,x.Customername

)

select *,(select IsCommited from OITW where oitw.ItemCode=cte2.ItemCode and WhsCode='WPBLRWH')'cmtdqty' from cte2