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