Select 5 most recent notifications with inactive status within single SELECT?

Question

I need to SELECT the 5 most recent notifications linked to an equipment but I also need to check that status DLFL (I0076 - deletion flag) is not active OR doesn't exist.

I think it should be pretty simple but I'm a bit confused with the fact that the status DLFL has 3 possible options.

Exists in JEST table with inactive flag INITIAL.
Exists in JEST table with inactive flag = "X". --> Valid record
Does not exist in JEST table. --> Valid record

I only managed to get the last 5 by selecting all the history notifications and then removing the ones that have the status DLFL active like in the code below, but this means that I'm selecting hundreds of lines from last 20 years to only use 5.

How would you avoid this?

SELECT qmnum, erdat, stat FROM viqmel
  LEFT OUTER JOIN jest ON jest~objnr = viqmel~objnr
                      AND jest~inact = @abap_false
                      AND jest~stat  = 'I0076'
  WHERE viqmel~equnr = @equi "Input parameter
    AND viqmel~kzloesch = @abap_false
  ORDER BY erdat DESCENDING
  INTO TABLE @DATA(equi_notifs).

DATA equi_notifs_valid LIKE equi_notifs.
LOOP AT equi_notifs ASSIGNING FIELD-SYMBOL(<equi_notif>) WHERE stat IS INITIAL.
  equi_notifs_valid = VALUE #( BASE equi_notifs_valid ( <equi_notif> ) ).
  IF lines( equi_notifs_valid ) >= 5.
    EXIT.
  ENDIF.
ENDLOOP.

Suncatcher Suncatcher · Accepted Answer · 2019-11-11T23:38:35

Your second loop has no sense since you join only those JEST lines that has inactive I0076 status, making a logical error so equipment with no status I0076 is not caught at all in your dataset and your loop condition is never met.

The solution is to move status condition into WHERE and make filtering there.

Why not to use NOT EXISTS construction?

SELECT qmnum, erdat, stat FROM viqmel
  LEFT OUTER JOIN jest ON jest~objnr = viqmel~objnr
 WHERE viqmel~kzloesch = @abap_false
   AND ( jest~inact = @abap_true
   AND   jest~stat  = 'I0076' ) OR
  NOT EXISTS ( SELECT * FROM jest WHERE jest~objnr = viqmel~objnr AND jest~stat = 'I0076' )
  ORDER BY erdat DESCENDING
  INTO TABLE @DATA(equi_notifs)
  UP TO 5 ROWS.

Also addition UP TO N ROWS eliminates your ugly loop.

BTW, you mentioned you need only undeleted equipment lines, didn't you? Why you used abap_false with inact, probably it should be abap_true?

Select 5 most recent notifications with inactive status within single SELECT?

1 Answers