checking Not in operator

[Former Member]

Hi experts

1. is it possible to use NOT IN operator

for e.g if i check few condition using ( IN operator ) and at the

same time want to check that is not there in my ( IN operator)

else pls do let me know by saying how can this be done.....

My query

========

Select pernr begda massn from pa0000 into corresponding fields of table it_pa0000

WHERE

MASSN in ('S1', 'S2', 'S3', 'S4', 'S7')

• and massn not in 'S5' " this is wat the condition i want to check and pernr in so_pernr.

Elseif pr_sep = 'X'.

Select pernr begda from pa0000 into corresponding fields of table it_pa0000

WHERE

BEGDA <= SY-DATUM AND

ENDDA >= SY-DATUM AND

MASSN in ('S5')

and pernr in so_pernr.

thanx & regards

Rachel

[Former Member]

write as :

Select pernr begda massn from pa0000 into corresponding fields of table it_pa0000

WHERE

pernr in so_pernr

MASSN in ('S1', 'S2', 'S3', 'S4', 'S7')

and massn NE 'S5' .

or create a range for MASSN ..

populate all the values for MASSN ..

r_massn-sign = 'E'. <-- for all other values pass 'I' ...

r_massn-option = 'EQ'.

r_massn-low = 'S5'.

append r_massn.

in select just use

select ....

where ...

massn in r_massn.

[Former Member]

Hi thanx for the reply

I made the changes as u said but then its getting me

an *error saying*

*The IN operator with "R_MASSN" is followed neither by an internal table*

*nor by a value list.*

DATA: s_massn TYPE RANGE OF CHAR10,

r_massn LIKE LINE OF s_massn.

r_massn-sign = 'E'. " <-- for all other values pass 'I' ...

r_massn-option = 'EQ'.

r_massn-low = 'S5'.

append r_massn to s_massn.

Select pernr begda massn from pa0000 into corresponding fields of table it_pa0000

WHERE

MASSN in ('S1', 'S2', 'S3', 'S4', 'S7')

and massn in r_massn ---> error and pernr in so_pernr.

[GauthamV]

Declare ranges like this.

Ranges: r_massn for pa0000-massn.

Select pernr begda massn from pa0000 into corresponding fields of table it_pa0000
WHERE
MASSN in ('S1', 'S2', 'S3', 'S4', 'S7')
and massn in r_massn.

[Former Member]

You can try what I have suggest above or do the below changes in the code

DATA: s_massn TYPE RANGE OF CHAR10,

r_massn LIKE LINE OF s_massn.

r_massn-sign = 'E'. " <-- for all other values pass 'I' ...

r_massn-option = 'EQ'.

r_massn-low = 'S5'.

append r_massn to s_massn.

Select pernr begda massn from pa0000 into corresponding fields of table it_pa0000

WHERE

MASSN in ('S1', 'S2', 'S3', 'S4', 'S7')

and massn in s_massn ---> error and pernr in so_pernr.

[Former Member]

Everybody is suggesting to use Ranges and i find thats the better solution for my requirment

thanx all ....

but i get an error message as

The IN operator with "S_MASSN" is followed neither by an internal table

nor by a value list...

can pls anybody tell me wats this error ??? and how to rectify it

elect pernr begda massn from pa0000 into corresponding fields of table it_pa0000

WHERE MASSN in ('S1', 'S2', 'S3', 'S4', 'S7')

and massn in s_massn -

---

> this is where i get the error

and pernr in so_pernr.

thanx

Rachel

[former_member222860]

Hi,

Ok'

use like this,

tabels: pa0000.

data: BEGIN OF it_pa0000 OCCURS 0.
        include structure pa0000.
data: end of it_pa0000.

select-options: so_pernr for pa0000-pernr.

select pernr begda massn from pa0000 into corresponding fields of table it_pa0000
WHERE massn IN ('S1', 'S2', 'S3', 'S4', 'S7')
  and massn NOT IN ('S5')
  and pernr in so_pernr.

LOOP AT it_pa0000.
WRITE:/ it_pa0000-PERNR, it_pa0000-begda, it_pa0000-MASSN.
ENDLOOP.

thanks

Edited by: Mahesh Reddy on Feb 18, 2009 10:16 AM

[Former Member]

Hi

thanx for helping out

I just want to explain my criteria once again

for eg

16.10.2006 31.12.9999 S5 separated

14.10.2006 15.10.2006 s2 promotion

18.09.2006 13.10.2006 s1 new hire

in this,, thou it has S1 in it, that shud not be displayed

since it has s5....

if it comes across s5 it shud not list the s1 .

The logic is since he got separated(s5) , he shud not be listed .

that y i have used the select and if else condition

if active employee means.....\

if pr_reg = 'X'.

Select pernr begda massn from pa0000 into corresponding fields of table it_pa0000

WHERE

MASSN in ('S1', 'S2', 'S3', 'S4', 'S7')

and pernr in so_pernr.

Elseif pr_sep = 'X'.

Select pernr begda from pa0000 into corresponding fields of table it_pa0000

WHERE

BEGDA <= SY-DATUM AND

ENDDA >= SY-DATUM AND

MASSN in ('S5')

and pernr in so_pernr.

Endif.

i have also tried giving

Select pernr begda massn from pa0000 into corresponding fields of table it_pa0000

WHERE

• MASSN in ('S1', 'S2', 'S3', 'S4', 'S7')

massn not in ('S5')

and pernr in so_pernr.

but since it comes across s1 there it gets listed ......

[Former Member]

Hi.

I think u will have to further process ur internal table in which u are getting data by SELECT statement.

loop at it_pa0001 into wa_it_pa0001.
  "here u make logic to delete the data from ur internal tabe
endloop.

[Former Member]

thank u so much .....

i think this wud be better for me

Loop at it_pa0001 into wa_pa0001.

wa_final-abkrs = wa_pa0001-abkrs.

read table it_pa0000 into wa_pa0000 with key pernr = wa_pa0001-pernr ." massn = 'S1'.

delete massn from where massn = 'S1' and massn <> 'S5'.

IF sy-subrc = 0.

wa_final-pernr = wa_pa0000-pernr.

wa_final-begda = wa_pa0000-begda.

Endif.

this is how i think the logic fits but its syntatically wrong i think....can u help me out

thanx in advance

[dev_parbutteea]

Hi,

Loop at it_pa0001 into wa_pa0001.

wa_final-abkrs = wa_pa0001-abkrs.

read table it_pa0000 into wa_pa0000

with key pernr = wa_pa0001-pernr

massn = 'S5'.

IF sy-subrc = 0.

delete it_pa0001 from where massn = 'S1' and massn 'S5'.

continue

else.

wa_final-pernr = wa_pa0000-pernr.

wa_final-begda = wa_pa0000-begda.

Endif.

Regards.

Edited by: Dev Parbutteea on Feb 18, 2009 11:34 AM

[Former Member]

write this code immediately after selecting pernr and massn in it_pa0000.

sort it_pa0000 by pernr massn.
data: pernrvalue like pa0000-pernr.
loop at it_pa0000 into wa_it_pa0000.
  pernrvalue = wa_it_pa0000-pernr.
 
  read table it_pa0000 into wa_it_pa0000 with key pernr = pernrvalue    massn = 'S1' " dnt use and.
  if sy-subrc = 0.
    read table it_pa0000 into wa_it_pa0000 with key pernr = pernrvalue   massn = 'S5'. " dnt use and.which i write here b4
    if sy-subrc = 0.
       " means this pernr have enteries for both S1 and S5 so deleted it.
" but if u want only that pernr's S1 and S5 enteries are deleted then use 
"(  delete it_pa0000 where pernr = pernrvalue and massn = 'S1' and massn = 'S5'.)
       delete it_pa0000 where pernr = pernrvalue. " This pernr's all enteries will be deleted.
    endif.  
endif.
clear: pernrvalue, wa_it_pa0000.
endloop.

after this perform ur rest of work.

Edited by: tahir naqqash on Feb 18, 2009 3:39 PM

Edited by: tahir naqqash on Feb 18, 2009 3:42 PM

Edited by: tahir naqqash on Feb 18, 2009 3:47 PM

[Former Member]

hi thanx for the reply

but u have pasted the code what i have as ERROR

delete pernr from wa_pa0000 where massn = 'S1' and massn <> 'S5'. still its showing error....

can anyone help ??

[dev_parbutteea]

Hi,

sorry i missed the following:

Loop at it_pa0001 into wa_pa0001.

wa_final-abkrs = wa_pa0001-abkrs.

read table it_pa0000 into wa_pa0000

with key pernr = wa_pa0001-pernr

massn = 'S5'.

IF sy-subrc = 0.

delete it_pa0001 from where pernr = wa_pa0000-pernr

continue

else.

wa_final-pernr = wa_pa0000-pernr.

wa_final-begda = wa_pa0000-begda.

Endif.

Regards.

[Former Member]

dnt use and here.

delete pernr from wa_pa0000 where massn = 'S1' and massn 'S5'.

" Delete and

[Former Member]

hi thanx

can you look into this delete stmt once again cos

delete it_pa0001 from where pernr = wa_pa0000-pernr

i changed as

delete pernr from it_pa0001 where pernr = wa_pa0000-pernr.

but then not working

[Former Member]

hi brother.

i have told u before that ist select from pa0000 into it_pa0000.
then sort it_pa0000 by pernr massn.
then loop only on it_pa0000 and delete ur all unnecessary enteries.
then process further ur work

[Former Member]

hi tahir

First of all sorry for troubling hell lot

you can very well look in my code

i have made modifications as u said but then

==============================

sort it_pa0000 by pernr massn.

Loop at it_pa0001 into wa_pa0001.

wa_final-abkrs = wa_pa0001-abkrs.

read table it_pa0000 into wa_pa0000 with key pernr = wa_pa0001-pernr. massn ='S5'.

*delete it_pa0000 where pernr = wa_pa0000-pernr.

delete pernr from wa_pa0000 where massn = 'S1' and massn 'S5'

IF sy-subrc = 0.

wa_final-pernr = wa_pa0000-pernr.

wa_final-begda = wa_pa0000-begda.

Endif.

read table it_pa0002 into wa_pa0002 with key pernr = wa_pa0001-pernr.

IF sy-subrc = 0.

wa_final-vorna = wa_pa0002-vorna.

wa_final-nachn = wa_pa0002-nachn.

Endif.

read table it_pa0587 into wa_pa0587 with key pernr = wa_pa0001-pernr.

IF sy-subrc = 0.

wa_final-eepfn = wa_pa0587-eepfn.

Endif.

read table it_pa0588 into wa_pa0588 with key pernr = wa_pa0001-pernr.

IF sy-subrc = 0.

wa_final-esino = wa_pa0588-esino.

Endif.

read table it_pa0021 into wa_pa0021 with key pernr = wa_pa0001-pernr .

IF sy-subrc = 0.

wa_final-favor = wa_pa0021-favor.

Endif.

read table it_pa0008 into wa_pa0008 with key pernr = wa_pa0001-pernr.

IF sy-subrc = 0.

wa_final-bet02 = wa_pa0008-bet02.

Endif.

Append wa_FINAL to IT_FINAL.

Endloop.

[Former Member]

i have made some changes in code ad mentioned it.

but i am still not clear about ur problem. that actually what u want. if u can tell me just a scenario hope i will help u more better.

dnt hesitate to tell ur problem

u are welcome always.

LOOP AT it_pa0001 INTO wa_pa0001.
  pernrval = pa0001-pernr. " new line
  wa_final-abkrs = wa_pa0001-abkrs.
  READ TABLE it_pa0000 INTO wa_pa0000 WITH KEY pernr = pernrval  massn ='S1'. " changed line , AND removed
  IF sy-subrc = 0.
    READ TABLE it_pa0000 INTO wa_pa0000 WITH KEY pernr = pernrval  massn ='S5'.
    IF sy-subrc = 0.
      DELETE it_pa0000  WHERE pernr = pernrval AND massn = 'S1' OR massn = 'S5' .
    ENDIF.
  ENDIF.
  wa_final-pernr = wa_pa0000-pernr.
  wa_final-begda = wa_pa0000-begda.
ENDIF.
READ TABLE it_pa0002 INTO wa_pa0002 WITH KEY pernr = wa_pa0001-pernr.
IF sy-subrc = 0.
  wa_final-vorna = wa_pa0002-vorna.
  wa_final-nachn = wa_pa0002-nachn.
ENDIF.
READ TABLE it_pa0587 INTO wa_pa0587 WITH KEY pernr = wa_pa0001-pernr.
IF sy-subrc = 0.
  wa_final-eepfn = wa_pa0587-eepfn.
ENDIF.


READ TABLE it_pa0588 INTO wa_pa0588 WITH KEY pernr = wa_pa0001-pernr.
IF sy-subrc = 0.
  wa_final-esino = wa_pa0588-esino.
ENDIF.

READ TABLE it_pa0021 INTO wa_pa0021 WITH KEY pernr = wa_pa0001-pernr .
IF sy-subrc = 0.
  wa_final-favor = wa_pa0021-favor.
ENDIF.

READ TABLE it_pa0008 INTO wa_pa0008 WITH KEY pernr = wa_pa0001-pernr.
IF sy-subrc = 0.

  wa_final-bet02 = wa_pa0008-bet02.
ENDIF.

APPEND wa_final TO it_final.
ENDLOOP.

[GauthamV]

Clear all the workarea's after Append wa_FINAL to IT_FINAL statement.

ex: clear wa_final,

clear wa_p0001.........

[Former Member]

hi

thax alot...................

i have come out with another logic

where i think if check for the End date value EQUALS '31.12.9999'i may get the ACTIVE emp

cos when end date is 31.12.9999 means that person is active ........and thus ommitting NOT IN operator

or in LOOP or DElete inside loop

Pls do help me whether my Select for this requirement is right???

cos still i face the same problem

Select pernr begda massn from pa0000 into corresponding fields of table it_pa0000

WHERE

Endda = '31.12.9999' and

MASSN in ('S1', 'S2', 'S3', 'S4', 'S7')

and pernr in so_pernr.

[Former Member]

yups Rachel!

this is right

if endda is 31.12.999 then this employee will be active

and make correct ur select

Select pernr begda massn from pa0000 into corresponding fields of table it_pa0000

WHERE

Endda = '99991231' " Date is given like this

and

MASSN in ('S1', 'S2', 'S3', 'S4', 'S7')

and pernr in so_pernr.

[Former Member]

thanx alot ....the problem is solved now

and thanx everybody for your valuable time and solutions

Rachel

[Former Member]

hi,

please see this thread for help

thanks

[Former Member]

Hi Rachel ,

It possible to use NOT IN operator.

you need to write two select query and according to your requirement codes will be executed.

Regards

Pinaki

[Former Member]

thanx not in operator doesnt works for me

[Former Member]

Have you tried this:

Select pernr begda massn from pa0000 into corresponding fields of table it_pa0000

WHERE

MASSN in ('S1', 'S2', 'S3', 'S4', 'S7') and

MASSN NOT IN ('S5').

[Former Member]

HI Rachel,

For that you need to create the Ranges

try this:

ranges: r_MASSN   type MASSN .

r_MASSN-sign = 'I'.
r_MASSN-option = 'EQ'.
r_MASSN-low     = 'S1'.
r_MASSN-high   = ''.
append r_MASSN.
clear r_MASSN.

r_MASSN-sign = 'I'.
r_MASSN-option = 'EQ'.
r_MASSN-low     = 'S2'.
r_MASSN-high   = ''.
append r_MASSN.
clear r_MASSN.

r_MASSN-sign = 'I'.
r_MASSN-option = 'EQ'.
r_MASSN-low     = 'S3'.
r_MASSN-high   = ''.
append r_MASSN.
clear r_MASSN.

r_MASSN-sign = 'I'.
r_MASSN-option = 'EQ'.
r_MASSN-low     = 'S4'.
r_MASSN-high   = ''.
append r_MASSN.
clear r_MASSN.

r_MASSN-sign = 'I'.
r_MASSN-option = 'NE'.    "Check this for S5
r_MASSN-low     = 'S5'.
r_MASSN-high   = ''.
append r_MASSN.
clear r_MASSN.

r_MASSN-sign = 'I'.
r_MASSN-option = 'EQ'.
r_MASSN-low     = 'S7'.
r_MASSN-high   = ''.
append r_MASSN.
clear r_MASSN.



your query 

Select pernr begda from pa0000 into corresponding fields of table it_pa0000
WHERE
BEGDA <= SY-DATUM AND
ENDDA >= SY-DATUM AND
MASSN in r_MASSN.   "here you will get your required data.
and pernr in so_pernr.

Regards!

[Former Member]

hi thanx for the reply

i tried but i am getting a error as

"FOR ... ." or "FOR ... OCCURS ... ." expected after "R_MASSN". near

ranges: r_MASSN type MASSN .

i dont know this ...and iam just trying ...can u pls guide me

thanx alot

[GauthamV]

Tables PA0000.

Ranges : r_massn for pa0000-massn.

[Former Member]

Just a slight modification to Prasant's code.

Instead of using option 'NE', use the sign 'E'.

....
r_MASSN-sign = 'E'.   " Exclude this entry
r_MASSN-option = 'EQ'.    
r_MASSN-low     = 'S5'.
r_MASSN-high   = ''.
append r_MASSN.
clear r_MASSN.
....

regards,

Advait

[Former Member]

I think you there should not any problem using NOT IN.

Try this:

Select pernr begda massn from pa0000 into corresponding fields of table it_pa0000

WHERE

MASSN in ('S1', 'S2', 'S3', 'S4', 'S7') and

MASSN NOT IN ('S5').

[Former Member]

sorry this doesnt works

[Former Member]

Hi Rachel,

This would help..kindly chk..

Select pernr begda massn from pa0000 
into corresponding fields of table it_pa0000
WHERE  MASSN in ('S1', 'S2', 'S3', 'S4', 'S7')
and massn NE S5.
"chk return code sy-subrc...

*if S5 is a select option then
s5-sign = 'E'.
modify s5 transporting sign where sign eq 'I'.
 "by this S5 contains only excluded values i.e 'not in' ..thn perform select query..as

Select pernr begda massn from pa0000 
into corresponding fields of table it_pa0000
WHERE  MASSN in ('S1', 'S2', 'S3', 'S4', 'S5','S7')

Regards,

Mdi.Deeba

Edited by: Mdi.Deeba on Feb 18, 2009 7:53 AM

Edited by: Mdi.Deeba on Feb 18, 2009 7:53 AM

[Former Member]

This is a sample code done to exclude specific order nos. Jus replace the same with S5(in ur case) and it will work. Create the corresponding internal table according to the data type of S5.

Your problem will be solved

RANGES: r_ordno FOR wa_ordno-ordno.

LOOP AT it_ordno INTO wa_ordno. "Say you have ord no in tab it_ordno

r_ordno-sign = 'I'.

r_ordno-option = 'EQ'.

r_ordno-low = wa_ordno-ordno.

APPEND r_ordno.

ENDLOOP.

SELECT * FROM AUFK

INTO TABLE it_tab

WHERE ordno NOT IN r_ordno.

Regards,

Priti.

[Former Member]

Hi,

Use the select query given below this works...

Select pernr begda massn from pa0000 into corresponding fields of table it_pa0000
WHERE NOT MASSN in 'S5'.

Regards,

Siddarth

[Former Member]

thanx but

neither

and not massn in ('S5')

or not in massn ('s5') works for me

[Former Member]

is ur problem solved.

If not then do this.

RANGES: r_massn FOR PA0000-massn.

r_massn-sign = 'I'.

r_massn-option = 'EQ'.

r_massn-low = 'S5'.

APPEND r_massn.

Select pernr begda massn from pa0000 into corresponding fields of table it_pa0000

WHERE

MASSN in ('S1', 'S2', 'S3', 'S4', 'S7') and

MASSN NOT IN r_massn.

Edited by: priti suryawanshi on Feb 18, 2009 8:11 AM

[former_member222860]

Hi,

Try like this:

SELECT pernr begda massn from pa0000 
 INTO corresponding fields of table it_pa0000
WHERE MASSN in ('S1') OR MASSN in ('S2') OR MASSN in ('S3') 
   OR MASSN in ('S4') OR MASSN in ('S7')
  AND MASSN NOT IN ('S5').

And you need not declare any range.

thanks\

Mahesh

Edited by: Mahesh Reddy on Feb 18, 2009 8:18 AM

Edited by: Mahesh Reddy on Feb 18, 2009 8:18 AM

[Former Member]

thanx priti

i tried by modifying ur code like below but still getting an errot saying

The IN operator with "R_MASSN" is followed neither by an internal table

nor by a value list.

types : begin of ty_test,

massn type pa0000-massn,

end of ty_test.

Data: it_massn type table of ty_test,

wa_massn type ty_test.

RANGES: r_massn FOR wa_massn.

LOOP AT it_massn INTO wa_massn. "Say you have ord no in tab it_ordno

r_massn-sign = 'I'.

r_massn-option = 'EQ'.

r_massn-low = wa_massn-massn.

APPEND r_massn.

ENDLOOP.

Select pernr begda massn from pa0000 into corresponding fields of table it_pa0000

WHERE

MASSN in ('S1', 'S2', 'S3', 'S4', 'S7')

and massn not in r_massn

and pernr in so_pernr.