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 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.
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.
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.
I hope this article gives you a new view/ways to handle data on the query in AMDP
Thanks for your attention