sap hana script error

[Former Member]

HI All,

i am getting the following error when i am trying to activate my sql script.

Repository: Encountered an error in repository runtime extension;error preparing statement create procedure "_SYS_BIC"."iqm/SRINI_CALC_VIEW" (  out iqm_cateogry_sales "_SYS_BIC"."iqm/SRINI_CALC_VIEW/tabletype/iqm_cateogry_sales"  ) language SQLSCRIPT sql security definer reads sql data as n /********* Begin Procedure Script ************/ n BEGIN nnpo = CE_CALC_VIEW("_SYS_BIC"."iqm/IQM_ANALYTIC",["GP","SOLDFOR","CATEGORYNAME","MANUFACTURER"]);niqm_category_sales = select SUM(GP) AS GP,SUM(SOLDFOR) AS SOLDFOR,CATEGORYNAME,MANUFACTURER FROM :pontGROUP BY CATEGORYNAME,MANUFACTURER;n END; n /********* End Procedure Script ************/ for oid {tenant: , package: iqm, name: SRINI_CALC_VIEW, type: 2}

my query is

BEGIN

po = CE_CALC_VIEW("_SYS_BIC"."iqm/IQM_ANALYTIC",["GP","SOLDFOR","CATEGORYNAME","MANUFACTURER"]);

iqm_category_sales = select SUM(GP) AS GP,SUM(SOLDFOR) AS SOLDFOR,CATEGORYNAME,MANUFACTURER FROM :po

          GROUP BY CATEGORYNAME,MANUFACTURER;

END;

[Former Member · Accepted solution]

Hi Srinivasa,

Just looking at the name of your view - iqm/IQM_ANALYTIC, it appears that it is an analytic view. Is that right? If so, you should be using CE_OLAP_VIEW instead of CE_CALC_VIEW. The latter is only to be used with calculation views and former with analytic views.

Also, unrelated to the error you are getting - from a performance point of view SAP have adviced not to use a mix of CE functions and SQL within the same procedure/calc view. You have to either stick to CE functions (in your e.g. the second SQL could be replaced with a combination of CE_PROJECTION and CE_CALC i suppose) or do it all using SQL for better performance.

Thanks,

Anooj

[Former Member · Accepted solution]

HI Anooj,

you are right is an analytic view.

if you dont mind can you give me a sample how can i achieve this please.

[Former Member · Accepted solution]

Hi Srinivasa,

Looking closer at your code, I see that you are using the select statement to just calculate the sum of the measures isn't it. In that case, you simply need the following as your code.

iqm_cateogry_sales = CE_OLAP_VIEW("_SYS_BIC"."iqm/IQM_ANALYTIC",["GP","SOLDFOR","CATEGORYNAME","MANUFACTURER"]);

I have just replaced CE_CALC_VIEW to CE_OLAP_VIEW. If GP and SOLDFOR have been defined as measures in your analytic view, CE_OLAP_VIEW will automatically aggregate the measures and add the group by clause for the dimensions you have selected. Hence, you don't need your second select statement at all.

Thanks,

Anooj

[Former Member · Accepted solution]

Thanks a lot for your help Anooj.

sorry one last thing how do i call this procedure. i activated it did activated how do i execute it now in sql.

[Former Member · Accepted solution]

I thought what you created was a scripted calculation view. If so, you should be able to see it under Content-><your package>->Calculation Views. You can do a data preview on it to see the data.

If it was a stored procedure that you created, you call it from SQL like:

Call <procedure_name> (?);

Thanks,

Anooj

[Former Member · Accepted solution]

why do we get 2 result setout

one with fully result set and other one as 1 out(?).

what is the second result set.

thanks again for your help Anoop