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

[SergioG_TX]

Isuru,

have you tried parsing the input param as a date data type

TO_DATE(<VAR_1>)

TO_DATE(<VAR_2>)

instead of just using the :var object name ?

[isuruwarn]

Hi Sergio,

Thanks for your response. I tried this method, and it would result in the same error.

Regards,

Isuru

[SergioG_TX]

try this :

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

what is the error message ?

[isuruwarn]

Thanks Sergio. This resulted in the following error:

ERROR: com.sap.hana.di.function [8250009] Syntax error: "incorrect syntax near "to_date""

[SergioG_TX]

try again, there was a missing ( parenthesis .. i edited the response...