Passing local params in hdbfunction as calc view input parameters

[isuruwarn]

Hi All,

We have a usecase where we need to call a Calculation View with parameters, from within an HDB Function. The problem is that we need to pass in some local variables defined within the hdbfunction. So inside the hdb function we have some code like:

lv_from_date nvarchar(10);
lv_to_date nvarchar(10);


lv_from_date := '2016-08-01';
lv_to_date := '2016-08-01';

select_kpi = 
  SELECT 
  date,
  SUM(kpi) AS kpi
  FROM "sap.sample::CVKF_BUSINESSLOGIC" (
  IP_STARTDATE => :lv_from_date,
  IP_ENDDATE => :lv_to_date
  )
  GROUP BY date;

When the above code is executed, it returns the following error:

Error: (dberror) column store error: "HDI_CONTAINER"."IPTEST": line 129 col 1 (at pos 1051): [2048] (range 3): column store error: search parameter error: [2018] A received argument has an invalid value;WITH PARAMETERS: ( 'IP_ENDDATE'='2016-08-22') not supported

Note - If the values are hardcoded within string literals as follows, the code executes without error:

select_kpi =
  SELECT
  date,
  SUM(kpi) AS kpi
  FROM "sap.sample.db::CVKF_BUSINESSLOGIC" (
    'PLACEHOLDER' = ( '$$IP_STARTDATE$$', '2016-08-01' ),
      'PLACEHOLDER' = ( '$$IP_ENDDATE$$', '2016-08-01' ),
  )
  GROUP BY date;

However, we need to use local variables. So does anyone know how we can pass in local variables, which are defined within the hdb function, as calc view input parameters? We are running on Hana SP12 (1.00.120.00.1462275491).

Thanks,

Isuru

[pfefferf · Accepted solution]

Hello Isuru,

why you are not using the syntax of your second example with the local variables?

select_kpi =  
  SELECT  
  date,  
  SUM(kpi) AS kpi  
  FROM "sap.sample.db::CVKF_BUSINESSLOGIC" (  
    PLACEHOLDER.'$$IP_STARTDATE$$' => :lv_from_date,  
    PLACEHOLDER.'$$IP_ENDDATE$$' => :lv_to_date)  
 )  
GROUP BY date;  

Regards,

Florian

[isuruwarn · Accepted solution]

Hi Florian,

Thanks for your reply. I tried these methods, but then it fails during creation, and does not compile. The first method will give the following error:

Could not execute 'CREATE FUNCTION "IPTEST"( ip_comparison_type nvarchar(10), ip_thing_type nvarchar(20) ) RETURNS ...'

Error: (dberror) sql syntax error: incorrect syntax near ":lv_from_date": line 80 col 41 (at pos 2348)

The second method will return the following error:

Could not execute 'CREATE FUNCTION "IPTEST"( ip_comparison_type nvarchar(10), ip_thing_type nvarchar(20) ) RETURNS ...'

Error: (dberror) sql syntax error: incorrect syntax near "=>": line 80 col 40 (at pos 2347)

Regards,

Isuru

[pfefferf · Accepted solution]

Can you change the single quotes enclosing the parameter names in double quotes. Single quotes are not allowed according to the syntax rules:

select_kpi =    
  SELECT    
  date,    
  SUM(kpi) AS kpi    
  FROM "sap.sample.db::CVKF_BUSINESSLOGIC" (    
    PLACEHOLDER."$$IP_STARTDATE$$" => :lv_from_date,       
  PLACEHOLDER."$$IP_ENDDATE$$" => :lv_to_date)    
)    
GROUP BY date;   

[isuruwarn · Accepted solution]

Hi Florian,

You were right. That worked! Thanks a lot for your help!

Regards,

Isuru