Query concatenate Invoice columns

[former_member268870]

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

[kvbalakumar · Accepted solution]

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