Skip to Content
Author's profile photo Sreehari V Pillai

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

Assigned Tags

      32 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Michelle Crapo
      Michelle Crapo

      Great read.

      Michelle

      Author's profile photo Sreehari V Pillai
      Sreehari V Pillai
      Blog Post Author

      Thanks Michelle.

      Read my confusing blog here and suggest edits please .

       

      Sree

      Author's profile photo Timo John
      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

      Author's profile photo Sreehari V Pillai
      Sreehari V Pillai
      Blog Post Author

      Adding Procedure proxy and external views to the list 🙂

      Author's profile photo Carlos Tolosa
      Carlos Tolosa

      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.

      Author's profile photo surendra kasetty
      surendra kasetty

      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

      Author's profile photo Sreehari V Pillai
      Sreehari V Pillai
      Blog Post Author

      Hi Surendra,

      Its said under the section in this blog.

      Can I use Select options in CDS ?

       

      Sree

       

      Author's profile photo Sudeep Dey
      Sudeep Dey

       

      Hi,

      I’m not able to see the table function option.
      How to create the table function?

      I'm in HANA 1709

      Thank you.

      Author's profile photo Sreehari V Pillai
      Sreehari V Pillai
      Blog Post Author

      From your below comment, I am sure now you are able to create. Let me know otherwise.

      Sreehari

      Author's profile photo Sudeep Dey
      Sudeep Dey

      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.

      Author's profile photo Sreehari V Pillai
      Sreehari V Pillai
      Blog Post Author

      can you post the complete code ? Not able to understand the error from the snippet.

      Sreehari

       

      Author's profile photo Tejaswi Paritala
      Tejaswi Paritala

      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.

       

       

      Author's profile photo Sreehari V Pillai
      Sreehari V Pillai
      Blog Post Author

      change :lt_mara mara to :lt_mara m and refer m .  this must be due to ambiguity .

       

      Sreehari

       

      Author's profile photo Tejaswi Paritala
      Tejaswi Paritala

      Thanks for helping

      Author's profile photo Invenio Solutions
      Invenio Solutions

      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

      Author's profile photo Avanish Joshi
      Avanish Joshi

      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

       

       

       

      Author's profile photo Shiladitya Ghosh
      Shiladitya Ghosh

      I'm facing the below error while trying to active the code.

      How can I avoid this?

      Author's profile photo Sreehari V Pillai
      Sreehari V Pillai
      Blog Post Author

      Screenshot is blurry . I am bit late to comment, I hope you must have fixed the issue by now.

       

      Sree

      Author's profile photo Mauricio Pinheiro Predolim
      Mauricio Pinheiro Predolim

      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

       

      Author's profile photo Sivakumar Arunachalam
      Sivakumar Arunachalam

      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_elementssel_opt @l_where2 INTO TABLE @DATA(it_prcd).

       

       

       

      Author's profile photo Sreehari V Pillai
      Sreehari V Pillai
      Blog Post Author

      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

      Author's profile photo Federico Sebastián Alvarez
      Federico Sebastián Alvarez

      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?

       

      Author's profile photo Sreehari V Pillai
      Sreehari V Pillai
      Blog Post Author

      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

      Author's profile photo Riah BEN ATTRA
      Riah BEN ATTRA

      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,

      Author's profile photo ravivarman ravivarman
      ravivarman ravivarman

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

      Author's profile photo Sreehari V Pillai
      Sreehari V Pillai
      Blog Post Author

      please check the reply to original qn. May be this would be a quick solution.

      Author's profile photo Sreehari V Pillai
      Sreehari V Pillai
      Blog Post Author

      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

      Author's profile photo Mohinder Singh Chauhan
      Mohinder Singh Chauhan

      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?

      Author's profile photo Sreehari V Pillai
      Sreehari V Pillai
      Blog Post Author

      Sorry , no other go . Let's see if anyone else has a solution. May be raise a separate question on this.

      Author's profile photo Santosh Satam
      Santosh Satam

      Thanks for the article

      Author's profile photo Sreehari V Pillai
      Sreehari V Pillai
      Blog Post Author

      It took 5 years 🙂 thanks mate

      Author's profile photo Ivanov Ivan
      Ivanov Ivan

      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