Skip to Content

Calculating mapped input parameter

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.

capture.png (56.0 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Jan 30, 2017 at 07:42 AM

    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

    Add comment
    10|10000 characters needed characters exceeded