Skip to Content
0
Aug 06, 2016 at 11:19 AM

Making a line total negative in a Union query

229 Views

Experts,

This query will pull Credit Memo's and Invoices. But the line total for both shows as positive.

Is there a way I can change just the CM line total to be a negative amount?

SELECT DISTINCT T0.[DocNum], T0.[ObjType], T0.[CardName], T0.[DocDate], T0.[NumAtCard] AS 'PO ref', T1.[ItemCode], T1.[Quantity], T1.[LineTotal], T0.[U_NBS_GPA_08], T0.[U_NBS_GPA_09], T0.[U_NBS_GPA_10], T0.[U_NBS_GPA_11], T0.[U_NBS_GPA_04], T0.[U_NBS_GPA_05], T0.[U_NBS_GPA_01], T0.[U_NBS_GPA_02], T0.[U_NBS_GPA_03], T0.[ShipToCode], T0.[CANCELED]

FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OJDT T2 ON T0.[TransId] = T2.[TransId] INNER JOIN JDT1 T3 ON T2.[TransId] = T3.[TransId]

WHERE T3.[Account] = '_SYS00000000434' AND T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1] AND T0.[CANCELED] != 'Y'

 

UNION ALL

 

SELECT DISTINCT T0.[DocNum], T0.[ObjType], T0.[CardName], T0.[DocDate], T0.[NumAtCard] AS 'PO ref', T1.[ItemCode], T1.[Quantity], T1.[LineTotal], T0.[U_NBS_GPA_08], T0.[U_NBS_GPA_09], T0.[U_NBS_GPA_10], T0.[U_NBS_GPA_11], T0.[U_NBS_GPA_04], T0.[U_NBS_GPA_05], T0.[U_NBS_GPA_01], T0.[U_NBS_GPA_02], T0.[U_NBS_GPA_03], T0.[ShipToCode], T0.[CANCELED]

FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OJDT T2 ON T0.[TransId] = T2.[TransId] INNER JOIN JDT1 T3 ON T2.[TransId] = T3.[TransId]

WHERE T3.[Account] = '_SYS00000000434' AND T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1] AND T0.[CANCELED] != 'Y'

Regards,

Marli