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.

Redoubtable

or group dynamically on database level https://mcmap.net/q/1777438/-wildcards-in-group-by-clause-in-opensql and then use the same loop on already grouped dataset – Typology

@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. – Sperrylite

Answer 1

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 )

 

Sperrylite

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 ... ) – Typology

@Sperrylite To return 3 condition how you will do in the method? Thanks – Cloe

@Cloe Sorry, I don't understand your question. – Sperrylite

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 – Cloe

@Cloe 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. – Sperrylite

Very interesting trick; I can't get it to work though. I'm using the following syntax: LOOP AT lt_test ASSIGNING FIELD-SYMBOL(<fs_dummy>) GROUP BY lcl_test=>get_group_key( ) ASSIGNING FIELD-SYMBOL(<fs>). and <fs> contains the literal names of the keys instead of their values. The function returns the string '<FS_DUMMY>-NAME'. – Mulderig

@Mulderig 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. – Sperrylite

Is there a way to guarantee that my_grouping_method returns a different key on each loop ? IE: to act as if the GROUP BY clause wasn't there. Seems counter-productive, I know, but it would make it possible to dynamically switch the GROUP BY clause on or off without changing the code of the loop. One solution would be to return the entire line as the group key. But the return parameter of the method can't be generic enough to encompass all possible line types. (I don't know the line type of the table in advance.) Another solution I see is to return a random sequence of chars. – Mulderig

@Cutter: You could just return the value of a counter counting up each time the method is called. – Sperrylite

@Sperrylite thanks. FYI I got a dump with the styntax LOOP AT get_table() GROUP BY ... Replacing get_table() with an itab solved the problem. – Mulderig