QUERY

[Former Member]

Dear Experts,

I need one column of ITEM GROUP NAME in Following query.

SELECT T0.[DocEntry], T0.[DocNum], T0.[U_ChallanRef], T0.[DocDate], T1.[ItemCode], T1.[Dscription],

T1.[Quantity], ufd.Descr 'Sub Contract Name', T0.[U_Challan_No], T0.[Comments] FROM OIGN T0 

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

LEFT JOIN (select FldValue,Descr from  ufd1 where TableID = 'OIGN' AND FieldID = '32') ufd  on ufd.FldValue=T0.[U_SubConName]

LEFT JOIN (select FldValue,Descr from  ufd1 where TableID = 'OIGN' AND FieldID = '33') UFDADD  on UFDADD.FldValue=T0.[U_SubConAdd] where ufd.Descr!='' AND T0.[DocDate] >=[%0] and T0.[DocDate] <=[%1]

Thanks & Regards,

Akshata

[Former Member · Accepted solution]

Hi Akshata.......

Try this.......

SELECT T0.[DocEntry], T0.[DocNum], T0.[U_ChallanRef], T0.[DocDate], T1.[ItemCode], T1.[Dscription], T5.ItmsGrpNam '# Grp Name',

T1.[Quantity], ufd.Descr 'Sub Contract Name', T0.[U_Challan_No], T0.[Comments] FROM OIGN T0 

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

LEFT JOIN (select FldValue,Descr from  ufd1 where TableID = 'OIGN' AND FieldID = '32') ufd  on ufd.FldValue=T0.[U_SubConName]

LEFT JOIN (select FldValue,Descr from  ufd1 where TableID = 'OIGN' AND FieldID = '33') UFDADD  on UFDADD.FldValue=T0.[U_SubConAdd] LEFT Join OITM T4 ON T4.ItemCode=T1.ItemCode LEFT Join OITB T5 ON T4.ItmsGrpCod=T5.ItmsGrpCod

where ufd.Descr!='' AND T0.[DocDate] >=[%0] and T0.[DocDate] <=[%1]

Regards,

Rahul

[KennedyT21]

HI Akshata,

Try this

SELECT T0.[DocEntry], T0.[DocNum], T0.[U_ChallanRef], T0.[DocDate],

  T1.[ItemCode], T1.[Dscription], T5.ItmsGrpNam  as GroupName,

T1.[Quantity], ufd.Descr 'Sub Contract Name', T0.[U_Challan_No], T0.[Comments] FROM OIGN T0

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

LEFT JOIN (select FldValue,Descr from  ufd1 where TableID = 'OIGN' AND FieldID = '32') ufd  on ufd.FldValue=T0.[U_SubConName]

LEFT JOIN (select FldValue,Descr from  ufd1 where TableID = 'OIGN' AND FieldID = '33') UFDADD  on UFDADD.FldValue=T0.[U_SubConAdd]

LEFT Join OITM T4 ON T4.ItemCode=T1.ItemCode

inner join OITB T5 ON T4.ItmsGrpCod=T5.ItmsGrpCod

where ufd.Descr!='' AND T0.[DocDate] >=[%0] and T0.[DocDate] <=[%1]

Regards

Kennedy