Query generator help

[ebie]

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

[former_member240324 · Accepted solution]

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

[ebie]

Dear Scott

Thank you so much for your prompt reply and solution