Skip to Content

With SAP HANA 1.0 SPS6(Rev 60), we can now leverage the concept of dynamic filters.   There have been several requests for this type of functionality, since SAP does not recommend the use of dynamic SQL(EXEC statement) when developing SQLScript procedures.  We now have a new statement in SQLScript called APPLY_FILTER.  This statement accepts two parameters.  The first parameter is the dataset in which you want to apply the filter.  This dataset can be a database table, database view, HANA attribute or calculation view, or even an intermediate table variable.  The second parameter is of course the filter condition itself. This would be very similar syntax that you would use in the WHERE clause of a SELECT statement.   In the following example, I have a SQLScript procedure which simply reads data from the “Products” table and applies a filter which is passed as an input parameter to the procedure.  The result set then shows the filtered dataset.

CREATE PROCEDURE get_products_by_filter(

            IN im_filter_string VARCHAR(5000),

            out ex_products “SAP_HANA_EPM_DEMO”.“sap.hana.democontent.epm.data::products” )

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  READS SQL DATA AS

BEGIN

ex_products =

APPLY_FILTER(“SAP_HANA_EPM_DEMO”.“sap.hana.democontent.epm.data::products”,

                   :im_filter_string) ;

END;

/wp-content/uploads/2013/07/ppic1_238497.png

To report this post you need to login first.

35 Comments

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

  1. Nagaraj G

    Hi Rich,

    Thanks for your information.

    Can you please explain what is

    SAP_HANA_EPM_DEMO”.“sap.hana.democontent.epm.data::products”

    Regards,

    Nagaraj

    (0) 
    1. Rich Heilman Post author

      Sure that is the name of a database table.  “SAP_HANA_EPM_DEMO” is the schema. “sap.hana.democontent.epm.data” is the package in which the table is stored in the repository.  “products” is the name of the table.  This table was created using a .hdbtable file in the repository.

      Cheers,

      Rich Heilman

      (0) 
  2. varada santosh

    Hi Rich

      Thanks a lot for sharing on this new feature.

       Can we use the APPLY_FILTER feature as part of Scripted Calculation views also.Or it is only limited to Procedures.

    Regards

    Santosh

    (0) 
  3. Gairik Acharya

    Thanks Rich. This will help us to pass select-options values and to use them in the sqlscript as a filter .

    A quick question : We are using ERP on HANA ( ECC6.06 , SP00 ) . Is it possible to upgrade our HANADB to SPS06 without upgrading the ERP to SPS02/03 ?

    (0) 
  4. William Liu

    Hi Rich,

    thanks for your sharing, but i have one confused question on that new feature.

    as you know, in version previous SP06, hana donot recommend to use dynamic SQL statement for the consideration of performence. if you use “APPLY_FILTER” statement, we actually use dynamic SQL statement. what is the performence difference between previous dynamic sql expression (i mean “EXEC …” statement) and this new feature.

    thanks

    (0) 
    1. Rich Heilman Post author

      APPLY_FILTER statement does not use dynamic SQL and no association with EXEC statement, so there should be no related performance issues when using APPLY_FILTER.

      Cheers,

      Rich Heilman

      (0) 
  5. Jonathan Haun

    I have what seams like a simple queston. If I wanted to add a dynamic filter to a projection in a calculation view that was int(leftstr(“BUDAT”,4)) <= int(leftstr( now(), 4)) why would I get a type conversion error when activating the calculation view. BUDAT is a varchar(8) column in my analytic view. If I create a calculated column using the syntax from either side of the operator it parses. int(leftstr(“BUDAT”,4)) returns the numeric year and int(leftstr( now(), 4)) returns the current year.

    All I am trying to do is restrict the data so that only records from the current year (or less) are included. I also want it to be dynamic so I don’t have to edit the calc view every year. There is budget and forcat data in the AV for future dates, but I don’t want it in this calc view.

    (0) 
  6. Jerry Wang

    Hi Rich,

    Thank you for your sharing new feature, I have 2 questions for this feature.

    Q1, The filter dataset cannot be set analytic view, right? The first parameter is the dataset in which you want to apply the filter.  This dataset can be a database table, database view, HANA attribute or calculation view, or even an intermediate table variable.

    Q2, whether we can use this feature at anywhere not only procedure? such as XS engine, BO tools and so on.

    Thanks.

    Regards,

    Jerry

    (0) 
    1. Rich Heilman Post author

      1) Yes, I think that is why I specifically left out analytic view from that list. 

      2) This is a SQLScript feature, so you can only use it within a SQLscript procedure. Of course you can call a SQLScript procedure(which is using this feature) from for example an xsjs service.

      Cheers,

      Rich

      (0) 
  7. shishupalreddy ramreddy

    Hi Rich,

    Very use full information which I have been trying as part of the requirement . As this feature is made available from SPS 6 , waiting for the upgrade to SPS 6 to explore more on this front.

    Thank you Very Much ……

    Cheers,

    Shishupal.

    (0) 
    1. Rich Heilman Post author

      You are simply providing a dynamic WHERE clause, so basically you put those additional fields into your filter string, separated by AND or OR.

      Cheers,

      Rich

      (0) 
  8. Siva rama Krishna Pabbraju

    Hi Rich,

    This statement accepts two parameters……………………………..The second parameter is of course the filter condition itself.

               I tried a lot understand your example but unable to grasp anything. I have read your above statements plenty of times and could able to figure out the hidden secret and could achieve what I want.  Thanks Rich.

    🙂

    Thanks and regards,

    Siva.

    (0) 
  9. Hyuk Joo Lee

    Hi Rich

    Thank you for your beautiful post~.

    I’m struggling with ‘SELECT-OPTION” in ADMP side, and your post is very helpful to me.

    The first parameter is for DataSet and it seems that only single table (in case of table..) is possible for the 1st parameter…

    What if more than 2 table with joins… are used for the 1st parameter..?

    Is it possible..? (I think I can make it work if I create an attribute view… but only tables are available.. unfortunately..)

    Do you have any guideline for this case…?

    Thank you in advance,

    Regards,

      Hyukjoo

    (0) 
  10. kuatroka kuatroka

    Hi Rich, thanks for the article. If I may one question.

    I need to create dynamic filtering scenario where depending on the user ID that accesses the Calculation view ( from SAP Design Studio) I need to bring for him only certain set of values for lets say “Cost Centre Groupings” ( not totally restricting like in an analytical privileges, but just default filtering)

    I have a mapping table with user  ID to Cost Centre Groupings relationship. The issue is how to pass the current user/ session user parameter in the filter value section of the Input Parameter that I have created  in my Calculation View?

    I know that for example statement “currentdate()” in the  filter section of the input parameter filter value, will give me the current date and I can filter on that, but is there a statement to get  the value of the current user ?

    Is there any reference guide where all these statements accepted by SAP HANA could be found ?

    And the second question is is there any way where procedures could be linked to the input parameters in SAP HANA?

    Is there any way we could write SQL logic somewhere within input parameter so we can deal with the complex scenarios?

    (0) 
  11. Lucas Ferraz

    Hi Rich,

    One question, considering the following statements:

    VAR_WHERE := ‘  DOCNUM = ”000000001” ‘;

    VAR_A = SELECT * FROM VIEW;
    VAR_FILTERED = APPLY_FILTER(:VAR_A,:VAR_WHERE);

    How does HANA will work on this case ? It will fetch all register from VIEW and then apply the filter over the internal variable, or it will apply the filter directly on the select statement ?

    Thanks !!

    (0) 
    1. Rich Heilman Post author

      In the end, the statements will be inlined and executed as…

      SELECT * FROM ( SELECT * FROM VIEW ) WHERE DOCNUM = ‘000000001’



      Cheers,

      RIch



      (0) 
      1. Suhas Karnik

        This solution may not have performance problems, but I would imagine the security concern would still be the same as with dynamic SQL. For instance, I could make this procedure return all records by concatenating an ‘ Or 1=1’ snippet at the end of the parameter.


        This can be risky if the parameter would be populated by a front-end application such as javascript or even XSJS based on user input because it essentially outsources the task of preventing injection to the front end.

        (0) 
        1. Rich Heilman Post author

          Yes, because we are passing the entire filter string as a parameter, you could just add anything to that string.  But in other scenarios, we actually do provide a level of protection against SQL injection when using APPLY_FILTER.

          Cheers,

          Rich

          (0) 
      2. Robert Shandley

        Thanks for that explanation Rich. I was looking for a way to use apply_filter on a parameterized CDS view, which doesn’t work. So I had to go about it the way Lucas wrote above and I had the same concerns about wasting resources.

        (0) 
  12. Alekhya Vemavarapu

    Hi Rich,

        Thanks for the great post.
    Would like to know the internal implementation of APPLY_FILTER (including time complexity).

    Can you Pl. suggest any resources for the same.

    Regards,

    Alekhya

    (0) 
  13. Naresh Setty

    Hi Rich,

    From your example , if I use calc view instead of table and if the calc view takes mandatory input parameters, how can we pass those input parameters when using the apply_filter as I believe this apply_filter takes only 2 params.

    ex_products =

    APPLY_FILTER(“SAP_HANA_EPM_DEMO”.“sap.hana.democontent.epm.data::products”,

    :im_filter_string) ;

     

    Can you pl. clarify

     

    Thanks,

    (0) 
  14. Alexander Gossmann

    APPLY_FILTER performance issue.

     

    Hi Rich, I came to a scenario which is realy performance critical and APPLY_FILTER seem to work not as wished in the query plan:

    I have to build a completely dynamic filter on a huge data set provided by a calc view. Therefore I use a scripted calc view. A user can select different combinations of selections through sapui5, which is captured and persisted in HANA – I can access these dynamic combination through a view.

    My first approach was to join calc view with my selection view. The query has a bad performance, as you can immagine.

    When I could use a dynamic projection, the performance would dramaticaly increase.

    The problem with APPLY_FILTER is, that it behaves even worse than the join criteria.

    The only solution left, to build a dynamic SQL statement would be EXECUTE IMMEDIATE, which needs to persist the resault set, which is not possible in the read only scripted calc view.

    Any idea?

    Thx a lot!

    Alex

     

     

     

    (0) 

Leave a Reply