Skip to Content
0
Jun 24, 2015 at 11:38 AM

Query Issue

23 Views

Hi Experts i made the following query

SELECT

T3.PostDate, T3.docnum,t2.ItemName, T9.ItemName as 'Plain Film',

sum(T8.Quantity) as 'Print Qty' , sum(T7.Quantity) as 'Plan Qty',sum(T3.PlannedQty) as 'Laminated Qty',

T0.[u_Dname] as 'Dept Name',T0.[u_Oname] as 'Optr Name',sum (T0.U_PRWAST1) as 'Pr Wast',

sum(T0.U_PLWAST1) as 'Pl Wast',sum (T0.U_TWAST1) as 'T Wast',

datename (MONTH, T3.PostDate)+' - '+ datename (Year, T3.PostDate) as 'Month',t13.Quantity as 'Resin',

t14.Quantity as 'Hardner', T0.U_LMWast1 as 'Lam Waste'

FROM

OIGN T0 INNER JOIN IGN1 T1 ON T0.DocEntry = T1.DocEntry

left outer join oitm t2 on T2.[ItemCode]=T1.[ItemCode]

LEFT OUTER JOIN OWOR T3 ON T3.DOCNUM=T1.[BaseRef]

left outer join WOR1 T4 on T4.DocEntry = T3.DocEntry

left join IGE1 T7 on T4.ItemCode = T7.ItemCode and T7.BaseRef = T3.DocNum and T7.ItemCode like 'FL-%%'

left join IGE1 T8 on T4.ItemCode = T7.ItemCode and T8.BaseRef = T3.DocNum and T8.ItemCode like 'SF-P-%%'

left join OITM T9 on T4.ItemCode = T9.ItemCode

left join IGE1 T13 on T4.ItemCode = T13.ItemCode and T13.BaseRef = T3.DocNum and t2.ItmsGrpCod = '137'

left join IGE1 T14 on T4.ItemCode = T14.ItemCode and T14.BaseRef = T3.DocNum and t2.ItmsGrpCod = '214'

where t1.itemcode like 'SF-L%%'

and (T4.ItemCode like 'FL-%%' or T4.ItemCode like 'SF-P-%%' or t2.ItmsGrpCod = '137' or t2.ItmsGrpCod = '214')

and T8.ItemCode is not null and T3.Status <> 'C' and datename (MONTH, T3.PostDate)+' - '+ datename (Year, T3.PostDate)

= 'May - 2015'

group by T0.[u_Dname],T0.[u_Oname],datename (MONTH, T3.PostDate),datename (Year, T3.PostDate),T0.U_LMWast1,

T3.PostDate, T3.docnum,t2.ItemName,T13.Quantity, T14.Quantity ,T9.ItemName

Order by T3.PostDate, T3.docnum, T0.[u_Dname], T0.[u_Oname]

which give me the following result in the attached screenshot

in this result resin and hardner columns are showing null but in sap production order the value exist both items resin and hardner are the part of same item serial which start with GL- but both have separate item group codes 1 is Resin 2 is Hardner all i want to call the values in these columns another screen shot for your reference

Regards Jamil

Attachments

query111.jpg (575.6 kB)
query123.jpg (340.2 kB)