CE_CALC If function

[Former Member]

Hello guys,

I am getting a syntax error when using CE_CALC if function. Please let me know if you know what needs to be corrected.

1) The below code doesn't have any syntax error.

BEGIN

  SQLBSEG_VIEW = CE_OLAP_VIEW ( "_SYS_BIC"."Brad_Test/AN_BSAS", [ "BUKRS", "HKONT", "SHKZG", "GJAHR", "MONAT", "WRBTR"]);

  var_out = CE_PROJECTION ( :SQLBSEG_VIEW, [ "BUKRS", "HKONT", "GJAHR", "MONAT", "WRBTR" AS CREDIT]);

END

2) When I add CE_CALC if function, I am getting the syntax error. Any idea? I appreciate any comments on this issue.

BEGIN

  SQLBSEG_VIEW = CE_OLAP_VIEW ( "_SYS_BIC"."Brad_Test/AN_BSAS", [ "BUKRS", "HKONT", "SHKZG", "GJAHR", "MONAT", "WRBTR"]);

  var_out = CE_PROJECTION ( :SQLBSEG_VIEW, [ "BUKRS", "HKONT", "GJAHR", "MONAT", CE_CALC( 'if("SHKZG" = ''H'', "WRBTR" * -1, 0)') AS CREDIT]);

END

* Error message is as the following *

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 7 col 129 (at pos 513)nSet Schema DDL statement: set schema "SAPMU1"nType DDL: create type "_SYS_BIC"."Brad_Test/CA_BSIS_BSAS/proc/tabletype/VAR_OUT" as table ("BUKRS" NVARCHAR(4), "HKONT" NVARCHAR(10), "GJAHR" NVARCHAR(4), "MONAT" NVARCHAR(2), "CREDIT" DECIMAL(13,2))nProcedure DDL: create procedure "_SYS_BIC"."Brad_Test/CA_BSIS_BSAS/proc" ( OUT var_out "_SYS_BIC"."Brad_Test/CA_BSIS_BSAS/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as  n /********* Begin Procedure Script ************/ n BEGIN n t t n t SQLBSEG_VIEW = CE_OLAP_VIEW ( "_SYS_BIC"."Brad_Test/AN_BSAS", [ "BUKRS", "HKONT", "SHKZG", "GJAHR", "MONAT", "WRBTR"]);n t nt var_out = CE_PROJECTION ( :SQLBSEG_VIEW, [ "BUKRS", "HKONT", "GJAHR", "MONAT", CE_CALC( 'if("SHKZG" = ''H'', "WRBTR" * -1, 0)') AS CREDIT]);nt n t nnEND nn/********* End Procedure Script ************/n

Thanks in advance,

Brad Kim

[former_member210482 · Accepted solution]

Hi Brad,

It is because you have not specified the output datatype. Something like this. Here i assume the result as integer.

var_out = CE_PROJECTION ( :SQLBSEG_VIEW, [ "BUKRS", "HKONT", "GJAHR", "MONAT", CE_CALC( 'if("SHKZG" = 'H', ("WRBTR" * -1), 0)',int) AS "CREDIT"]);

Hope this helps.

Regards,

Safiyu

[Former Member · Accepted solution]

Hi Safiyu,

That was very helpful. It worked. Thanks.

Regards,

Brad

[former_member210482 · Accepted solution]

Hi Brad,

Your Welcome.

Cheers,

Safiyu

[Former Member]

Folks,

I just want to share additional findings for all of you who may face a similar problem. The following is my final version of script view. I had to add "SHKZG" and "WRBTR" to the final output even though what I need was CREDIT only. It seems that components of CREDIT should exist in the var_out for the calculation.

SQLBSEG_VIEW = CE_OLAP_VIEW ( "_SYS_BIC"."Brad_Test/AN_BSAS", [ "BUKRS", "HKONT", "SHKZG", "GJAHR", "MONAT", "WRBTR" ]);

var_out = CE_PROJECTION ( :SQLBSEG_VIEW, [ "BUKRS", "HKONT", "GJAHR", "MONAT", "SHKZG", CE_CALC('IF("SHKZG"=''H'', "WRBTR" * -1, 0)',decimal(13,2)) AS CREDIT, "WRBTR" ]);

Regards,

Brad

[Former Member]

Sytax is: CE_CALC(‘<expr>’,<result_type>) ;   ..like your defining an actual calculated column, so you need type...system haveto CAST expressions result into some type.

Example:

CE_CALC(‘”SALES” * :vat_rate’, decimal(10,2)) AS “SALES_VAT”

Hope it helps