Calculation View error for CE_PROJECTION while applying Input Parameter

[Former Member]

Let say i am in starting stage of learning HANA. My question may look like silly but it is a big deal for me. So, please help me to understand where and what is wrong with my procedure. I really appreciate your help.

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

BEGIN

  sel1 = CE_COLUMN_TABLE("STAGING"."PARTIES",["PARTY_ID","PARTY_NUMBER","PARTY_NAME","PARTY_TYPE","VALIDATED_FLAG","COUNTRY","STATE","CITY"]);

sel2 = CE_COLUMN_TABLE("STAGING"."PARTY_SITES",["PARTY_ID","LOCATION_ID","PARTY_SITE_NUMBER"]);

  Cal1 = CE_JOIN(:sel1, :sel2, ["PARTY_ID"], ["PARTY_ID","PARTY_TYPE","VALIDATED_FLAG","COUNTRY","STATE","CITY","LOCATION_ID"]);

  var_out = CE_AGGREGATION(:Cal1, [count("PARTY_ID") as "PARTY_ID", '"COUNTRY" = :IN_CNT'])

END

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

Please note that :IN_CNT is a input parameter with following settings

Parameter type = DIRECT

Default Value = CONSTANT

Data Type = VARCHAR(60)

I am getting following error message:

Message :

  Internal deployment of object failed;Repository: Encountered an error in repository runtime extension;Internal Error:Deploy Calculation View: SQL: sql syntax error: incorrect syntax near ""COUNTRY" = :IN_CNT": line 7 col 70 (at pos 744)nSet Schema DDL statement: set schema "SYSTEM"nType DDL: create type "_SYS_BIC"."test/TEST/proc/tabletype/VAR_OUT" as table ("PARTY_ID" BIGINT, "COUNTRY" VARCHAR(60))nProcedure DDL: create procedure "_SYS_BIC"."test/TEST/proc" ( IN in_cnt VARCHAR(60),  OUT var_out "_SYS_BIC"."test/TEST/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as  n /********* Begin Procedure Script ************/ n BEGIN n t sel1 = CE_COLUMN_TABLE("STAGING"."PARTIES",["PARTY_ID","PARTY_NUMBER","PARTY_NAME","PARTY_TYPE","VALIDATED_FLAG","COUNTRY","STATE","CITY"]);n t sel2 = CE_COLUMN_TABLE("STAGING"."PARTY_SITES",["PARTY_ID","LOCATION_ID","PARTY_SITE_NUMBER"]);n t Cal1 = CE_JOIN(:sel1, :sel2, ["PARTY_ID"], ["PARTY_ID","PARTY_TYPE","VALIDATED_FLAG","COUNTRY","STATE","CITY","LOCATION_ID"]);n t var_out = CE_AGGREGATION(:Cal1, [count("PARTY_ID") as "PARTY_ID", '"COUNTRY" => :IN_CNT'])n /nnEND /********* End Procedure Script ************/n

[Former Member]

Hi,

I think you need to use one CE_PROJECTION and one CE_AGGREGATION instead of just one CE_AGGREGATION.

Best regards,

Wenjun

[Former Member]

Wenjun,

Thanks for your quick reply. I really appreciate. I did how you suggested. but still i am getting the error message. Can you please suggest where am i doing wrong.

Modified Script:

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

BEGIN

  sel1 = CE_COLUMN_TABLE("TABLE"."A",["PARTY_ID","PARTY_NUMBER","PARTY_NAME","PARTY_TYPE","VALIDATED_FLAG","COUNTRY","STATE","CITY"]);

  sel2 = CE_COLUMN_TABLE("TABLE"."B",["PARTY_ID","LOCATION_ID","PARTY_SITE_NUMBER"]);

  Cal1 = CE_JOIN(:sel1, :sel2, ["PARTY_ID"], ["PARTY_ID","PARTY_TYPE","VALIDATED_FLAG","COUNTRY","STATE","CITY","LOCATION_ID"]);

  Cal2 = CE_PROJECTION(:Cal1,["PARTY_ID","VALIDATED_FLAG","COUNTRY"],'"COUNTRY" = :IN_CNT');

  VAR_out = CE_AGGREGATION(:Cal2, [count("PARTY_ID") as "PARTY_ID","COUNTRY","VALIDATED_FLAG"],["VALIDATED_FLAG"]);

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

Error Message:

Message :

  Internal deployment of object failed;Repository: Encountered an error in repository runtime extension;Internal Error:Deploy Calculation View: SQL: sql syntax error: incorrect syntax near ",": line 8 col 78 (at pos 846)nSet Schema DDL statement: set schema "SYSTEM"nType DDL: create type "_SYS_BIC"."csc-content-test/CL_TEST/proc/tabletype/VAR_OUT" as table ("PARTY_ID" BIGINT, "COUNTRY" VARCHAR(60), "VALIDATED_FLAG" VARCHAR(1))nProcedure DDL: create procedure "_SYS_BIC"."csc-content-test/CL_TEST/proc" ( IN in_cnt VARCHAR(60),  OUT var_out "_SYS_BIC"."csc-content-test/CL_TEST/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as  n /********* Begin Procedure Script ************/ n BEGIN n t sel1 = CE_COLUMN_TABLE("CSC_STAGING"."CG1_HZ_PARTIES",["PARTY_ID","PARTY_NUMBER","PARTY_NAME","PARTY_TYPE","VALIDATED_FLAG","COUNTRY","STATE","CITY"]);n t sel2 = CE_COLUMN_TABLE("CSC_STAGING"."CG1_HZ_PARTY_SITES",["PARTY_ID","LOCATION_ID","PARTY_SITE_NUMBER"]);n t Cal1 = CE_JOIN(:sel1, :sel2, ["PARTY_ID"], ["PARTY_ID","PARTY_TYPE","VALIDATED_FLAG","COUNTRY","STATE","CITY","LOCATION_ID"]);n t Cal2 = CE_PROJECTION(:Cal1,["PARTY_ID","VALIDATED_FLAG","COUNTRY"],'"COUNTRY" = :IN_CNT');n t VAR_out = CE_AGGREGATION(:Cal2, [count("PARTY_ID") as "PARTY_ID","COUNTRY","VALIDATED_FLAG"],["VALIDATED_FLAG"]);nEND /********* End Procedure Script ************/n

[Former Member]

I guess i found the problem. I did modified the could as follow and it worked fine.

VAR_out = CE_AGGREGATION(:Cal2, [count("PARTY_ID")], ["COUNTRY", "VALIDATED_FLAG"]);

Again thanks for your help.

[Former Member]

Hi,

You're welcome. Sorry for the late reply. Yeah, you made it. The reason is very simple.

VAR_out = CE_AGGREGATION(:Cal2, [count("PARTY_ID") as "PARTY_ID","COUNTRY","VALIDATED_FLAG"],["VALIDATED_FLAG"]);

The above is incorrect since you need to group by "COUNTRY" which is correct in the below.

VAR_out = CE_AGGREGATION(:Cal2, [count("PARTY_ID")], ["COUNTRY", "VALIDATED_FLAG"]);

Best regards,

Wenjun

[Former Member]

Hi,

The syntax of CE_AGGREGATION is as follows.

CE_AGGREGATION (<var_table>, <aggregate_list> [, <group_columns>]);

You can also check this. CE_AGGREGATION - SAP HANA SQLScript Reference - SAP Library

Best regards,

Wenjun