Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Handling multi value input parameters

Hi folks,

When I pass a SINGLE value to a parameter in my calc view it works nicely and I get very fast results;

SIMPLE CALC VIEW:

var_out  = select MAT.MENGE, MAT.BWART, MAT.MJAHR from "MySchema"."MSEG" MAT where MJAHR = '2005' and BWART = :MoveType;

CALL VIEW LIKE THIS:

SELECT TOP 10000 "BWART", sum("TOTALQUANTITY") AS "TOTALQUANTITY" FROM "_SYS_BIC"."myView" ('PLACEHOLDER' = ('$$MoveType$$', '101')) GROUP BY "BWART"

Now to try passing multiple values into single parameter I'm trying to call like this;

SELECT TOP 10000 "BWART", sum("TOTALQUANTITY") AS "TOTALQUANTITY" FROM "_SYS_BIC"."myView" ('PLACEHOLDER' = ('$$MoveType$$', '''101'',''104''')) GROUP BY "BWART"

BUT for this to work, I have to change the syntax in my calc view to something like this which performs horrendously;

REVISED CALC VIEW:

vartable = select MAT.MENGE, MAT.BWART, MAT.MJAHR from "MySchema"."MSEG" MAT where MJAHR = '2005';

var_out = CE_PROJECTION ( :vartable, ["MENGE" As TOTALQUANTITY, "BWART"], 'in("BWART", :MoveType)');

Notice there is now a projection in addition to the select statement.  This is because it's the only way I can get example from SQL script guide to work for multiple values.  I would expect to be able to do this more simply without projection like the following but it does not work;

REVISED CALC VIEW:

var_out  = select MAT.MENGE, MAT.BWART, MAT.MJAHR from "MySchema"."MSEG" MAT where MJAHR = '2005' and BWART IN (:MoveType);

I can not find any way to do this via the initial select statement, no matter how I arrange the single quotes in my calling SQL.  The only thing that works is via the projection and with the IN() syntax from the SQL guide example and I get very bad response.  My theory is that it is passing over the same data twice when done in this manner.  I'm wondering if anybody can get this to work without the additional projection step AND/OR are there any improvements to multi-value parameter support in upcoming REV8 release?

NOTE: I'm using HANA REV68 for these examples. Also I'm only selecting top 10000 as this is just a test that I want to apply to a much more complicated view if I ever get this to work.

Thanks,

-Patrick

Former Member
Former Member replied

Hi Patrick,

I think possible way to handle this is using the APPLY_FILTER technique. Check documentation in SQL Script guide.

This gives complete flexibility to use conditions like IN, BETWEEN ..etc.

I have also checked in Visualize plan that it does filter pushdown.

Example SQL Script code below where IN_KUNNR is a Input parameter

lt_out = select kunnr,name1 from kna1;

var_out = APPLY_FILTER(:lt_out,:IN_KUNNR);

You can call your view using

SELECT TOP 200 "KUNNR", "NAME1" FROM "_SYS_BIC"."Z_TEST" ('PLACEHOLDER' = ('$$IN_KUNNR$$', 'KUNNR IN (''0000000003'',''0000000020'' )'))

Hope this works for your use case.

Thanks,

Sagar

1 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question