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
Great read.
Michelle
Thanks Michelle.
Read my confusing blog here and suggest edits please .
Sree
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
Adding Procedure proxy and external views to the list 🙂
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 Surendra,
Its said under the section in this blog.
Can I use Select options in CDS ?
Sree
Hi,
I’m not able to see the table function option.
How to create the table function?
I'm in HANA 1709
Thank you.
From your below comment, I am sure now you are able to create. Let me know otherwise.
Sreehari
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.
can you post the complete code ? Not able to understand the error from the snippet.
Sreehari
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.
change :lt_mara mara to :lt_mara m and refer m . this must be due to ambiguity .
Sreehari
Thanks for helping
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
Puneet,
I haven’t tried this on my system yet, but pass the select options values as
select * from Zqmamdp_qualitynotification( s_selopt = gv_where )
into table @gt_data
I'm facing the below error while trying to active the code.
How can I avoid this?
Screenshot is blurry . I am bit late to comment, I hope you must have fixed the issue by now.
Sree
Hi Guys,
Is it possible to use more than one select-options as parameter into table function?
I saw your code and your declared three differents select-options but you are using only one.
Thanks,
Mauricio
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_knumv-sign = 'I'.
s_knumv-option = 'EQ'.
s_knumv-low = wa_knumv-knumv.
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_elements( sel_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
Hi Pillai, great post.
Question: how do you specify the APPLY FILTER first argument, when the argument is a table name like "SAP_ECC"."MARD" instead of just MARD?
My SCN account was blocked , just got it back today.
you cannot specify the table name directly . You will have to fetch it first to a temporary table / variable and pass it to apply_filter
Hi Sreehari,
Great blog !
On the other hand I have a little issue, what to do if the number of characters passed in lv_where is greater than 1000 characters?
Best regards,
I have the issue of having WHERE clause select options more than 1000 characters. We cannot specify the parameter type as STRING for a CDS view.
Please let me know how we can handle where clause string length having more than 1000 characters.?
please check the reply to original qn. May be this would be a quick solution.
sorry for late reply. I am not into (much into ) SAP anymore . My quick win would be , to define 2 or 3 variables with 1000 chars length to pass to the CDS .
from the top layer, I will split my dynamic query to 2 or 3 chunks of 1000 chars and later concatenate inside the function. Will it help ?
sel_op1 : abap.char(1000) ,
sel_op2 : abap.char(1000)
Sree
Thanks for the article, really helpful. I am also stuck with the issue of using abap.char(1000) and system is not accepting it and rightly so. Unfortunately above approach will not work because we dont know how many characters the dynamic will hold. Any other way?
Sorry , no other go . Let's see if anyone else has a solution. May be raise a separate question on this.
Thanks for the article
It took 5 years 🙂 thanks mate
Nice blog!
Some years later I am trying to push down in the AMDP method all the select options a a string(or multiple char(1000)s ) and use it directly in the complex where clause.
So instead of pre-fetching separate tables and use the results in the main query I want to add a dynamic where clause to the main selection.
The main reason for this is that the where clause includes filter on fields that are not part of the result set and are important in the logic that joins the tables in the query.
The question I want to answer with this exercise is if this will speed up the selection process.
I intend to construct the select statement and then execute it via exec() statement.
Any hints, pros/cons comments are welcomed
Ivan