Skip to Content

How to Add Additional Query

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jan 28 at 11:55 AM

    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

    Add comment
    10|10000 characters needed characters exceeded