ABAP News for 7.40, SP08 – GROUP BY for Internal Tables
You know the GROUP BY clause from SQL. There was not such a clause for internal tables up to now. All we had was that clumsy group level processing with statements AT NEW … that relied on the order of table columns and contents that is sorted respectively.
With release 7.40, SP08 there is a real GROUP BY clause for LOOP AT itab that is much more powerful than the SQL one.
DATA flights TYPE TABLE OF spfli WITH EMPTY KEY.
SELECT * FROM spfli
WHERE carrid = ‘…’
INTO TABLE @flights.
DATA members LIKE flights.
LOOP AT flights INTO DATA(flight)
GROUP BY ( carrier = flight-carrid cityfr = flight-cityfrom )
ASCENDING
ASSIGNING FIELD-SYMBOL(<group>).
CLEAR members.
LOOP AT GROUP <group> ASSIGNING FIELD-SYMBOL(<flight>).
members = VALUE #( BASE members ( <flight> ) ).
ENDLOOP.
cl_demo_output=>write( members ).
ENDLOOP.
cl_demo_output=>display( ).
Looks like dreaded nested LOOPs, but it isn’t quite that – no quadratic behavior! What happens here is that the first LOOP statement is executed over all internal table lines in one go and the new GROUP BY addition groups the lines. Technically, the lines are bound internally to a group that belongs to a group key that is specified behind GROUP BY.The group key is calculated for each loop pass. And the best is, it need not be as simple as using only column values, but you can use any expressions here that normally depend on the contents of the current line, e.g. comparisons, method calls, …. The LOOP body is not evaluated in this phase!
Only after the grouping phase, the LOOP body is evaluated. Now a second (but not nested) loop is carried out over the groups constructed in the first phase. Inside this group loop you can access the group using e.g. the field symbol <group> that is assigned to the group in the above example. If you want to access the members of the group, you can us the new LOOP AT GROUP statement, which enables a member loop within the group loop. In the example, the members are inserted into a member table and displayed.
Here another example, where the group key is evaluated from method calls:
LOOP AT flights INTO DATA(wa)
GROUP BY ( tz_from = get_time_zone( wa-airpfrom )
tz_to = get_time_zone( wa-airpto ) )
ASSIGNING FIELD-SYMBOL(<group>).
…
ENDLOOP.
Of course, there is also expression enabled syntax for grouping internal tables.
In a first step, we get rid of LOOP AT GROUP by replacing it with a FOR expression:
DATA members LIKE flights.
LOOP AT flights INTO DATA(flight)
GROUP BY ( carrier = flight-carrid cityfr = flight-cityfrom )
ASCENDING
ASSIGNING FIELD-SYMBOL(<group>).
members = VALUE #( FOR m IN GROUP <group> ( m ) ).
cl_demo_output=>write( members ).
ENDLOOP.
cl_demo_output=>display( ).
The IN GROUP is a new addition to FOR. Second, away with the outer LOOP:
TYPES t_flights LIKE flights.
DATA out TYPE REF TO if_demo_output.
out = REDUCE #( INIT o = cl_demo_output=>new( )
FOR GROUPS <group> OF flight IN flights
GROUP BY ( carrier = flight-carrid cityfr = flight-cityfrom )
ASCENDING
LET members = VALUE t_flights( FOR m IN GROUP <group> ( m ) ) IN
NEXT o = o->write( members ) ).
out->display( ).
FOR GROUPS is another new FOR variant. Believe me, it does the same as the variants above. But for reasons of readability, the combination of LOOP AT GROUP with a FOR IN GROUP within might be the preferable one, at least for this example 😈 .
For a deep dive, read this and this!
PS: If you are confused what kind of loops and nesting takes place, have a look at my comment below.
PPS: For a step-by-step approach to GROUP BY see GROUP BY for Internal Tables – Step by Step
Once again, very interesting.
1. Why actually do we have to define the group_result ({INTO group}|{ASSIGNING <group>} in case we are only interested of his members?
i.e.
Why the syntax is "LOOP AT GROUP <group>"
instead of
"LOOP AT GROUP" (or "LOOP AT GROUP group_name")
?
2. Regarding last example,
The monsters, oh the monsters...
1. You can nest all these LOOPs and FORs. Inside a nested loop there might be several groupings available. Therefore, you must explicitly specify the groups you want to iterate.
2. 😀
Hi Horst,
first of all, I'm not an ABAP developer.. but, nonetheless, I need to deal with ABAP because I ended up in a company that works with SAP.. but I'm a big fan of your posts because you bring new stuff to the table.
my question to you is: why not just a for or foreach like other languages?
as far I understood, the group by (key) is a full iteration over the list performing a simple comparison statement to add or not something into a group?
what I mean is, it seemed to me ABAP was moving to a less statement / keyword language, but I can only assume folks at SAP cannot do it because ABAP itself is darn slow to cope with high processing, hence introductions of new keywords and statement to run 'simple loops' in a C based kernel which effectively, will provide some speed.
it's rather pointless to have 300 keywords to do simple logic, because instead of executing the simple logic in a code developed in ABAP we run it in the kernel using C.. wouldn't it be best to just expose a C API, this way when we need some extra speed we just code in C directly?
also, I understand a internal_table in ABAP may not necessarily come from a query, but I did have the impression you mentioned the 'kernel' group by is better (and I can only assume you are comparing performance) than a group by on the database layer.. how is that even remotely possible?
[]'s
D.
There is a general FOR ...
Yes, but the full iteration can be limited by a WHERE condition. The "simple comparison" statement" can be any expression, including method calls.
Hmm, not really. In fact ABAP can be astonishingly fast, once you have the data on the application server and if you do it right. The new GROUP BY can more be seen as a convenience tool that relieves you from programming the temporary internal group table for yourself. The simple fast loops are already available since long.
Your'e questioning the existence of ABAP as a 4GL on its application server as a whole. You might also ask, why do we need SQL, if I can access the contents of dabase tables with C directly? Or why do we need database tables at all, having a fprintf in C. Such a discussion should be started separately. But please be aware that a concept like internal tables is not readily available in C and we are not talking about simple C FORs here.
I said it is more powerful than the GROUP BY of SELECT. While SELECT allows you to group by contents of database columns only, the GROUP BY for internal tables can group by arbitrary expressions.
fairly positive this is not what I meant.. but I like the fact at least inside an expression we can use a for.
a comparison is a comparison, they are usually simple and most cases they are not simple is due to problematic code (99% of the cases I saw so far) - would love to understand what happens behind the curtains; "where" is much like a filter applied most probably the same way as group?
I totally understand, and this is exactly what I question: if I do a loop, some if's internally and end up with another 'internal table' (the group version) - I suppose the performance will be the same right? - somehow I feel that using the 'group' will be a lot faster.. and yet, another 'keyword' added for a simple for + ifs.
not really questioning the existence: I understand why ABAP still exists, and we need SQL because SQL works pretty well - we also need the database layer in there, however its rather tricky when you have to explain that a " loop. loop. endloop. endloop." is not 'really' a nested loop.. that IS a nested loop, unless my eyes are playing tricks on me.. - now I honestly believe this is about performance, but you don't think this is about performance, so we move the page.
Hi Daniel,
it is a nested loop by "syntax". But it is not really in nested loop in ABAP because the first loop which really does the grouping work is done in the kernel. So if you only count what happens in the ABAP, the performance is linear and not quadratic. This is what Horst meant with "not nested". Of course, you can't trick the theoretical complexity here. So actually we have 3 loops here: the first loop, which does all the work in the kernel. The outer loop over the grouping sets in ABAP and the inner loop over the grouping members. The last two loops which you see in the ABAP are (together!) only linear in the size of the resulting internal table.
You should compare the GROUP BY (and all constructor expressions) not to ancient C constructs like for(;;) or even foreach(...) but to much more powerful constructs coming from functional programming languages like Haskell (e.g. list comprehension). You will see _much_ more complex expressions in your "comparisons" in your favourite language too in the future. Just look what is possible in C++ or Java nowadays with lambda's. I guess that not all lambda functions contain only "simple comparisons" ;-).
Best regards,
Kilian.
Hi Killian,
pretty positive I won't see anything like this, and I still don't understand what you mean by complex expressions... doing a simple map with a key based on comparisons carrying a list inside is something pretty basic.
even list comprehension, as you mentioned, can be easily achieved in OO, so I still cannot find any other good argument other than speed to have such in the kernel "with a nested loop by syntax" (God knows what you mean by that) - instead of doing the same in a OO fashion..
there are no real favourites in my list, there are the good ones and the bad ones..
Cheers,
D.
When teaching ABAP and ABAP Objects to Java programmers you are often asked, why do you have concepts like internal tables at all? Why don't you offer an OO framework that does the same? The answer is, because ABAP is ABAP where traditionally statements and type concepts play the role that OO programmed frameworks do in lower level languages. It would be nonsense to introduce system classes in ABAP that replace the existing internal table framework (and yes of course in this case also for performance reasons). ABAP Objects offers the capabilities of OO in ABAP but is not there to replace established ABAP concepts. We tried to explain this also here: http://help.sap.com/abapdocu_740/en/index.htm?file=abenobj_oriented_guidl.htm.
Besides performance there is also the convenience argument. Already the old internal table grouping implemented by the AT NEW ... commands was purely convenience. You could achieve the same with IF ... ENDIF but ABAP offered simply shortcuts for that involving some preconditions regarding the contents of the internal tables.
The new GROUP BY can be seen in the same tradition. It relieves you from programming the grouping yourself and by the possiblity of using arbitrary expressions for the grouping condition it is very powerful and reusable for all kinds of tasks. In an OO framework I guess you would have to offer a redefinable method that defines the grouping criteria and I think it would involve a good deal of RTTS to create and handle the internal reference table that is created under the hood of the GROUP BY addition. Creating (not filling) the internal artifacts might in fact be faster in the kernel compared to doing it with ABAP means. But that should be negligible compared to the processing of the loops itself and I don't expect that the GROUP BY - LOOP AT GROUP are faster than a selfprogrammed ABAP framework since in the end the same kernel modules are called.
So from my point of view, the answer to your question is:
-> It lies in the foundation of the ABAP language to offer functionality in the form of statements or more recently in the form of built-in expressions where other languages use OO frameworks. It is often for performance reasons, but not always. (In fact there are even some ABAP statements that are implemented at least partly in ABAP itself).
hi Horst,
more likely you're not teaching anything to a Java person - probably, just trying to explain the foundations of something that makes not a lot of sense by all other standards around the globe.. - remember: the Java person usually saw 'a lot of stuff', more likely to tell an ABAPer what work and what doesn't work and not the opposite as you may think.
🙂
now, internal tables.. good question, and as far I can understand by reading own SAP code, it's because SAP itself cannot get rid of it.. ' ABAP is ABAP ' is a rather 'lame' (no offense here, I just don't know a better word to use) excuse, anyone with very little knowledge will understand ABAP is a procedural language and somehow, probably due a lot of pressure, it has introduced some OO elements to shut the hippies up.. of course, since it's not a good OO implementation, more likely the recommendations "don't even try something fancy because you will fail miserably" are pretty much the recommendations (or difference) between doing OO in Java / .NET / C++ and ABAP.
and as I mentioned before, I understand performance argument: the convenience argument on the other hand, it's rather difficult for me to grasp.. ABAP has far too many keywords to do rather simple basic tasks, in OO you can easily achieve with some interface and some inline class declaration - C++/Java/.NET -- while of course, not in ABAP "OO".
so till here, all understood: ABAP has no real power when working with Objects and no real Collections framework, the GROUP BY it's about performance and convenience (of remembering another way of arranging keywords instead of just applying some logic; the very same logic someone has written in C layer) which I cannot understand, but one question still remains:
- why not a native interface? - that would more likely to solve the performance issue in future right?
Cheers,
D.
Hi Daniel,
I've long fought with myself whether I should enter this, lets call it "discussion". I don't know why you still work with ABAP, if you don't like the language. Language wars never have and never will come to a conclusion.
"ABAP is ABAP" is as lame as "JAVA is JAVA" or "C++ is C++", but some kind of true if you know the history of these languages and the reason why they are made in the way they are made.
ABAP was build as language for high robust business applications in a time where only COBOL, FORTRAN and PL/1 are used for these kind of applications. Sometimes you can still smell the taste of COBOL in the coding of some ABAP developers.
Unfortunately (or not) SAP has to handle backward compatibility of all ABAP statements, so it's not possible to recreate the language from the scratch even if they try (and they have tried already, believe me).
But with every evolution (not revolution) ABAP becomes better and better, especially with the last support packages (SP5 and SP8 of NW 7.40). I'm working with the language since more than 20 years now, and for the job we are doing, it's still the best solution IMHO.
Side note: if you want to create a table/list/collection (or what ever you want to call it) of everything, you can do it even with ABAP, but you don't really want to do it:
I don't think internal tables are a weakness of ABAP, quite the contrary. But as always, this is my personnel view and nobody has to be in complete agreement.
hi Uwe,
ABAP is just another language I have to deal with.. it's not about 'still work with ABAP', sometimes I still have to touch it, but not all the time.. and somehow I find myself one of the 'lucky' developers who takes time to come here read, talk and try to understand motives behind the language and all the new things being developed, even thou I really don't use it much.. not too sure if we can say the same about the majority of developers out there that use ABAP every day.. many folks around me have no clue what's happening in the new SP's, and they would not understand the sample you just posted either..
and since I'm talking about your sample, well.. it is pretty much like an Object[], which is not what's in question here... and btw, I do have my classes to deal with collections already coded... still, does not answer why there's no such classes already coded and delivered by SAP, so someone inclined to OO could use them instead of internal tables.
IMO it seems SAP is breaking the 'ABAP' shackles lately investing a lot in other technologies and more and more people like me will show up, and they will have eventually to land their hands in ABAP..
it seems there's too much 'emotion' from the core 'users' because they seem to have no escape... but, that's just my opinion.
Cheers,
D.
Hi Daniel,
it is actually very easy to understand. The _two_ loops together just run 1 times through the internal table. So there is written two times a LOOP statement, but only one run through the internal table.
That is very simple mathematics, you don't have to be a god to understand that ;-).
Best regards,
Kilian.
hi Kilian,
it is a nested loop, it's what happens in the kernel even thou it will only pass once in one of them.. please, ABAP expressions are not nearly as powerful as lambda, in fact I cannot even see a reference between both.. ABAP expressions are just simple expressions, getting better but not even close.
it's a problem with semantics and it looks like you have had yours beaten out of your brain: "loop. loop. endloop. endloop." is a nested loop, it's quite clear and I hope you don't challenge that..
now, if yet another keyword was added "group into a. loop a. endloop." my eyes and my brain would not see this as a nested loop.. not to say, I think it would also make it more readable.
Cheers,
D.
Hello Horst,
Unfortunately i have to wait till January for our sandbox to get updated to the latest ABAP release.
A few of questions -
BR,
Suhas
Hi Horst,
ATC reports "Nested Loop" finding when i use a LOOP AT GROUP. For illustration i copied DEMO_LOOP_GROUP_BY_VALUES to Z-namespace and checked it with ATC.
Got the following finding -
Although this is Prio 2 finding, but IMO ATC (or rather Code Inspector) must not be reporting this.
Any ideas?
BR,
Suhas
PS - I replaced LOOP AT GROUP with FOR...IN GROUP and ATC doesn't report it 😎
Tell that to the Code Inspector ... 😛
(Ignorare human est)
I thought SAP must have adjusted the CI checks so that LOOP AT GROUP isn't reported as nested loop. Anyway, it is not an ABAP language problem but an application problem 😉
In fact, there are no CI warnings for that in my 7.40/7.50 systems. -> Those checks seem to be not too relevant.
Hi Suhas,
I know your comment is quite old at this point, but we are just implementing ATC and are getting the same warning when using LOOP AT GROUP. Since Horst said he didn't get the error in his system, I suspect there must be a note out there somewhere that resolves the issue, but I haven't been able to find it yet. Did you ever have any luck?
Thanks,
Scott
Hi Horst,
Really nice and interesting feature. Recently, i have to aggregate the data in an internal table and do sum on one column , find the max and min for another column, where i did this (group by )by myself. Now i can replace this method with this new construct. Nevertheless, will there be any function like to sum/average on those internal fields by applying after group by will come soon or is it too much to expect 🙂 ?
Vijayan B
In the moment, I don't know anything about such plans.
Hi Mr. Keller,
any news on this topic?
Greetings
Maurice Straube
nope ...
Workarounds
may be allowing arithmetic operations based on group would have made it more modular .. just an idea! 😎
Nice article Horst.
Kopf Aua
(but not your fault 😆 )
Nice as always, thank you!
My thoughts exactly. 😎
Started SAP / ABAP 6yrs ago coming from a Java background, and always complained about the verbose syntax (no functional expressions, intermediate DATA symbols for no apparent reasons, ...).
Now Mr. Keller seems to be determined to turn this around and give JavaScript & Scala a run for their money. How about Closures and Promises next?! 😉
Not Mr. Keller but Mr. Bohlmann, who presented his stuff at TechEd himself this year, honor to whom honor is due.
I'm only writing the documentation ...
Since there has been some confusion what kind of loops take place here, let me add a little example, where I implement the same grouping task without and with GROUP BY.
REPORT ...
CLASS demo DEFINITION.
PUBLIC SECTION.
CLASS-METHODS:
class_constructor,
main,
meth1,
meth2.
PRIVATE SECTION.
CLASS-DATA:
numbers TYPE TABLE OF i WITH EMPTY KEY,
out TYPE REF TO if_demo_output,
limit1 TYPE i VALUE 3,
limit2 TYPE i VALUE 6.
ENDCLASS.
CLASS demo IMPLEMENTATION.
METHOD class_constructor.
numbers = VALUE #( FOR j = 1 UNTIL j > 10 ( j ) ).
ENDMETHOD.
METHOD main.
out = cl_demo_output=>new( ).
meth1( ).
out->display( ).
meth2( ).
out->display( ).
ENDMETHOD.
METHOD meth1.
TYPES:
BEGIN OF helper_struc,
key TYPE string,
refs TYPE TABLE OF REF TO i WITH EMPTY KEY,
END OF helper_struc,
helper_tab TYPE TABLE OF helper_struc WITH EMPTY KEY.
DATA(helper_tab) = VALUE helper_tab(
( key = |below| )
( key = |between| )
( key = |above| ) ).
"Grouping
LOOP AT numbers REFERENCE INTO DATA(dref).
IF dref->* < limit1.
ASSIGN helper_tab[ key = |below| ]-refs TO FIELD-SYMBOL(<fs>).
INSERT dref INTO TABLE <fs>.
ELSEIF dref->* <= limit2.
ASSIGN helper_tab[ key = |between| ]-refs TO <fs>.
INSERT dref INTO TABLE <fs>.
ELSE.
ASSIGN helper_tab[ key = |above| ]-refs TO <fs>.
INSERT dref INTO TABLE <fs>.
ENDIF.
ENDLOOP.
"Group loop
DATA members LIKE numbers.
LOOP AT helper_tab INTO DATA(helper_struc).
out->begin_section( helper_struc-key ).
members = VALUE #( FOR m IN helper_struc-refs ( m->* ) ).
out->write( members )->end_section( ).
ENDLOOP.
ENDMETHOD.
METHOD meth2.
DATA members LIKE numbers.
LOOP AT numbers INTO DATA(number)
GROUP BY COND string(
WHEN number < limit1 THEN |below|
WHEN number <= limit2 THEN |between|
ELSE |above| )
INTO DATA(group).
out->begin_section( group ).
members = VALUE #( FOR m IN GROUP group ( m ) ).
out->write( members )->end_section( ).
ENDLOOP.
ENDMETHOD.
ENDCLASS.
START-OF-SELECTION.
demo=>main( ).
The simple task is to group the numbers of an array type internal table into three groups.
The first method meth1 works without GROUP BY. Instead the grouping is done explicitly in ABAP using a helper table helper_tab. After the grouping the lines of the nested table refs of the helper table point to the respective lines of the original table numbers for each group that is identified by the key column. After the first loop a second loop is carried out over the helper table and in each loop pass the nested tables refs are evaluated to access the group members.
The second method meth2 works with GROUP BY. The result is exactly the same as for the first method. Comparing the methods, you can see that by GROUP BY the first loop in meth1, the grouping loop, is done implicitly and that in fact it is the second loop, the group loop, that you see here.
As Kilian already stated above, we have three loops: the grouping loop, the group loop and the member loop (the latter, only if you want to). Using GROUP BY hides the grouping loop and the respective helper artifacts. Of course, you can always program it by yourself as shown for a simple example in meth1 but GROUP BY is more convenient and shorter as shown in meth2.
Dear Horst,
I’m just becoming a huge fan of the group by, but honestly, the syntax is killing me. The things which are confusing me most (just posting some sample code in order to refer more easy to the components later-on):
LOOP AT items INTO DATA(some_workarea)
GROUP BY ( some_workarea-component = RHS )
REFERENCE INTO DATA(group).
"process groups
The “outer” LOOP which “constructs” the groups refers to the items – which are actually not really looped at (but the distinct groups). Also, the many different workareas created/referred to were confusing me – particularly the one used in order to apply conditions for the grouping (in my sample some_workarea): It should not be used anywhere except in the brackets after the group by, since within the LOOP at groups, the group itself has a component for each criterion defining the group – am I right?
If this – well – kind of confusing as the item of the group is actually not of relevance in the group-loop.
If my understanding is correct, I wonder why the syntax does not read like this:
GROUP items REFERENCE INTO DATA(group) BY (items-component = RHS ).
"process groups
LOOP AT MEMBERS OF GROUP group ASSIGNING FIELD-SYMBOL(<item>).
"process the group members
ENDLOOP.
I’m sure you and the rocking colleagues have also thought of other syntaxes and “mine” is not too far-fetched, so I guess there must be a reason why you chose the existing notation.
In any case, I don’t fully understand your comment that “this looks like nested loops, but it isn’t”. Also debugging it, it looks like a nested loop based on an intermediate table of groups (which is implicitly created). Is it actually comparable to the following old style AT:
SORT items BY <group_criteria>.
LOOP AT items INTO DATA(some_workarea).
AT NEW <group criterion>.
CREATE DATA group TYPE HANDLE OF group_criteria.
MOVE-CORRESPONDING some_workarea TO group.
“Add the calculated values to group
END AT.
ASSIGN some_workarea TO <ls_item>.
This would mean that the each member of items is actually accessed only once. What brings me to the question how the sorting, which would be necessary for the AT NEW, is performed - Particularly if a criterion is calculated in a method. What about secondary keys on the internal table which is grouped?
If this was “compiled to AT”, is the part written after the LOOP AT members processed AT END OF?
Cheers,
Oliver
Hi Oliver,
well, first it was simply for reuse reasons that the GROUP BY addition was added to the existing LOOP AT syntax instead of inventing a new keyword, because allmost all the features of LOOP AT (WHERE conditions etc.) are also needed for the grouping loop. Second, you cannot introduce new statements like GROUP in a downward compatible way, because there might be macros of that name.
Of course GROUP BY is not merely an addition but defines a totally new variant of the existing LOOP AT statement. I had to learn all that in the hard way too. In the case of the FOR expression, the syntax FOR GROUPS ... OF goes more into the direction that you proposed.
I said, "it isn't quiet that". All I wanted to point out is that the inner loop is not executed for each line of the internal table but for each group only. All in all, the GROUP BY stuff should be seen as something for your convenience that relieves you from doing it in ABAP yourself. There are different ways of achieving the same in ABAP. I have shown an example with IFs above, Of course, you mightt also use ATs, if the table is sorted appropriately.
Regarding all the
I (at least) tried to explain their meaning in the documentation, hopefully giving enough examples. Regarding the workarea/field-symbol/reference variable of the grouping loop, please be aware that it can be adressed in the loop if you use representative binding. Only if you work with group key binding (optionally defined by INTO GROUP), it is initial within the loop.
Best
Horst
Sorry for the late entry into this discussion.
Regarding the last issue in your answer, Horst:
I think (I suppose similar to Oliver) that it would be great to make the workarea/field-symbol/reference variable of the grouping loop optional, because in >95% of cases you will not be using it outside of the GROUP BY-clause. (And it also makes the whole construct more difficult to understand for the novice, because it introduces an element, that is most likey not used.)
Best regards, Edo
Hi Edo,
Sorry, but I don't get it.
We cannot "make make the workarea/field-symbol/reference variable of the grouping loop optional", because you need it in the GROUP BY clause in order to define the group key. Syntactically you can define a group key without the result of the grouping loop but that's the 1% case (see an example). Therefore, TRANSPORTING NO FIELDS is not allowed. That's what I say in the documentation too: "The output behavior result for the table rows is still needed for the construction of the group key group_key ".
Can you give a short example, how you would code a table dependend grouping without "workarea/field-symbol/reference variable of the grouping loop"?
Horst
Hi Horst,
thanks for asking again.
I mean shortening the first LOOP statement in your original code like this:
LOOP AT flights INTO DATA(flight)
GROUP BY ( carrier = flight-carrid cityfr = flight-cityfrom )
ASCENDING
ASSIGNING FIELD-SYMBOL(<group>).
I think this would be a good short form for a very common case. The case where
Best regards, Edo
Hi Edo,
Now I see. As a short form it makes sense.
Will forward the proposal to development.
Cheers!
Horst
PS: If you like it short: FOR
Hi Edo,
"Development" does not like the idea of such a short form for several reasons.
One important point is that in
LOOP AT flights INTO DATA(flight)
GROUP BY ( carrier = flight-carrid cityfr = flight-cityfrom )
the RHS' of carrier = flight-carrid cityfr = flight-cityfrom are expression positions not just column names. Reducing it to mere column names would be another syntax variant, where you cannot write GROUP BY ( to_upper( carrid ) ) or something similar. Such a new syntax variant could confuse further. In the present state, you construct a group key with explicitly defined names (the LHS') and you can use these names inside the loop as the component names of <group>.
An already existiing simplification is the representative binding, where you leave away the INTO/ASSIGNING after GROUP BY. With that you reuse the INTO clause given before GROUP BY.
With explicit INTO-clause for the group key:
LOOP AT flights INTO DATA(flight1)
GROUP BY ( carrier = flight1-carrid cityfr = flight1-cityfrom )
ASCENDING
INTO DATA(group).
out->begin_section( |Group Key: { group-carrier }, { group-cityfr }| ).
LOOP AT GROUP group ASSIGNING FIELD-SYMBOL(<flight1>).
Reusing the INTO-clause:
LOOP AT flights INTO DATA(flight2)
GROUP BY ( carrier = flight2-carrid cityfr = flight2-cityfrom )
ASCENDING.
out->begin_section( |Group Key: { flight2-carrid }, { flight2-cityfrom }| ).
LOOP AT GROUP flight2 ASSIGNING FIELD-SYMBOL(<flight2>).
Now you have only one INTO-clause!
And last but not least: If you need only one column for the group key (seems to be a common use case), the syntax is close to that what you want:
LOOP AT flights INTO DATA(flg) GROUP BY flg-carrid.
Best
Horst
ok. Thanks, and thanks for the other blog. Edo
I get really confused by collecting a bookmark list of some documentation parts, but I don't know how to get the address of a particular page , after right click I only get the information "JavaScript"..
What kind of documentation do you mean? Regarding the ABAP Keyword Documentation, you can add ?file=name.htm to the URL to link to subnodes. Regarding SCN blogs I don´t have another recipe but searching for existing links and copy those.
Yes, the keyword documentation. The question is: How to find the filename - in the browser's address field I can't find it....
Mouse-over or right-click the node in the tree structure.
.....shows "JavaScript" 😉
Only? I see more, e.g.
javascript:call_link('abenabap_dictionary.htm')
The name of the file is inside the brackets and can be placed behind ?file= in the URL..
I'll try it next week, I started my vacation one hour ago. 😉
Start with below link which has navigation tree on left pane:
http://help.sap.com/abapdocu_740/en/index.htm
Clicking any link opens content but does not change URL in address bar of browser.
Using Firefox, I do right-click on right pane and This frame -> Show only this frame. Now I see the same content without the navigation tree.
http://help.sap.com/abapdocu_740/en/ABENABAP.htm
Clicking any link opens the content and gives you correct URL in address bar of browser.
You can either do string substitution of javascript links or browse without navigation tree to see real URLs.
I started my vacation 12 days ago ... 😆 .
This means, your current vacation is longer than all my vacations in 14 combined LOL Sry for being off topic
Horst,
I'm trying this great feature in order to teach it to our developers (we are on SP9, so it should work).
I copied you example rather literally, but I am still getting an error.
Can you give me any idea what I do wrong?
DATA lt_members TYPE STANDARD TABLE OF zat_my_table WITH EMPTY KEY.
LOOP AT lt_table
ASSIGNING FIELD-SYMBOL(<niv1f>)
GROUP BY ( deel_3 = <niv1f>-deel_3
size = GROUP SIZE
index = GROUP INDEX )
ASCENDING
ASSIGNING FIELD-SYMBOL(<lr_niv1f>).
lt_members = VALUE #( FOR m IN GROUP <lr_niv1f> ( m ) ).
ENDLOOP.
The error is "Type of "M" cannot be converted in "ZAT_MY_TABLE".
How do I have to define this "m"?
Thanks a lot for helping.
Kris
I don't see all of your declarations but it seems that lt_members is a table of a table. Why? Omit the TABLE OF in its declaration?
Thanks for the reply. I couldn't look at it earlier than now.
I changed the declaration of IT_MEMBERS into:
DATA lt_members LIKE lt_table. ... as in the exemple.
And now it works fine. But I don't really see the difference between the two definitions.
Thanks anyway.
Kris
Not?
If you write TYPE TABLE OF table_type you get, a table of a table type, meaning a tabular line type. You must either write TABLE OF line_type or TYPE table_type or LIKE table_object. to get a table with the same line type as another table.
Horst
That part I got.
But the original definition was:
DATA lt_members TYPE STANDARD TABLE OF zat_my_table WITH EMPTY KEY
And "lt_table" in the LIKE has the same structure ...
Maybe ABAP gets confused since "lt_table" is created dynamically in a SELECT-statement?
SELECT uuid, getal_1, deel_2, deel_3
FROM zat_my_table
INTO TABLE @DATA(lt_table) .
If I change the column-list into a '*', the original definition works also, as I notice now.
Thanks for helping! 🙂
Kris
Hi Horst,
Thanks for your detailed information doc.
I agree that with the GROUP BY , we can know the size of each group i.e. no.of records in each group. But is there any possibility to check the no. of different groups ? This is to identify unique group count.
Thanks
Praveen.
Dear Horst. we try to make the grouping multilevel but got stuck with below code.
when we run this the line
ASSIGNINGÂ FIELD-SYMBOL(<level_2>).
is blocking the assigning of level_1_details.
when I remove the assigning field-symbol(<level_2>) everything works fine and .
What do I miss in this new concept of grouping that I can't level it up like this?
Thanks Arthur
Does the following help?
https://blogs.sap.com/2016/06/23/group-by-for-internal-tables-step-by-stepc/
It also didn't help when I first investigated it.
Problem with multilevel is that apparently you cant stack the grouping and access the data.
Now problem solved to loop through the group 2 times 1 for adding levels and one to write data.
If this logic of multilevel grouping can be solved more easily I would be glad to hear.
below my test programm for the multilevel grouping and calculation
thanks Arthur
Hi Keller ,
My question might not sound correct , but please answer me .
From this blog i came to understand group by is introduced instead of At new and some other such features . Is it right ?
Second thing I tried to implement parallel cursor using this group by like this
Loop at lt_vbak into data(ls_vbak).
LOOP AT lt_vbap INTO DATA(ls_vbap)
GROUP BY ( vbeln = ls_vbak-vbeln )Â - here i have used header vbeln
ASCENDING
ASSIGNING FIELD-SYMBOL(<group>).
but it is not working correctly could you please say will it work
I tested and found that the traditional way "LOOP inside LOOP" has better performance than "LOOP ... GROUP BY".
Below is the performance comparison:
lt_spfli has 28 lines
lt_spfli has 14,680,064 lines
It seems this new feature doesn't have better performance.
Interesting and weird. I don't see a good reason why the performance in the kernel would be so slow (3x). Maybe there's something else. I'll test that. Thank you for the warning!
I can reproduce partially the additional cost on my SAP system (7.52 SP 0/ kernel 753 SP 300), but it's only 1.5x.
On my system, the additional cost is due to the construction of the "internal internal table", because with the below code I reproduce the GROUP BY behavior with old ABAP, and the performance is almost identical, so we must keep that in mind when we work with high volume of data:
Yes, you can try to run multiple times.
Sometimes 1.5x, however, sometimes 2x or even 3x.