In my blog  ABAP News for 7.40, SP08 – GROUP BY for Interna… | SCN I introduced the GROUP BY addition for LOOP AT for internal tables.

Since there seems to be some confusion about the syntax and its meaning, let us approach the grouping of internal tables step by step using a very simple case and you will see that it isn’t that complicated at all. I also attached the full text of a program, that executes the following steps and produces some output.

Say, you have an internal table spfli_tab TYPE TABLE OF spfli  filled with data from database table SPFLI (what else …).

Step 1, Grouping by one column

The most simple thing you can do is grouping by one column:

LOOP AT spfli_tab INTO wa

                  GROUP BY wacarrid.

       … wacarrid ...

ENDLOOP.


Inside the loop you can access wa, especially wa-carrid. wa contains the first line of each group that is created by GROUP BY. This is called representative binding. Inside the loop wa represents a group.


In order to access the members of each group, you add a member loop:


LOOP AT spfli_tab INTO wa

                  GROUP BY wa-carrid.

  …

  LOOP AT GROUP wa INTO DATA(member).

    … member-… …

  ENDLOOP.

  …

ENDLOOP.


member is a structure with the line type of spfli_tab and contains the members of each group.


Step 2, Grouping by more than one column


If you want to group by more than one grouping criterion (columns in the most simple case), you write:


LOOP AT spfli_tab INTO wa

                  GROUP BY ( key1 = wa-carrid key2 = wa-airpfrom ).

  … wa-carrid … wa-airpfrom …

ENDLOOP.


In this case, you construct a structured group key. In the case of representative binding shown here, wa is reused for accessing the group key inside the loop.


In order to access the members of the groups, you can add exactly the same member loop as in step 1 above.


Step 3, Group key binding for grouping by one column


Besides the representative binding, where the INTO-clause of LOOP AT is reused for accessing the group, you can define a group key binding:


LOOP AT spfli_tab INTO wa

                  GROUP BY wa-carrid

                  INTO DATA(key).

  … key …

ENDLOOP.


Not too different from step 1 above, eh? Instead of reusing wa, you use an elementary data object key that for convenience is declared inline here. Group key binding offers some more functionality compared to representative binding (additions WITHOUT MEMBERS, GROUP SIZE, GROUP INDEX). If you don’t need those, you can stay with representative binding. Nevertheless, I myself always prefer group key binding, since it makes the group key more explicit. But that’s a question of taste.


Inserting the member loop works as before:


LOOP AT spfli_tab INTO wa

                  GROUP BY WA-CARRID

                  INTO DATA(key).

  …

  LOOP AT GROUP key INTO member.

    … members …

  ENDLOOP.

  …

ENDLOOP.


Note, that you access the group using the name key now.


Step 4, Group key binding for grouping by more than one column


Last but not least, group key binding for structured group keys:


LOOP AT spfli_tab INTO wa

                  GROUP BY ( key1 = wa-carrid key2 = wa-airpfrom )

                  INTO DATA(key).

  … key-key1 … key-key2 …

ENDLOOP.


Now, key is a structure with the components key1 and key2. The member loop can be added exactly as in step 3.


If you aren’t interested in the members, you can use the NO MEMBERS addition in order to save some run time and some memory. E.g.


LOOP AT spfli_tab INTO wa

                  GROUP BY ( key1 = wa-carrid key2 = wa-airpfrom

                             index = GROUP INDEX size = GROUP SIZE )

                  WITHOUT MEMBERS

                  INTO DATA(key).

  … key-key1 … key-key2 … key-index … key-size … 

ENDLOOP.


A member loop is not possible here. But key is enriched with some predefiined optional components for additional informations.


Conclusion


Not too complicated, or?


Having understood these simple facts, you can go on, read the documentation, and do more complicated things, e.g. exploiting the fact that you can use expressions for the RHS of the group key definitions or using FOR expressions instead of LOOP AT.

To report this post you need to login first.

16 Comments

You must be Logged on to comment or reply to a post.

  1. Rüdiger Plantiko

    Thanks for the clarifications, Horst!

    When reviewing my example in "SELECT DISTINCT" for internal tables – contest… | SCN,

    I see that I could rewrite the GROUP BY approach further. Here is my GROUP BY proposal from that SCN message:

    form with_grouping using it_vbak type vbak_tab

                        changing ct_ernam type ernam_tab.

         clear ct_ernam.

         loop at it_vbak assigning field-symbol(<ls_vbak>)

                         group by <ls_vbak>-ernam

                         ascending

                         assigning field-symbol(<lv_group>).

           append <lv_group> to ct_ernam.

         endloop.

    endform.

    Firstly, I could switch off group member binding (which, as the docu says, could improve performance, for the price of not being able to perform an inner LOOP AT GROUP or to access <ls_vbak> inside of the loop). Apart from performance, it clarifies my intent that I am only interested in the group key here, not in the table entries themselves:

    loop at it_vbak assigning fieldsymbol(<ls_vbak>)

                         group by <ls_vbak>ernam

                         without members

                         assigning fieldsymbol(<lv_group>).

           append <lv_group> to ct_ernam.

    endloop.


    If I wrote “append <ls_vbak>-ernam to ct_ernam” instead of working with <lv_group>, I get no syntax error (or even a warning) in my release (Kernel 742, SAP_BASIS 740.0008), as I would have expected, but a short dump GETWA_NOT_ASSIGNED in execution.


    Alternatively, I could simply write


    loop at it_vbak assigning fieldsymbol(<ls_vbak>)

                         group by <ls_vbak>ernam.

           append <ls_vbak>ernam to ct_ernam.

    endloop.

    This is clearly shorter, similar in size to the old-style COLLECT approach. But inside of the loop, each first row of it_vbak with a new ERNAM shows up, although I don’t need the full row, and using the other fields would be misleading, since they are randomly in a sense (this is why the were “commented out” with stars in the previous loop grouping idioms AT NEW… and  AT END OF … ).

    Indeed, it would be nice to have something like

    loop at it_vbak group by ernam assigning field-symbol(<lv_group>).

           append <lv_group> to ct_ernam.

    endloop.


    (as discussed in the comment section of your former blog post), but I fully understand that this was rejected by the developers for the reason that the position after GROUP BY is a general expression, which could contain things like function calls to build the key values (and writing just the column name ERNAM breaks this concept, it is no valid expression).


    Regards,

    Rüdiger

    (0) 
  2. Bruce Tjosvold

    We are on Support Package SAPKA74007. There were many syntax errors in the zipped code attachment.  Would you expect the code to work on our system?  I have attached a screen print with the syntSyntax_errors.jpgax errors.

    Thanks

    Bruce

    (0) 
    1. Horst Keller Post author

      In the first sentence I refer to “ABAP News for 7.40, SP08“.

      Therefore, I would not expect the code to work on your system, but as Sandra said from SAPKA74008 on (and in fact it does).


      To see the release notes in your system, run TA ABAPHELP and enter NEWS.


      Horst



      (1) 
  3. Suhas Saha

    Thanks Horst! Now i guess that i can use the GROUP BY construct without hitting the F1 key 😛


    I wish this lucid explanation were part of ABAP Keyword documentation 😉

    (0) 
    1. Horst Keller Post author

      Your wish is my command … 😉

      But as you see from the attached program’s name, I already created it for that purpose in the ABAP Example Library in package SABAPDEMOS and of course the code and the description will be part of the documentation of the next release.

      (1) 
  4. Steve Mo

    Thanks for more clear explanation, Horst.

    One question: Does GROUP BY support dynamic programming? Such as GROUP BY <wa>-(lv_dynamic). I tried but syntax error exists said dynamic attribute is not allowed.

    Is that possible with some other ways?

    BR,
    Steve

    (0) 
        1. Horst Keller Post author

          There’s been a migration, see my 

          Blog.

          Redirects should work but they don’t. Adjusting all links by hand? Maybe a ticket from customer side to SAP can help.

          (0) 
  5. Robin Sun

    Hi Horst,

     

    Great blog indeed! This is really an easy to be understood way.

    When I was trying this piece of code, it works fine very well.

    CLASS demo DEFINITION.
      PUBLIC SECTION.
        CLASS-METHODS:
          main,
          class_constructor.
      PRIVATE SECTION.
        CLASS-DATA
              itab TYPE TABLE OF i WITH EMPTY KEY.
    ENDCLASS.
    
    CLASS demo IMPLEMENTATION.
      METHOD main.
        DATA(out) = cl_demo_output=>new( ).
    
        DATA members LIKE itab.
    
        out->begin_section( `LE 5, BT 2 AND 7, GT 5` ).
        LOOP AT itab INTO DATA(wa)
              GROUP BY
                COND string(
                  WHEN wa <= 5 THEN `LE 5`
                  WHEN wa >  2 AND wa <= 7 THEN `BT 2 AND 7`
                  WHEN wa >  5 THEN `GT 5` )
              ASSIGNING FIELD-SYMBOL(<group>).
          out->begin_section( <group> ).
          CLEAR members.
          LOOP AT GROUP <group> ASSIGNING FIELD-SYMBOL(<wa>).
            members = VALUE #( BASE members ( <wa> ) ).
          ENDLOOP.
          out->write( members )->end_section( ).
        ENDLOOP.
    
        out->next_section( `BT 2 AND 7, LE 5, GT 5` ).
        LOOP AT itab INTO wa
        GROUP BY
          COND string(
            WHEN wa >  2 AND wa <= 7 THEN `BT 2 AND 7`
            WHEN wa <= 5 THEN `LE 5`
            WHEN wa >  5 THEN `GT 5` )
    
          ASSIGNING <group>.
          out->begin_section( <group> ).
          CLEAR members.
          LOOP AT GROUP <group> ASSIGNING <wa>.
            members = VALUE #( BASE members ( <wa> ) ).
          ENDLOOP.
          out->write( members )->end_section( ).
        ENDLOOP.
    
        out->display( ).
      ENDMETHOD.
      METHOD class_constructor.
        itab = VALUE #( FOR j = 1 UNTIL j > 10 ( j ) ).
      ENDMETHOD.
    ENDCLASS.
    
    START-OF-SELECTION.
      demo=>main( ).

    But if I change a little bit for the loop at group like this below

    CLASS demo DEFINITION.
      PUBLIC SECTION.
        CLASS-METHODS:
          main,
          class_constructor.
      PRIVATE SECTION.
        CLASS-DATA
              itab TYPE TABLE OF i WITH EMPTY KEY.
    ENDCLASS.
    
    CLASS demo IMPLEMENTATION.
      METHOD main.
        DATA(out) = cl_demo_output=>new( ).
    
        DATA members LIKE itab.
    
        out->begin_section( `LE 5, BT 2 AND 7, GT 5` ).
        LOOP AT itab INTO DATA(wa)
              GROUP BY (
                key = COND string(
                        WHEN wa <= 5 THEN `LE 5`
                        WHEN wa >  2 AND wa <= 7 THEN `BT 2 AND 7`
                        WHEN wa >  5 THEN `GT 5` )
                index = GROUP INDEX
                size  = GROUP SIZE
              )
              ASSIGNING FIELD-SYMBOL(<group>).
          out->begin_section( <group>-key ).
          CLEAR members.
          LOOP AT GROUP <group> ASSIGNING FIELD-SYMBOL(<wa>).
            members = VALUE #( BASE members ( <wa> ) ).
          ENDLOOP.
          out->write( members )->end_section( ).
        ENDLOOP.
    
        out->next_section( `BT 2 AND 7, LE 5, GT 5` ).
        LOOP AT itab INTO wa
        GROUP BY
          COND string(
            WHEN wa >  2 AND wa <= 7 THEN `BT 2 AND 7`
            WHEN wa <= 5 THEN `LE 5`
            WHEN wa >  5 THEN `GT 5` )
    
          ASSIGNING <group>.
          out->begin_section( <group> ).
          CLEAR members.
          LOOP AT GROUP <group> ASSIGNING <wa>.
            members = VALUE #( BASE members ( <wa> ) ).
          ENDLOOP.
          out->write( members )->end_section( ).
        ENDLOOP.
    
        out->display( ).
      ENDMETHOD.
      METHOD class_constructor.
        itab = VALUE #( FOR j = 1 UNTIL j > 10 ( j ) ).
      ENDMETHOD.
    ENDCLASS.
    
    START-OF-SELECTION.
      demo=>main( ).

    I just couldn’t activate this one. The editor is just hanging there without any error message or warning ones. I am wondering if there are something wrong with it?

    Thank you in advance for your feedback.

     

    Regards,

    Robin Sun

    (0) 
    1. Horst Keller Post author

       

      Wow, nice finding!

      I experience the same in our latest development system.

      I report it to development.

      Thanks for notifying.

      Horst

      (0) 
        1. Horst Keller Post author

           

          Indeed, there is an error in the compiler that is corrected now in the current release (due to your notification).

          The good news is, it is not urgent.

          There is a syntax error in your program that leads to the endless loop. You reuse <group> with a wrong type.

          Change the last part of your code to the following and it compiles.

              out->next_section( `BT 2 AND 7, LE 5, GT 5` ).
              LOOP AT itab INTO wa
              GROUP BY
                COND string(
                  WHEN wa >  2 AND wa <= 7 THEN `BT 2 AND 7`
                  WHEN wa <= 5 THEN `LE 5`
                  WHEN wa >  5 THEN `GT 5` )
                ASSIGNING FIELD-SYMBOL(<groupi>).
                out->begin_section( <groupi> ).
                CLEAR members.
                LOOP AT GROUP <groupi> ASSIGNING <wa>.
                  members = VALUE #( BASE members ( <wa> ) ).
                ENDLOOP.
                out->write( members )->end_section( ).
              ENDLOOP.
          

           

           

          (1) 
          1. Robin Sun

            Hi Horst,

             

            Nice correction, and now I will remeber for each loop, it would be better to use a new key variant.

            Thank you again for this fanstatic series of blogs because via these simply words to introduce the new features, I could have this improvement today, and if I get it everyday, I believe it would be a dramatic leap finally.

             

            Best regards,

            Robin Sun

             

            (0) 

Leave a Reply