Handling SELECT OPTIONS in CDS using Table Function

[sreehari_vpillai]

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