Nested grouping in internal table

Question

I would like to get all lines, which have different entries to fields anln1 and anln2. For this I need a fitting OpenSQL statement.

E.g. there is the following table:

anln1 | anln2 | datum    | psp      | przt
------------------------------------------
10007 | 0     | 20140101 | 12345678 | 1
10007 | 0     | 20140101 | 11111111 | 99
10007 | 1     | 20140101 | 12345678 | 1
10007 | 1     | 20140101 | 11111111 | 99

All entries for anln1 + anln2 should repeat their combination of datum, psp and przt if there is another subnumber, e.g. anln2=1.

Unfortunately my table has breaches to this specification (SQLFiddle: http://sqlfiddle.com/#!2/f5d1f):

anln1 | anln2 | datum    | psp      | przt
------------------------------------------
10000 | 0     | 20140101 | 12345678 | 60
10000 | 0     | 20140101 | 11111111 | 40
10000 | 1     | 20140101 | 11111111 | 100
10000 | 2     | 20140101 | 11111111 | 100
10000 | 3     | 20140101 | 22222222 | 100
10001 | 0     | 20140101 | 12312312 | 100
10001 | 1     | 20140101 | 12312312 | 100
10001 | 2     | 20140101 | 12312312 | 100
10002 | 0     | 20140101 | 11111111 | 100
10003 | 0     | 20140101 | 11111111 | 100
10004 | 0     | 20140101 | 11111111 | 100
10005 | 0     | 20140101 | 22222222 | 100
10005 | 1     | 20140101 | 33333333 | 100
10006 | 0     | 20140101 | 11111111 | 20
10006 | 0     | 20140101 | 22222222 | 80
10006 | 1     | 20140101 | 11111111 | 30
10006 | 1     | 20140101 | 11111111 | 70
10007 | 0     | 20140101 | 12345678 | 1
10007 | 0     | 20140101 | 11111111 | 99
10007 | 1     | 20140101 | 12345678 | 1
10007 | 1     | 20140101 | 11111111 | 99

As a result to my query I need all the lines identified, where my specification is breached. The correct lines should be just left out. Correct lines are the ones where anln1 is 10001, 10002, 10003, 10004, 10007.

So, the result should look like this:

anln1 | anln2 | datum    | psp      | przt
------------------------------------------
10000 | 0     | 20140101 | 12345678 | 60
10000 | 0     | 20140101 | 11111111 | 40
10000 | 1     | 20140101 | 11111111 | 100
10000 | 2     | 20140101 | 11111111 | 100
10000 | 3     | 20140101 | 22222222 | 100
10005 | 0     | 20140101 | 22222222 | 100
10005 | 1     | 20140101 | 33333333 | 100
10006 | 0     | 20140101 | 11111111 | 20
10006 | 0     | 20140101 | 22222222 | 80
10006 | 1     | 20140101 | 11111111 | 30
10006 | 1     | 20140101 | 11111111 | 70

I tried things with GROUP BY, HAVING and COUNT(...) > 1, but I didn't get to a useful result. Is this even solvable with (Open)SQL?

Really looking forward to your help! Please use my SQLFiddle (http://sqlfiddle.com/#!2/f5d1f) to try around.

Not sure what is wrong, but... at the first look, all records are unique, so HAVING COUNT(*) does not help. Try to remove anln2 from select statement and check it again. — Maciej Los, Jan 12 '15 at 17:38

K B · Accepted Answer · 2015-06-30T10:35:03.493

After selecting the entries with anln2 <> 0, I worked on with an internal table.

First I sorted the selected result like this:

 SORT gt_internaltable BY anln1 anln2 datum psp przt.

Then I looped over the internal table and removed all the double entries...

 LOOP AT gt_internaltable INTO gs_tablerow.
    AT NEW anln1.
      CLEAR g_count.
    ENDAT.

    g_count = g_count + 1.

    AT END OF anln1.
      IF g_count > 1. " delete double entries
        DELETE gt_internaltable WHERE anln1 = gs_tablerow-anln1
                         AND   anln2 = gs_tablerow-anln2
                         AND   datum = gs_tablerow-datum
                         AND   psp = gs_tablerow-psp
                         AND   przt = gs_tablerow-przt.
      ENDIF.
    ENDAT.
  ENDLOOP.

In the end, a the list of the entries breaching my specification is left over in gt_internaltable.

score 0 · Answer 2 · answered Oct 07 '20 at 15:12

I do not thinks it is achievable through OpenSQL.

Here is how to achieve this using modern ABAP syntax using grouping:

TYPES: BEGIN OF ty_anla,
        anln1 TYPE anln1,
        anln2 TYPE anln2,
        datum TYPE erdat,
        psp   TYPE c LENGTH 8,
        przt  TYPE i,
       END OF ty_anla,
       tty_anla TYPE STANDARD TABLE OF ty_anla WITH NON-UNIQUE KEY primary_key COMPONENTS anln1.

DATA: lt_input TYPE tty_anla,
      lt_output TYPE tty_anla.

lt_output = lt_input = 
VALUE #( ( anln1 = 10000 anln2 = 0 datum = '20140101' psp = 12345678 przt = 60 )
         ( anln1 = 10000 anln2 = 0 datum = '20140101' psp = 11111111 przt = 40 )
         ( anln1 = 10000 anln2 = 1 datum = '20140101' psp = 11111111 przt = 100 )
         ( anln1 = 10000 anln2 = 2 datum = '20140101' psp = 11111111 przt = 100 )
         ( anln1 = 10000 anln2 = 3 datum = '20140101' psp = 22222222 przt = 100 )
         ( anln1 = 10001 anln2 = 0 datum = '20140101' psp = 12312312 przt = 100 )
         ( anln1 = 10001 anln2 = 1 datum = '20140101' psp = 12312312 przt = 100 )
         ( anln1 = 10001 anln2 = 2 datum = '20140101' psp = 12312312 przt = 100 )
         ( anln1 = 10002 anln2 = 0 datum = '20140101' psp = 11111111 przt = 100 )
         ( anln1 = 10003 anln2 = 0 datum = '20140101' psp = 11111111 przt = 100 )
         ( anln1 = 10004 anln2 = 0 datum = '20140101' psp = 11111111 przt = 100 )
         ( anln1 = 10005 anln2 = 0 datum = '20140101' psp = 22222222 przt = 100 )
         ( anln1 = 10005 anln2 = 1 datum = '20140101' psp = 33333333 przt = 100 )
         ( anln1 = 10006 anln2 = 0 datum = '20140101' psp = 11111111 przt = 20 )
         ( anln1 = 10006 anln2 = 0 datum = '20140101' psp = 22222222 przt = 80 )
         ( anln1 = 10006 anln2 = 1 datum = '20140101' psp = 11111111 przt = 30 )
         ( anln1 = 10006 anln2 = 1 datum = '20140101' psp = 11111111 przt = 70 )
         ( anln1 = 10007 anln2 = 0 datum = '20140101' psp = 12345678 przt = 1 )
         ( anln1 = 10007 anln2 = 0 datum = '20140101' psp = 11111111 przt = 99 )
         ( anln1 = 10007 anln2 = 1 datum = '20140101' psp = 12345678 przt = 1 )
         ( anln1 = 10007 anln2 = 1 datum = '20140101' psp = 11111111 przt = 99 )
                                                                  ).

LOOP AT lt_input ASSIGNING FIELD-SYMBOL(<fs_inp>) USING KEY primary_key GROUP BY ( anln1 = <fs_inp>-anln1 index = GROUP INDEX size = GROUP SIZE ) REFERENCE INTO DATA(common_anln1).
LOOP AT GROUP common_anln1 ASSIGNING FIELD-SYMBOL(<fs_member>) GROUP BY ( datum = <fs_member>-datum psp = <fs_member>-psp przt = <fs_member>-przt index = GROUP INDEX size = GROUP SIZE ) REFERENCE INTO DATA(common_key).
 DATA(common_key_size) = common_key->*-size.
 EXIT.
ENDLOOP.
 CHECK common_anln1->*-size = common_key_size.
 DELETE lt_output WHERE anln1 = common_anln1->*-anln1.
ENDLOOP.

Here we group first by ANLN1 key and within these groups we then check datum+psr+psp key so that the group sizes are equal. This means all ANLN1s possess the same key.

In the outcome in the lt_output you will see the desired result:

  10000   0 2014-01-01 12345678 60 
  10000   0 2014-01-01 11111111 40 
  10000   1 2014-01-01 11111111 100 
  10000   2 2014-01-01 11111111 100 
  10000   3 2014-01-01 22222222 100 
  10005   0 2014-01-01 22222222 100 
  10005   1 2014-01-01 33333333 100 
  10006   0 2014-01-01 11111111 20 
  10006   0 2014-01-01 22222222 80 
  10006   1 2014-01-01 11111111 30 
  10006   1 2014-01-01 11111111 70 
  10007   0 2014-01-01 12345678 1 
  10007   0 2014-01-01 11111111 99 
  10007   1 2014-01-01 12345678 1 
  10007   1 2014-01-01 11111111 99

10007 is here because it is not a correct line as per your definition All entries for anln1 + anln2 should repeat their combination of datum, psp and przt. Different 10007 anln2 values have different psp values.

Nested grouping in internal table

2 Answers2