Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

UNION ALL is not working in SAP HANA SQL Script?

Hi,

I have written an SQL statement as below.

SELECT GJAHR, SUM(DMBTR) AS "101_DMBTR", SUM(0) as "102_DMBTR" FROM "SAPR3"."MSEG"

where  BWART = '101' and werks = '1102'

group  by GJAHR

union  all

SELECT GJAHR, SUM(0) AS "101_DMBTR", SUM(DMBTR) as "102_DMBTR" FROM "SAPR3"."MSEG"

where  BWART = '102' and werks = '1102'

group  by GJAHR

order  by GJAHR desc;

and the out put i'm getting is

Here i want to club the values into a single row by year but the output i'm getting is into 2 rows.

can anyone suggest me how to solve this issue.

Regards,

Ramana.

Tags:
Former Member
Former Member replied

Hello Ramana,

You could either put your query as the inner query:

SELECT GJAHR, SUM(101_DMBTR) as "101_DMBTR", SUM(102_DMBTR) AS "102_DMBTR"

FROM (<your query goes here>)

GROUP BY GJAHR

Or reformulate your query with a case:

SELECT GJAHR,

  SUM(CASE WHEN BWART = '101' THEN DMBTR ELSE 0 END) AS "101_DMBTR",

  SUM(CASE WHEN BWART = '102' THEN DMBTR ELSE 0 END) AS "102_DMBTR"

FROM "SAPR3"."MSEG"

WHERE WERKS = '1102'

GROUP BY GJAHR

order  by GJAHR desc;

Best regards

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question