Skip to Content
1

Query generator help

Nov 22, 2016 at 10:31 AM

30

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Scott McClure Nov 22, 2016 at 06:10 PM
1

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

Share
10 |10000 characters needed characters left characters exceeded
Ebie Conrad Nov 24, 2016 at 08:40 AM
0

Dear Scott

Thank you so much for your prompt reply and solution

Share
10 |10000 characters needed characters left characters exceeded