Issue with Calculated Column on Analytical View with Projection

[Former Member]

Hi,

I'm trying to create a project over analytical view and trying to create a calculated column TEST

Created an Output Parameter TEST NVARCHAR 10

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

BEGIN

var_tab = ce_olap_view("_SYS_BIC"."student99/CEA1_99",[KUNNR, MATNR, NETREVENUE]);

var_out =

CE_PROJECTION( :var_tab, ["KUNNR", "MATNR", "NETREVENUE",           CE_CALC('midstr("TEST",2,5)', string) ]);

--out = CE_PROJECTION( :proj_tab,  ["A", "B", "C",   CE_CALC('midstr( "D"   , 2, 5)', string)  ]);

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

Error

<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 107 (at pos 551) (qp_gram.y:31564) nSet Schema DDL statement: set schema "ECC6"nType DDL: create type "_SYS_BIC"."student999/CVS_5/proc/tabletype/VAR_OUT" as table ("KUNNR" NVARCHAR(10), "MATNR" NVARCHAR(18), "NETREVENUE" DECIMAL(15,0), "TEST" NVARCHAR(10))nProcedure DDL: create procedure "_SYS_BIC"."student999/CVS_5/proc" ( OUT var_out "_SYS_BIC"."student999/CVS_5/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as n /********* Begin Procedure Script ************/ n BEGIN n nvar_tab = ce_olap_view("_SYS_BIC"."student99/CEA1_99",[KUNNR, MATNR, NETREVENUE]); n var_out = n--CE_PROJECTION(:var_tab, [KUNNR, MATNR, NETREVENUE, CE_CALC('midstr( "TEST", 2, 5)', string) ]);nCE_PROJECTION( :var_tab, ["KUNNR", "MATNR", "NETREVENUE", CE_CALC('midstr("TEST",2,5)', string) ]);n--out = CE_PROJECTION( :proj_tab, ["A", "B", "C", CE_CALC('midstr( "D" , 2, 5)', string) ]);nEND /********* End Procedure Script ************/nVersion: 71n</info>

Thanks in advance

[former_member184768 · Accepted solution]

Can you please replace the CE_CALC function line with the following:

CE_PROJECTION( :var_tab

             , ["KUNNR"

              , "MATNR"

              , "NETREVENUE"

              , CE_CALC('midstr("KUNNR",2,5)', NVARCHAR(10)) as "TEST" ]);

One point, you may want to change the KUNNR in midstr with the column from which you need to select the midstr value. TEST cannot be used in midstr as it doesn't occur in var_tab.

Regards,

Ravi

[Former Member · Accepted solution]

Thanks Ravi

It worked