Skip to Content

Query concatenate Invoice columns

Experts,

I have a query listing Sales quotes and the linked Order and Invoice numbers. I have 2 sets of Invoice data depending if the linked document was a Order (_SO) or a Quote (_SQ).

How can I put the Invoice Number, Date and Status in the same columns for both cases?

SELECT DISTINCT	
	T0.DocEntry AS 'Quote', T0.DocDate as 'QuoteDate', T1.DocStatus AS 'QuoteStatus',
	T2.DocEntry AS 'Order', T2.DocDate as 'OrderDate', T3.DocStatus AS 'OrderStatus',
	T4.DocEntry AS 'Invoice_SO', T4.DocDate as 'InvoiceDate_SO', T5.DocStatus AS 'InvoiceStatus_SO',
	T6.DocEntry AS 'Invoice_SQ', T6.DocDate as 'InvoiceDate_SQ', T7.DocStatus AS 'InvoiceStatus_SQ'


FROM QUT1 T0
INNER JOIN OQUT T1 ON T0.[DocEntry] = T1.[DocEntry]


----- Sales Order linked to Quote
LEFT OUTER JOIN RDR1 T2 ON T2.BaseEntry = T0.DocEntry
	AND T2.BaseLine = T0.LineNum
LEFT OUTER JOIN ORDR T3 ON T2.[DocEntry] = T3.[DocEntry]


----- Invoice linked to Sales Order
LEFT OUTER JOIN INV1 T4 ON T4.BaseEntry = T2.DocEntry
	AND T4.BaseLine = T2.LineNum
LEFT OUTER JOIN OINV T5 ON T4.[DocEntry] = T5.[DocEntry]


---- Invoice linked to Quote
LEFT OUTER JOIN INV1 T6 ON T6.BaseEntry = T0.DocEntry
	AND T6.BaseLine = T0.LineNum
LEFT OUTER JOIN OINV T7 ON T6.[DocEntry] = T7.[DocEntry]


ORDER BY T0.DocEntry DESC
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Mar 14 at 05:49 PM

    Hi,

    Try this

    SELECT DISTINCT T0.DocEntry AS 'Quote', T0.DocDate AS 'QuoteDate', T1.DocStatus AS 'QuoteStatus',
        T2.DocEntry AS 'Order', T2.DocDate AS 'OrderDate', T3.DocStatus AS 'OrderStatus',
        ISNULL(T4.DocEntry, T6.DocEntry) AS 'Invoice', ISNULL(T4.DocDate, T6.DocDate) AS 'InvoiceDate',
        ISNULL(T5.DocStatus, T7.DocStatus) AS 'InvoiceStatus'
    FROM QUT1 T0
         INNER JOIN OQUT T1 ON T0.[DocEntry]=T1.[DocEntry]
         ----- Sales Order linked to Quote
         LEFT OUTER JOIN RDR1 T2 ON T2.BaseEntry=T0.DocEntry AND T2.BaseLine=T0.LineNum AND T2.BaseType=T1.ObjType
         LEFT OUTER JOIN ORDR T3 ON T2.[DocEntry]=T3.[DocEntry]
         ----- Invoice linked to Sales Order
         LEFT OUTER JOIN INV1 T4 ON T4.BaseEntry=T2.DocEntry AND T4.BaseLine=T2.LineNum AND T4.BaseType=T3.ObjType
         LEFT OUTER JOIN OINV T5 ON T4.[DocEntry]=T5.[DocEntry]
         ---- Invoice linked to Quote
         LEFT OUTER JOIN INV1 T6 ON T6.BaseEntry=T0.DocEntry AND T6.BaseLine=T0.LineNum AND T6.BaseType=T1.ObjType
         LEFT OUTER JOIN OINV T7 ON T6.[DocEntry]=T7.[DocEntry]
    ORDER BY T0.DocEntry DESC;

    Regards,

    Bala

    Add comment
    10|10000 characters needed characters exceeded