How to Add Additional Query

[former_member593234]

SELECT T1.[DocNum] as 'SO Doc No.', T1.[DocDate] as 'SO Date', T1.DocStatus as ' SO Status', T1.[CardName] as 'Customer Name', T12.CardFName as 'Branch',
T1.Comments AS 'Remarks',T9.SlpName, T10.firstName AS 'SO Owner',T1.DocTotal,T1.Ref1, T1.Ref2, T1.NumAtCard as 'BP Reference No.', T1.PoPrss, T1.Address2, T1.U_TandC
FROM RDR1 T0 INNER JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry
left outer join OSLP T9 on T9.SlpCode = T1.SlpCode
left outer join OHEM T10 on T10.empID = T1.OwnerCode
left outer join OCRD T12 on T12.CardCode = T1.CardCode
WHERE T1.[DocDate] >='[%0]' and T1.[DocDate] <='[%1]' and (T9.SlpName = '[%2]' or '[%2]' ='')
and (T1.CardName = '[%3]' or '[%3]' ='') and T1.DocStatus = '[%4]'
Group by T1.[DocNum] , T1.[DocDate] , T1.DocStatus, T1.[CardName] , T12.CardFName ,
T1.Comments ,T9.SlpName, T10.firstName ,T1.DocTotal,T1.Ref1, T1.Ref2, T1.NumAtCard , T1.PoPrss, T1.Address2, T1.U_TandC

I want to add item details (Part No, Dscrptn & Item code) in the above query. Please help

[Johan_H]

Hi,

Please add the Item Master Data table OITM:

SELECT T1.[DocNum] as 'SO Doc No.'
     , T1.[DocDate] as 'SO Date'
     , T1.DocStatus as ' SO Status'
     , T1.[CardName] as 'Customer Name'
     , T12.CardFName as 'Branch'
     , T1.Comments AS 'Remarks'
     , T9.SlpName
     , T10.firstName AS 'SO Owner'
     , T1.DocTotal
     , T1.Ref1
     , T1.Ref2
     , T1.NumAtCard as 'BP Reference No.'
     , T1.PoPrss
     , T1.Address2
     , T1.U_TandC

     , T2.ItemName
     , T2.CodeBars
     --etc

FROM RDR1 T0
     INNER JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry
     INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode
     left outer join OSLP T9 on T9.SlpCode = T1.SlpCode
     left outer join OHEM T10 on T10.empID = T1.OwnerCode
     left outer join OCRD T12 on T12.CardCode = T1.CardCode
WHERE T1.[DocDate] >='[%0]' and T1.[DocDate] <='[%1]' 
 and (T9.SlpName = '[%2]' or '[%2]' ='')
 and (T1.CardName = '[%3]' or '[%3]' ='')
 and T1.DocStatus = '[%4]' 
Group by T1.[DocNum] , T1.[DocDate] , T1.DocStatus, T1.[CardName] , T12.CardFName ,T1.Comments ,T9.SlpName, T10.firstName ,T1.DocTotal,T1.Ref1, T1.Ref2, T1.NumAtCard , T1.PoPrss, T1.Address2, T1.U_TandC
, T2.ItemName
, T2.CodeBars
--etc

Please be aware that your query will now give you one row per item, so information like the document number will be repeated as many times as there are items in the order.

Regards,

Johan