Error Using CE_AGGREGATION

[Former Member]

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

BEGIN

var_tab = CE_COLUMN_TABLE("EFASHION_TUTORIAL"."SHOP_FACTS");           

  var_out =

    CE_AGGREGATION( (:var_tab, SUM(AMOUNT_SOLD) , [ARTICLE_ID, WEEK_ID]);

--  CE_AGGREGATION( (:var_tab, SUM(D)          , [A,           B, C  ]);

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

O/P

0   ARTICLE_ID   INT

1  WEEK_ID      INT

2  AMOUNT_SOLD  DECIMALS   34

Error Message

<info>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: sql syntax error: incorrect syntax near "(": line 8 col 21 (at pos 352) (qp_gram.y:31564) nSet Schema DDL statement: set schema "ECC6"nType DDL: create type "_SYS_BIC"."student999/CVS_43/proc/tabletype/VAR_OUT" as table ("ARTICLE_ID" INTEGER, "WEEK_ID" INTEGER, "AMOUNT_SOLD" DECIMAL(34,0))nProcedure DDL: create procedure "_SYS_BIC"."student999/CVS_43/proc" ( OUT var_out "_SYS_BIC"."student999/CVS_43/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as n /********* Begin Procedure Script ************/ n BEGIN nvar_tab = CE_COLUMN_TABLE("EFASHION_TUTORIAL"."SHOP_FACTS"); t n tn tn var_out =n CE_AGGREGATION( (:var_tab, SUM(AMOUNT_SOLD) , [ARTICLE_ID, WEEK_ID]);n-- CE_AGGREGATION( (:var_tab, SUM(D) , [A, B, C ]);n n-- var_out =n--CE_PROJECTION(:var_tab, [ARTICLE_ID,WEEK_ID,AMOUNT_SOLD], ' "WEEK_ID" = ''243'' AND "ARTICLE_ID" = ''171889'' ');nnnnEND /********* End Procedure Script ************/nVersion: 71n</info>

Thanks in Advance

[Former Member · Accepted solution]

Hi Venkat,

You have an extra open bracket after CE_AGGREGATION.

Can you try changing the statement to:

var_out = CE_AGGREGATION(:var_tab, [SUM(AMOUNT_SOLD)] , [ARTICLE_ID, WEEK_ID]);

Thanks,

Anooj

[Former Member · Accepted solution]

Hi Anoop,

Thanks a lot.

I scratched my head for long enough and was about to leave it and one last time checked email and saw your reply and it worked wonders.

Actually i copied the syntax from SAP HANA Script Guide and it was error there as well

One More Thing,

If i change the code in SQL Editor too many time i get an error which is posted below and if i create a new one with same code it works

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: cannot use duplicate name of function or procedure: student999/CVS_45/proc: line 1 col 29 (at pos 28) (ptime/query/checker/check_id.cc:111) nSet Schema DDL statement: set schema "ECC6"nType DDL: create type "_SYS_BIC"."student999/CVS_45/proc/tabletype/VAR_OUT" as table ("AMOUNT_SOLD" DECIMAL(34,0), "ARTICLE_ID" INTEGER, "WEEK_ID" INTEGER)nProcedure DDL: create procedure "_SYS_BIC"."student999/CVS_45/proc" ( OUT var_out "_SYS_BIC"."student999/CVS_45/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as n /********* Begin Procedure Script ************/ n BEGIN n t n t n t nvar_tab= CE_COLUMN_TABLE("EFASHION_TUTORIAL"."SHOP_FACTS"); nnvar_out = CE_AGGREGATION(:var_tab, [SUM(AMOUNT_SOLD)] , [ARTICLE_ID, WEEK_ID]);nn--var_out = CE_AGGREGATION(:var_tab, [SUM(AMOUNT_SOLD)] , [ARTICLE_ID, WEEK_ID]);nnEND /********* End Procedure Script ************/nVersion: 71n</info>

Anyhow awarded full points to You

[Former Member · Accepted solution]

Glad that worked for you.

The error message indicates that you are trying to create a procedure that already exists. I suggest you run the "drop procedure <proc_name>;" statement before-hand.

Thanks,

Anooj

[Former Member · Accepted solution]

Anoop,

So what you are saying this

the first statement should be

drop procedure <proc_name>;

This is what i tried but its giving me  asytax error

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

BEGIN

drop procedure "_SYS_BIC"."student999/CVS_2";

-- drop procedure <proc_n>;

            var_out =

CE_JOIN_VIEW( "_SYS_BIC"."student99/LOCATION_99",[KUNNR,REGIO,LAND1]);           

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

<info>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: sql syntax error: incorrect syntax near "procedure": line 6 col 7 (at pos 257) (qp_gram.y:31564) nSet Schema DDL statement: set schema "ECC6"nType DDL: create type "_SYS_BIC"."student999/CVS_2/proc/tabletype/VAR_OUT" as table ("KUNNR" NVARCHAR(10), "REGIO" NVARCHAR(3), "LAND1" NVARCHAR(3))nProcedure DDL: create procedure "_SYS_BIC"."student999/CVS_2/proc" ( OUT var_out "_SYS_BIC"."student999/CVS_2/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as n /********* Begin Procedure Script ************/ n BEGIN nn n drop procedure "_SYS_BIC"."student999/CVS_2";n-- drop procedure <proc_n>;n t var_out = n t nCE_JOIN_VIEW( "_SYS_BIC"."student99/LOCATION_99",[KUNNR,REGIO,LAND1]); t nnEND /********* End Procedure Script ************/nVersion: 71n</info>

[Former Member · Accepted solution]

No sorry I thought you were creating the procedure from the SQL editor as opposed to the user interface option in the HANA Studio. Since you are using the latter option, it should still let you change the procedure code once activated. Not sure why it throws that error. Suggest you delete the procedure and start from a clean slate. Once activated, make a change and try activating again to see if the error still occurs.

[former_member182114 · Accepted solution]

Hi Ventak,

Better you create new threads for new topics.

Anyhow, this issue on duplication when creating can happens due to internal problems (depending on revision it happens too many times, I faced a lot of this on rev35).

What Anoop suggested is drop the object on SQL Studio, not to put this drop inside your coding.

The procedure on package it's a metadata, and when you click on activate other objects will be created like procedure, table type and 3 other internal tables (extension _INT, TMP, RET) for each step you do inside your coding (select, ce_aggregate, ce_projection... among others).

So maybe the best option is to not figth agains it and create your procedure with other name and move forward.

Regards, Fernando Da Rós