LOOP AT... GROUP BY with dynamic group key

Question

I am trying to loop by grouping data with dynamic group parameter.

We can use dynamic queries on WHERE conditions on loops but I do not know whether it is possible to use a dynamic string in group condition.

Here is the sample where user decides by which field to group and then put additional logic based on the decision:

DATA query TYPE string.
IF i_condition_type = 'ERNAM'.
  query = |ERNAM = MARA-ERNAM|.
ELSE.
  query = |ERSDA = MARA-ERSDA|.
ENDIF.

LOOP AT lt_mara INTO DATA(mara) GROUP BY ( (query) ) "syntax error
                                ASSIGNING FIELD-SYMBOL(<group>).
  LOOP AT GROUP <group> ASSIGNING FIELD-SYMBOL(<line_data>).
    "//do something
  ENDLOOP.
ENDLOOP.

Is there any way to do this? I am also open to other ideas besides grouping because if I can't dynamically group, I will copy so many lines and change only the group key.

or group dynamically on database level https://stackoverflow.com/a/61063867/911419 and then use the same loop on already grouped dataset — Suncatcher, Jul 18 '20 at 07:53
@SandraRossi There are ways to do `GROUP BY` dynamically which don't exactly work with strings as conditions but can do a lot of things you would usually use dynamic conditions for. If you are interested, check out my answer. — Philipp, Jul 20 '20 at 16:08

Philipp · Accepted Answer · 2020-07-20T15:51:13.113

5

As pointed out in the comments, LOOP AT ... GROUP BY doesn't support dynamic group-by clauses from strings.

In this simple example you could create your grouping key dynamically at runtime by creating it with an inline expression like COND or SWITCH:

LOOP AT lt_mara INTO DATA(mara) GROUP BY 
    SWITCH string(
       i_condition_type
       WHEN 'ERNAM' THEN mara-ernam
       WHEN 'ERSDA' THEN mara-ersda
       ELSE ''
     )

But your key-building logic might be too complex to express with an expression (or at least an expression which is still readable by a human being). In that case there is something else you can do: group on values returned by a method:

LOOP AT lt_mara INTO DATA(mara) 
     GROUP BY my_grouping_method( line = mara 
                                  condition = i_condition_type )

The implementation of that method can then include any logic you need to form the grouping key at runtime:

METHOD my_grouping_method.
  IF condition = 'ERNAM'.
    result = line-ernam.
  ELSE.
    result = line-ersda.
  ENDIF.    
ENDMETHOD.

The grouping method can also be a method of a different object. So you could represent your grouping condition as an own class. That would allow you to write code like this:

 DATA(lo_group_condition) = NEW zcl_mara_group_condition( 'ERNAM' ). 

 LOOP AT lt_mara INTO DATA(mara) 
     GROUP BY lo_group_condition->get_key_from( mara )

edited Jul 20 '20 at 15:51

answered Jul 20 '20 at 15:06

Philipp

67,764
9
118
153

It should be noted that first approach (COND/SWITCH) is good only for simple group keys, it cannot handle composite ones like `GROUP BY ( key1 = key1 key2 = key2 key3 = key3 ... ) ` – Suncatcher Aug 11 '20 at 16:22
@Philipp To return 3 condition how you will do in the method? Thanks – ekekakos Jul 16 '21 at 06:48
@ekekakos Sorry, I don't understand your question. – Philipp Jul 16 '21 at 07:14
How to do the below rbukrs = wa_acdoca-rbukrs belnr = wa_acdoca-belnr gjahr = wa_acdoca-gjahr ). The parameter LINE in your example is type TABLE? And the RESULT what type is it? Thanks – ekekakos Jul 16 '21 at 07:38
@ekekakos Yes, the `line` import parameter would be the line-type of your table. `result` can be whatever you want. When you want to group by multiple values in your grouping functions, then I would concatenate them into a string. Or maybe the grouping function can return a structure? I would have to try that. – Philipp Jul 16 '21 at 07:45
Very interesting trick; I can't get it to work though. I'm using the following syntax: `LOOP AT lt_test ASSIGNING FIELD-SYMBOL() GROUP BY lcl_test=>get_group_key( ) ASSIGNING FIELD-SYMBOL().` and `` contains the literal names of the keys instead of their values. The function returns the string `'-NAME'`. – Cutter Aug 03 '23 at 01:32
@Cutter The method after `GROUP BY` gets executed for every line and returns **the value** that's used to assign that table line to a group. Returning a string with the name of a field doesn't make any sense in this context. If you need more help with solving your current problem, please open a new question. – Philipp Aug 03 '23 at 07:35

LOOP AT... GROUP BY with dynamic group key

1 Answers1

Linked

Related