How to process and use multi-value input parameter in a scripted view in HANA

This write-up is to explain on how to use a multi-value input parameter directly in   a scripted view in HANA .

This is  a common requirement in different business development cases based on HANA modeling .

So rather than having a graphical projection on top of scripted view where we deal with the filter based on multi-value input parameter , which is a work-around to deal with this , here we are here going to see how we can directly process and code the multi-input parameter for data restrictions on select queries in scripted views.

This design is going to reduce the run-times .

This below one is a multi-value input parameter.

Challenge:

In scripted calculation views , we cannot directly use this kind of input parameter to restrict the data out from the select queries like follows:

SELECT * FROM <TABLE> WHERE MATNR IN :P_MATNR .

Reason :

The Multiple values of input parameter are assigned as a horizontal list of values ; each enclosed in single quotes , separated from each other by a comma symbol .

This can be noticed by writing a select query for the values of multi-value input parameter in a test scripted view like follows.

In this test view , we have a column MATNR of varchar[100] , P_MATNR is the multi value input parameter as shown above.

P_MATNR has to be assigned to a variable to see this data  as we are doing in below code.

We are going to execute this view for output with below values :

Output is like below:

Observations from the output above :

[1] Data of different input values is contained in a horizontal line.

[2] Each value is enclosed in single quotes

[3] Comma symbol is present between two values .

Comma is not seen after the last value

[4] Every data value is creating additional 3 characters [ One comma symbol and Two single quote symbols ]except the last data value .

The last data value doesn’t have a comma symbol after it .

[5] If a comma symbol is appended at the end of last data value , then we will have all of the different data values in a consistent data pattern like the below

This is done by the concat() function in sql as below .

This data processing step using concat() is going to help in further logic where each of the individual values of the multi-input parameter are extracted into an usable format to restrict the select queries in the script.

Now all data values including the last one have:

->Every data value is has additional 3 characters [ One comma symbol and Two single quote symbols ]

-> Total length of this output string = ( n + 3) * m

Where m = number of input values passed to the multi value input parameter;

             n = length of the input parameter value

Like for example here , if we pass three input values to the parameter P_MATNR , m = 3

and if the length of the MATNR column in system is 8 , then n = 8

So , overall string length = 33 as shown in below screen shot.

          

This data processing is going to help us when we further process this data into a readable format for the select queries .

In the logic below , we are going to transpose the horizontally available data into a columnar format where each data value of input parameter is shown in each row.

This will help further in utilizing the input parameter’s values for restricting data out of select query in the logic.

The code at line 13 in above defines the number of loop runs to be made next .

This is equal to the number of input parameters passed.

We took 11 here because input parameter is of length 8 and every input data value is made to have additional 3 characters as explained above .

So , this calculation at line 13 , will assign number of input values passed as value to variable J.

Code in lines 15 to 29 in the above screen shot  , will do below [a] and then [b]->

[a] derive the exact value of each input data value and assign it to the table variable

[b] then process the string of input parameter values to exclude the value that is assigned already to table value in [a].

This sequential steps  [a] and [b] will continue as a loop till all of the input parameter values are processed and assigned as individual values devoid of single quotes and comma symbols ; one per each row into the final output column.

So , final output is going to be like this when we pass below input:

Input :

Output :

Please find the attached text file for the above explained code .

Since the data of multi-value input parameter is processed into a columnar format of a  table variable , this can get used further to restrict data out of select queries on further business logic that is there in the code like :

var_x = SELECT * FROM <TABLEX> WHERE MATNR IN ( SELECT * FROM :v_matnr) ;

Here , v_matnr is the table variable which has data of multiple input values processed into different rows each  as explained above.

If a graphical view is being used , we can directly do this by IN() operator as follows :