SQL, CASE, GROUPING, RENAME

[Former Member]

Hello all,

I am creating a store procedure in HANA to be used to return an output which is at article (ARTNR), customer (KUNNR), kf (MENGE).

Based on the requirement I have to aggregate using if, then logic. From my SQL knowledge I have to use CASE with aggregation grouping logic. Thus I have written the below code which works fine.

SELECT ARTNR,

             CASE WHEN ITEM_SHIPTO_KUNNR IS NULL THEN HEADER_SHIPTO_KUNNR

             ELSE ITEM_SHIPTO_KUNNR

             END,

             SUM(MENGE) AS MENGE

FROM "_SYS_BIC"."development.danielle/AN_AFS_RET_OPEN_ORDERS"

WHERE AUART IN ('ZRO1','ZRO2','ZFR1')

GROUP BY ARTNR,  CASE WHEN ITEM_SHIPTO_KUNNR IS NULL THEN HEADER_SHIPTO_KUNNR ELSE ITEM_SHIPTO_KUNNR END;

The resulting query column for KUNN is named as the CASE logic and I need to change this to AS KUNNR. However I cant rename within the logic above because rules around grouping applying before the select statement. If I cant rename the column here how can I get the output in the format I need?

Any help on how to rename the case to AS KUNNR would be very appreciated. I have tried doing another external SELECT statement to rename the case logic but it doesnt seem to recognise it, however this is probably my syntax.

Kind regards,

Danielle

[former_member184768 · Accepted solution]

Hi,

Are you sure the following did not work for you:

SELECT ARTNR,

             (CASE WHEN ITEM_SHIPTO_KUNNR IS NULL THEN HEADER_SHIPTO_KUNNR

             ELSE ITEM_SHIPTO_KUNNR

             END) as "KUNNR",

             SUM(MENGE) AS MENGE

FROM "_SYS_BIC"."development.danielle/AN_AFS_RET_OPEN_ORDERS"

WHERE AUART IN ('ZRO1','ZRO2','ZFR1')

GROUP BY ARTNR,  CASE WHEN ITEM_SHIPTO_KUNNR IS NULL THEN HEADER_SHIPTO_KUNNR ELSE ITEM_SHIPTO_KUNNR END;

Similar code works fine for me. (I am on revision 38)

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravindra,

Yes that did work but the problem was then trying to group the case as KUNNR as group is applied technicall before the select. I managed to find away around this though with the following sub-select.

VAR_OUT = SELECT ARTNR, KUNNR, SUM(MENGE) AS MENGE

    FROM (SELECT ARTNR,

          SUM(MENGE) AS MENGE,

           CASE WHEN ITEM_SHIPTO_KUNNR IS Null

                THEN HEADER_SHIPTO_KUNNR

                ELSE ITEM_SHIPTO_KUNNR

           END AS KUNNR

    FROM "_SYS_BIC"."foundation.orders/AN_AFS_RET_OPEN_ORDERS"

          WHERE AUART IN ('ZRO1','ZRO2','ZFR1')

    GROUP BY ARTNR, ITEM_SHIPTO_KUNNR, HEADER_SHIPTO_KUNNR)

   GROUP BY ARTNR, KUNNR;

[former_member184768 · Accepted solution]

Hi Danielle,

Glad that the subquery worked for you, but just out of curiosity, how does it matter if you use the CASE statement in GROUP BY clause or the KUNNR alias. In my opinion, the requirement from the SELECT statement is more important. Or did I miss something here. 🙂

BTW, in your subquery option, first you are fetching the dataset and grouping by 4 attributes, then grouping the result set again for 2 attributes.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravi,

I have tested without the subquery and you are right it does not matter. I thought grouping by the case after renaming the column would not work but now I know the grouping happens before the select so this is not an issue in the result. The result named column is as KUNNR. The code is slightly longer as I need to include the full case statement in the group by and order by clause but the execution is quicker. I will use your example. Thank you for your help.

Kind regards,

Danielle

[henrique_pinto]

Wouldn't it be easier to create a calculated attribute KUNNR in your Analytic View?

It could something like:

if(isnull("ITEM_SHIPTO_KUNNR"), "HEADER_SHIPTO_KUNNR", "ITEM_SHIPTO_KUNNR"))

Then just use it in your GROUP BY clause, straightforwardly.

[Former Member]

Hi Henrique,

Yes that is another option but we avoided it as the requirement is very project specific and the model is a global model. I could add as a calculated attribute but then the calculation would be executed everytime the model used and this logic is only needed when the SP is run on an infrquent basis. But yes you are indeed right that would be another option definitely!

Kind regards,

Danielle

[henrique_pinto]

Well, you're adding a new field, not modifying anything. So, it should be ok for the other teams using this analytic view.