Skip to Content
Author's profile photo Ankit Rastogi

AMDP: Avoiding FOR ALL ENTRIES and pushing calculation to Database Layer

1.    Objective

The objective of this document is to explain step-by-step process to create AMDP method using multiple select queries to avoid FOR ALL ENTRIES and push calculation to database layer.

2.    Requirement

Requirement is to fetch records from database table MATDOC based on certain plant and storage location combination. On the fetched records, perform calculation e.g. summation on quantity based on various combinations e.g. Material/ Plant/Storage Location, Material/Plant, Material.  The developer would like to leverage AMDP to address this requirement.

Relevant fields of MATDOC Table:

Fields Type Key



Input Tables:

List of Materials

Fields Type Key


List of Plant and Storage Location combination

Fields Type Key


Output Tables:

Fields Type Key


3.    Understanding limitation in FOR ALL ENTRIES select statement

In a select query, with FOR ALL ENTRIES, one can’t use Group BY clause. The addition GROUP BY has no effect if FOR ALL ENTRIES is used.

With new directive of S/4 HANA coding, all the calculation should be pushed to database layer. Hence one can’t leverage the code pushdown if FOR ALL ENTRIES is used in select query.

To avoid FOR ALL ENTRIES in select query, one can go ahead and use multiple ranges for each field of driver table of select query. But with multiple ranges, we get cross referencing entries.

1.     Range Table cross referencing entries


Plant Storage Location Number of Entries in MATDOC with Plant/Storage Location combination Number of Entries in MATDOC when both Plant/Storage location are passed as individual ranges
0001 0001 412
1010 0002 0
SUM 412 460


As you can see number of entries are considerably increased because of cross referencing of plant and storage location i.e. Plant 0001 & Storage location 0002 combination AND Plant 1010 & Storage location 0002 combination is fetching extra (458 – 412 = 48) Entries.

4.    Configuration

The following steps explain step by step configuration:

1.     Create an AMDP Method inside a class

Include the IF_AMDP_MARKER_HDB interface in the class. See below screenshot.

Define the method as below screenshot. Input parameters include list of materials and list of plant and storage locations.

2.     Write first select statement

Prepare first select statement based on list of materials and list of plant & storage locations. See below screenshot. Pay attention to AMDP method implementation syntax.

Here we have used inner join on database table with input parameter table.

3.     Write subsequent select statement

One good feature of AMDP is that one can write select statements on local variables e.g. local internal tables. Write second select statement on records fetched in 1st select statement and use GROUP BY clause.

4.     Use GROUP BY clause in resulting dataset

Now when we have resulting dataset, we can write further select statements on local internal table obtained in 1st select statement with various conditions of GROUP BY class. This will enable us to perform quantity summation (calculation) and prepare output in desired format. One can write multiple select statements based on requirements. See below screenshot.

5.    Test

Now run the AMDP method by executing class from SE24 transaction. It should open the window to test the method.

Populate the Material List, Plant List and Storage Location List as below

Press execute button and see the result in export parameter table ET_PLANT_QTY

6.    Coding

Coding part follows standard SQL Script references. Here select statement is broken into multiple steps depending upon select options.

See below screenshot for Class/Method definition

See below screenshot for Method implementation.

7.    Limitation

All standard limitations of AMDP such as:

  1. An AMDP class can only be edited in ADT (Eclipse).
  2. Client will not be handled automatically like in open SQL.
  3. In case of CDS Views, write appropriate annotations in CDS View definition for client handling so that they can be used inside AMDP. Accordingly, AMDP definition will change.
  4. Exposed associations in CDS Views can’t be accessed inside AMDP.
  5. As of now, AMDP only works when underlying database is HANA.



Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Gaurav Sharan
      Gaurav Sharan

      Cool...Straight forward example.. !  Well Done !!

      Author's profile photo Mehmet Dagnilak
      Mehmet Dagnilak

      I loved the ideas of joining with an internal table and selecting from an local internal table with grouping. Thank you very much for pointing these!

      I wonder how Hana handles selecting from an internal table. Does it read all the data into the internal table and then summarize it, or does it do all the calculations in one step?

      Author's profile photo Ankit Rastogi
      Ankit Rastogi
      Blog Post Author

      Hi Mehmet

      I guess it would be similar to any other DB tables i.e. fetch all the data then do the summation.



      Author's profile photo Zhe Zhao
      Zhe Zhao

      With ABAP 7.52, an internal table can be specified as a data source data source of a query. So we can join an internal table with datebase table directly by using Open SQL. However, it is not possible to use more than one internal table in an SQL command.

      Author's profile photo Mehmet Dagnilak
      Mehmet Dagnilak

      I loved this even better 🙂 I wish companies could upgrade more often..

      Author's profile photo Ankit Rastogi
      Ankit Rastogi
      Blog Post Author

      Thanks Zhe for letting us know on that.

      Author's profile photo Sijin Chandran
      Sijin Chandran

      Hi Ankit,

      First very thanks for this informative blog.

      Am pretty new to AMDP coding, I have one question,

      While implementing AMDP Methods like below:

        METHOD get_kna1_catalog BY DATABASE PROCEDURE
                        FOR HDB
                        LANGUAGE SQLSCRIPT
                        OPTIONS  READ-ONLY
                        USING kna1.

      can't we have more than one Table reference in the using part ( highlighted below ).

      USING kna1


      In my case in the same method I want to fetch from various related tables like knvv, knvp etc and at this part am not able to mention more than one table.

      Helpful views much appreciated.



      Author's profile photo Sijin Chandran
      Sijin Chandran

      Found the answer,

      Yes we can : 

      Author's profile photo S Patil
      S Patil

      Hi Ankit,

      Very informative blog ! Thank you !!!



      Author's profile photo B@lu .
      B@lu .

      i have some doubts can any one clear me

      1.can we use AMDP for SAP supported  Database(s)  other than HANA?

      2.if a CDS view is implemented with AMDP Table Functions. so can we access it by Open SQL statements?