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.