Skip to Content
avatar image
Former Member

Pivot Query issue

Dear Experts,

While running below Pivot Query I am getting this "Error while converting datatype varchar to numeric"

Select distinct p.ItemCode, p.ItemName, p.ItemStatus, p.PackingType, p.Division, p.UOM, p.MinimumOrderedQty, p.BLR, (case when p.KOL= 0 and p.GHZ= 0 then CONVERT(decimal(20,6),p.ClosingQty) else 'NA' end)as BLRClosingQty, p.KOL, (case when p.BLR = 0 and p.GHZ=0 then CONVERT(decimal(20,6),p.ClosingQty) else 'NA' end)as KOLClosingQty, p.GHZ, (case when p.KOL= 0 and p.BLR=0 then CONVERT(decimal(20,6),p.ClosingQty) else 'NA' end) as GHZClosingQty from( select distinct T2.LocCode as Location,T0.ItemCode as ItemCode, t0.ItemName as ItemName, (Case when t0.[frozenFor]='Y' then 'Inactive' when t0.[frozenFor]='N' then 'Active' else 'NA' end) as ItemStatus, t0.U_packingType as PackingType,t1.ItmsGrpNam as Division,t0.SalUnitMsr as UOM,t6.MinOrdrQty as MinimumOrderedQty ,SUM(t2.Quantity) as OutwardQty, t3.OnHand as ClosingQty from OITM T0 left join OITB T1 on t1.ItmsGrpCod = t0.ItmsGrpCod left join OILM T2 on T0.ItemCode = T2.ItemCode and t2.TransType = 13 left join OITW T3 on t3.WhsCode = T2.LocCode and t3.ItemCode = t0.ItemCode left join OLCT T4 on T4.Code = T2.Location left join OITM t6 on t6.ItemCode = t0.ItemCode WHERE T2.DocDate between '2017-12-01 00:00:00' and '2017-12-06 00:00:00' and T2.BPCardCode not in ('C1002213', 'C1002215', 'C1001525', 'C1002211', 'C1002216', 'C1002212', 'C1002214', 'C1001524', 'C1001526', 'C1001527' ) and T2.AccumType = 1 group by t0.ItemCode, t0.ItemName, t2.LocCode, t3.OnHand ,t1.ItmsGrpNam,t0.createdate ,t0.SalUnitMsr,t6.MinOrdrQty,t0.U_packingType,t0.[frozenFor] )S Pivot (SUM(OutwardQty) For Location in ([BLR],[GHZ],[KOL]) )P Group By p.ItemCode,p.ItemName,p.ClosingQty ,p.Division,p.UOM,p.MinimumOrderedQty,p.PackingType,p.ItemStatus,p.BLR,p.GHZ,p.KOL


Vedanth M

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers