cancel
Showing results for 
Search instead for 
Did you mean: 

Need Help with a Query

Former Member
0 Kudos
56

Hello Experts,

I need to build a query which will show a base document and a target document in the same query. For example, a particular sales order and an invoice. Any help would be greatly appreciated

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Gordon,

thank you very much. The syntax work great.

Former Member
0 Kudos

Hi,

Try this first:

SELECT DISTINCt T3.DOCNUM SO#,T3.DocStatus,T1.DOCNUM DLN#, T4.DOCNUM INVOICE#, T0.ITEMCODE, T1.CARDCODE, T1.CARDNAME , T1.DOCDATE, T0.QUANTITY, T0.PRICE, T0.LINETOTAL, T0.TOTALFRGN, T0.RATE

FROM DBO.DLN1 T0

INNER JOIN DBO.ODLN T1 ON T0.DOCENTRY = T1.DOCENTRY

LEFT JOIN DBO.RDR1 T2 ON T0.BASEENTRY = T2.DOCENTRY AND T0.BaseLine = T2.LineNum

LEFT JOIN DBO.ORDR T3 ON T2.DOCENTRY = T3.DOCENTRY

LEFT JOIN DBO.INV1 T4 ON T4.BASEENTRY = T0.DOCENTRY AND T4.BaseLine = T0.LineNum

LEFT JOIN DBO.OINV T5 ON T4.DOCENTRY = T5.DOCENTRY

WHERE T0.DOCNUM like '[%0\]%'

Thanks,

Gordon

Former Member
0 Kudos

This was of great help to me also.... as i was tackling the same problem.

Works great as long as there is a "delivery" but some of our order go direct to from order to invoice

I was able to modify the code to handle that but is there a way to handle BOTH situations in the same query?

ie..pick up either

order > delivery > invoice

order > invoice

thanks

Gary

Former Member
0 Kudos

Since we don't have those data to check, you could change query by yourself. Make the INV1 and OINV as the base. All others are linked by target docentry then.

Former Member
0 Kudos

Agree... was able to handle the situation in two querys...

one that addressed invoice > Delivery > Order

another that addresses Invoice > Order

Do you know if there is a way to combine the querys so that I wouldn't need to run them separatly?

Former Member
0 Kudos

You may use CASE function to combine them.