Skip to Content

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

To report this post you need to login first.

4 Comments

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

  1. Timo John

    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

    (1) 

Leave a Reply