Using multiple entries as Input Parameter in Sql Script

[former_member215561]

Hi,

I have a requirement to do a selection on a table to fetch MATNR realated information which is passed to the model as input parameter.

I have a declared an input parameter with multiple entries checked in my model and my query is as below.

select "MATNR","KNUMH" from "A600" where "MATNR" in (:P_Matnr);

P_Matnr => MAT1,MAT2,MAT3

But this query is not returning any values

[pfefferf]

Hello Deric,

please can you add some more information to understand your scenario. In the header you mentioned SQLScript. So did you create a scripted calculation view with an input parameter allowing multiple values? Can you share also how you consume the view (in an sql statement; executing data preview)?

Thx,

Florian

[former_member215561]

Hi Florian,

Yes you are correct. We are using a scripted calculation view having input parameter with multiple entries. We execute the data preview and the values were passed as in screen shot

The expected result should work as 'IN' operator in SQL query.

[kakshat]

Hi Deric,

Are you sure that the parentheses around p_matnr are required? I am wondering if they're making the system think that it's supposed to look into the variables contained in p_matnr.

Akshat

[former_member215561]

Yes Kumar,

Without parenthesis IN operator is giving syntax error

[vara_yarramsetty]

Hi Deric: Input parameter requires Placeholder.

select field1, filed2

from calcview ('PLACEHOLDER' = "INPUTPARAMTER1", 'VAL1, VAL2')

Refer to the following link for more info:-

http://scn.sap.com/community/hana-in-memory/blog/2013/10/04/input-parameters-and-variables-in-sap-ha...

[pfefferf]

Hello Deric,

the behavior of multi-value enabled parameters for scripted calculation views is that the values are transferred as one string in the parameter (e.g. "'00001', '00002'"). This value is interpreted as one argument by the in operator and therefore you get no match.

You can use the APPLY_FILTER construct to reach your goal, but I would not recommend it due to possible performance and security issues.

Here is a quick and dirty example:

Calling the view in preview mode with two values for the parameter produces the output. I added the PARA column with the input parameter value, so you can see how it looks like.

That this works, you have to consider that the length of the input parameter has to be increased. Otherwise the value would be cut of (e.g. if the parameter has a length of 10 and its length would not increased, the value of it would be "'030486001" in my case.

I would not recommend to use that approach due to the mentioned disadvantages. Try to find a solution using the graphical calc. view approach if possible (especially if it just a simple select as in your above example).

Best Regards,

Florian

[former_member215561]

Hi Florian,

Thanks for the solution, there is one more issue I face while implementing multiple value filter.

How I can pass an array of values to my model from UI5 application via xsodata. Is there a way to specify my input parameter  is of array type in my xsodata definition.

Thanks

Deric

[pfefferf]

Hi Deric,

there is not really a support for this. Options/Alternatives for that case were already discussed in post Multiple value input parameter in XS ODATA | SCN.

Regards,

Florian

[former_member182302]

Hi Deric,

You can use XS ODATA but you should also mind about the limitations of the URL length in ODATA while implementing the multiple value filters.

the alternate solution was to encapsulate the CV in the XSJS and passing the multiple values from UI as JSON format to the XSJS and thereby as a Table Type parameter to the Procedure ( If you are using CV, then this will not be working as CV cannot accept the table type input parameter )

Regards,

Krishna Tangudu