Calculating mapped input parameter

[Ibrahem]

Dear Experts,

I am using HANA Studio and I want to set the value of mapped input parameters (P_FiscalYear,

P_ComparisionFiscalYear) based on the value of (P_FiscalYear - 1). But the only option available is to set it as constant.

[pfefferf · Accepted solution]

It is not possible to add a mapping with an expression to e.g. reduce a fiscal year parameter by one year.

An option w/o changing the source view would be to create a table function as a kind of wrapper for the source view. In that table function the parameter can be adjusted (fiscal year reduced by one year in your case) and transferred to the source view. The table function can be uses as data source in the calling view (the parameter mapping can be done in that case w/o adjustment, cause the reduction of the value is done in the table function).

An example for such a table function can be as following (I just implemented a simple example with one parameter, but of course several parameters are possible):

FUNCTION "MISC"."test.misc.functions::getDataWithReducedFiscalYear" ( IP_Fiscal_Year nvarchar(4) ) 
	RETURNS table (ID integer, FISCAL_YEAR nvarchar(4), FISCAL_YEAR_PARAM nvarchar(4))
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER AS
BEGIN
  -- reduce original fiscal year parameter by one year
  declare reduced_fiscal_year nvarchar(4) := :IP_Fiscal_Year - 1;
  
  -- get data from source calc. view with reduced fiscal year parameter
  return select * from "test.misc.model::CV_test_misc_fiscal_year" (PLACEHOLDER."$$IP_Fiscal_Year$$" => :reduced_fiscal_year );
END;

Regards,
Florian