cancel
Showing results for 
Search instead for 
Did you mean: 

Issue with If function in CE_CALC

Former Member
0 Kudos

I want to use a if function in CE_CALC ,seems the issue is with the second CE_CALC. It says attribute not found. But i have the attributes

FEB_MTD_BS in output structure and also CURMONTHAVG . Kindly help me

Statement#1 :

var_out = CE_PROJECTION(:v4,["ZGL_ACCOUNT","POPER","PRCTR","ZZPRODUCT","RBUKRS","RASSC","ZZTRTYP","CURTYPE","RYEAR","RVERS","SIGNDATA","CURMONTHBAL","CURMONTHAVG","YTODATEBAL","ZBAL_FLAG","BASE_ACCOUNT","ZREL_TYPE","H_SIGN","Text",

CE_CALC('"CURMONTHAVG"',DECIMAL(23,5)) AS "JAN_MTD_BS",

CE_CALC('if("POPER" = "01","CURMONTHAVG",0)',DECIMAL(23,5)) AS "FEB_MTD_BS"]);

Message :

    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: Attribute not found in column table: 01: line 13 col 1 (at pos 1376)nSet Schema DDL statement: set schema "GEEDASA"nType DDL: create type "_SYS_BIC"."Sandeep_Reddy.C_S.BlEND_LOGIC/YCV_SQL/proc/tabletype/VAR_OUT" as table ("ZGL_ACCOUNT" VARCHAR(10), "POPER" VARCHAR(10), "PRCTR" VARCHAR(10), "ZZPRODUCT" VARCHAR(10), "RBUKRS" VARCHAR(10), "RASSC" VARCHAR(10), "ZZTRTYP" VARCHAR(10), "CURTYPE" VARCHAR(2), "RYEAR" VARCHAR(10), "RVERS" VARCHAR(4), "SIGNDATA" DECIMAL(23,5), "CURMONTHBAL" DECIMAL(23,5), "CURMONTHAVG" DECIMAL(23,5), "YTODATEBAL" DECIMAL(23,5), "ZBAL_FLAG" VARCHAR(1), "BASE_ACCOUNT" VARCHAR(10), "ZREL_TYPE" VARCHAR(3), "H_SIGN" VARCHAR(10), "Text" NVARCHAR(21), "JAN_MTD_BS" DECIMAL(23,5), "FEB_MTD_BS" DECIMAL(23,5))nProcedure DDL: create procedure "_SYS_BIC"."Sandeep_Reddy.C_S.BlEND_LOGIC/YCV_SQL/proc" ( OUT var_out "_SYS_BIC"."Sandeep_Reddy.C_S.BlEND_LOGIC/YCV_SQL/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as  n /********* Begin Procedure Script ************/ n BEGIN  t  n t nv1 = CE_OLAP_VIEW("_SYS_BIC"."Sandeep_Reddy.C_S.BlEND_LOGIC/ZAV1_PLAN_BASE", ["POPER","PRCTR","ZZPRODUCT","RBUKRS","RASSC","ZZTRTYP","CURTYPE","RYEAR","RACCT","RVERS","SIGNDATA","CURMONTHBAL","CURMONTHAVG","YTODATEBAL"]);nv2 = CE_JOIN_VIEW("_SYS_BIC"."Sandeep_Reddy.C_S/ZGL_ACCOUNT_ATV", ["ZGL_ACCOUNT","ZBAL_FLAG","BASE_ACCOUNT","ZREL_TYPE","H_SIGN","Text"]);nv3 = CE_PROJECTION(:v1, ["RACCT" AS "ZGL_ACCOUNT", "POPER","PRCTR","ZZPRODUCT","RBUKRS","RASSC","ZZTRTYP","CURTYPE","RYEAR","RVERS","SIGNDATA","CURMONTHBAL","CURMONTHAVG","YTODATEBAL"]);nv4 = CE_LEFT_OUTER_JOIN( :v3, :v2, ["ZGL_ACCOUNT"], ["ZGL_ACCOUNT","POPER","PRCTR","ZZPRODUCT","RBUKRS","RASSC","ZZTRTYP","CURTYPE","RYEAR","RVERS","SIGNDATA","CURMONTHBAL","CURMONTHAVG","YTODATEBAL","ZBAL_FLAG","BASE_ACCOUNT","ZREL_TYPE","H_SIGN","Text"]);n nnvar_out = CE_PROJECTION(:v4,["ZGL_ACCOUNT","POPER","PRCTR","ZZPRODUCT","RBUKRS","RASSC","ZZTRTYP","CURTYPE","RYEAR","RVERS","SIGNDATA","CURMONTHBAL","CURMONTHAVG","YTODATEBAL","ZBAL_FLAG","BASE_ACCOUNT","ZREL_TYPE","H_SIGN","Text",nCE_CALC('"CURMONTHAVG"',DECIMAL(23,5)) AS "JAN_MTD_BS",nCE_CALC('if("POPER" = "01","CURMONTHAVG",0)',DECIMAL(23,5)) AS "FEB_MTD_BS"nn]);nnnEND /********* End Procedure Script ************/n

Accepted Solutions (0)

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

The cause for the error message is the wrong usage of double quotation marks:

CE_CALC('if("POPER" = "01","CURMONTHAVG",0)',DECIMAL(23,5)) AS "FEB_MTD_BS"])

                      ^  ^--- these indicate a column instead of a literal value.


In order to use a literal you need to use single quotation marks instead (').

And as you're in a quoted section already (the expression is embedded in two ' ' quotation marks, you'll have to escape them, by using a triple of single quotation marks:

CE_CALC('if("POPER" = '''01''',"CURMONTHAVG",0)',DECIMAL(23,5)) AS "FEB_MTD_BS"])

Seriously, this is so much easier and nicer to do in SQL... I have no idea why everyone keeps trying to code "low level".


- Lars