Skip to Content

Handling of SELECT-OPTIONS parameters within AMDP

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:


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].


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.


  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!

You must be Logged on to comment or reply to a post.
  • 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,


    • 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

      • 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.



        • 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

          • 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.




          • 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

  • 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.


  • 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

  • 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...

  • 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.


    • 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.

    • 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

    • 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

  • 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

    • 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.

  • 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_x-val.

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

    • 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

    • 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

  • Hi,

    How do I define the execute method import parameter?




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

    CLASS-METHODS execute


            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

  • 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

    • Thank you Thomas for your quick reply , I just fixed it . 🙂   thanks a lot . But not much difference in the performance after using amdp 🙁

  • 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.


    • if you read the blog carefully, SELECT OPTIONS is not even available in AMDP too. But, conventionally, ABAP has the feature of select options to read user inputs. What matters here is , converting these range structures to meaningful where clause so that SQL can understand.
      Coming to your question - select option is understood only by ABAP Open SQL. CDS is not ABAP. So select options is not understood by CDS. But, from the abap layer, while querying  a CDS view, you can use the range variable.
      Or, why don't you use a table function ?

      Read my Blog here -


  • Hello Experts,


    Thanks for providing insights into using input parameters.

    I have a scenario where there are multiple inputs (Date, MANDT and Store) passed as a string to the AMDP class to filter out records in the table. e.g when values are passed to Date, MANDT and Store, the filter1 value looks

    FILTER1= RCLNT = '320' AND ( BUDAT BETWEEN '20180401' AND '20180724') AND( WERKS = '4797') 

    Now I want to create a variable to handle the case when WERKS = blank.

    How do I  check whether FILTER1 has WERKS= Some value OR is Blank?


    Any leads would be greatly appreciated.




    • I am not sure what you want to do.

      Do you need a where condition that matches both emtpy values and some given value? That would be ...  ( WERKS = ‘4797’ OR WERKS = ' ' ) 

      Or do you want to know how to fill a selection table with this condition? Then you need two entries in the WERKS selection table:

      WERKS EQ 4979

      WERKS EQ

      Or do you want to pass the information for searching with and without empty entries? Then you could pass a flag and do a IF statement in AMDP to switch between the condition with and without blank condition.

      Regards, Thomas

      • Hello Thomas,


        Thanks for providing detailed workarounds.

        I was basically looking for the third option that you mentioned. I applied the same with an IF condition towards the end of AMDP and then applied the filter.

        This way it worked.


        Thank you,




  • I gave this one a try last week and got the AMDP execution failed error and invalid column name. Finally I managed to solve the error. The sample code is misleading. It should not be IV_CLIENT_FIELD = 'CLIENT'. but instead IV_CLIENT_FIELD = 'MANDT'

    There was another dump after fixing this error as the system was running out of space when I filter after select query.

    Conclusion: If you are working with a huge table like MARA then you need to filter it before the select query and not after.