Skip to Content

The ABAP Managed Database Procedures (AMDP) framework provides the higher level of integration of the advanced HANA capabilities into ABAP applications. It allows creating and managing SQLScript-based DB procedures from the ABAP platform by using so called AMDP methods.

One difficulty faced by developers when working with AMDPs is the handling of SELECT-OPTIONS parameters (selection tables or range tables). The present blog will exactly tackle that topic.

Simply explained, the handling of SELECT-OPTIONS parameters in the context of AMDPs requires two steps:

  1. Conversion of the selection tables into an SQL WHERE clause using method CL_SHDB_SELTAB=>COMBINE_SELTABS( )
  2. Handling of dynamic WHERE clauses within the AMDP method using the function APPLY_FILTER

Step 1: Conversion of SELECT-OPTIONS parameters into an SQL WHERE clause

For those of you who have been generating dynamic WHERE clauses till now using the class CL_LIB_SELTAB: Do no longer use it again and if possible even replace such calls (as explained below) in your existing code!

The new class CL_SHDB_SELTAB – especially its static method COMBINE_SELTABS( )– shall be used for this purpose instead. It provides a comfortable coverage of the conversion functionality for SAP HANA (refer to SAP Note 2124672SMP login required). This conversion routine includes checks for SQL injections during the conversion of the selection tables into an SQL WHERE clause. ABAP 7.4 SP08 and higher is required in order to apply the above mentioned SAP Note.

Here is a simple demo report showing how to convert the SELECT-OPTIONS parameters into a WHERE clause:

/wp-content/uploads/2015/03/amdp_select_options_01_698747.png

As shown above, you just have to pass an internal table (defined here using the new value operator VALUE) filled with as many SELECT-OPTIONS parameters as required by your scenario. The name of the relevant field (NAME) and of the data reference to the corresponding SELECT-OPTIONS table (DREF) is required for each entry. In case of relevance, it is recommended to specify the exporting parameter IV_CLIENT_FIELD with ‘CLIENT’ or ‘MANDT’ (depending on the related table field name) to ensure the addition of the client filter to the WHERE clause.

The method returns the dynamic WHERE condition as a string which can then be passed to the AMDP method. Let’s now go to the next step.

Step 2: Handling of dynamic WHERE clauses within the AMDP method

What needs to be done is very simple: The SQLScript statement APPLY_FILTER is used to apply the selection criteria to the relevant dataset which can be a database table/view, a HANA view (except Analytical view) or an intermediate table variable.

Below you can see a code simple showing how to apply the dynamic WHERE clause in both cases; directly on a data source (table or view) [CASE 1] or on an intermediate dataset (table variable) [CASE 2].

/wp-content/uploads/2015/03/amdp_select_options_02_698748.png

The APPLY_FILTER function expects two parameters. The first one is the dataset to which you want to apply the filter and the second one is the generated WHERE clause which is passed as a string argument. Find more information about the APPLY_FILTER function in the SAP HANA SQLScript reference.

Summary:

  1. Static method COMBINE_SELTABS( ) of the new class CL_SHDB_SELTAB shall be used for the conversion of SELECT-OPTIONS parameters (selection tables or range tables) into an SQL WHERE clause when running on HANA DB.
    • The optional parameter IV_CLIENT should be specified with ‘CLIENT’ or ‘MANDT’) when applicable
    • This class implementation is provided for HDB (refer to SAP Note 2124672SMP login required)
    • ABAP 7.4 SP08 and higher is required in order to apply the above SAP Note
  2. The class CL_LIB_SELTAB and its methods are obsolete
  3. Use the SQLScript function APPLY_FILTER to apply the selection criteria to the selected data in the AMDP
    • The function can be applied on database tables/views, HANA views (except Analytical views) or table variables

That’s it… bye!

To report this post you need to login first.

39 Comments

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

  1. Amol Samte

    Hello Carine,

    Very nice blog.

    1. Does apply filter may affect performance? Because we are fetching data and then        we are applying filter to final data set.

    2. How to handle cross client at the time of transporting in AMDP.

    – Regards,

      Amol

    (0) 
    1. Thomas Gauweiler

      Hi Amol,

      1) if possible apply filters to the table directly as shown with result_1.

      If you need a to use a table variable like with result_2 I would apply all statically known filters directly with the first select. (e.g. filters on the client field).

      2) you can omit the iv_client parameter and add a a condition with the client field on your own to the WHERE condition.

      Best Regards, Thomas

      (0) 
      1. Amol Samte

        Hi Thomas,

        As of now we are doing in same pattern like writing if else code for MANDT. But it will be good if client handling dependency can be handled automatically.

        Thanks,

        Amol

        (0) 
        1. Thomas Gauweiler

          Hi Amol,

          I do not understand your requirement. In a scenario where I only use the current client, then I can use the iv_client parameter as shown (I have to pass the field name as the class cannot know on which table you want to do a SELECT).

          In any other scenario I will anyway have to specify the needed client value explicitly.

          What more automatism do you want?

          Best Regards, Thomas

          (0) 
          1. Amol Samte

            Hi Thomas,

            Thanks for your reply.

            Actually I have to fetch data from different schema.

            e.g. In development we have schema RD2 and Production environment we have RP2.

            In below code I have to put  schema name RD2 for development server while transporting to production system I am changing to RP2.

            So I am finding how to handle schema while transporting.

            Capture.PNG

            Regards,

            Amol

            (0) 
            1. Thomas Gauweiler

              Hi Amol,

              that is a completely different problem. You need to switch between different tables. The answer from a DB perspective would by synonyms. They could be used to point to the same table in different schemata.

              But it is very difficult to use synonyms with AMDP. You have to maintain them on your own and you would have to ensure, that the synonym exists in each system of your landscape before the AMDP gets transported to it. Otherwise you risk syntax errors during import.

              And you cannot use tables of your own ABAP schema, as these would to be declared in the USING clause.

              As this is independent of APPLY_FILTERS and SELECT-OPTIONS we should stop the discussion within this topic here.

              Best Regards, Thomas

              (0) 
  2. Sreehari V Pillai

    Thanks for the nice blog 🙂

    Amol Samte :  If you see the CASE 1, we are applying it directly on the table or view. But the second case, for sure it will degrade the performance. I used to handle the ‘SELECT-OPTION’ situation with alternate solution. I will issue one select ‘COLUMN’ from the tableusing the range variable from abap stack first and get the list of possible values. Then I pass the data to AMDP and then process it. You can pass the sy-mandt value as importing parameter to the AMDP method.

    Sree

    (0) 
  3. HANALearner M

    How to we handle below scenario ?

    We have 2 tables to JOIN and also need to apply FILTER where they are a few fields on table 1 and rest on table 2 i.e filter has fields applicable for both the tables.

    Appreciate if you give an example code.

    Thanks in advance

    (0) 
  4. HANALearner M

    Is it OK or not OK to use CE functions inside AMDP methods instead of regular SQL statements?  Would it provide better performance if we use CE functions….How do we use Filter when we want to use CE Functions…

    (0) 
        1. Jasmin Gruschke

          Hi Amol,
          please have a look at the latest version of the note. There has been an issue in the version (I think it was V2) you applied which has been fixed yesterday.
          Cheers,
            Jasmin

          (0) 
  5. Vipin Nagpal

    Hi,

    I am trying to execute above example, while executing i have realized that BP_ID and COMPANY_NAME column does not exist in database table snwd_so. If i am trying to execute a report i am getting short dump with message “invalid column name”.

    Please correct me if my observation is wrong.

    Thanks

    (0) 
    1. Christian Seitel

      Yes, adhering to the screenshots the where-clause is constructed for table “snwd_bpa” and then applied to table “snwd_so” resulting in a column name mismatch.

      (0) 
    2. Carine Tchoutouo Djomo Post author

      Hi Vipin,

      you’re fully right! Thanks for the feeback!

      Let me just put it this way: I wanted to know who really implements the example… 😉 – Just kidding. I’ve updated the blog with the correct screenshots and it should work for you now.

      Kind regards, Carine

      (0) 
    1. Thomas Gauweiler

      Hi Mr Eli,

      you want to use APPY_FLITER on a table, but return only some columns of it?

      To my knowledge this is not psssible within a single statement. But the optimizer should be smart enough to avoid overhead in the two steps like:

           tmp = APPLY_FILTER( table, … );

           result = SELECT … FROM :tmp;

      Best Regards, Thomas

      (0) 
  6. Kunal Wani

    Hi Carine…Thanx for this nice blog.

    1) In Class Definition part of AMDP, what Type should be declaired for “iv_where_cond”.

    2) In “ABAP For HANA” course , it explained PARAMETERS for CDS Views. Similarly can you please explain how to use SELECT-OPTIONS for CDS View

    (0) 
    1. Jasmin Gruschke

      Hi Kunal,
      1) you can use type string.
      2) Carine’s article explains how to do “code pushdown” of select options to DB level (as the AMDPs respectively the DB procedures are directly executed on the database), while the “old” select options are rather ABAP language constructs. For CDS views, you can just use the SELECT-OPTIONS, when you consume the CDS View in an Open SQL statement.
      Cheers,
        Jasmin

      (0) 
  7. Priyanka H Mallesh

    Carine Tchoutouo Djomo: Hi Carine , where good blog . let me know more information on APPLY FILTER as i have to pass two tables

    SELECT a~partner, b~stat, b~udate, b~utime, b~inact

             INTO TABLE @lt_but000_crm_jcds

             FROM zbut000 AS a INNER JOIN zcrm_jds1 AS b

               ON a~partner_guid = b~objnr

              FOR ALL ENTRIES IN @lt_x

            WHERE a~partner IN @s_partnr

              AND b~stat IN @s_estat

              AND b~udate IN @s_date

              AND b~udate = ( SELECT MAX( udate ) FROM zcrm_jds1 WHERE objnr = b~objnr )

              AND b~utime = ( SELECT MAX( utime ) FROM zcrm_jds1 WHERE objnr = b~objnr

                                                                  AND udate = ( SELECT MAX( udate ) FROM zcrm_jds1 WHERE objnr = b~objnr ) )

              AND b~inact = @lt_xval.

    so I am uisng AMDP with Select options , but getting error where i am passing the values in Apply filter.

    (0) 
    1. Amol Samte

      Hi,

      I guess you are writing above query in AMDP and which is an advanced open SQL thus it is not supporting to AMDP.

      -Amol S

      (0) 
    2. Thomas Gauweiler

      Usually you just do a JOIN in an AMDP instead of an FOR ALL ENTRIES in ABAP as you can pass the interal table as parameter into the AMDP.

      But there is not dynamic version of a join. This only works for fixed join conditions.

      Regards, Thomas

      (0) 
  8. Pattanaik Satyaki

    Hi,

    How do I define the execute method import parameter?

    PUBLIC SECTION.

        INTERFACES: IF_SHDB_DEF,

                               if_amdp_marker_hdb.

        TYPES: ty_where_t type IF_SHDB_DEF=>TT_NAMED_DREF. “where clause

    CLASS-METHODS execute

          IMPORTING

            value(iv_where_clause) TYPE ty_where_t.

    system throws following syntax error.

    The method “execute” contains a database procedure, which means that the row type of “IV_WHERE_CLAUSE” must be structured. All components of the row type must be elementary.

    Thanks, Saty

    (0) 
          1. Amol Samte

            Dear Srikant,

            I guess providing e mail id here is against SCN rules of engagement…

            You can open thread here anytime we are here to support you 🙂

            -Amol S

            (0) 
  9. Thomas Gauweiler

    The column names do not match: you do a

      SELECT a.client, a.partner, b.stat, b.udate, b.utime, b.inact

    so your columns are named CLIENT, PARTNER, STAT, UDATE, UTIME and INACT.


    But in the call to COMBINE_SELTABS you name them as S_PARTNER, S_DATE and S_ESTAT. Here you have to use the same as in the SELECT ( and as in the result table ).


    Regards, Thomas

    (0) 
  10. Bilen Cekic

    i am 2 years late to comment this blogpost but one of the biggest advantage of apply_filter is it supports SQL inline declarations.

    in dynamic sql you cannot do;

    exec 'lt_DAta = select * from.....'

    but here you can simply do;

    lt_Data = apply_Filter(table_name, :cond )

    lets say you have a parallel processing in ABAP. with EXEC statement you need to use local temporary table to store the data. If 2 different process try to generate same internal table name at the same time, it will give “duplicate table name” error. But second example will just work fine.

     

    (0) 

Leave a Reply