ABAP 740 – LOOP AT with GROUP BY

By | October 27, 2015 | Concepts | 22,904 | 8

The great new addition to old and gold LOOP AT is a GROUP BY in ABAP 740. This is an amazing addition to LOOP AT. Lets check it out!

Introduction

LOOP AT must be one of most commonly used syntax. You must be using that often as well. In ABAP 740, this new great addition GROUP BY has amazing capabilities.

ABAP_740_Loop_Group_usage

Syntax

This is highlevel syntax using the GROUP BY

 
LOOP AT t_customres INTO DATA(ls_cust1)
     GROUP BY  ( route = ls_cust1-route
                 size  = GROUP SIZE
                 index = GROUP INDEX )
      ASCENDING
      REFERENCE INTO DATA(route_group).
 

LOOP AT with GROUP BY

As the name suggests, GROUP BY addition groups the rows of the ITAB and the executes the LOOP across the groups. The loop gets executed in two phases:

  • Group – A loop would be executed on the Group which would be created by GROUP condition
  • Group Loop – A loop would be on each group to access the rows of the group. This are members.

Sounds amazing, huh?

Things to Remember

Few points to remember whenever you need to use this GROUP BY:

  • Group Key – You can specify any fields from the ITAB as part of the key.
  • Sorting order – default sorting order of the field is ASCENDING. But you can also specify ASCENDING or DESCENDING explicitly
  • Group members – If don’t need to access the members, you can use the WITHOUT MEMBERS

Lets dip dive in the Examples and learn more.

Example 1 – Get Unique values using the GROUP BY

I’m using the same customer/route test data which I have used in other ABAP 740 examples. In this example 1, we’ll list down the unique Routes by creating the group based on the field ROUTE. The output would be also captured in the result variable created automatically.

Also notice the use of the WITHOUT MEMBERS as members were not accessed at all.

TYPES:
  BEGIN OF ty_customer,
    customer TYPE char10,
    name     TYPE char30,
    city     TYPE char30,
    route    TYPE char10,
  END   OF ty_customer.
TYPES: tt_customers TYPE SORTED TABLE OF ty_customer
          WITH UNIQUE KEY customer.
 
TYPES: tt_citys TYPE STANDARD TABLE OF char30 WITH EMPTY KEY.
 
DATA(t_customres) =
VALUE tt_customers(
  ( customer = 'C0001' name = 'Test Customer 1' city = 'NY' route = 'R0001' )
  ( customer = 'C0002' name = 'Customer 2'      city = 'LA' route = 'R0003' )
  ( customer = 'C0003' name = 'Good Customer 3' city = 'DFW' route = 'R0001' )
  ( customer = 'C0004' name = 'Best Customer 4' city = 'CH' route = 'R0003' )
  ( customer = 'C0005' name = 'So So Customer 5' city = 'NY' route = 'R0001' )
).
 
 
* Simply get the unique Routes, use WITHOUT MEMBERS
LOOP AT t_customres INTO DATA(ls_cust_2)
     GROUP BY  ( route = ls_cust_2-route )
      ASCENDING
      WITHOUT MEMBERS
      REFERENCE INTO DATA(route_group_2).
 
  WRITE: / route_group_2->route.
 
 
ENDLOOP.
 

The output would be like:

ABAP_740_Loop_Group_Example_1_Output

Example 2 – Usage of MEMBERS, SIZE and INDEX

The 2nd phase of the GROUP BY is the access the members of the LOOP. To get the members of the group, we need to LOOP AT GROUP within the main LOOP. This inner LOOP needs to use the group result to get the members. The new BASE addition of VALUE will provide all the field values for non group fields. More on BASE in upcoming articles.

There are two inbuilt additions for the GROUP BY key.
SIZE – This would have the number of rows in that group
INDEX – Would be index number of the group, starting with 1.

When the single field is used to make the key, the result would be created as the single field as shown in earlier example. When there are more than one field, the automatic variable would contain all those fields, as in this example.

* Creates a group of Route
*   with members as all fields which matches the group
LOOP AT t_customres INTO DATA(ls_cust1)
     GROUP BY  ( route = ls_cust1-route
                 size  = GROUP SIZE
                 index = GROUP INDEX )
      ASCENDING
      REFERENCE INTO DATA(route_group).
 
  WRITE: / 'Group', route_group->index.
  WRITE: / route_group->route.
 
  DATA(members) = VALUE tt_customers( ).
  LOOP AT GROUP route_group ASSIGNING FIELD-SYMBOL(<route>).
    members = VALUE #( BASE members ( <route> ) ).
  ENDLOOP.
 
  LOOP AT members INTO DATA(ls_member).
    WRITE: /(5)", ls_member-customer, ls_member-name, ls_member-city.
  ENDLOOP.
  WRITE: /(5)", 'Group size ', route_group->size.
  SKIP 2.
ENDLOOP.
 

The output of this example is like this:

ABAP_740_Loop_Group_Example_2_Output

In Debugging, you can see the created structure which is automatically created.

ABAP_740_Loop_Group_Size_Index_Debugger

Example 3 – Group By without explicit Result variable

You can also choose not to specify the result variable at all. If you do so, the workarea used for the LOOP AT would be used for the putting the group data as well. But it would be the entire first matching record with group key.

 
* Creates a group of Route and City
*  but without using the Group result
LOOP AT t_customres INTO DATA(ls_cust)
     GROUP BY  ( route = ls_cust-route
                 city  = ls_cust-city )
      ASCENDING.
 
  WRITE: / ls_cust.
 
  DATA(members) = VALUE tt_customers( ).
  LOOP AT GROUP ls_cust ASSIGNING FIELD-SYMBOL(<route>).
    members = VALUE #( BASE members ( <route> ) ).
  ENDLOOP.
 
  LOOP AT members INTO DATA(ls_member).
    WRITE: /(5)", ls_member-customer, ls_member-name.
  ENDLOOP.
  SKIP 2.
ENDLOOP.

Output this example 3. As you can see here the entire first row of the group is being set in the LOOP.

ABAP_740_Loop_Group_Example_3_Output

Takeaway from GROUP BY

This would be great addition to the GROUP BY. Also, I think it would make sense to use the explicit result work area all the time, instead of using the main loop workarea. It would be more confusing to use the main workarea.

Table of Content – ABAP 740 Concepts

Like It? Share!!

Don't miss an Update

Get notified of the new post, right into your inbox

Naimesh Patel{273 articles}

I'm SAP ABAP Consultant for more than a decade. I like to experiment with ABAP especially OO. I have been SDN Top Contributor.
Follow :

Explore all of his 273 articles.

Load comments

8 Comments

  • Steve Oldner

    Wow! Lots of new stuff to learn. I’ll be looking forward to more.

    Thanks Naimesh for the great work!

  • Hello Naimesh,

    First of all let me thank you for your amazing work!
    I have been following zevolving for years now and it’s definitly one of the best, if not the best, sites on ABAP that we can find!

    Just decided to comment on this to aware readers that this new adition to LOOP is only available in SP8 of Netweaver 7.4. Maybe you can alert this in the post? Just a suggestion..

    By the way, you can have a look in http://www.abapinho.com also, it’s not my site actually but I am a great contributor.
    It’s also another great source for those who like to learn a litle bit of ABAP every day.

    Thanks again for your great work and dedication.
    Kind Regards
    Sérgio Fraga

  • Thanks Naimesh for the share.. Interesting.

    Regards,
    Raju.

  • Forgot to mention: your new theme is better and smoother.. :)

    Regards,
    Raju.

  • Gokul

    Hi,

    in performance is it better or both and new syntax are same

  • Hi Naimesh – Just curious.. Have you started adopting the VALUE # and @ in real project scenarios?

    Regards,
    Raju.

  • Hi Raju – Yes, I’m trying to use all these new syntax but due to current client’s very distributed landscape in different EHP version doesn’t allow me to go to full extent yet.

    Regards,
    Naimesh Patel

  • Hi Naimesh,
    Its a very informative ! Has cleared a lot of doubts. Thanks for sharing!

    For more information on SAP and SAP Support you can visit http://www.apprisia.com/

Comments on this Post are now closed. If you have something important to share, you can always contact me.

You seem to be new here. Subscribe to stay connected.