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