ABAP on SAP HANA. Part X. AMDP with SELECT OPTIONS

What is AMDP, How to declare an AMDP class, How to identify an AMDP method? We also consumed the AMDP Class method is our Program and displayed the output.

If you have not visited the earlier article or if you have not worked in AMDP earlier, we would sincerely suggest you have a glance of that topic so that you can understand this and appreciate this article.

In the example demonstrated in the earlier article, all the selection screen elements were PARAMETERS. Using the PARAMETERS in AMDP Method SELECTs were straight forward. Today we would show how we can pass SELECT OPTIONs of the screen to AMDP Methods and use them. Please note, we cannot directly pass SELECT options as is it to AMDP Methods. This is one limitation of AMDP. We need to select the data from the database and then APPLY the Filter using the function APPLY_FILTER.

Let us hit it hard again. AMDP Class-Methods cannot take SELECT OPTIONS as input. So SELECT OPTIONS need to be converted to FILTER STRING using some way and then pass the FILTER STRING as an input PARAMETER of the of the AMDP Method.

The actual syntax to filter the selected data would look like below:

* Filtration based on Selection screen input
ex_it_tcode_role = APPLY_FILTER( :ex_it_tcode_role, :ip_filters );

EX_IT_TCODE_ROLE would have all the data and APPLY_FILTER would keep the subset using IP_FILTERS value.

How do we pass IP_FILTERS?

Ans: It has to be passed as STRING.

METHODS get_t_code_role_matrix
IMPORTING
VALUE(ip_object) TYPE agobject
VALUE(ip_langu) TYPE menu_spras
VALUE(ip_line) TYPE menu_num_5
VALUE(ip_filters) TYPE string " PARAMETER for the SELECT OPTION String
EXPORTING
VALUE(ex_it_tcode_role) TYPE tt_tcode.

How do we generate the filter string from SELECT OPTIONS?

Ans: You are the programmer, you find your way to generating the filter. It should act as the WHERE clause. Or like the FILTER using RANGE table.

Do not worry, we would show you an easy way.

If S_TCODE and S_ROLE are two SELECT OPTIONS of a program, then the string for AMDP filter can be generated using the class CL_SHDB_SELTAB method COMBINE_SELTABS as shown below.

DATA(lv_where) = cl_shdb_seltab=>combine_seltabs(
it_named_seltabs = VALUE #(
( name = 'TCODE' dref = REF #( s_tcode[] ) )
( name = 'ROLE' dref = REF #( s_role[] ) )
) ).

If the above syntax is little confusing, then check the alternative for the same syntax.

cl_shdb_seltab=>combine_seltabs(
EXPORTING
it_named_seltabs = VALUE #(
( name = 'TCODE' dref = REF #( s_tcode[] ) )
( name = 'ROLE' dref = REF #( s_role[] ) )
)
RECEIVING
rv_where = DATA(lv_where) ).

Feeling better now?

Add class CL_SHDB_SELTAB method COMBINE_SELTABS on your cheat sheet.

What does the above class method do?

Ans: See it yourself in debug mode.

I am sure by now you are curious to know how we use it in the Program (after all you are a programmer by heart).

Real Time working Program to show handling of SELECT OPTION in AMDP:

*--------------------------------------------------------------------*
* Created by: www.sapspot.com
* Description: This program consumes the AMDP Class/Method and
* shows how to send SELECT OPTIONS to AMDP and use
* APPLY_FILTER function in AMDP Method.
*--------------------------------------------------------------------*
REPORT zmm_tcode_role_report NO STANDARD PAGE HEADING
LINE-COUNT 132.
*--------------------------------------------------------------------*
* TABLES
*--------------------------------------------------------------------*
TABLES: agr_define.
 
*--------------------------------------------------------------------*
* DATA DECLARATION
*--------------------------------------------------------------------*
* Inline data declaration for the AMDP Class Instance
DATA(lr_data) = NEW zcl_user_role_amdp( ).
 
*--------------------------------------------------------------------*
* SELECTION SCREEN
*--------------------------------------------------------------------*
SELECTION-SCREEN: BEGIN OF BLOCK block1 WITH FRAME TITLE text-t01.
SELECT-OPTIONS:
s_tcode FOR syst-tcode,
s_role FOR agr_define-agr_name.
SELECTION-SCREEN: END OF BLOCK block1.
 
*--------------------------------------------------------------------*
* INITIALIZATION.
*--------------------------------------------------------------------*
 
*--------------------------------------------------------------------*
* START-OF-SELECTION.
*--------------------------------------------------------------------*
START-OF-SELECTION.
 
* Build where clause for data fetching
* Class-Method to convert the select options to a dynamic where clause which
* will be passed to the AMDP for data filteration after data selection
DATA(lv_where) = cl_shdb_seltab=>combine_seltabs(
it_named_seltabs = VALUE #(
( name = 'TCODE' dref = REF #( s_tcode[] ) )
( name = 'ROLE' dref = REF #( s_role[] ) )
) ).
 
* Calling the AMDP method to get the data
CALL METHOD lr_data->get_t_code_role_matrix
EXPORTING
ip_object = 'S_TCODE'
ip_langu = sy-langu
ip_line = '00000'
ip_filters = lv_where
IMPORTING
ex_it_tcode_role = DATA(it_tcode_role).
 
*--------------------------------------------------------------------*
* END-OF-SELECTION.
*--------------------------------------------------------------------*
END-OF-SELECTION.
 
* Publishing the data in an output
cl_demo_output=>display_data(
EXPORTING
value = it_tcode_role
name = 'AMDP to show APPLY_FILTER function' ).
*--------------------------------------------------------------------*

Real AMDP Class Method showing usage of APPLY_FILTER for SELECT OPTIONS:

CLASS zcl_user_role_amdp DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
 
PUBLIC SECTION.
 
INTERFACES if_amdp_marker_hdb.
 
TYPES:
BEGIN OF ty_tcode,
tcode TYPE agval,
ttext TYPE ttext_stct,
role TYPE agr_name,
rtext TYPE agr_title,
END OF ty_tcode .
 
TYPES:
tt_tcode TYPE STANDARD TABLE OF ty_tcode .
 
METHODS get_t_code_role_matrix
IMPORTING
VALUE(ip_object) TYPE agobject
VALUE(ip_langu) TYPE menu_spras
VALUE(ip_line) TYPE menu_num_5
VALUE(ip_filters) TYPE string
EXPORTING
VALUE(ex_it_tcode_role) TYPE tt_tcode.
 
PROTECTED SECTION.
PRIVATE SECTION.
 
ENDCLASS.
 
CLASS zcl_user_role_amdp IMPLEMENTATION.
 
METHOD get_t_code_role_matrix
BY DATABASE PROCEDURE
FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING agr_1251 tstct agr_texts.
 
ex_it_tcode_role = select a.low,
b.ttext,
a.agr_name,
c.text
from agr_1251 as a
inner join tstct as b on a.low = b.tcode
inner join agr_texts as c on a.agr_name = c.agr_name
where
a.mandt = :ip_client
AND a.object = :ip_object
AND b.sprsl = :ip_langu
AND c.spras = :ip_langu
AND C.LINE = :ip_line
ORDER BY a.low, a.agr_name;
 
* Filtration based on Selection screen input
ex_it_tcode_role = APPLY_FILTER( :ex_it_tcode_role, :ip_filters );
 
ENDMETHOD.
 
ENDCLASS.

Some point for the explorers.

1. If you do not want to use CL_SHDB_SELTAB=>COMBINE_SELTABS to build your Filter String, you can do it yourself using CONCATENATE function.

CONSTANTS: lc_augdt TYPE augdt VALUE '00000000'. " Clearing Date
 
READ TABLE s_budat INTO lst_budat INDEX 1.
IF SY-SUBRC = 0.
lv_where_augdt = |AUGDT = '| && |{ lc_augdt }| &&
|' OR AUGDT > '| && |{ lst_budat-high }'|.
ENDIF.

It is same as ( AUGDT = ‘00000000’ OR AUGDT = ‘20161129’ ).

2. If you think the below syntax to generate the dynamic WHERE CLAUSE string is bit complex, then try to use the alternative.

DATA(lv_where) = cl_shdb_seltab=>combine_seltabs(
it_named_seltabs = VALUE #(
( name = 'TCODE' dref = REF #( s_tcode[] ) )
( name = 'ROLE' dref = REF #( s_role[] ) )
) ).

This alternative shown below is a lengthy approach but might be simple and easy to understand for some of us. After all, everyone has the right to be different.

TRY.
 
** Type declaration for getting the Method's input table type compactibility
TYPES:
BEGIN OF ty_named_dref,
name TYPE string,
dref TYPE REF TO data,
END OF ty_named_dref,
 
lt_named_dref TYPE STANDARD TABLE OF ty_named_dref WITH DEFAULT KEY.
 
** Range Table for Select options
TYPES: 
lt_tcode_range_tab TYPE RANGE OF syst_tcode,
lt_role_range_tab TYPE RANGE OF agr_name.
 
DATA: 
ls_named_dref TYPE ty_named_dref,
lty_named_dref TYPE lt_named_dref,
lv_dref TYPE REF TO data.
 
FIELD-SYMBOLS: <fs_range_tab_for_sel_option> TYPE ANY TABLE.
 
ls_named_dref-name = 'TCODE'.
 
CREATE DATA lv_dref TYPE lt_tcode_range_tab.
 
ASSIGN lv_dref->* TO <fs_range_tab_for_sel_option>.
 
IF <fs_range_tab_for_sel_option> IS ASSIGNED.
 
<fs_range_tab_for_sel_option> = s_tcode[].
ls_named_dref-dref = lv_dref.
APPEND ls_named_dref TO lty_named_dref.
 
ENDIF.
 
CLEAR: lv_dref, ls_named_dref.
UNASSIGN <fs_range_tab_for_sel_option>.
 
ls_named_dref-name = 'ROLE'.
 
CREATE DATA lv_dref TYPE lt_role_range_tab.
 
ASSIGN lv_dref->* TO <fs_range_tab_for_sel_option>.
 
IF <fs_range_tab_for_sel_option> IS ASSIGNED.
 
<fs_range_tab_for_sel_option> = s_role[].
ls_named_dref-dref = lv_dref.
APPEND ls_named_dref TO lty_named_dref.
CLEAR ls_named_dref.
 
ENDIF.
 
* Create the WHERE Clause
cl_shdb_seltab=>combine_seltabs(
EXPORTING
it_named_seltabs = lty_named_dref
RECEIVING
rv_where = DATA(lv_where) ).
 
CATCH cx_shdb_exception. "
 
ENDTRY.

Let us see in debug mode, how lty_named_dref look like.

No brainer: Output of lv_where need to be the same.

Huh!! I am sure by now you are convinced that you would rather spend some time understanding new syntax at the top of this article than writing this bunch of redundant codes shown above. Our job was to place all the MENU, it is up to you to decide which one you like.

3. If you observe the code for APPLY_FILTER closely, you would notice, filtering is done after we select a bunch of unwanted data and apply the Filter. Doesn’t it impact the performance negatively?

See, we selected everything here.

* Populating intermediate internal table
ex_it_tcode_role = select a.low,
b.ttext,
a.agr_name,
c.text
from agr_1251 as a
inner join tstct as b on a.low = b.tcode
inner join agr_texts as c on a.agr_name = c.agr_name
where
a.mandt = :ip_client
AND a.object = :ip_object
AND b.sprsl = :ip_langu
AND c.spras = :ip_langu
AND C.LINE = :ip_line
ORDER BY a.low, a.agr_name;

Then we applied the Filter.

* Filtration based on Selection screen input
ex_it_tcode_role = APPLY_FILTER( :ex_it_tcode_role, :ip_filters );

Experts suggest wherever possible, we should apply the filter to the DB table directly and then play around with the resultant data set.

For example, if ip_code_where_clause has S_TCODE select option, then we can directly apply the filter on the database table AGR_1251.

it_codes = APPLY_FILTER( agr_1251, :ip_code_where_clause);

Thus, APPLY_FILTER function can be applied to DB Tables and also it can be applied to Internal Tables.

After the epic is over, let us introduce the main character of our today’s story, i.e. APPLY_FILTER. The function APPLY_FILTER expect two PARAMETERS.

i – Dataset (example AGR_1251 (DB table, CDS View); :ex_it_tcode_role (Internal table)) which needs to be filtered.

ii – Generated WHERE clause which is passed to AMDP method as String.

4. After going through the above information one would have a doubt. Why did SAP not allow SELECT OPTIONS to be directly used in AMDP as in normal ABAP?

Ans: We would request experts to provide some explanation to this query.
We feel SAP deliberately chose this path to push down the select option to database level in accordance with its code to data paradigm shift strategy. AMDPs are executed directly on the database, hence the select options in the form of filter string would be executed on the database. On the other hand SELECT OPTION is just an ABAP language construct which cannot be directly executed on database level