###### Personal Insights # 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
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

### Assigned Tags  