SAP HANA SCRIPT

[former_member230865]

Hi Guys,

Need your help regarding sqlscript on HANA,

On the screenshot, what i want to do is to sum the doctotal base on cardname,and INV#.

Output should be like this

CardCode, CardName.................DocTotal...................INV#

C000301     PUREGOLD          SUM(DOcTotal)           2,3,4

T.I.A

[Former Member · Accepted solution]

Hi Norman,

Below is the sample data.

Below is the query that provides the required output.

If you also want all the other fields from the table after aggregation, then use the below query. This has one more INNER JOIN

SELECT  T2.CARD_CODE,T2.CARD_NAME,T2.SUM_DOCTOTAL,T2.INV_LIST,T3.COLUMN_1,T3.COLUMN_2,....,T3.COLUMN_N

FROM

(

SELECT T0.CARD_CODE,T0.CARD_NAME,T0.SUM_DOCTOTAL,STRING_AGG(T1.INV,',') AS INV_LIST

FROM

  (

    SELECT CARD_CODE,CARD_NAME,SUM(DOCTOTAL) AS SUM_DOCTOTAL

    FROM <Schema_name>.CARD_MASTER

    GROUP BY CARD_CODE,CARD_NAME

  ) T0

INNER JOIN

  <Schema_name>.CARD_MASTER T1

ON

  T0.CARD_CODE = T1.CARD_CODE

GROUP BY T0.CARD_CODE,T0.CARD_NAME,T0.SUM_DOCTOTAL

) T2

INNER JOIN

  <Schema_name>.CARD_MASTER T3

ON

  T2.CARD_CODE = T3.CARD_CODE;

Regards,

Sunny

[former_member230865 · Accepted solution]

Thank you Sunny!

[former_member226419]

What o/p should come for 'C000001'?

[Former Member]

Hi Sumeet,

Check the query result for Card Code 4.

You may use COALESCE function to convert NULL to a Default value for the COLUMN. I have used 0 as the defualt value (check row 4 and column INV_LIST1 in the screenshot).

Regards,

Sunny

[former_member230865]

Hi Sunny!

What if there are duplicate entry in column INV?

TNX,

Norman

[Former Member]

Hi Norman,

Below is the sample data and the query output.

Regards,

Sunny

[former_member230865]

Hi Sunny!

Another question,

on the screenshot, sum the doctotal base on cardname,cardcode,docdate and counterref as list.