Skip to Content

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    Posted on Jun 10, 2014 at 04:52 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 10, 2014 at 12:44 PM

    Hi Patrick,

    1) Have a look on the below blog , Even i tried the same so eventually went for this approach.

    SAP HANA: Handling Dynamic Select Column List and Multiple values in input parameter

    Graphical approach works but with the projection:

    Using Multiple Values in Input parameter for filtering in Graphical Calculation View

    2) Also try APPLY_FILTER as mentioned above by sagar.

    Regards,

    Krishna Tangudu

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Patrick,

      Please note that APPLY_FILTER also has some disadvantages similar to dynamic SQL (documented in SQLScript guide) since where predicate criteria is applied dynamically.

      The query plan can be different when your calc view is embedded in another SQLScript and final SQL that is generated may be looking very different when compared to just executing called view from SQL editor.

      I would suggest to check following:

      1. Check using plan visualization if the APPLY_FILTER criteria is getting pushed down to called views foundation data tables.

      2. I have also seen in past that generating SQL plan cache was taking lot of time when APPLY_FILTER was used and subsequent executions were faster. Did you check if this is your problem?

      However 1 min seems too long and the underlying problem may be due to missing filter pushdown to data foundation table in called views.

      Thanks, Sagar

  • author's profile photo Former Member
    Former Member
    Posted on Jun 13, 2014 at 08:17 PM

    Hi Patrick,

    APPLY_FILTER will actually get all the records and applies the filter on the result set. It is bound to be slow.

    Instead if you could rewrite the code using CE functions then apply the filter on the projection directly then you will see vast performance improvement.

    lt_out = select kunnr,name1 from kna1;

    var_out = APPLY_FILTER(:lt_out,:IN_KUNNR);

    Regards

    PK

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.