Problem with UNION ALL in SAP HANA SQL Script Procedure?

[#1]

Hi,

I need to calculate the Wrench Time for a Breakdown Duration from Status of Breakdown Notification from table JCDS.

The data in JCDS table is

i have used SQL Script Procedure to calculate the changes from one status to the other status and the output i'm getting is like below.

I'm not able to club the values as per the Notification Number and below is the code i used.

SELECT NOTIFNO, OBJECT, ENRDUR, MHMDUR, MHSDUR, MHPDUR, MHODUR, MHJDUR, MHRDUR, NODUR

        FROM   :SQL_STATUS_ENR

        UNION

        SELECT NOTIFNO, OBJECT, ENRDUR, MHMDUR, MHSDUR, MHPDUR, MHODUR, MHJDUR, MHRDUR, NODUR

        FROM   :SQL_STATUS_MHM

        UNION

        SELECT NOTIFNO, OBJECT, ENRDUR, MHMDUR, MHSDUR, MHPDUR, MHODUR, MHJDUR, MHRDUR, NODUR

        FROM   :SQL_STATUS_MHS

        UNION

        SELECT NOTIFNO, OBJECT, ENRDUR, MHMDUR, MHSDUR, MHPDUR, MHODUR, MHJDUR, MHRDUR, NODUR

        FROM   :SQL_STATUS_MHP

        UNION

        SELECT NOTIFNO, OBJECT, ENRDUR, MHMDUR, MHSDUR, MHPDUR, MHODUR, MHJDUR, MHRDUR, NODUR

        FROM   :SQL_STATUS_MHO

        ORDER BY NOTIFNO, OBJECT;

Can anyone suggest me how to club the values as per Notification into a single row.

Regards,

Ramana.

[pfefferf · Accepted solution]

Hi Ramana,

as Vivek said you should use UNION ALL so that you will not lose any entries (cause UNION removes duplicates).

Regarding your question, that you "club" (group) the values into a single row per notification you can apply a group on your select.

For example, groupying by Notification Number and Object:

SELECT  NOTIFINO,
                  OBJECT,

                  SUM(ENRDUR),

                  SUM(MHMDUR),

                  SUM(MHSDUR),

                  ...

FROM (

< your select from above >

)

GROUP BY NOTIFINO, OBJECT.

Best Regards,

Florian

[Former Member · Accepted solution]

Hi Vivek/Florian,

Thanks for your reply.

Here my problem is Data Type conversion as UNION ALL will work with column of same data type.

I think wherever 0 values are exists then HANA is considering it as Decimal and where ever values exists its showing with 3 decimals as Decimal DATA Type.

Because of the DATA Type mismatch UNION ALL is not clubbing the values againt NOTIFNO and OBJECT.

Even i tried with TO_DECIMAL (0, 15, 3) to convert to the same DATA Type but HANA internally removes decimal places.

Regards,

Ramana

[former_member226419 · Accepted solution]

I just tried to club values of a field with 2 decimal places and 4 decimal places and its showing no error for me in UNION ALL.

[Former Member · Accepted solution]

Hi Sumeet,

I'm for the tables with values as below.

Please do the UNION for the below data and please let me know.

I have used SQL as below

SQL_STATUS_1 =

        SELECT NOTIFNO, OBJECT, SUM(ENRDUR) AS "ENRDUR", SUM(MHMDUR) AS "MHMDUR",

                                SUM(MHSDUR) AS "MHSDUR", SUM(MHPDUR) AS "MHPDUR",

                                SUM(MHODUR) AS "MHODUR", SUM(MHJDUR) AS "MHJDUR",

                                SUM(MHRDUR) AS "MHRDUR", SUM(NODUR) AS "NODUR"

        FROM   :SQL_STATUS_ENR

        GROUP  BY NOTIFNO, OBJECT

        UNION  ALL

        SELECT NOTIFNO, OBJECT, SUM(ENRDUR) AS "ENRDUR", SUM(MHMDUR) AS "MHMDUR",

                                SUM(MHSDUR) AS "MHSDUR", SUM(MHPDUR) AS "MHPDUR",

                                SUM(MHODUR) AS "MHODUR", SUM(MHJDUR) AS "MHJDUR",

                                SUM(MHRDUR) AS "MHRDUR", SUM(NODUR) AS "NODUR"

        FROM   :SQL_STATUS_MHM

        GROUP  BY NOTIFNO, OBJECT

        UNION  ALL

        SELECT NOTIFNO, OBJECT, SUM(ENRDUR) AS "ENRDUR", SUM(MHMDUR) AS "MHMDUR",

                                SUM(MHSDUR) AS "MHSDUR", SUM(MHPDUR) AS "MHPDUR",

                                SUM(MHODUR) AS "MHODUR", SUM(MHJDUR) AS "MHJDUR",

                                SUM(MHRDUR) AS "MHRDUR", SUM(NODUR) AS "NODUR"

        FROM   :SQL_STATUS_MHS

        GROUP  BY NOTIFNO, OBJECT

        UNION  ALL

        SELECT NOTIFNO, OBJECT, SUM(ENRDUR) AS "ENRDUR", SUM(MHMDUR) AS "MHMDUR",

                                SUM(MHSDUR) AS "MHSDUR", SUM(MHPDUR) AS "MHPDUR",

                                SUM(MHODUR) AS "MHODUR", SUM(MHJDUR) AS "MHJDUR",

                                SUM(MHRDUR) AS "MHRDUR", SUM(NODUR) AS "NODUR"

        FROM   :SQL_STATUS_MHP

        GROUP  BY NOTIFNO, OBJECT ORDER  BY NOTIFNO, OBJECT;

and i'm getting the output as below

SQL Code is not clubbing by NOTIFNO wise and it's showing in different rows.

Please suggest me how to write UNION ALL using SQL Script.

Regards,

Ramana.

[lbreddemann · Accepted solution]

Ramana,

you are grouping and aggregating the data on the wrong level, before the UNION operation.

From what you described you want to perform the SUM/GROUP on the result of the UNION operations.

Thus your query should look similar to this:

select NOTINFO, OBJECT, SUM (MHSDUR), SUM(...)

from ( SELECT NOTINFO,...

          UNION ALL

          SELECT NOTINFO...

          UNION ALL ...)

group by NOTINFO, OBJECT...

In fact you could simply surround your existing query with the additional aggregation since SUMming up the already summed up durations will still yield valid results.

- Lars

[Former Member · Accepted solution]

Hi Lars,

Thanks for your reply.

Can you suggest me the clear syntax for UNION ALL mentioned above because i'm facing some issues.

Regards,

Ramana.

[Former Member]

Hi Ramana

SQL_STATUS_1 =

        SELECT NOTIFNO, OBJECT, SUM(ENRDUR) AS "ENRDUR", SUM(MHMDUR) AS "MHMDUR",

                                SUM(MHSDUR) AS "MHSDUR", SUM(MHPDUR) AS "MHPDUR",

                                SUM(MHODUR) AS "MHODUR", SUM(MHJDUR) AS "MHJDUR",

                                SUM(MHRDUR) AS "MHRDUR", SUM(NODUR) AS "NODUR"

        FROM   :SQL_STATUS_ENR

        GROUP  BY NOTIFNO, OBJECT

        UNION  ALL

        SELECT NOTIFNO, OBJECT, SUM(ENRDUR) AS "ENRDUR", SUM(MHMDUR) AS "MHMDUR",

                                SUM(MHSDUR) AS "MHSDUR", SUM(MHPDUR) AS "MHPDUR",

                                SUM(MHODUR) AS "MHODUR", SUM(MHJDUR) AS "MHJDUR",

                                SUM(MHRDUR) AS "MHRDUR", SUM(NODUR) AS "NODUR"

        FROM   :SQL_STATUS_MHM

        GROUP  BY NOTIFNO, OBJECT

        UNION  ALL

        SELECT NOTIFNO, OBJECT, SUM(ENRDUR) AS "ENRDUR", SUM(MHMDUR) AS "MHMDUR",

                                SUM(MHSDUR) AS "MHSDUR", SUM(MHPDUR) AS "MHPDUR",

                                SUM(MHODUR) AS "MHODUR", SUM(MHJDUR) AS "MHJDUR",

                                SUM(MHRDUR) AS "MHRDUR", SUM(NODUR) AS "NODUR"

        FROM   :SQL_STATUS_MHS

        GROUP  BY NOTIFNO, OBJECT

        UNION  ALL

        SELECT NOTIFNO, OBJECT, SUM(ENRDUR) AS "ENRDUR", SUM(MHMDUR) AS "MHMDUR",

                                SUM(MHSDUR) AS "MHSDUR", SUM(MHPDUR) AS "MHPDUR",

                                SUM(MHODUR) AS "MHODUR", SUM(MHJDUR) AS "MHJDUR",

                                SUM(MHRDUR) AS "MHRDUR", SUM(NODUR) AS "NODUR"

        FROM   :SQL_STATUS_MHP

        GROUP  BY NOTIFNO, OBJECT

        ORDER  BY NOTIFNO, OBJECT;

Sql_out  = SELECT NOTIFNO, OBJECT, SUM(ENRDUR) AS "ENRDUR", SUM(MHMDUR) AS "MHMDUR",

                                SUM(MHSDUR) AS "MHSDUR", SUM(MHPDUR) AS "MHPDUR",

                                SUM(MHODUR) AS "MHODUR", SUM(MHJDUR) AS "MHJDUR",

                                SUM(MHRDUR) AS "MHRDUR", SUM(NODUR) AS "NODUR"

        FROM  :  SQL_STATUS_1

GROUP  BY NOTIFNO, OBJECT

ORDER  BY NOTIFNO, OBJECT;

Best Regards

Khaga

[Former Member]

Hi Lars,

Thanks for your reply.

The syntax you have provided above is not working. i mean it's giving syntax error too.

Please suggest me in detail.

Regards,

Ramana.

[lbreddemann]

I gave you the sketch of the SQL statement, not the actual full command.

Please flesh it out yourself - it's pretty straight forward.

Just put your previous query into the FROM clause and add the SELECT and GROUP BY clauses again. That's it.

[vivekbhoj]

Hi Ramana,

From your code, it seems you are using UNION and not UNION ALL

Try UNION ALL and then check again.

Regards,

Vivek