Skip to Content

Making a line total negative in a Union query

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    Posted on Aug 11, 2016 at 10:20 AM

    Hi Marli,

    Please try the below query,......

    SELECT DISTINCT CASE WHEN T0.ObjType = 13 THEN 'Sales' WHEN T0.ObjType = 14 THEN 'Returns' ELSE 'Error' END AS 'TransType',

    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] = 'N'

     

    UNION ALL

     

    SELECT DISTINCT CASE WHEN T0.ObjType = 13 THEN 'Sales' WHEN T0.ObjType = 14 THEN 'Returns' ELSE 'Error' END AS 'TransType',

    T0.[DocNum], T0.[ObjType], T0.[CardName],

    T0.[DocDate], T0.[NumAtCard] AS 'PO ref', T1.[ItemCode], (T1.[Quantity]*-1),

    (T1.[LineTotal]*-1), 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] = 'N'

    Rgds,

    Kamlesh Naware

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 09, 2016 at 12:26 PM

    Hi

    modify your SQL statement in the ORIN select: -1 * T1.LineTotal.

    Both documents save the amounts in the same way. The document means if the quantity is out or in.

    Kind regards

    Agustín Marcos Cividanes

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 10, 2016 at 07:38 AM

    Try this

    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] = 'N'

     

    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] = 'N'

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.