Help with Union All function?

[#1]

Hello,

I am trying to make a query that will give me the same information as the built in Sales Analysis report in order to generate dashboards. So far I have managed to get two separate queries working - one for sales invoices and one for sales credits per customer. Both queries work OK on their own but when I try and merge them with a Union All function I get the following error:

1). [SAP AG][LIBODBCHDB32 DLL][HDBODBC32] Syntax error or access violation;257 sql syntax error: incorrect syntax near "UNION": li  '' (SWEI)

All the query information is below, I think I am using the UNION ALL function correctly but I am new at this and so I could use some help. Any pointers would be very much appreciated.

SELECT T0."CardCode", T0."CardName", COUNT(T0."DocNum") AS "No. of Invoices", SUM(T1."LineTotal") AS "Annual Sales To Date", T2."U_BTotal", SUM(T1."LineTotal") - T2."U_BTotal" AS "Budget Over/Under"

FROM OINV T0  INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN OCRD T2 ON T0."CardCode" = T2."CardCode"

WHERE T0."CANCELED" = 'N'

GROUP BY T0."CardCode", T0."CardName", T2."U_BTotal"

ORDER BY T0."CardName"

UNION ALL

SELECT T0."CardCode", T0."CardName", COUNT(T0."DocNum") AS "No. of Credits", SUM(T1."LineTotal"*-1) AS "Annual Credits To Date", T2."U_BTotal", SUM(T1."LineTotal"*-1) - T2."U_BTotal" AS "Budget Over/Under"

FROM "FISPAKLIVE"."ORIN"  T0 INNER JOIN "FISPAKLIVE"."RIN1"  T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN OCRD T2 ON T0."CardCode" = T2."CardCode"

WHERE T0."CANCELED"  = 'N'

GROUP BY T0."CardCode", T0."CardName", T2."U_BTotal"

ORDER BY T0."CardName"

Thanks a lot.

[Former Member · Accepted solution]

Hi Ali,

When you make a query with union, the order by clause must be put in the final query.

Try this:

SELECT T0."CardCode", T0."CardName", COUNT(T0."DocNum") AS "No. of Invoices", SUM(T1."LineTotal") AS "Annual Sales To Date", T2."U_BTotal", SUM(T1."LineTotal") - T2."U_BTotal" AS "Budget Over/Under"

FROM OINV T0  INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN OCRD T2 ON T0."CardCode" = T2."CardCode"

WHERE T0."CANCELED" = 'N'

GROUP BY T0."CardCode", T0."CardName", T2."U_BTotal"

UNION ALL

SELECT T0."CardCode", T0."CardName", COUNT(T0."DocNum") AS "No. of Credits", SUM(T1."LineTotal"*-1) AS "Annual Credits To Date", T2."U_BTotal", SUM(T1."LineTotal"*-1) - T2."U_BTotal" AS "Budget Over/Under"

FROM "FISPAKLIVE"."ORIN"  T0 INNER JOIN "FISPAKLIVE"."RIN1"  T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN OCRD T2 ON T0."CardCode" = T2."CardCode"

WHERE T0."CANCELED"  = 'N'

GROUP BY T0."CardCode", T0."CardName", T2."U_BTotal"

ORDER BY T0."CardName"

Regards,

Laís

[Former Member · Accepted solution]

Hi Lais,

Thanks a lot for your help - that executed without a problem. I was under the impression that the union all function would help me minus the credits from the invoices in order to get an accurate sales figure per customer... Can you tell me is that possible with Union All? If I have misunderstood the function, I will start again and try a different method to recreate the report.

Thanks again,

Ali

[Former Member · Accepted solution]

You are welcome

The group by function are used to sum numerical values by some aggregation. In your case, it's counting the number of registres and summing values of invoices by the other fields. The minus is just a value that you want subtract from the total calculated.

If it's not clear, I used to learn sql in this site: SQL UNION Operator

It's very heltpfull!

Regards,

Laís

[Former Member · Accepted solution]

Thanks for the link Lais - very helpful .

[Former Member]

Hi,

If i want to add another column in order by getting error message, how to give more than one column in order by. please help me with an option

Thanks & Regards,

JK Prabhu