Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
chau1995
Active Participant

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
2 Comments