CE Function's Calculation View - Private Attributes & Measures retrival error

[Former Member]

Hi All,

I'm having issues in below script based calculation view query when i try to retrieve  Private Attritutes and Measures from Analytical view

If you look at the Below KUNNR and Matnr are coming from the attribute view with in the analytical view and net revenue is the calculated measure of the analytical view

VV010 is the  MEASURE of the analytical view - If i remove that than i activate the script  but if i don't i get the below message

/********* 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)  ]);

--CE_PROJECTION( :var_tab, ["KUNNR", "MATNR", "NETREVENUE",           CE_CALC('midstr("MATNR",2,5)', NVARCHAR(10) as "TEST") ]);

CE_PROJECTION( :var_tab

             , ["KUNNR"

              , "MATNR"

              , "NETREVENUE"

              , "VV010"

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

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

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

<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: Attribute not found in column table: VV010: line 13 col 17 (at pos 692) (ptime/query/checker/proc_check_cefunc.cc:511) nSet Schema DDL statement: set schema "ECC6"nType DDL: create type "_SYS_BIC"."student999/CVS_52/proc/tabletype/VAR_OUT" as table ("KUNNR" NVARCHAR(10), "MATNR" NVARCHAR(18), "NETREVENUE" DECIMAL(15,0), "VV010" DECIMAL(15,2), "TEST" NVARCHAR(10))nProcedure DDL: create procedure "_SYS_BIC"."student999/CVS_52/proc" ( OUT var_out "_SYS_BIC"."student999/CVS_52/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) ]);n--CE_PROJECTION( :var_tab, ["KUNNR", "MATNR", "NETREVENUE", CE_CALC('midstr("MATNR",2,5)', NVARCHAR(10) as "TEST") ]);nCE_PROJECTION( :var_tabn , ["KUNNR"n , "MATNR"n , "NETREVENUE"n , "VV010"n , CE_CALC('midstr("KUNNR",2,5)', NVARCHAR(10)) as "TEST" ]);n--out = CE_PROJECTION( :proj_tab, ["A", "B", "C", CE_CALC('midstr( "D" , 2, 5)', string) ]);nEND /********* End Procedure Script ************/nVersion: 71n</info>

[Former Member · Accepted solution]

Hi Venkat,

Two things:

1. You have changed the name of the field VV010 to GrossRevenue in your analytic view and hence this should be the field that you use in the scripted calc view. To check the field names of your analytic view - please double-click the corresponding column view that has been generated within _SYS_BIC schema.

2. Secondly, you are using the ce_olap_view to read the data from the analytic view for a set of fields. You do no have GrossRevenue(VV010) selected within there and hence your table variable var_tab will not have that field to be used in subsequent statements.

Thanks,

Anooj

[Former Member · Accepted solution]

Hi Anooj,

I have changed as you suggested but still i'm getting error

I'm learning Hana on my own, so please bear with my questions

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: GROSSREVENUE: line 5 col 82 (at pos 332) (ptime/query/checker/proc_check_cefunc.cc:511) nSet Schema DDL statement: set schema "ECC6"nType DDL: create type "_SYS_BIC"."student999/CVS_52/proc/tabletype/VAR_OUT" as table ("KUNNR" NVARCHAR(10), "MATNR" NVARCHAR(18), "NETREVENUE" DECIMAL(15,0), "GROSSREVENUE" DECIMAL(15,2), "TEST" NVARCHAR(10))nProcedure DDL: create procedure "_SYS_BIC"."student999/CVS_52/proc" ( OUT var_out "_SYS_BIC"."student999/CVS_52/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, GrossRevenue]); n var_out = n--CE_PROJECTION(:var_tab, [KUNNR, MATNR, NETREVENUE, CE_CALC('midstr( "TEST", 2, 5)', string) ]);n--CE_PROJECTION( :var_tab, ["KUNNR", "MATNR", "NETREVENUE", CE_CALC('midstr("MATNR",2,5)', NVARCHAR(10) as "TEST") ]);nCE_PROJECTION( :var_tabn , ["KUNNR"n , "MATNR"n , "NETREVENUE"n , "GrossRevenue"n , CE_CALC('midstr("KUNNR",2,5)', NVARCHAR(10)) as "TEST" ]);n--out = CE_PROJECTION( :proj_tab, ["A", "B", "C", CE_CALC('midstr( "D" , 2, 5)', string) ]);nEND /********* End Procedure Script ************/nVersion: 71n</info>

[Former Member · Accepted solution]

Hi Venkat,

No problem in asking questions, that is what this forum is for.

Go to _SYS_BIC schema and double-click on the view student99/CEA1_99 to see it's structure. See if you can find the field GrossRevenue there. I suggest you specifiy each field in your ce_olap_view call within quotes(").

Thanks,

Anooj

[former_member184768 · Accepted solution]

Hi Venkat,

Can I suggest one simple thing, to make everything consistent.

Can you please rename GrossRevenue to GROSSREVENUE and try using the upper case column name everywhere.

I think the VAR_OUT doesn't let you type column names in mixed / Lower case and the case for column name in Mixed case (in View definition) and UPPER CASE in VAR_OUT doesn't match.

Hence to keep everything simple, please try with everything in UPPERCASE.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Anooj and Ravi,

I did as Ravi Suggested ,went back to analytical view and changed everything to Caps rather than a combination of small and caps and then the calculation worked .

As Ravi Stated may be i should be just start using just CAPS.

Ravi,

FYI - I was just following a student exercise from TZ100 Hana Course Manual

Thank you both of you

[former_member186390]

Hi,

I have a similar Scenario; DIFF_IN_DAYS is a calculated column in AT_DSO_BSID. I am using Capital Letters everywhere but still I am getting the error "

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: DIFF_IN_DAYS: line 8 col 14 (at pos 648)nSet Schema"

My Query is;

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

BEGIN

  IT_SELECT = CE_JOIN_VIEW("_SYS_BIC"."ZHANA_BSOH.Model.WorkingCapital.DSO/AT_DSO_BSID",["BUKRS","GJAHR", "MONAT","DMBTR","DIFF_IN_DAYS"]);

  IT_FILTER = CE_PROJECTION(:IT_SELECT,["BUKRS","GJAHR","MONAT","DMBTR",

  CE_CALC('"DIFF_IN_DAYS" - :IN_DSO_DAYS'

  ,integer) AS "CALC_DIFF_DAYS"],

  '"BUKRS"= :IN_BUKRS AND "GJAHR" = :IN_FISCAL_YEAR AND "MONAT" <= :IN_FROM_PERIOD AND "MONAT" >= :IN_TO_PERIOD');

  IT_FINAL = CE_PROJECTION(:IT_FILTER,["BUKRS",CE_CALC('if("CALC_DIFF_DAYS" >= 0 AND "CALC_DIFF_DAYS" <= 30,"0-30d",if("CALC_DIFF_DAYS" >= 31 AND "CALC_DIFF_DAYS" <= 60,"31-60d",if("CALC_DIFF_DAYS" >= 61 AND "CALC_DIFF_DAYS" <= 90,"61-90d",if("CALC_DIFF_DAYS" >= 91 AND "CALC_DIFF_DAYS" <= 120,"91-120d","Not Due"))))',NVARCHAR(8)) AS "PERIOD", "DMBTR"]);

  IT_AGG = CE_AGGREGATION(:IT_FINAL,[SUM("DMBTR") AS "DMBTR"],["BUKRS","PERIOD"]);

  VAR_OUT = CE_PROJECTION(:IT_AGG,["BUKRS", "PERIOD", "DMBTR"]);

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

Please suggest.

Regards, Amit