Introduction

In the previous article, we have already handled duplicate problem by using window function (that is ROW_NUMBER), you can see it at link: https://blogs.sap.com/2020/09/01/using-row_number-to-handle-duplicate-in-amdp/

So besides ROW_NUMBER statement, Is there any WINDOW FUNCTION interesting?

Previously, to sum accumulate or sum total value on the dataset, we will perform it by using LOOP…ENDLOOP on ABAP program. In this way, it is easy to code and handles, but low performance. So, if I want to use window function to perform this problem, are there any ways to use it?

Assume that we have a dataset which depicted as the image below:

With this dataset, how do we calculate accumulate and sum total MENGE value by group key (BUKRS, WERKS, LGORT, MEINS, MATNR)?

If you are a newbie in AMDP, you can learn through the article: https://blogs.sap.com/2020/08/25/first-program-with-amdp-method/

Class Definition

CLASS ZCL_AMDP_SUM DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
    INTERFACES IF_AMDP_MARKER_HDB.
    TYPES:
      BEGIN OF TY_MATDOC,
        BUKRS        TYPE BUKRS,
        EBELN        TYPE EBELN,
        EBELP        TYPE EBELP,
        WERKS        TYPE WERKS_D,
        LGORT        TYPE LGORT_D,
        MEINS        TYPE MEINS,
        MATNR        TYPE MATNR,
        MENGE        TYPE MENGE_D,
        ACCUMULATE   TYPE MENGE_D,
        TOTAL        TYPE MENGE_D,
      END   OF TY_MATDOC,
      TT_MATDOC TYPE STANDARD TABLE OF TY_MATDOC.

  CLASS-METHODS:
    TRIGGER_MATDOC IMPORTING VALUE(IV_CLIENT) TYPE SY-MANDT
                             VALUE(IV_WERKS)  TYPE WERKS_D
                             VALUE(IV_BUKRS)  TYPE BUKRS
                             VALUE(IV_LGORT)  TYPE LGORT_D
                   EXPORTING VALUE(ET_RESULT) TYPE TT_MATDOC.
  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.

Class implementation

In this problem, I will use SUM ( ) OVER statement to resolve it. Data in this dataset will split into partitions. Then, value in each partition will be calculated.

By using PARTITION BY statement, data will be divided into partitions. This window function is applied to each partition separately and computation for each group. If you don’t specify any partition in this statement, the function will handle all rows of the dataset as a single partition

And ORDER BY statement, data will be ordered within each partition of the result set

CLASS ZCL_AMDP_SUM IMPLEMENTATION.
  METHOD TRIGGER_MATDOC BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT
                        OPTIONS READ-ONLY
                        USING MATDOC.
   IT_TABLE =
    SELECT T1.BUKRS,
           T1.EBELN,
           T1.EBELP,
           T1.WERKS,
           T1.LGORT,
           T1.MEINS,
           T1.MATNR,
           --original value
           T1.MENGE,
           --accumulate value
           SUM( T1.MENGE ) OVER ( PARTITION BY T1.BUKRS, T1.WERKS, T1.LGORT, T1.MEINS, T1.MATNR
                                  ORDER BY T1.BUKRS, T1.WERKS, T1.LGORT, T1.MEINS, T1.MATNR
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS ACCUMULATE,
           --sum total value                      
           SUM( T1.MENGE ) OVER ( PARTITION BY T1.BUKRS, T1.WERKS, T1.LGORT, T1.MEINS, T1.MATNR
                                  ORDER BY T1.BUKRS, T1.WERKS, T1.LGORT, T1.MEINS, T1.MATNR ) AS TOTAL
      FROM MATDOC AS T1
     WHERE T1.MANDT = :IV_CLIENT
       AND T1.WERKS = :IV_WERKS
       AND T1.BUKRS = :IV_BUKRS
       AND T1.LGORT = :IV_LGORT;
    ET_RESULT = SELECT * FROM :IT_TABLE;
  ENDMETHOD.
ENDCLASS.

When we add ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW statement, data will be calculated with a range from the FIRST row to the CURRENT row of this partition, as depicted in the image below:

If we don’t use any frame statement, data will be calculated from first row to the end of row of this partition, same as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING statement.

Main Program

Create an ABAP program to perform this AMDP which you created previously

TYPES:
  BEGIN OF TY_MATDOC,
    BUKRS        TYPE BUKRS,
    EBELN        TYPE EBELN,
    EBELP        TYPE EBELP,
    WERKS        TYPE WERKS_D,
    LGORT        TYPE LGORT_D,
    MEINS        TYPE MEINS,
    MATNR        TYPE MATNR,
    MENGE        TYPE MENGE_D,
    ACCUMULATE   TYPE MENGE_D,
    TOTAL        TYPE MENGE_D,
  END   OF TY_MATDOC,
  TT_MATDOC TYPE STANDARD TABLE OF TY_MATDOC.
DATA:
  GT_MATDOC TYPE TT_MATDOC.


START-OF-SELECTION.
  DATA(L_CHECK) = CL_ABAP_DBFEATURES=>USE_FEATURES(
                                          EXPORTING REQUESTED_FEATURES =
                                                      VALUE #(
                                                               ( CL_ABAP_DBFEATURES=>CALL_AMDP_METHOD )

                                                               ( CL_ABAP_DBFEATURES=>AMDP_TABLE_FUNCTION )
                                                             )
                                           ).

"CHECK AMDP
IF L_CHECK = ABAP_FALSE.
  CL_DEMO_OUTPUT=>DISPLAY( 'Please check system again' ).

ELSE.

  ZCL_AMDP_SUM=>TRIGGER_MATDOC(
                                EXPORTING IV_CLIENT = SY-MANDT
                                          IV_WERKS  = 'MM02'
                                          IV_BUKRS  = 'FS01'
                                          IV_LGORT  = '1000'
                                IMPORTING ET_RESULT = GT_MATDOC
                              ).
  CL_DEMO_OUTPUT=>DISPLAY( GT_MATDOC ).
ENDIF.

Run the program and you will see results. Congratulate, we successfully calculate accumulate and total value.

Summary

I hope this article gives you a new view/ways to handle data on the query in AMDP

Thanks for your attention