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

[mmcisme1]

Great read.

Michelle

[sreehari_vpillai]

Thanks Michelle.

Read my confusing blog here and suggest edits please .

 

Sree

[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

[sreehari_vpillai]

Adding Procedure proxy and external views to the list 🙂

[former_member544291]

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

[sreehari_vpillai]

Hi Surendra,

Its said under the section in this blog.

Can I use Select options in CDS ?

 

Sree

 

[former_member590120]

Hi,

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

I'm in HANA 1709

Thank you.

[former_member590120]

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.

[sreehari_vpillai]

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

Sreehari

[sreehari_vpillai]

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

Sreehari

 

[former_member389033]

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.

 

 

[sreehari_vpillai]

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

 

Sreehari

 

[former_member389033]

Thanks for helping

[former_member326875]

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

[former_member325607]

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

[shila85]

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



How can I avoid this?

[former_member639039]

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

 

 

 

[sreehari_vpillai]

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

 

Sree

[sreehari_vpillai]

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

[carlostol]

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.

[former_member206638]

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?

 

[sreehari_vpillai]

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

[former_member273190]

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


 

[former_member729553]

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,

[ravi_cvs]

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

[sreehari_vpillai]

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

[sreehari_vpillai]

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

[former_member517243]

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?

[sreehari_vpillai]

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

[former_member669371]

Thanks for the article

[sreehari_vpillai]

It took 5 years 🙂 thanks mate

[ivan_ivanov]

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

[yogesh_odhwani2]

Hi,

 

When Where condition is more than 1333 charachter , how to pass in Class for filtering data for the same.

[LeonvNiekerk]

If you use a list of values as input, you could think of using an alternative could be using a (temporary) table that holds your values from the selection combined with a join 

[ali-kaplan]

this blogpost has helped me out very well! thank you so much!
-> ABAP Cheat Sheet in Github about AMDP is also very helpful! : https://github.com/SAP-samples/abap-cheat-sheets/blob/main/12_AMDP.md

In my case I had to convert a pretty huge select inside of an old programm which is used as an extractor report with 16 left outer joins into amdp as it was slowing down the report speed. 

I used only get_data method and had to apply the filter method apply_filter inside get_data which was a kinda confusing in my case as it was giving some strange error. Here is how I solved (just in case anybody would need) :

• select fields and assign to it_result
• do apply_filter method and assign to it_result_filtered
• RETURN :it_result_filtered;

  METHOD get_data BY DATABASE FUNCTION
                  FOR HDB LANGUAGE SQLSCRIPT
                  OPTIONS READ-ONLY
                  USING mara marc mvke mbew
                        t320 mlgn makt mard
                        crvm_b crfh mapr prop
                        t405 t001w t006a.

    --init mandt variable
    declare lv_clnt "$ABAP.type( MANDT )" := session_context('CLIENT');
    --init spras variable
    declare lv_langu "$ABAP.type( SPRAS )" := session_context('LOCALE_SAP');

    it_result = SELECT
**           MARA
           a.mandt AS client,
           a.matnr,
           a.pstat,
           a.lvorm,
           a.mtart,
           a.mbrsh,
           a.matkl,
           a.bismt,
           a.meins,
           a.bstme,
           a.groes,
           a.wrkst,
           a.normt,
           a.ekwsl,
           a.brgew,
           a.ntgew,
           a.gewei,
           a.volum,
           a.voleh,
           a.behvo,
           a.raube,
           a.tempb,
           a.tragr,
           a.stoff,
           a.spart,
           a.wesch,
           a.etiar,
           a.etifo,
           a.ean11,
           a.numtp,
           a.prdha,
           a.qmpur,
           a.vabme,
           a.kzkfg,
           a.xchpf,
           a.vhart,
           a.magrv,
           a.extwg,
           a.satnr,
           a.mstae,
           a.mstav,
           a.mstde,
           a.rbnrm,
           a.mhdrz,
           b.lzeih,
           a.mhdhb,
           a.mhdlp,
           a.nrfhg,
           a.xgchp,
           a.iprkz,
           a.rdmhd,
           a.mtpos_mara,
           a.sled_bbd,
           a.rmatp,
           a.msbookpartno,
**           MARC
           b.werks,
           b.pstat                AS pstat_c,
           b.lvorm                AS lvorm_c,
           b.mmsta,
           b.mmstd,
           b.maabc,
           b.ekgrp,
           b.ausme,
           b.dismm,
           b.dispo,
           b.plifz,
           b.webaz,
           b.perkz,
           b.perkz               AS perkz_c,
           b.ausss,
           b.disls,
           b.beskz,
           b.sobsl,
           b.minbe,
           b.eisbe,
           b.bstmi,
           b.bstma,
           b.bstfe,
           b.bstrf,
           b.mabst,
           b.sbdkz,
           b.kzaus,
           b.ausdt,
           b.nfmat,
           b.kzbed,
           b.miskz,
           b.fhori,
           b.rgekz,
           b.fevor,
           b.bearz,
           b.ruezt,
           b.tranz,
           b.basmg,
           b.dzeit,
           b.ueeto,
           b.ueetk,
           b.uneto,
           b.wzeit,
           b.insmk,
           b.insmk                AS insmk_c,
           b.ladgr,
           b.xchpf                AS xchpf_c,
           b.usequ,
           b.lgrad,
           b.auftl,
           b.mtvfp,
           b.periv,
           b.periv                AS periv_c,
           b.kautb,
           b.kautb                AS kautb_c,
           b.kordb,
           b.herkl,
           b.herkr,
           b.mtver,
           b.prctr,
           b.mrppp,
           b.fxhor,
           b.vrmod,
           b.vint1,
           b.vint2,
           b.stlal,
           b.stlan,
           b.plnnr,
           b.aplal,
           b.losgr,
           b.sobsk,
           b.frtme,
           b.lgpro,
           b.disgr,
           b.kausf,
           b.qmatv,
           b.rwpro,
           b.awsls,
           b.stdpd,
           b.xmcng,
           b.lfrhy,
           b.rdprf                AS rdprf_c,
           b.kzkup,
           b.strgr,
           b.lgfsb,
           b.eprio,
           b.qmata,
           b.plnty,
           b.sfcpf,
           b.shflg,
           b.shzet,
           b.kzech,
           b.megru,
           b.mfrgr,
           b.loggr,
           b.fprfm,
           b.casnr,
           b.gpnum,
           b.steuc,
           b.matgr,
           b.vspvb,
           b.shpro,
           b.ahdis,
           b.eislo,
           b.ncost,
           b.ppskz,
           b.scm_grprt,
           b.scm_giprt,
           b.scm_scost,
           b.scm_reldt,
           b.scm_rrp_type,
           b.scm_heur_id,
           b.scm_package_id,
           b.scm_sspen,
           b.scm_get_alerts,
           b.scm_res_net_name,
           b.scm_conhap,
           b.scm_conhap_out,
           b.scm_shelf_life_loc,
           b.scm_shelf_life_dur,
           b.scm_maturity_dur,
           b.scm_shlf_lfe_req_min,
           b.scm_shlf_lfe_req_max,
           b.scm_lsuom,
           b.scm_reord_dur,
           b.scm_target_dur,
           b.scm_tstrid,
           b.scm_stra1,
           b.scm_peg_past_alert,
           b.scm_peg_future_alert,
           b.scm_peg_strategy,
           b.scm_peg_wo_alert_fst,
           b.scm_fixpeg_prod_set,
           b.scm_whatbom,
           b.diber,
           b.altsl,
           b.vrbmt,
           b.vrbwk,
           b.vrbdt,
           b.kzkfk,
           b.autru,
**           MVKE
           c.vkorg,
           c.vtweg,
           c.lvorm                AS lvorm_e,
           c.versg,
           c.bonus,
           c.provg,
           c.sktof,
           c.vmstd,
           c.aumng,
           c.lfmng,
           c.scmng,
           c.schme,
           c.vrkme,
           c.mtpos,
           c.dwerk,
           c.prodh,
           c.pmatn,
           c.kondm,
           c.ktgrm,
           c.prat1,
           c.prat2,
           c.prat3,
           c.prat4,
           c.prat5,
           c.prat6,
           c.prat7,
           c.prat8,
           c.prat9,
           c.prata,
           c.rdprf,
**           MBEW
           d.bwkey,
           d.bwtar,
           d.lvorm                AS lvorm_w,
           d.salk3,
           d.vprsv,
           d.verpr,
           d.stprs,
           d.peinh,
           d.bklas,
           d.salkv,
           d.lfgja,
           d.lfmon,
           d.bwtty,
           d.stprv,
           d.laepr,
           d.zkprs,
           d.zkdat,
           d.zplp1,
           d.zpld1,
           d.kaln1,
           d.bwva1,
           d.vers1,
           d.hrkft,
           d.ekalr,
           d.mlmaa,
           d.mlast,
           d.hkmat,
           d.eklas,
           d.xbewm,
           d.kosgr,
**           MLGN
           e.lgnum,
           e.lvorm                AS lvorm_n,
           e.lgbkz,
           e.ltkze,
           e.ltkza,
           e.lhmg1,
           e.lhme1,
           e.lety1,
           e.lvsme,
           e.block,
           e.kzmbf,
           e.bsskz,
           e.mkapv,
           e.bezme,
           e.plkpt,
           e.vomem,
           e.l2skr,
**           MAKT
           f.spras,
           f.maktx,
**           MARD
           g.lgort,
           g.pstat                AS pstat_d,
           g.lvorm                AS lvorm_d,
           g.lwmkb,
           g.diskz,
           g.lsobs,
           g.lminb,
           g.lbstf,
           g.lgpbe,
**           CRVM_B
           h.objty,
           h.objid,
**           CRFH
           i.fgru1,
           i.fgru2,
           i.planv,
           i.bzoffb,
           i.bzoffe,
           i.steuf,
           i.ktsch,
           i.mgform,
           i.ewform,
**           MAPR
           j.pnum1,
**           T405
           k.uebtk,
           k.kzabs,
**           PROP
           l.prmod,
**           T001W
           m.land1,
**           T006A
           n.mseh3,
**           T320
           o.lgnum                AS lgnum_3
      FROM mara AS a
             LEFT OUTER JOIN
               marc AS b ON a.matnr = b.matnr AND a.mandt = b.mandt
                 LEFT OUTER JOIN
                   mvke AS c ON a.matnr = c.matnr AND a.mandt = c.mandt
                     LEFT OUTER JOIN
                       mbew AS d ON b.matnr = d.matnr AND b.werks = d.bwkey AND a.mandt = d.mandt
                         LEFT OUTER JOIN
                           t320 AS o ON b.werks = o.werks AND a.mandt = o.mandt
                             LEFT OUTER JOIN
                               mlgn AS e ON a.matnr = e.matnr AND o.lgnum = e.lgnum AND a.mandt = e.mandt
                                 LEFT OUTER JOIN
                                   makt AS f ON a.matnr = f.matnr AND a.mandt = f.mandt
                                     LEFT OUTER JOIN
                                       mard AS g ON a.matnr = g.matnr AND b.werks = g.werks AND a.mandt = g.mandt
                                         LEFT OUTER JOIN
                                           crvm_b AS h ON b.matnr = h.matnr AND b.werks = h.werks AND a.mandt = h.mandt
                                             LEFT OUTER JOIN
                                               crfh AS i ON h.objty = i.objty AND h.objid = i.objid AND a.mandt = i.mandt
                                                 LEFT OUTER JOIN
                                                   mapr AS j ON b.werks = j.werks AND b.matnr = j.matnr AND a.mandt = j.mandt
                                                     LEFT OUTER JOIN
                                                       prop AS l ON j.pnum1 = l.pnum1 AND l.hsnum = '00' AND l.versp = '00' AND a.mandt = l.mandt
                                                         LEFT OUTER JOIN
                                                           t405 AS k ON a.ekwsl = k.ekwsl AND a.mandt = k.mandt
                                                             LEFT OUTER JOIN
                                                               t001w AS m ON b.werks = m.werks AND a.mandt = m.mandt
                                                                 LEFT OUTER JOIN
                                                                   t006a AS n ON n.spras = :lv_langu AND a.bstme = n.msehi AND a.mandt = n.mandt

      WHERE a.mandt = lv_clnt
        AND f.spras = lv_langu
      ORDER BY a.matnr,
               b.werks;

      it_result_filtered = APPLY_FILTER( :it_result, :iv_where_amdp );

      RETURN :it_result_filtered;
  ENDMETHOD.