Conditional itab grouping alike HAVING clause in SQL

Question

This is a follow-up question for this one but instead of aggregation I want to process groups based on some condition and cannot figure out the proper syntax for this. I need to exclude groups which contain documents with status "deleted", if at least one of the members of the group has this status.

I tried so far GROUP...WITHOUT MEMBERS, LOOP...FOR GROUPS, REDUCE and this is the solution I end up with

DATA(lt_valid_doc) = VALUE tt_struct( 
      FOR ls_valid IN VALUE tt_struct( 
             FOR GROUPS <group_key> OF <wa> IN lt_ilot
             GROUP BY ( guid = <wa>-guid guid2 = <wa>-guid2 ) ASCENDING
             LET not_deleted = REDUCE #( INIT valid TYPE t_ref_s_struct
                                         FOR <m> IN GROUP <group_key>
                                         NEXT valid = COND #( 
                                               WHEN valid IS NOT BOUND OR <m>-stat = 'I1040' 
                                               THEN REF #( <m> ) ELSE valid ) )
             IN ( not_deleted->* ) )
      WHERE ( status NE 'I1040' ) 
      ( ls_valid ) ).

However, this solution seems redundant to me (I1040 filter indicated twice). Is there any syntax that allows doing this in one statement (REDUCE, GROUP or whatever) without constructing nested table on-the-fly and filtering it like I am doing now?

If I use WHERE condition on all of the above statements (GROUP...WITHOUT MEMBERS, LOOP...FOR GROUPS and REDUCE) it only filters base lines for grouping not the groups itself. I need somewhat similar to HAVING in SQL.

UPDATE OK, here is real-life compilable example based on BSEG table. The task is find only unreveresed docs, i.e.to exclude all docs with reversed (XNEGP = true) lines.

TYPES: BEGIN OF t_s_bseg,
         bukrs  TYPE bseg-bukrs,
         belnr  TYPE bseg-belnr,
         gjahr  TYPE bseg-gjahr,
         buzei  TYPE bseg-buzei,
         xnegp  TYPE bseg-xnegp,
       END OF t_s_bseg,
       tt_bseg TYPE SORTED TABLE OF t_s_bseg WITH EMPTY KEY.
TYPES: t_ref_s_bseg TYPE REF TO t_s_bseg.

DATA(lt_valid_fi_doc) = VALUE tt_bseg( 
  FOR ls_valid IN VALUE tt_bseg( 
         FOR GROUPS <group_key> OF <wa> IN lt_bseg
         GROUP BY ( bukrs = <wa>-bukrs belnr = <wa>-belnr gjahr = <wa>-belnr ) ASCENDING
         LET not_reversed = REDUCE #( INIT valid TYPE t_ref_s_bseg
                                     FOR <m> IN GROUP <group_key>
                                     NEXT valid = COND #( 
                                           WHEN valid IS NOT BOUND OR <m>-xnegp = abap_true 
                                           THEN REF #( <m> ) ELSE valid ) )
         IN ( not_reversed->* ) )
  WHERE ( xnegp NE abap_true ) 
  ( ls_valid ) ).

Input lines

bukrs belnr       gjahr buzei xnegp
1000  0100000001  2019  1 
1000  0100000001  2019  2
1000  0100000003  2019  1
1000  0100000003  2019  2
1000  0100000004  2019  1
1000  0100000004  2019  2     X

Doc 0100000004 has reversed line so result should be

bukrs belnr       gjahr buzei xnegp
1000  0100000001  2019   
1000  0100000003  2019

It would help a lot if this was a compilable example with all the dependencies provided. — Jagger, May 23 '19 at 06:05
@Jagger added FI-based example, my case is from SRM so hardly could be reproduced by everybody — Suncatcher, May 23 '19 at 15:27

score 1 · Answer 1 · answered May 23 '19 at 10:03

Here's a solution which doesn't repeat the selection, but one question remains, is that really "better"?

The solution is based on generating an empty line if the group of lines contains one line with status 'I1040', instead of keeping the unwanted line. I'm not sure, but maybe another similar solution could keep the reference to the line (not_deleted), plus adding an auxiliary variable to know whether the reference is to keep or not. I found it more intuitive to use table indexes (INDEX INTO), but that might not work if tt_struct is a hashed table type.

I provide the code with an ABAP Unit Test so that you can quickly try it yourself.

CLASS ltc_main DEFINITION FOR TESTING
      DURATION SHORT RISK LEVEL HARMLESS.
  PRIVATE SECTION.
    METHODS test FOR TESTING.
    METHODS cut.
    TYPES : BEGIN OF ty_struct,
              guid TYPE string,
              stat TYPE string,
            END OF ty_struct,
            tt_struct      TYPE STANDARD TABLE OF ty_struct WITH EMPTY KEY,
            t_ref_s_struct TYPE REF TO ty_struct.
    DATA: lt_ilot      TYPE tt_struct,
          lt_valid_doc TYPE tt_struct.
ENDCLASS.

CLASS ltc_main IMPLEMENTATION.
  METHOD cut.
    lt_valid_doc = VALUE #(
          FOR ls_valid IN VALUE tt_struct(
                 FOR GROUPS <group_key> OF <wa> IN lt_ilot
                 GROUP BY ( guid = <wa>-guid ) ASCENDING
                 LET x1 = REDUCE #(
                        INIT x2 = 0
                        FOR <m> IN GROUP <group_key> INDEX INTO x3
                        NEXT x2 = COND #(
                              WHEN <m>-stat = 'I1040' THEN -1
                              ELSE COND #( WHEN x2 <> 0 THEN x2 ELSE x3 ) ) )
                 IN ( COND #( WHEN x1 <> -1 THEN lt_ilot[ x1 ] ) ) )
          WHERE ( table_line IS NOT INITIAL )
          ( ls_valid ) ).
  ENDMETHOD.

  METHOD test.

    lt_ilot = VALUE #(
        ( guid = 'A' stat = 'I1000' )
        ( guid = 'A' stat = 'I1040' )
        ( guid = 'B' stat = 'I1020' )
        ( guid = 'C' stat = 'I1040' )
        ( guid = 'D' stat = 'I1040' )
        ( guid = 'D' stat = 'I1000' ) ).

    cut( ).

    cl_abap_unit_assert=>assert_equals( act = lt_valid_doc
          exp = VALUE tt_struct( ( guid = 'B' stat = 'I1020' ) ) ).

  ENDMETHOD.
ENDCLASS.

Wow! I am not very experienced with REDUCE syntax and that's a novelty for me that it can construct lines with conditions recursively, like this `NEXT x2 = COND #( WHEN stat =... THEN -1 ELSE COND #( WHEN x2 <> 0 THEN x2 )`. Overall, I think your solution is less readable and less concise than mine)) In spite it filters status only once, nevertheless it wasn't able to get rid of nested table construction — Suncatcher, May 27 '19 at 13:23
The `x1 <> -1` condition is literally the same as second status filter, so no gain at all, neither in performance no in readability. — Suncatcher, May 27 '19 at 13:28

futu · Answer 2 · 2019-05-27T19:28:40.460

I really hope that was some kind of personal test case, and you do not use this coding in a productive environment. If I would have to understand what you want to achieve looking only at the coding, I would hate you ;).

The key to easily solve this problem is sorting the table so that the delete condition is always in the first row of the group you want to process:

Solution1 with output of unique list:

DATA: lt_bseg TYPE STANDARD TABLE OF t_s_bseg.

SORT lt_bseg BY belnr xnegp DESCENDING.
DELETE ADJACENT DUPLICATES FROM lt_bseg COMPARING belnr.
DELETE lt_bseg WHERE xnegp = abap_true.

Solution2

with output of a non-unique list:

DATA: lt_bseg TYPE STANDARD TABLE OF t_s_bseg,
      lf_prev_belnr TYPE belnr,
      lf_delete TYPE char1.

SORT lt_bseg BY belnr xnegp DESCENDING.

LOOP AT lt_bseg ASSIGNING FIELD-SYMBOL(<ls_bseg>).

    IF <ls_bseg>-belnr <> lf_prev_belnr.
        lf_delete = <ls_bseg>-xnegp.
        lf_prev_belnr = <ls_bseg>-belnr.
    ENDIF.

    IF lf_delete = abap_true.
        DELETE lt_bseg.
    ENDIF.

ENDLOOP.

`I would hate you` I don't really care :) It works and that's the main thing. — Suncatcher, May 27 '19 at 13:04
And btw, your solution1 works only for the primitive case with binary field like XNEGP, the main example with string-based status I1040 is not working with your sorting. — Suncatcher, May 27 '19 at 13:11

score -2 · Answer 3 · answered May 23 '19 at 12:17

the following solution might not be the prettiest one, but its simple. I's easier to remove a whole group if one member meets a condition than to add a whole group if all of them fail the condtition. Just an idea.

TYPES : BEGIN OF ty_struct,
          guid TYPE string,
          stat TYPE string,
        END OF ty_struct,
        tt_struct TYPE STANDARD TABLE OF ty_struct WITH EMPTY KEY.


DATA(lt_ilot) = VALUE tt_struct(
    ( guid = 'A' stat = 'I1000' )
    ( guid = 'A' stat = 'I1040' )
    ( guid = 'B' stat = 'I1020' )
    ( guid = 'C' stat = 'I1040' )
    ( guid = 'D' stat = 'I1040' )
    ( guid = 'D' stat = 'I1000' )).

LOOP AT lt_ilot INTO DATA(ls_ilot) WHERE stat = 'I1040'.
  DELETE lt_ilot WHERE guid = ls_ilot-guid.
ENDLOOP.

Your solution doesn't include grouping, several lines with the same guid are possible. — Suncatcher, May 23 '19 at 14:19
If I have understood correctly the OP's request, the expected result is the unique line "B I1020" (all lines from groups with I1040 are to be removed). — Sandra Rossi, May 23 '19 at 14:19

Conditional itab grouping alike HAVING clause in SQL

3 Answers3