Getting Error while creating Calculation view.

[former_member16553]

Hi Expert,

I was trying to create one simple calculation view using SQL script but getting below error.

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

BEGIN

create type var_out1 as table (ebeln varchar(10),menge decimal);

        var_out  = CE_COLUMN_TABLE ("SAPEH2"."EKKO",[EBELN,AEDAT] );

        var_out1 = CE_COLUMN_TABLE ("SAPEH2"."EKPO",[EBELN,MENGE] );

        var_out2 - CE_UNION_ALL (:var_out1,:var_out2);

END

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

I thought , i might need to declare a table type for output table var_out1 and var_out2. I tried by declaring table like below couldn't get any success.

create type var_out1 as table (ebeln varchar(10),menge decimal);

create type var_out2 as table (ebeln varchar(10), aedat DATE ,menge decimal);

error:

Message :

     Internal deployment of object failed;Repository: Encountered an error in repository runtime extension;Internal Error:Deploy Calculation View: SQL: sql syntax error: CREATE TYPE is not allowed in SQLScript: line 5 col 2 (at pos 277)nSet Schema DDL statement: set schema "SAPEH2"nType DDL: create type "_SYS_BIC"."pkg-sapuser03.test_giri/CA_SALES1/proc/tabletype/VAR_OUT" as table ("EBELN" VARCHAR(10), "AEDAT" DATE, "MENGE" DECIMAL(13,3))nProcedure DDL: create procedure "_SYS_BIC"."pkg-sapuser03.test_giri/CA_SALES1/proc" ( OUT var_out "_SYS_BIC"."pkg-sapuser03.test_giri/CA_SALES1/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as  n /********* Begin Procedure Script ***********/ n BEGIN n n create type var_out1 as table (ebeln varchar(10),menge decimal);n create type var_out2 as table (ebeln varchar(10), aedat DATE ,menge decimal);n n t   var_out  = CE_COLUMN_TABLE ("SAPEH2"."EKKO",[EBELN,AEDAT] );n t   var_out1 = CE_COLUMN_TABLE ("SAPEH2"."EKPO",[EBELN,MENGE] );n t   var_out2 - CE_UNION_ALL (:var_out1,:var_out2);nnEND /******** End Procedure Script ************/n

Could you please help me to understand the cause of this?

Thanks,

Girdhari

[lbreddemann]

When using the Information Modeler you don't need to write the CREATE TYPE... command yourself.

All you got to do is to simply specify the input/output structures of your procedure and the types will be generated automagically during activation.

- Lars

[former_member16553]

Hi Lars,

Thanks for the reply.

If i understood correctly , you are suggesting to create var_out1 and var_out2 in output ? If yes can you please guide me on how to do that? I tried but did not find anything suitable to declare table.

Thanks,

Girdhari

[lbreddemann]

How one can specify the input/output structures for scripted calculation views is explained in the SAP HANA Developer Guide.

It also comes integrated in your SAP HANA Studio (key 'F1' gets you there).

- Lars

[former_member16553]

Thanks Lars..

I will check and get back to you with result.

[former_member182302]

Are you trying to create more than 1 output parameter from CV ? or You are trying to create the table variables inside to be used?

Regards,

Krishna Tangudu