Skip to Content
0

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

Feb 25, 2017 at 03:57 AM

405

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Florian Pfeffer
Feb 25, 2017 at 05:39 AM
0

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).

Show 3 Share
10 |10000 characters needed characters left characters exceeded

I double checked my first query and it produces a string of this form '''124667'',''345678''' which I thought was correct and does work when I replace the loanstring variable with this specific string in the select statement above.

I also tried just filling the loanstring variable with a sungle loan number without any quotes at all and this works. It almost looks like the select statement above only expects loanstring to be a single loan number and if you put anything else in it an error will occur.

Have tou ever done anything like this before in a scripted view? Another thought I had was to use a dynamic sql select statement and construct the select statement as one big string.

0

The above example I gave was written in a sample scripted calc. view. The values in your parameter string must only be enclosed in single quotes which are not escaped. Cause the values are already in a string variable you do not have to escape it as it would be necessary when you write the string directly in the script.

And the above example works of course.

0

Thanks for your help. This worked.

0