ABAP developers target a moving Netweaver platform (7.0, or 7.02 or 7.31 or 7.40 or 7.5x or the Cloud) where the
state of the art method to
shoot yourself in the foot is a contest between vintage idioms and idioms using recent additions to the language.
* Add line to internal table LT_ENTRY
ls_entry-action = shoot.
ls_entry-aim = foot.
APPEND ls_entry TO lt_entry.
* Version 2 using field-symbol
APPEND INITIAL LINE TO lt_entry ASSIGNING <ls_entry>.
<ls_entry>-action = shoot.
<ls_entry>-aim = foot.
* Version 3
APPEND VALUE #( action = shoot
aim = foot ) TO lt_entry.
* Version 4
lt_entry = VALUE #( BASE lt_entry
( action = shoot aim = foot ) ).
ABAP developers struggle in a popularity contest that drives the adoption of
no nonsense features but pushes powerful features available in Netweaver out of fashion because they are or
seem complex, like
- LOOP AT WHERE
- A Graph Library
- A Lexer
- table expression's DEFAULT / OPTIONAL parameter
- GROUP BY for internal tables.
Case in Point
GROUP BY for internal tables is nothing new under the sun. Horst Keller announced it
3 years ago and then produced a
sequel last year explaining binding. Then Kerem Koseoglu called it
cool. I think it outmodes the following idioms
- SORT + DELETE ADJACENT DUPLICATE.
- COLLECT for unique table entries.
- LOOP AT NEW
and it is simple enough so everyone should be using it, today.
So what is GROUP BY for internal tables?
Read the introduction courtesy Horst Keller and (his) official documentation. I will motivate with
before and after samples below and perfect the confusion by saying
grouping is
- classification: creation of groups and unique assignment of table entries to a group
- dynamic mapping while defining a group key for sorting
- native: trying to implement a dynamic sorting logic once made me miss native priority queues in ABAP
I was not using it because...
- It does not work on my Netweaver stack, we still have 7.31 around and downgrading is a pain...
- Once every second week, I have an epic coding moment when I really grasp GROUP BY. But then, it passes...
- It is arcane, I didn't have to, was not forced use it to and could live without it.
- It must hurt, it isn't called bleeding edge for nothing...
I am now using it because...
7.40 is not cutting edge (anymore), and compared to the
procedural to objects conundrum, GROUP BY is a low-hanging fruit with an impressive documentation.
I abstract its power with a word: grouping.
Grouping is a recurring problem in data processing. It is widely applicable, it is a
pattern. While refactoring, GROUP BY reveals the intent of the design better than the alternative ABAP constructs.
With a native idiom available, it is easier to talk about design. We will hear ABAP developer bragging about grouping soon:
- Do not DELETE ADJACENT DUPLICATES, GROUP BY a given key
- Do not LOOP.. AT NEW, GROUP BY
- Do not sort the table according to an alternate key, GROUP BY varying keys.
Fireside Chat with CAPRA
- CAPRA: Okay, so you are an ABAP developer? That don't impress me much. Don't get me wrong, I think you're alright, but which subset of the 700+ keywords do you really know and use?
- me: I can GROUP BY with representative binding!
- CAPRA: wow! are you now a member of a group WITHOUT MEMBERS?
- me: Stop kidding me!
How am I using it?
Change Purchase Orders
To update a list of purchase order items via BAPI_PO_CHANGE each BAPI call is done with the collected list of items belonging to a single purchase order. I used to do it like this
METHOD update.
DATA ls_item LIKE LINE OF it_item.
DATA lt_item TYPE tt_item.
LOOP AT it_item INTO ls_item.
AT NEW ebeln.
CLEAR lt_item.
ENDAT.
APPEND ls_item TO lt_item.
AT END OF ebeln.
update_po( lt_item ).
ENDAT.
ENDLOOP.
ENDMETHOD. "update
I can now create a GROUP at Purchase Order level:
METHOD update.
LOOP AT it_item INTO DATA(ls_item) GROUP BY ls_item-ebeln INTO DATA(lv_ebeln).
update_po( VALUE #( FOR g IN GROUP lv_ebeln ( g ) ) ).
ENDLOOP.
ENDMETHOD.
Priority Queue for Messages
The BAPI call returns messages of type A, E, X, I, W, S. I have pick one to be displayed in an ALV output, I do not care which info/warning/status message is selected, but if error messages (A, E, X) should be preferred.
The design is to order the return messages according to type and pick up the message with the highest priority for display. In a first approach, I assume type X does not occur so lexical ordering AEISWX is appropriate:
METHOD get_current_message.
DATA lt_return LIKE it_return.
DATA ls_return TYPE bapiret2.
* Priority
SORT lt_return BY type.
LOOP AT lt_return INTO ls_return.
EXIT.
ENDLOOP.
rv_message = ls_return-message.
ENDMETHOD. "get_current_message
With GROUP BY it would be:
METHOD get_current_message.
* Priority
LOOP AT it_return INTO DATA(ls_return) GROUP BY ls_return-type.
rv_message = ls_return-message.
RETURN.
ENDLOOP.
ENDMETHOD.
And it is now easy to roll out my custom ordering logic
METHOD priority.
rv_prio = SWITCH #( iv_type WHEN 'A' THEN 0
WHEN 'X' THEN 1
WHEN 'E' THEN 2
WHEN 'W' THEN 3
WHEN 'S' THEN 4
ELSE 5 ).
ENDMETHOD.
METHOD get_current_message.
LOOP AT it_return INTO DATA(ls_return) GROUP BY priority( ls_return-type ).
rv_message = ls_return-message.
RETURN.
ENDLOOP.
ENDMETHOD.
Wait, I could use the standard
translate( ) function here
METHOD get_current_message.
LOOP AT it_return INTO DATA(ls_return)
GROUP BY translate( val = ls_return-type from = `AXEWS ` to = `012345` ).
rv_message = ls_return-message.
RETURN.
ENDLOOP.
ENDMETHOD.
Well, the code have two bugs, thanks
christian.guenter and
sdfraga
- Message type 'I' is not handled
- the ASCENDING or DESCENDING addition must be used to reorder the groups
This is an opportunity to write failing ABAP unit tests before applying this correction and check if the tests now pass:
METHOD get_current_message.
LOOP AT it_return INTO DATA(ls_return) GROUP BY
translate( val = ls_return-type from = `AXEWSI ` to = `0123456` ) ASCENDING.
rv_message = ls_return-message.
RETURN.
ENDLOOP.
ENDMETHOD.
Filter IDocs by Segment
To filter out entries containing a given segment from a list of IDocs, I could do
* we check witch idoc message types contains the required segment
lt_edidc[] = ct_edidc[].
SORT lt_edidc BY idoctp cimtyp.
DELETE ADJACENT DUPLICATES FROM lt_edidc COMPARING idoctp cimtyp.
LOOP AT lt_edidc ASSIGNING <ls_edidc>.
REFRESH lt_syntax.
CALL FUNCTION 'EDI_IDOC_SYNTAX_GET'
EXPORTING
pi_idoctyp = <ls_edidc>-idoctp
pi_cimtyp = <ls_edidc>-cimtyp
TABLES
pt_syntax_table = lt_syntax
EXCEPTIONS
OTHERS = 0.
READ TABLE lt_syntax TRANSPORTING NO FIELDS WITH KEY segtyp = iv_segnam.
IF sy-subrc NE 0.
DELETE ct_edidc WHERE idoctp = <ls_edidc>-idoctp
AND cimtyp = <ls_edidc>-cimtyp.
ENDIF.
ENDLOOP.
But now that I think in groups, I will GROUP BY:
METHOD filter_by_segment.
DATA lt_syntax TYPE STANDARD TABLE OF edi_iapi06.
LOOP AT ct_edidc INTO DATA(ls_edidc)
GROUP BY ( idoctp = ls_edidc-idoctp
cimtyp = ls_edidc-cimtyp )
WITHOUT MEMBERS ASSIGNING FIELD-SYMBOL(<ls_group>).
CLEAR lt_syntax[].
CALL FUNCTION 'EDI_IDOC_SYNTAX_GET'
EXPORTING
pi_idoctyp = <ls_group>-idoctp
pi_cimtyp = <ls_group>-cimtyp
TABLES
pt_syntax_table = lt_syntax
EXCEPTIONS
OTHERS = 0.
CHECK NOT line_exists( lt_syntax[ segtyp = iv_segnam ] ).
DELETE ct_edidc WHERE idoctp = <ls_group>-idoctp
AND cimtyp = <ls_group>-cimtyp.
ENDLOOP.
ENDMETHOD.
The WITHOUT MEMBERS addition is needed because entries in the GROUP are changed.
Report Contract Release Documentation
From a list of purchasing contract items I compare the release history with the sum of invoices. A s those are value contracts an aggregation of the items is made to compare at contract header level. Without GROUP BY, LOOP AT NEW helps:
METHOD add_data.
DATA lv_logsy TYPE logsys.
DATA ls_totals TYPE lcl_contract=>ts_totals.
DATA lv_error TYPE flag.
DATA lo_contract TYPE REF TO lcl_contract.
FIELD-SYMBOLS <ls_ekab> TYPE ts_ekab.
CHECK mt_ekab IS NOT INITIAL.
lv_logsy = get_logsys( ).
CREATE OBJECT lo_contract
EXPORTING
io_ekab = me
io_rfc = io_rfc.
* Value Contracts
LOOP AT mt_ekab ASSIGNING <ls_ekab>.
AT NEW konnr.
lo_contract->consumption( EXPORTING iv_konnr = <ls_ekab>-konnr
IMPORTING es_totals = ls_totals
ev_error = lv_error ).
ENDAT.
<ls_ekab>-logsy = lv_logsy.
<ls_ekab>-wrbtr = read_history( <ls_ekab> ).
IF <ls_ekab>-erekz EQ abap_true.
<ls_ekab>-total = <ls_ekab>-wrbtr.
ELSE.
<ls_ekab>-total = <ls_ekab>-netwr.
ENDIF.
IF lv_error EQ abap_false.
<ls_ekab>-rewwb = ls_totals-value.
ENDIF.
ENDLOOP.
ENDMETHOD.
But grouping with GROUP BY is simpler to grasp
METHOD add_data.
CHECK mt_ekab IS NOT INITIAL.
DATA(lv_logsy) = get_logsys( ).
DATA(lo_contract) = NEW lcl_contract( io_ekab = me
io_rfc = io_rfc ).
LOOP AT mt_ekab INTO DATA(ls_ekab) GROUP BY ls_ekab-konnr INTO DATA(lv_konnr).
lo_contract->consumption( EXPORTING iv_konnr = lv_konnr
IMPORTING es_totals = DATA(ls_totals)
ev_error = DATA(lv_error) ).
LOOP AT GROUP lv_konnr ASSIGNING FIELD-SYMBOL(<ls_ekab>).
<ls_ekab>-logsy = lv_logsy.
<ls_ekab>-wrbtr = read_history( <ls_ekab> ).
<ls_ekab>-total = SWITCH #( <ls_ekab>-erekz WHEN 'X' THEN <ls_ekab>-wrbtr
ELSE ls_ekab>-netwr ).
IF lv_error EQ abap_false.
<ls_ekab>-rewwb = ls_totals-value.
ENDIF.
ENDLOOP.
ENDLOOP.
ENDMETHOD.
Aggregate Function SUM like SQL's GROUP BY
Next - building a subtotal like the aggregate GROUP BY for database tables
METHOD convert.
CLEAR et_comm.
LOOP AT it_ekab ASSIGNING FIELD-SYMBOL(<ls_ekab>)
GROUP BY ( ekgrp2 = <ls_ekab>-ekgrp2
konnr = <ls_ekab>-konnr
logsy = <ls_ekab>-logsy ) INTO DATA(ls_group).
DATA(ls_comm) = VALUE ts_comm( ekgrp2 = ls_group-ekgrp2
konnr = ls_group-konnr
logsy = ls_group-logsy ).
LOOP AT GROUP ls_group INTO DATA(ls_ekab).
ls_comm-ktwrt = ls_ekab-ktwrt. " Contract target value
ls_comm-total = ls_ekab-rewwb. " Contract sum of releases
ls_comm-kwaers = ls_ekab-kwaers. " Contract currency
ADD ls_ekab-total TO ls_comm-wrbtr. " Sum invoices or PO value if no EREKZ
ENDLOOP.
ls_comm-exceeded = xsdbool( ls_comm-wrbtr + ls_ekab-rewwb > ls_comm-ktwrt ).
APPEND ls_comm TO et_comm.
ENDLOOP.
ENDMETHOD.
Now, downgrading is pain,
METHOD convert.
DATA ls_comm TYPE ts_comm.
FIELD-SYMBOLS <ls_ekab> TYPE ts_ekab.
CLEAR et_comm.
TYPES: BEGIN OF ts_key,
ekgrp2 TYPE ekgrp,
konnr TYPE konnr,
logsy TYPE logsys,
END OF ts_key.
DATA lt_key TYPE SORTED TABLE OF ts_key WITH UNIQUE KEY ekgrp2 konnr logsy.
DATA ls_key LIKE LINE OF lt_key.
LOOP AT mt_ekab ASSIGNING <ls_ekab>.
MOVE-CORRESPONDING <ls_ekab> TO ls_key.
INSERT ls_key INTO TABLE lt_key.
ENDLOOP.
LOOP AT lt_key INTO ls_key.
LOOP AT it_ekab ASSIGNING <ls_ekab> WHERE ekgrp2 = ls_key-ekgrp2
AND konnr = ls_key-konnr
AND logsy = ls_key-logsy.
AT FIRST.
MOVE-CORRESPONDING <ls_ekab> TO ls_comm.
ENDAT.
ls_comm-ktwrt = <ls_ekab>-ktwrt. " Contract target value
ls_comm-total = <ls_ekab>-rewwb. " Contract sum of releases
ls_comm-kwaers = <ls_ekab>-kwaers. " Contract currency
ADD <ls_ekab>-total TO ls_comm-wrbtr. " Sum invoices or PO value if no EREKZ
AT LAST.
DATA lv_sum LIKE ls_comm-wrbtr.
lv_sum = ls_comm-wrbtr + <ls_ekab>-rewwb.
IF lv_sum > ls_comm-ktwrt.
ls_comm-to_be_checked = abap_true.
ELSE.
ls_comm-to_be_checked = abap_false.
ENDIF.
APPEND ls_comm TO et_comm.
ENDAT.
ENDLOOP.
ENDLOOP.
ENDMETHOD. "convert
Expressions
Modern ABAP introduced expressions, creating a conflict almost by design as new features were hitherto added to ABAP as new statements.
- A statement does something: statements work on data; they are imperative and applied in sequence.
- An expression returns a value: pure functions (without side effect) are composable. But note ABAP does not try to be a functional language, the type system does not support complex/algebraic data types
So GROUP BY also has an expression oriented form.
METHOD convert.
et_comm = VALUE #( FOR GROUPS ls_group OF <ls_ekab> IN it_ekab
GROUP BY ( ekgrp2 = <ls_ekab>-ekgrp2
konnr = <ls_ekab>-konnr
logsy = <ls_ekab>-logsy
ktwrt = <ls_ekab>-ktwrt " target value
total = <ls_ekab>-rewwb " sum of releases
kwaers = <ls_ekab>-kwaers " Contract currency
exceeded = <ls_ekab>-exceeded
rewwb = <ls_ekab>-rewwb
count = GROUP SIZE )
( VALUE #( LET lv_sum = REDUCE wrbtr( INIT lv_val TYPE wrbtr
FOR ls_ekab IN GROUP ls_group
NEXT lv_val = lv_val + ls_ekab-total )
IN BASE CORRESPONDING ts_comm( ls_group )
wrbtr = lv_sum " Sum invoices or PO value if no EREKZ
to_be_checked = xsdbool( lv_sum + ls_group-rewwb > ls_group-ktwrt ) ) ) ).
ENDMETHOD.
Are you using it?
Discern wisdom from myths and you will know when GROUP BY should be used. Should it be more widely used? I say yes. You can check grouping now and add your comment, or write yet another blog about it.
