Using ABAP Managed Database Procedure (AMDP)

The programming guideline Use Open SQL is particularly relevant for AMDP too. The use of AMDP is not recommended if the same task can be achieved using Open SQL (or ABAP CDS).

The biggest change SAP HANA brings to ABAP programming is the way that you access data in database tables. SAP HANA brings the new concept of code pushdown, which is moving business logic out of the application server into the database.

There are two ways to move some of this logic to the database layer: stored procedures
and views. The idea, as always, is to reduce the amount of data transferred between the database and the application server—something you always strive for, because this is one of the golden rules.

In order to develop SAP HANA artifacts from within the ABAP development environment, you have to build and call two new types of ABAP repository objects: core data services (CDS) views and ABAP Managed Database Procedure (AMDP) objects.

Before the CDS view concept came along, you had to retrieve the data from the database using a view and then do some manipulation of that data in your ABAP program. Now, because the CDS view (which runs in the database) has taken over some of the tasks that formerly you would have done in the ABAP program (which runs on an application server), those tasks—or, more accurately, the code to do those tasks—has been pushed down into the database.

AMDP

SAP recommends using Native SQL in ABAP only when the optimization problem cannot be solved using standard OpenSQL tools, in particular, AMDP should be used only if some HANA-specific things are involved or the amount of data is too large to transfer it between the DBMS and the application server.

After coding logic inside AMDP method, you can consume it in ABAP report, or use AMDP same as delegate method in CDS table function.

With AMDP, We can take advantage of new features of HANA (code push-down technique), hence we still code all logic on application layer, then this logic will be executed on the database layer.

If you have a class that has at least one method in which you would like to delegate processing to the database, then the only change you need to make to the definition of that class is to include the interface IF_AMDP_MARKER_HDB.

You can control whether a method is run on the normal ABAP application server or within the database by adding some extra lines to the implementation of the method.

/* Definition */

CLASS zcl_ABC DEFINITION.
PUBLIC SECTION.
INTERFACES IF_AMDP_MARKER_HDB.
METHOD get_ABC_data
IMPORTING VALUE( this_and_that ) TYPE whatever
VALUE( id_client ) TYPE sy-mandt
EXPORTING VALUE( the_other ) TYPE something_else.

/* Implementation*/
CLASS zcl_ABC IMPLEMENTATION.
METHOD get_ABC_data
BY DATABASE PROCEDURE
FOR hdb
LANGUAGE SQLSCRIPT
USING zt__items.
" SQLScript code goes here
ENDMETHOD.ENDCLASS.

• the words BY DATABASE PROCEDURE, which says that this method will generate and execute a stored procedure in the database at runtime.
• FOR hdb, which means that you want to use an SAP HANA database, and LANGUAGE, to say what language you want
• the USING statement, in which you have to give an explicit list of what database tables (or views) you’re going to use within the procedure.

The change here is that CDS views manage the client for you; in an AMDP, you have to specify the client.

Examples

CL_PPH_READ_CLASSIC

Blog post: AMDP class and methods and how to use in an extractor

Creating class implementation and method

CLASS zcl_test_extractor_amdp DEFINITION

PUBLIC

FINAL

CREATE PUBLIC.

PUBLIC SECTION.

INTERFACES if_amdp_marker_hdb .


BEGIN OF ty_attributes,

object_id      TYPE crmt_object_id_db

,guid           TYPE crmt_object_guid

,process_type   TYPE crmt_process_type_db

,P_DESCRIPTION  TYPE crmt_description

,process_desc    type crmt_description

END OF  ty_attributes.


TYPES:

tt_attributes     TYPE STANDARD TABLE OF  ty_attributes,


CLASS-METHODS get_result

IMPORTING

VALUE(iv_clnt)             TYPE mandt

VALUE(ip_filters)          TYPE string

EXPORTING

VALUE(et_attribute_details) TYPE   tt_attributes

RAISING

cx_amdp_error.

ENDCLASS.

Syntax for common operations

Declare internal table inside AMDP class

class zcl_com_final definition
 public
 final
 create public.
  public section.
    interfaces if_amdp_marker_hdb.
    types: begin of ty_itab,
	     rownum        type int2,
             db_key        type /bobf/conf_key,
             prod_hr_id    type /dmf/hierarchy_id,
            creation_date  type dats,	     		       
end of ty_itab,
gt_itab type standard table of ty_itab with unique key primary_key components rownum db_key.

Declare ABAP data type inside the SQL script

declare lv_timestamp   "$ABAP.type( TZNTSTMPS )";

Delete adjacent duplicates

This statement deletes the adjacent duplicate records based on the field “db_key” from table lt_itab

Lt_itab_noduplicates =  SELECT * FROM (  select
                        row_number() over ( partition by db_key ORDER BY db_key ) as  rownum , * from
                        :lt_itab) where rownum = 1 ;

Calling AMDP methods with parameters

public section.
class-methods:
      get_ofrmain
        importing
          value(p_adzone)  type  char255
        exporting
          value(et_ofrmain)   type gt_itab.

method ofr_adzone
      by database function
      for hdb
      language sqlscript
      options read-only
      using  zcl_com_final=>get_ofrmain.

call "ZCL_COM_FINAL=>GET_OFRMAIN"  ( P_ADZONE => :P_ADZONE ET_OFRMAIN => :ET_OFRMAIN );
LT_OFRMAIN = SELECT * FROM :ET_OFRMAIN;

Check if Internal table is not initial

SELECT COUNT(*) INTO numrows FROM :LT_OFRMAIN;
   IF numrows > 0 then
// program logic
   END IF;

Select the client specific data

method Prd_dept
      by database function
      for hdb
      language sqlscript
      options read-only.
    RETURN select _Prd.mandt  as clnt, _Prd.sfs_dept_num,_Prd.sfs_dept_desc
             from "SAP_S4HANA"."ZPRD_DEPT" as _Prd where _Prd.mandt = session_context('CLIENT');
  endmethod.

Convert the Rows to Columns using “Case Statement” ( Transposition )

method get_prodatt
        by database function
        for hdb
        language sqlscript
        options read-only
        using zprd_attr.

    lt_att = select prodhrid,

             max (case
             when attribute = '0001' then
             cast(attributevalue as char( 255 ))
             end ) as ATTRIBUTEVALUE1,

            max (case
            when attribute = '0002' then
            cast(attributevalue as char( 255 ))
            end ) as ATTRIBUTEVALUE2,

            max (case
            when attribute = '0003' then
            cast(attributevalue as char( 255 ))
            end ) as ATTRIBUTEVALUE3

            from zprd _attr
            group by prodhrid;

return select  prodhrid , concat(  ATTRIBUTEVALUE1, concat(ATTRIBUTEVALUE2, ATTRIBUTEVALUE3)  )
       as Ovrline  from :lt_att;

  endmethod.

Select based on importing parameter coming in the class

Select based on an internal table
Select Unique records
To filter unique values from an internal table
Combine select from 2 internal tables
To add select criteria based on a range
Using case and end case
Syntax for using SUM

Additional information

• https://itpfed.com/want-to-avoid-hana-amdp-mistakes-read-this-2/