UNION ALL is not working in SAP HANA SQL Script?

[Former Member]

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.

[Former Member · Accepted solution]

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

[Former Member]

Hi Florian,

Thanks for your reply.

I mean to say, it's not even local type.

It's a return type of select query which don;t any declarations either locally or globally.

The same query worked when i tried with global table type.

Thanks once again.

Regards,

Ramana.

[pfefferf]

Hi Ramana,

i think your understanding of UNION ALL is wrong (which is also part of your other questions ).

UNION ALL combines the data sets which are returned by your selects. It does not make a grouping over the sets. In your selects the group clause is only applied to the single selects.

As already explaind by and me in your other question, you just have to surround your statement. For your above case:

SELECT GJAHR, SUM("101_DMBTR"), SUM("102_DMBTR") FROM

(

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

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

  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;

Best Regards,

Florian

[Former Member]

Hi Florian,

Thanks for your reply.

The above code is working but will it work only on CATALOG objects or will it work local tables in SQL Script too.

Regards,

Ramana.

[pfefferf]

Hi Ramana,

this works also on "local tables" in SQLScript.

Best Regards,

Florian