Select query from PRPS and JEST doesn't retrieve expected result

[airwave]

Dear Experts,

do you have an idea why I am still getting results with stat= 'E0012' and inact = SPACE although

as per below mentioned Select query they should be clearly delimited (see EXISTS Command)

I am expecting hits with:

stat= 'E0012' and inact = X

and NOT

stat= 'E0012' and inact = SPACE

<strong>
  TYPES: BEGIN OF ty_s_prps,
           posid TYPE ps_posid,
           post1 TYPE ps_post1,
           psphi TYPE ps_psphi,
           pbukr TYPE ps_pbukr,
           objnr TYPE j_objnr,                              "20160712
         END OF ty_s_prps,

 TYPES: ty_t_prps TYPE TABLE OF ty_s_prps.
 
 DATA:  lt_prps  TYPE ty_t_prps.
 
     SELECT   a~posid
             a~post1
             a~psphi
             a~pbukr                                      
             a~objnr                                       
       INTO TABLE lt_prps
             FROM prps AS a
             INNER JOIN jest AS j ON a~objnr = j~objnr
              WHERE a~belkz EQ lc_on_x            
                AND a~loevm EQ space                    
                AND a~pbukr IN lt_sel_bukrs_range
*               AND a~plakz EQ lc_on_x                                  
                AND j~stat  EQ 'I0002'
                AND j~inact EQ space
                AND a~fakkz EQ lc_on_x
                               AND EXISTS ( SELECT * FROM jest WHERE   stat = 'E0012' AND inact = 'X' 
                                                                       OR stat NE 'E0012').  </strong><br>

Br

Ilhan

[airwave]

Is it not possible to achieve this with ABAP at all?

Requirement:

('stat' I0002 must be present AND 'inact' not equal to “X”) AND (( 'stat' E0012 must not be present) OR ('stat' E0012 is present AND inact equal to “X”))

[airwave]

Hello Raymond,

this returned 0 entries unfortunatelly.

AND NOT EXISTS ( SELECT * FROM jest WHERE stat = 'E0012' AND inact = ' ')

[Sandra_Rossi]

Please use the COMMENT button for comments, asking for complements, adding details, replying to a comment or a proposed solution or to the OP question, etc., ANSWER is only to propose a solution, dixit SAP text at the right of the answer area.

If you want to target someone, if this person has posted an Answer, use the button COMMENT, if this person is the Original Poster of the question he/she will be automatically informed, otherwise copy/paste their hyperlinked name e.g. ilhan.ertas so that the person receives a warning (NB: @ doesn't work/but typing this character will suggest hyperlinked names).

[raymond_giuseppi]

As soon as another status than 'E0012' exists in JEST, the subquery is true cause of the OR in the WHERE clause in the subquery.

If you want to exclude WBS with Customer Status E0012 set, try to use a not exist on record with status E0012 and inact initial.

AND NOT EXISTS ( SELECT * FROM jest WHERE stat = 'E0012' AND inact = ' ')

Else elaborate on your exact query?

[raymond_giuseppi]

From your second "answer" (use Comment not Answer, rea "before answering" at lower right part of screen

You wrote

( 'stat' E0012 must not be present) OR ('stat' E0012 is present AND inact equal to “X”)<br>

If E0012 is present then inact value is initial or 'X' so it's exact same test than my code, so there are only 3 cases

• good - No record
• good - Record inact = 'X'
• bad - Record with inact initial

Selecting good records or excluding bad record should give same result.

Also you should add OBJNR to the subquery

AND NOT EXISTS ( SELECT * FROM jest as s1 WHERE s1~stat = 'E0012' AND s1~inact = ' ' and s1~objnr = a~objnr )
AND EXISTS ( SELECT * FROM jest as s2 WHERE s2~stat = 'I0002' AND s2~inact = ' ' and s2~objnr = a~objnr )<br>