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.

[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