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