Skip to Content

Handling SELECT OPTIONS in CDS using Table Function

Introduction

This quick blog describes how to push down select options feature of ABAP layer to CDS using Table functions. Before reading this short blog, I would suggest you to go through  AMDP-Handling select options

We have been seeing many questions in SCN and other forums regarding how to use select options in AMDP , CDS etc. SELECT OPTIONS is not an SQL feature . Its an ABAP ( or OPEN SQL ) feature which SQL script does not understand . So, thinking to push this select option variable ( range ) directly to DB layer is not possible .

Implementation

What can  we do ? Only filtering technique SQL can understand is using a where clause . In select options ,we have many choices of passing inputs , like EQ , NE , CP , BETWEEN etc. If we can convert these options into a where clause understood by SQL , we can execute it dynamically in the DB layer.

Can I use Select options in CDS ?

Yes you can , but only in the outer layer right after projecting the data , like we do in ABAP. That is, while consuming this CDS from the ABAP layer you can use the range variable . But this is not what we are looking at . We need to push this down to the DB layer .

 

Let us create a table function first, to fetch the list of materials along with its text .

 

This table function accepts a string as parameter and returns the material number and text .

In the corresponding implementation, we use APPLY_FILTER function available in SQL to apply dynamic where clause. So, in the sel_opt parameter of table function, we are supposed to pass a dynamic where clause corresponding to the select options .

Here, the filter is applied on mara table and the returned result set is saved in a variable to use further.

 

Now the ABAP side.

data : l_matnr type matnr,
       lt_tab type TABLE OF zdemo_sel_opt,
       L_WHERE TYPE STRING.
SELECT-OPTIONS : s_matnr for l_matnr.


START-OF-SELECTION.

L_WHERE = CL_SHDB_SELTAB=>combine_seltabs(
                                      EXPORTING it_named_seltabs =
                                            value #( ( name = 'MATNR' dref = REF #( s_matnr[] ) ) )
                                            iv_client_field = 'MANDT'
                                ).

WRITE L_WHERE.

 

What happening here is ,

The abap program has a selection screen with material number as a select option.

The CL_SHDB_SELTAB and it’s static method combine_sel_tabs returns a dynamic where clause based on the select option variable we pass .

We can specify multiple fields and it”s range variables together . This must be done when all the specified columns are available in the target projection. ( We can specify MATNR , ERSDA and MATKL together as all these fields are available in MARA table).

Client column name is an optional parameter. If we pass this field , client field is also added in the dynamic where clause.

 

I passed 2 material numbers in the selection screen and the generated where clause is

 

Now, we shall pass this value ( where clause ) to the table function we created.

select * from zdemo_sel_opt( sel_opt = @l_where ) into table @lt_tab.

 

and the where clause is executed in the DB layer and data is filtered.

 

Now , I made a small change in the SQL Script code in table function. Now the columns projected have new names.

Now we are applying the filter on a variable which has fields Client , Material , lang and Description. So , changed the dynamic where clause as

L_WHERE = CL_SHDB_SELTAB=>combine_seltabs(
                                      EXPORTING it_named_seltabs =
                                            value #( ( name = 'Material' dref = REF #( s_matnr[] ) )
                                                     ( name = 'lang' dref = REF #( s_spras[] ) )
                                                     )
                                            iv_client_field = 'Client'
                                ).

 

the apply_filter function accepts a table , a view or a table variable in the scope and apply dynamic where clause.

Read my other blog on – mapping abap features with abap on hana

Sreehari

20 Comments
You must be Logged on to comment or reply to a post.
  • Thanks for sharing,

    this demonstrates by the way the increased complexity in the ABAP-Areas: Until now we neede Open-SQL which is complex enough.

    Now we need to handle: ABAP + CDS; ABAP + AMDP, CDS + AMDP …

    This brings great new fun but will take years to arrive in the implementing majority of ABAPers out there.

    Thanks for helping

    • Great post.

      But I think Timo is right. ABAP used to be very straight forward. Now there are a bunch of artifacts with no significant advantages (but lots of troubleshooting) to accomplish what it used to be a simple things like a SELECT-OPTIONS.

      Thank you all.

  • Hello Srihari,

    when i execute the table function, i can give only one value, i cannot give multiple values, is it possible to get multiples values on table function?

    Thanks,

    Surendra Kasetty

  • Hi,

    I’m getting Error in the following lines

    lt_mara = apply_filter(mara , :sel_op);

    return select mara.mandt , mara.matnr , makt.maktx

     

    The error is source code of this class is incomplete in the lt_mara.

    How to resolve the error?

    Thank You.

  • Hi Sreehari,

    Thanks for sharing.

    I am getting below error in AMDP Method.

    Error : SQLSCRIPT message: invalid identifier: Z131_CL_AMDP_TEST=>MARA#covw &A0&A1&A2&A3&A4&A5&A6&A7&A8&A9

     

    Kindly help.

     

     

    /
  • Hello Sreehari,,

    Firstly thanks to you , to provide a very nice document which helps to create a table function with select options.

     

    I go through the document but I am giving a error while using select option in report where clause  it is giving me an error select option was not bound.

     

    Could you please help me on this.

    Regards,

    Puneet

  • Hi Sreehari,

    I’m facing time out error for this simple CDS with Table Function. Can you please help

    CDS with Table Function:

    ————————————–

    @EndUserText.label: ‘CDS view with Table function for PRCD_Elements’
    define table function YCDS_PRCD_ELEMENTS
    with parameters sel_opt : abap.char( 1000 )
    returns {
    client :mandt;
    knumv :knumv;
    kposn :kposn;
    kschl :kscha;
    kbetr :vfprc_element_amount;
    kpein :kpein;
    kmein :kmein;
    knumh :knumh;
    kwert :vfprc_element_value;
    waerk :waerk;
    kinak :kinak;
    koaid :koaid;

    }
    implemented by method ycl_amdp_prcd_elements=>get_data;

     

    Class for CDS

    ________

    CLASS ycl_amdp_prcd_elements DEFINITION
    PUBLIC
    FINAL
    CREATE PUBLIC .

    PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.
    CLASS-METHODS get_data for table FUNCTION YCDS_PRCD_ELEMENTS.
    PROTECTED SECTION.
    PRIVATE SECTION.
    ENDCLASS.

    CLASS ycl_amdp_prcd_elements IMPLEMENTATION.

    Method get_data
    BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING PRCD_ELEMENTS.

    lt_prcd = APPLY_FILTER( prcd_elements, :Sel_opt );

    RETURN

    SELECT
    Prcd_elements.client,
    Prcd_elements.knumv,
    Prcd_elements.kposn,
    Prcd_elements.kschl,
    Prcd_elements.kbetr,
    Prcd_elements.kpein,
    Prcd_elements.kmein,
    Prcd_elements.knumh,
    Prcd_elements.kwert,
    Prcd_elements.waerk,
    Prcd_elements.kinak,
    Prcd_elements.koaid

    from prcd_elements
    * INNER join prcd_elements p2
    * on p2.client = prcd.client and
    * p2.knumv =prcd.knumv

    Order by Prcd_elements.knumv,
    Prcd_elements.kposn;

    ENDMethod.

    ENDCLASS.

    ————————————-

    Called in program as below :

     


    SPAN {
    font-family: “Courier New”;
    font-size: 15pt;
    color: #000000;
    background: #FFFFFF;
    }
    .L0S31 {
    font-style: italic;
    color: #808080;
    }
    .L0S33 {
    color: #4DA619;
    }
    .L0S52 {
    color: #0000FF;
    }
    .L0S55 {
    color: #800080;
    }
    .L0S70 {
    color: #808080;
    }
    MOVE-CORRESPONDING it_tab[] TO it_knumv[].
    IF it_knumv[] IS NOT INITIAL.
    DELETE ADJACENT DUPLICATES FROM it_knumv COMPARING knumv.
    LOOP AT it_knumv INTO wa_knumv.
    s_knumvsign ‘I’.
    s_knumvoption ‘EQ’.
    s_knumvlow wa_knumvknumv.
    APPEND s_knumv.
    ENDLOOP.

    l_where2 cl_shdb_seltab=>combine_seltabs(
    EXPORTING it_named_seltabs =
    VALUE #name ‘KNUMV’ dref REF #s_knumv[] )
    iv_client_field ‘CLIENT’ ).

    *PERFORM Fetch_prcd.

    SELECT FROM ycds_prcd_elementssel_opt @l_where2 INTO TABLE @DATA(it_prcd).

     

     

     

    • how length your dynamic where clause ? ( l_where2 )  .

      Can you try hard coding a simple where clause which might return few result sets and see if you get timeout still ?

      Sree