Percent_Rank() function in CE script Calculation View

[former_member186082]

Hi All,

I need help in achieving Percent_Rank() function in CE scripted calculation view. When I run this code in SQL console, it works. When I use the same in CE_CALC, I end up with syntax error. Below mentioned are the code snippets I used in SQL console and Calc View.

In SQL Console

SELECT PERCENT_RANK() OVER (ORDER BY "FIELD") FROM "SCHEMA"."TABLE";

This returns me the results. Similarly in calc view, I tried

BEGIN

     OP1 = CE_COLUMN_TABLE ("ECC"."MARA");

     var_out = CE_PROJECTION(:OP1,["MATERIAL",

                                                         CE_CALC('PERCENT_RANK() OVER(ORDER BY "MATERIAL")',DECIMAL(10,2)) AS MAT_RANK ] );

END;

Any ideas on this..??

Regards,
Chandu.

[Former Member · Accepted solution]

You can't do PERCENT_RANK() in CE_CALC. Instead you do this:

In SQL Console

SELECT PERCENT_RANK() OVER (ORDER BY "FIELD") FROM "SCHEMA"."TABLE";

This returns me the results. Similarly in calc view, I tried

BEGIN

     OP1 = CE_COLUMN_TABLE ("ECC"."MARA");

     var_temp = CE_PROJECTION(:OP1,["MATERIAL", "RANK_COLUMN");

    var_out = SELECT PERCENT_RANK(RANK_COLUMN) OVER (ORDER BY "FIELD") AS MAT_RANK FROM :var_temp;

END;

[former_member186082 · Accepted solution]

Thanks Ravindra & John for your replies.

Regards,
Chandra.

[former_member184768]

Hi Chandu,

I don't think that the window function like PERCENT_RANK() is supported in CE_CALC function.

You can check the F1 help in HANA Studio for CE_CALC and can check the supported functions.

Regards,

Ravi