Skip to Content

In HANA scripted calculation view call to calc view with input parameters

I have a graphical calculation view with an multiple value input parameter for loan numbers that I need to call from a scripted calculation view in which I determine the list of loan numbers to pass into the input parameter on the calculation view. Is there any way to do this in a scripted calculation view.

What I have tried so far seems to give me an error. Here is my scripted calculation view:

/********* Begin Procedure Script ************/

BEGIN

declare loanstring varchar 3000;

loans = SELECT concat(concat( ””””, string_agg( “loanNumber”, ””’, ””’)), ””””)

FROM “_SYS_BIC”.“test.correspondence/ANNUAL_LOAN_STATEMENT_BP_LOANS_BY_FISCAL_YEAR_MONTH”(PLACEHOLDER.“$$I_FISCAL_MONTH$$” => ‘4’, PLACEHOLDER.“$$I_FISCAL_YEAR$$” => ‘2016’);

select * into :loanstring from :loans;

VAR_OUT = SELECT *

FROM “_SYS_BIC”.“test.loan_details/LOAN_DETAILS”(PLACEHOLDER.“$$I_LANGUAGE$$” => ‘en’, PLACEHOLDER.“$$I_KEY_DATE$$” => ‘20170219’, PLACEHOLDER.“$$I_LOAN_NUMBER$$” => :loanstring );

END /********* End Procedure Script ************/

But when I run this I get a column search error.

Any help would be appreciated. Thanks

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Feb 25, 2017 at 05:39 AM

    Your functionality which builds the parameter string does not really look correct. In the coding you show there are double quotes and double quotes/single quotes mixtures which are not valid. Maybe that is due to copy and paste into this post.

    The string containing the multiple parameters values must follow the following format in case of character based values: 'value 1', 'value 2', 'value 3' ...
    It also must be considered that any special characters (like single quotes), in the values you determine, must be escaped (but I do not assume that you have special characters in your loan numbers). It can look like following (also including escaping single quotes functionality):

    declare loanstring nvarchar(3000);
      
    select '''' || string_agg("loanNumber", ''', ''') || '''' into loanstring
    from 
    ( 
         select replace("loanNumber", '''', '\''') as "loanNumber"
         from <... your source ...>
    );
    

    Please consider also if your "loanstring" variable length of varchar 3000 is enough (it depends on the results you expect from your first query).

    Add comment
    10|10000 characters needed characters exceeded