Query Issue

[former_member252592]

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

[former_member212181 · Accepted solution]

Hi Jamil,

Please change "FROM" area to below. You used base ref & Item code as a link. We should use Objtype, Docentry and Line number

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 WOR1 T4 on T1.BaseType = 202 and T1.BaseEntry = T4.DocEntry and  T1.BaseLine  = T4.LineNum

LEFT OUTER JOIN OWOR T3 ON T4.DocEntry = T3.DocEntry

left join IGE1 T7 on T7.BaseType = 202 and T7.BaseEntry = T4.DocEntry and  T7.BaseLine  = T4.LineNum and T7.ItemCode like 'FL-%%'

left join IGE1 T8 on T8.BaseType = 202 and T8.BaseEntry = T4.DocEntry and  T8.BaseLine  = T4.LineNum and T8.ItemCode like 'SF-P-%%'

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

left join IGE1 T13 on T13.BaseType = 202 and T13.BaseEntry = T4.DocEntry and  T13.BaseLine  = T4.LineNum and t2.ItmsGrpCod = '137'

left join IGE1 T14 on T14.BaseType = 202 and T14.BaseEntry = T4.DocEntry and  T14.BaseLine  = T4.LineNum and t2.ItmsGrpCod = '214'

thanks

unnikrishnan

[kothandaraman_nagarajan]

Hi,

Try this:

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  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]

Thanks.

[former_member252592]

thanx for the reply Nagarajan i tried your query, it changes only the plain film columns values which is not required my required result is still not full fill

thanx & Regards

Jamil

[former_member184146]

Hi ,

try this

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' and T13.ItemCode like 'FL-%%'

left join IGE1 T14 on T4.ItemCode = T14.ItemCode and T14.BaseRef = T3.DocNum and t2.ItmsGrpCod = '214' and T14.ItemCode like 'SF-P-%%'

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]

--Manish