Script based calc view throws feature not supported error

[Former Member]

Hi,

Here's the script.

SQLA_VIEW= CE_OLAP_VIEW

("_SYS_BIC"."e2sc-hana/AV_ORDERS_PROM",

[

"SUPPLIER_NAME","ITEM_NAME","ITEM_DESCRIPTION","CUST_SITE_NAME",

"PO_NUMBER", "PO_CREATION_DATE", "UNIT_PRICE", "PO_LINE_ITEM_ID",

AVG("CYCLE_TIME")  AS "AVG_CYCLE_TIME"

]

);

var_out = CE_PROJECTION(:SQLA_VIEW, ["ITEM_NAME","AVG_CYCLE_TIME"]);

Basic I have a calculated column defined called CYCLE_TIME in the Analytic view (AV_ORDERS_PROM) that does this: daysbetween("PO_CREATION_DATE","DATETIMESTAMP")

Once this is computed at an order/line/schedule level, I want to compute the average in the calc view.

I tried  SUM("CYCLE_TIME") / COUNT("CYCLE_TIME") and somehow the / operator was not liked by the engine.

Then I tried AVG function as above and it throws the following error:

Repository: Activation failed for at least one object;At least one runtime reported an error during activation. Please see CheckResults for details

• Activation is performed with activation mode Cascade one-phase.
• Internal deployment of object failed;Repository: Encountered an error in repository runtime extension;Internal Error:Deploy Calculation View: SQL: transaction rolled back by an internal error: feature not supportednSet Schema DDL statement: set schema "E2SC_HANA"nType DDL: create type "_SYS_BIC"."e2sc-hana/CV_OCT3/proc/tabletype/VAR_OUT" as table ("ITEM_NAME" VARCHAR(64), "AVG_CYCLE_TIME" DOUBLE)nProcedure DDL: create procedure "_SYS_BIC"."e2sc-hana/CV_OCT3/proc" ( OUT var_out "_SYS_BIC"."e2sc-hana/CV_OCT3/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as  n /********* Begin Procedure Script ************/ n BEGIN nnSQLA_VIEW= CE_OLAP_VIEW n("_SYS_BIC"."e2sc-hana/AV_ORDERS_PROM", n[n"SUPPLIER_NAME","ITEM_NAME","ITEM_DESCRIPTION","CUST_SITE_NAME",n"PO_NUMBER", "PO_CREATION_DATE", "UNIT_PRICE", "PO_LINE_ITEM_ID", n AVG("CYCLE_TIME")  AS "AVG_CYCLE_TIME"n]n);nn--var_out = CE_PROJECTION(:SQLA_VIEW, ["SUPPLIER_NAME","ITEM_NAME","CUST_SITE_NAME","AVG_CYCLE_TIME"]);nvar_out = CE_PROJECTION(:SQLA_VIEW, ["ITEM_NAME","AVG_CYCLE_TIME"]);nnnEND /********* End Procedure Script ************/nVersion: 77n

[former_member184768]

Hi Sudarshan,

Please try the following:

1. Ensure that the CYCLE_TIME is defined as the measure in your AV.
2. Since there is no AVG function directly available, you may have to calculate it with SUM / COUNT
3. Use only the CYCLE_TIME in the CE_OLAP_VIEW
4. Use CE_AGGREGATION to define the SUM and COUNT with ITEM_NAME as dim.
5. Define the var_out with CE_PROJECTION using CE_CALC something like

CE_CALC('"SUM_CYCLE_TIME" / "CNT_CYCLE_TIME"', INTEGER) AS "AVG_CYCLE_TIME"

I tried the above and it works for me.

Regards,

Ravi

[Former Member]

Hi Ravi,

Thanks for the response, here's my script. In output parameters I have defined all 4

AVG_CYCLE_TIME,ITEM_NAME,CNT_CYCLE_TIME,SUM_CYCLE_TIME.Tried several other combinations...

  /********* Begin Procedure Script ************/

BEGIN

SQLA_VIEW = CE_OLAP_VIEW

("_SYS_BIC"."e2sc-hana/AV_ORDERS_PROM", ["CYCLE_TIME","ITEM_NAME"]);

SQLB_VIEW = CE_AGGREGATION

(:SQLA_VIEW,

[SUM("CYCLE_TIME") AS "SUM_CYCLE_TIME", COUNT("CYCLE_TIME") AS "CNT_CYCLE_TIME"], ["ITEM_NAME"]

);

var_out= CE_PROJECTION

(:SQLB_VIEW,

["SUM_CYCLE_TIME","CNT_CYCLE_TIME",

CE_CALC('"SUM_CYCLE_TIME" / "CNT_CYCLE_TIME"', INTEGER) AS "AVG_CYCLE_TIME","ITEM_NAME"]);

END

/********* End Procedure Script ************/

This throws the following error

• Internal deployment of object failed;Repository: Encountered an error in repository runtime extension;Internal Error:Deploy Calculation View: SQL: transaction rolled back by an internal error: return type mismatch: Attribute name "AVG_CYCLE_TIME"  different from Attribute name: "SUM_CYCLE_TIME" : line 12 col 1 (at pos 516)nSet Schema DDL statement: set schema "E2SC_HANA"nType DDL: create type "_SYS_BIC"."e2sc-hana/CV_OCT3/proc/tabletype/VAR_OUT" as table ("AVG_CYCLE_TIME" INTEGER, "ITEM_NAME" VARCHAR(64), "SUM_CYCLE_TIME" DOUBLE, "CNT_CYCLE_TIME" SMALLINT)nProcedure DDL: create procedure "_SYS_BIC"."e2sc-hana/CV_OCT3/proc" ( OUT var_out "_SYS_BIC"."e2sc-hana/CV_OCT3/proc/tabletype/VAR_OUT" )

[Former Member]

Hi Ravi,

Ignore the above, the above error went way after I defined the output parameters in the same sequence as my var_out. However here's the issue we saw earlier this week. Notice from the screenshot avg is showing as sum and also the count is 1 instead of 3. Expected average across the 3 records for this particular item should be 11.333 isnt it?

Thanks,

Sudarshan.