Skip to Content
Based on SAP HANA SPS 11

Update 1.6.2017

Retesting the APPLY_FILTER() approach on rev. 112.07 showed that it is now possible to simply hand APPLY_FILTER() an IN condition with a list of integers.
This approach for the workaround of the general limitation on multiple input parameter values for scripted calculation views is also shown in SAP note  “2315085 – Query with Multi-Value Parameter on Scripted Calculation View Fails with Incorrect Syntax Error.

Therefore, if you are using a current version of SAP HANA and still develop/use scripted calculation views, it is not recommended to use the CE-function approach I explained in this blog post. The limitation that this approach worked around has been fixed in SAP HANA.


Dear readers

there is a long-standing modelling problem with SAP HANA calculation views:

Using multiple input parameters to filter data similar to the SQL IN predicate.

This discussion Handling multi value input parameters can be taken as a comprehensive example.

It seems so straight forward at first and so practical.

Once the input parameter is defined the data preview tool built into SAP HANA Studio or your reporting client of choice can read the meta data for it and present the user with a nice UI dialog to specify values.

Something as fancy as this:

input par.png

Now, the way that this works is rather counter intuitive.

For graphical calculation views, there are a couple of nicely written blog posts available, like Using Multiple Values in Input parameter for filtering in Graphical Calculation View but it seems that scripted calculation views did simply not want to be as flexible.

For those, rather clunky (and not very well performing) solutions had to be built to make it possible at all, (see SAP HANA: Handling Dynamic Select Column List and Multiple values in input parameter or How to process and use multi-value input parameter in a scripted view in HANA)

Either the solution involved dynamic SQL or some form of parameter string mangling with loops and pseudo-dynamic temporary result set constructs.

Other approaches proposed to avoid the problem altogether and use multiple parameters (instead of one multi-valued parameter).

Developer arrogance driving solution finding…

The last time I read one of those discussions (yesterday) I thought:

 

“This cannot be the right solution. There must be some easier way to do it!”

So arrogance got the better of me – HA! It cannot be that difficult. (It’s so cheesy that for once Comic Sans is a fitting choice).

I dare to guess that nearly every developer had that feeling every now and then (if not, I would have a hard time finding a good explanation for so many drastically underestimated development efforts…)

Attacking the problem

My first impulse was to use the APPLY_FILTER() function, but I soon learned what many others probably discovered before: it doesn’t solve the problem.

The reason for that is the way APPLY_FILTER() works.

It takes the table variable and your filter string and constructs a new SQL statement.

For example, if your table variable is called vfact and your input parameter selection was 1, 2 and 5 your scripted calculation view could look like this:

/********* Begin Procedure Script ************/
BEGIN
  vfact = select * from fact;
  declare vfiltD10 nvarchar(50); -- this is a temp variable to construct the filter condition
  vfiltD10 = ' "DIM10" IN ( ' || :IP_DIM10 || ' )';
  var_out = APPLY_FILTER (:vfact, :vfiltD10);
END /********* End Procedure Script ************/

This compiles fine and if you try to run it with some parameters you are greeted with a surprise:

SELECT
     "DIM10", "DIM100", "DIM1000", "DIM1000000",
     "KF1", "KF2"
FROM "_SYS_BIC"."devTest/MULTIIP"
        ('PLACEHOLDER' = ('$$IP_DIM10$$','1,3,6')) ;

Could not execute ‘SELECT “DIM10”, “DIM100”, “DIM1000”, “DIM1000000”, “KF1”, “KF2” FROM “_SYS_BIC”.”devTest/MULTIIP” …’ in 373 ms 962 µs .

SAP DBTech JDBC: [2048]: column store error: search table error:  [2620] “_SYS_BIC”.”devTest/MULTIIP/proc”: [130] (range 2) InternalFatal exception: not a valid number string ‘1,3,6’

Not only is this error annoying, but it’s FATAL… shudder!

After some investigation, I found out that the input parameter not only provides the digits and the separating commas but also the enclosing single-quotes.

Nothing easier than getting rid of those:

  vfiltD10 = ' "DIM10" IN ( ' || replace (:IP_DIM10 , char(39), '')  || ' )';

With this, the single-quotes get easily removed (39 is the ASCII value for the single quotes and the CHAR function returns the character for the provided ASCII code – this just makes it easier to handle the double-triple-whatever-quotation syntax required when the single-quote character should be put into a string).

Of course, seeing that we have not yet reached the end of this blog post, you already know: that wasn’t the solution.

The problem here was not only the quotation marks but also that  SAP HANA does not parse the string for the input parameter value. The result for the filter variable is that we do not get the condition

  actual condition          ===> syntax structure

  “DIM10” IN ( 1, 3, 6)     ===> X IN ( c1, c2, c3)

but

  “DIM10” IN ( >’1, 3, 6′<) ===> X IN ( c1 )

So even when we remove the quotation marks, we still end up with just one value (I enclosed this single value in >’ ‘< for easier distinction).

Interlude

The different syntax structures pointed out above are easily overlooked also in standard SQL. Often developers do not fully realise that an IN condition with 3 parameters is structurally different from an IN condition with 2 or 4 parameters.

Whenever the number fo parameters of the IN condition changes, the statement is effectively a new statement to the database, requiring new parsing and optimisation and also allocating its own space in the shared SQL cache.

This is another detail that ABAP developers do not need to worry about, since the

SAP NetWeaver database interface gracefully splits up IN-lists into equal chunks and recombines the result set automatically. See this ancient piece SAP Support case “FOR ALL ENTRIES disaster” for more details.

One approach to avoid this issue can be to use temporary tables instead of the IN condition. Especially when parsing/query optimisation is taking a long time for your application, this might be an approach worthwhile to implement.

Back to the main topic though!

So, the “obvious” approach of using APPLY_FILTER() does not help in this case.

Is it possible that it is just not possible to take multiple input parameter values into an IN list? But graphical calculation views can do it – and rather easy.

And in this observation laid the key for the solution. What is different between graphical and scripted calculation views?

Right, graphical calculation views do not produce SQL for the boxes we draw up.

Technically speaking it replaces them with Plan Operators – very much similar to the abandoned CE_-functions.

Do you see where this is heading?

Yes, indeed. The solution I found works with CE_-functions.

Oh, how very heretic!

May the performance gods get angry with me for making the SAP HANA execution switch engines…

But first, let’s look at the solution, shall we?

/********* Begin Procedure Script ************/
BEGIN
     vfact = select * from fact;
  var_out = CE_PROJECTION(:vfact,
                     [ "DIM10", "DIM100", "DIM1000", "DIM1000000"
                     , "KF1", "KF2" ],
                      'IN ("DIM10", $$IP_DIM10$$)');
END /********* End Procedure Script ************/

Easy to see, this approach mimics the filter approach for graphical calculation views.

To not over complicate things I only used the CE_PROJECTION function for the filter part – everything else is still in efficient, familiar SQL.

Important to note is that this works only, when the input parameter is referenced with the $$<name>$$ format.

Also important to recall is that the complete filter expression needs to be provided as one string enclosed in single quotation marks ( ‘ <filter expression goes here> ‘ ).

OK!“, you may say, “this works, but now you broke the holy rule of CE_-functions damnation. The performance of this surely is way worse due to the implicit engine change!

Well, let’s have a look into this!

First the explain plan for the SQL based statement:

SELECT
     "DIM10","DIM100","DIM1000", "DIM1000000",
     "KF1","KF2"
FROM FACT
where DIM10 IN (1,3,6) ;

OPERATOR_NAME   OPERATOR_DETAILS                                         EXEC_ENGINE SUBTREE_COST

COLUMN SEARCH   FACT.DIM10, FACT.DIM100, FACT.DIM1000, FACT.DIM1000000,  COLUMN       1.645529062

                FACT.KF1, FACT.KF2                                                  

                (LATE MATERIALIZATION, OLTP SEARCH, ENUM_BY: CS_TABLE)              

  COLUMN TABLE  FILTER CONDITION:                                                   

                (ITAB_IN (DIM10))                                                   

                FACT.DIM10 = 1 OR FACT.DIM10 = 3 OR FACT.DIM10 = 6       COLUMN    

Now the scripted calculation view version:

SELECT
     "DIM10","DIM100","DIM1000", "DIM1000000",
     "KF1","KF2"
FROM "_SYS_BIC"."devTest/MULTIIP"
        ('PLACEHOLDER' = ('$$IP_DIM10$$','1,3,6')) ;

OPERATOR_NAME   OPERATOR_DETAILS                                         EXEC_ENGINE SUBTREE_COST

COLUMN SEARCH   FACT.DIM10, FACT.DIM100, FACT.DIM1000, FACT.DIM1000000,  COLUMN       1.645529062

                FACT.KF1, FACT.KF2                                                  

                (LATE MATERIALIZATION, OLTP SEARCH, ENUM_BY: CS_TABLE)              

  COLUMN TABLE  FILTER CONDITION:                                                   

                (ITAB_IN (DIM10))                                                   

                FACT.DIM10 = 1 OR FACT.DIM10 = 3 OR FACT.DIM10 = 6       COLUMN     

See any difference?

No?

That’s right, there is none. And yes, further investigation with PlanViz confirmed this.

SAP HANA tries to transform graphical calculation views and CE_-functions internally to SQL equivalents so that the SQL optimizer can be leveraged. This does not always work since the CE_-function are not always easy to map to a SQL equivalent, but a simple projection with a filter works just fine.

Now there you have it.

Efficient and nearly elegant IN condition filtering based on multiple input parameters.

There you go, now you know.

Have a great weekend everyone!

Lars

To report this post you need to login first.

13 Comments

You must be Logged on to comment or reply to a post.

    1. Patrick Bachmann

      Lars great blog as always!  Somehow I missed this and it was my protege who pointed it out to me.  We will surely try this approach soon.  I particularly like the mention of CE functions damnation. LOL.

      -Patrick

      (0) 
  1. Krishna Tangudu

    Thanks for sharing Lars

    A quick question, isn’t it better from SP11 to not to use input parameters for filtering multiple values.

    if the only reason for sending parameters is for performance ( apart from the functional requirement ) ,I noticed that even if we don’t send the multiple value parameters and apply the filter condition directly on the CV, i still see the same kind of plan being executed.

    Something like below:

    /wp-content/uploads/2016/07/36_993926.png

    So isn’t directly applying the filter conditions on the CV is better?

    Regards,

    Krishna Tangudu

    (0) 
    1. Lars Breddemann Post author

      Hi Krishna

      good question!

      My answer has two parts.

      1. From a performance point of view, I expect both approaches to be equal, if and when the calculation view can be transformed into a SQL equivalent plan. This should lead to the same degree of filter push-down.

      So, performance wise, both options should be on par.

      2. More important (to me at least), using parameters just for filtering purposes makes the code unnecessarily complex and difficult to read and maintain.

      So, I would try to stick to filtering via the WHERE clause whenever possible.

      Cheers,

      Lars

      (0) 
  2. Renjith E P

    Hello Lars,

    One Doubt.
    When we apply a filter as “where clause” on a complex calculation view with many inner join nodes and many left outer join nodes, Does the filter push down always happen ?

    Thanks in advance for your input.

    (0) 
    1. Lars Breddemann Post author

      In most cases, the filter-pushdown should work – regardless of the join type or number.
      There can be however scenarios where the pushdown cannot work – so it’s a good idea to always check for that.

      (0) 
  3. Jaret Funk

    Hi Lars,

     

    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

        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’);

    VAR_OUT = SELECT *

    FROM “_SYS_BIC”.“test.loan_details/LOAN_DETAILS”(PLACEHOLDER.“$$I_LANGUAGE$$” => ‘en’, PLACEHOLDER.“$$I_KEY_DATE$$” => ‘20170219’, PLACEHOLDER.“$$I_LOAN_NUMBER$$” => :loans );

    END /********* End Procedure Script ************/

     But when I run this I get the following error:

     

    argument type mismatch exception: argument type mismatch: Can’t use column expression as column view parameter: line 1 col 837 (at pos 836) (please check lines: 10)

     

    Any help would be appreciated.  Thanks

    (0) 
  4. Martin Donadio

    Hi Lars,

    Nice blogpost and approach for solving the filter in scripted calc. views with multi-input parameters.

    I must admit that I felt nostalgic when I saw the CE_ function. It took me to the early days of HANA back in 2011 :).

    I wanted to share that I found a way using the APPLY_FILTER function, but constructing the filter string as below
    declare fil varchar(nnnn) := ‘DIM IN(”’|| REPLACE(:IP,’,’,”’,”’) ||”’)’;

     

    performance wise I am not sure how good this solution is compared to the CE_ function approach.

    BR, Martin

    (1) 
    1. Lars Breddemann Post author

      Hey Martin

      I just revisited this blog and the original problem. My trusty SP11 instance is now on rev 112.07 and surprisingly enough this made the workaround I explained in this blog superfluous.

      APPLY_FILTER is now able to deal with multiple numeric conditions out of the box.

      The SAP note  “2315085 – Query with Multi-Value Parameter on Scripted Calculation View Fails with Incorrect Syntax Error actually showcases the APPLY_FILTER() approach that failed when I first tried it.

      (0) 
  5. Subhasish Haldar

    Hi Lars,

    So in case of graphical modelling, is it possible to do the same thing after modifying the filter expression from ‘IN’ to ‘APPLY_FILTER’ ?

     

     

    Thanks,

    Subhasish

     

    (0) 
    1. Lars Breddemann Post author

      Graphical calculation views never had the problem of not being able to handle multiple parameter values. Also, the APPLY_FILTER function is only available for scripted calculation views.

      (0) 

Leave a Reply