0

I am trying to do spit by dimension on an analytic view within a stored procedure, and I want to pass the measure on which I will apply the aggregation function dynamically. So I did the following:

 create procedure procHO (in  currentMeasure varchar(60))
 language sqlscript as
 begin
 data_tab = select MONTH_NAME as ID, sum(:currentMeasure) from
 _SYS_BIC."schema/analyticView" GROUP BY MONTH_NAME;
 end;

then I call the procedure this way:

call procHO("MARGIN");

but I am getting an error saying :

inconsistent datatype: only numeric type is available for aggregation function: line 5 col 38 (at pos 124) Could not execute 'call procHO("MARGIN")'

I also tried to do this using CE_ functions, here is what I did:

create procedure procHO1(in currentMeasure varchar(60))
language sqlscript as
begin
out1 = CE_OLAP_VIEW("schema/analyticView", ["MONTH_NAME",  
SUM(:currentMeasure)]);
end;

and I call the procedure this way:

call procHO1("MARGIN");

but still, I am getting an error saying:

feature not supported: line 5 col 70 (at pos 157)

Could not execute 'call procHO1("MARGIN")'

by the way, as a workaround, it is possible to create a dynamic SQL query that would resolve the issue, here is an example:

create procedure procHO2(in currentMeasure varchar(60))
language sqlscript as
begin

exec 'select  MONTH_NAME AS ID, sum('||:currentMeasure||') as SUM_MEASURE from
_SYS_BIC."schema/analyticView" GROUP BY MONTH_NAME';
end;

I call it this way

call procHO2('MARGIN');

but I don't want to create the SQL query dynamically since it's not recommended by SAP.

So what to do to pass an aggregated measure dynamically?

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Mohamed Ali JAMAOUI
  • 14,275
  • 14
  • 73
  • 117

1 Answers1

1

this is what the sample code from the documentation:

CREATE PROCEDURE my_proc_caller (IN in_client INT, IN in_currency INT, OUT outtab mytab_t) LANGUAGE SQLSCRIPT
BEGIN
outtab = SELECT * FROM CALC_VIEW (PLACEHOLDER."$$client$$" => :in_client , PLACEHOLDER."$$currency$$" => :in_currency );
END;

of course this works only on the latest release. Which revision are you running on?

ongis-nade
  • 74
  • 2
  • thanks, I will check this when have access to SAP HANA instance. I am using revision 67 – Mohamed Ali JAMAOUI Oct 29 '13 at 18:25
  • the example you are giving above is for a calculation view not an analytic view. In the case of an analytic view passing the measure as parameter doesn't seem to work. Could you try it with your recommendations and tell me if it's working I won't have access to hana for few days. Thanks – Mohamed Ali JAMAOUI Oct 29 '13 at 18:31