Skip to Content

Query generator help

the below query is to check if a certain item has been billed to a business partner by sales order. i would like to refine the query to show only the sales orders where the specific (A802073) item was not billed to the business partner.

SELECT T1.[DocNum], T1.[CardName], T1.[DocDate], T2.itemcode, t2.Dscription from ODLN T0 INNER JOIN DLN1 T2 ON T0.DocEntry = T2.DocEntry INNER JOIN RDR1 T3 ON T3.DocEntry = T2.BaseEntry AND T2.BaseType = 17 AND T3.LineNum = T2.BaseLine INNER JOIN ORDR T1 ON T1.DocEntry = T3.DocEntry WHERE t2.itemcode <> '%%A802073%%' and t1.cardcode between [%0] and [%1] and t1.docdate >= [%2] ORDER BY T1.docnum

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Nov 22, 2016 at 06:10 PM

    I think a subquery will help you:

    SELECT T1.[DocNum], T1.[CardName], T1.[DocDate], T2.itemcode, t2.Dscription
    from ODLN T0
    INNER JOIN DLN1 T2 ON T0.DocEntry = T2.DocEntry
    INNER JOIN RDR1 T3 ON T3.DocEntry = T2.BaseEntry AND T2.BaseType = 17 AND T3.LineNum = T2.BaseLine
    INNER JOIN ORDR T1 ON T1.DocEntry = T3.DocEntry
    WHERE t1.cardcode between [%0] and [%1]
    and t1.docdate >= [%2]
    and not exists (select 'x' from DLN1 T4 where T4.docEntry = t0.docentry and T4.itemcode = 'A802073')

    ORDER BY T1.docnum

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 24, 2016 at 08:40 AM

    Dear Scott

    Thank you so much for your prompt reply and solution

    Add comment
    10|10000 characters needed characters exceeded