Skip to Content
-2

how to use IN operator in INNER JOIN

Feb 18, 2017 at 11:43 AM

162

avatar image

hello all,

how do i use IN operator in INNER JOIN,

inner-join.jpg (94.7 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
kamil stêrk Feb 18, 2017 at 01:00 PM
1

Hello,

"IN" operation ,you must use it after where condition.

select a~pspnr
       a~posid
       a~objnr
       a~vernr
       a~verna
       a~fkstl
       a~prctr
       a~werks
       into corresponding fields of table it_prps
       from prps as a
       inner joing jest as b
       on a~objnr = b~objnr
       where a~werks eq p_werks and
             a~belkz eq 'X' and
             a~loevm ne 'X' and
             b~stat in ('I0001','E0008') and
             b~inact eq  ''.
Show 9 Share
10 |10000 characters needed characters left characters exceeded

Hi kamil,

thanks for you reply,

my intention is to get records which have status both I0001 and E0008,(i.e system status = I0001 and users status = E0008)

in my case this is not happening,

where i went wrong.

thanks and regards,

vinay.

0

Hello VINAY REDDY,

If you follow the code from Kamil, the result will be pspnr which have stat = 'I0001' or 'E0008' not both 'I0001' and 'E0008'.

My solution for this case is making 2 selection steps:
Select a~pspnr
...
into table it_a
where a~werks eq p_werks and a~belkz eq 'X' and a~loevm ne 'X' and b~stat eq 'I0001' and b~inact eq ''.

Select a~pspnr ... into table it_b where a~werks eq p_werks and a~belkz eq 'X' and a~loevm ne 'X' and b~stat eq 'E0008' and

b~inact eq ''.

LOOP AT it_a into ls_a.
read table lt_b with key pspnr = ls_a-pspnr.
if sy-subrc = 0.
append to ..... into it_prps (table contains records which have status both I0001 and E0008)

endif

ENLOOP

Regards,
Thanh.

0

hi vinay,

i think,No record exists for where condition in the jest table.

can you check jest table for I0001 and E0008 and inact = '' condition.

0

Hi kamil,

records does exist, see below screen shots,

i passed the objnr to jest table

it has both E0008 and I0001 status ..

prps.jpg (37.0 kB)
jest.jpg (56.3 kB)
0

Then it's not matching the PRPS record.

0

hi matthew,

in the selection screen i have declared

parameters : p_werks type prps-werks.

and gave plant 4010 while F8

and the above said WBS does exists in plant 4010.

plant.jpg (107.2 kB)
prps.jpg (47.0 kB)
0

And is BELKZ equal to 'X' and LOEVM not equal to 'X'?

Also check for conversion routines on these fields - that can also produce a mismatch that you might not see through SE16.

The fact is your data doesn't match your selection. The only other explanation is that SQL has suddenly stopped working, which seems a little unlikely.

Another thought that occurs to me is that we might not understand what you are expecting. Please post exactly what you are expecting to have inside it_prps and what you are getting.

0

hi mathew,

i need all records that have status both 'I0001' and 'E0008'.

but if i write code like this

select a~pspnr
a~posid
a~objnr
a~vernr
a~verna
a~fkstl
a~prctr
a~werks
into corresponding fields of table it_prps
from prps as a
inner join jest as b
on a~objnr = b~objnr
where a~werks eq p_werks
and a~belkz eq'X'
and a~loevm ne'X'
and b~stat in('I0001' , 'E0008')
and b~inact eq ' '.

i am getting records which have either I0001 or E0008 but not both.

0
Show more comments
Further commenting has been locked.
kamil stêrk Feb 20, 2017 at 07:01 AM
0

hi vinay,

try this code, after add prps table where condition.

if return records,you problem prps where condition.

select a~pspnr
       a~posid
       a~objnr
       a~vernr
       a~verna
       a~fkstl
       a~prctr
       a~werks
       into corresponding fields of table it_prps
       from prps as a
       inner join jest as b
       on a~objnr = b~objnr
       where  
             b~stat in ('I0001','E0008') and
             b~inact eq  ''.
Share
10 |10000 characters needed characters left characters exceeded
Raymond Giuseppi
Feb 20, 2017 at 07:28 AM
0

Use TWO joins (or better two subqueries) as you actually want to check existence of two records.

Regards,
Raymond

Show 4 Share
10 |10000 characters needed characters left characters exceeded

hi raymond,

how do i use sub query as this is not a static value,

0

select a~pspnr
       a~posid
       a~objnr
       a~vernr
       a~verna
       a~fkstl
       a~prctr
       a~werks
       into corresponding fields of table it_prps
       from prps
       where a~werks = p_werks
         AND a~belkz = 'X'
         AND a~loevm = ''
         AND exists( SELECT objnr FROM jest WHERE objnr = prps~objnr AND stat = 'I0001' AND inact = '' )
         AND exists( SELECT objnr FROM jest WHERE objnr = prps~objnr AND stat = 'E0008' AND inact = '' ).
0

getting above error.

select.jpg (96.0 kB)
0

Insert a space character between SELECT and (.

0
Further commenting has been locked.
kamil stêrk Feb 20, 2017 at 07:33 AM
0

hi vinay,

i get the records my system.

i think,you should go to step by step.

1-select prps table where conditon

2-select jest table where conditon

3-loop at compare 2 tables

4-write result

after this senerio,if ok,try again join 2 tables.


screen1.png (32.5 kB)
Show 1 Share
10 |10000 characters needed characters left characters exceeded

hi kamil,

no still its not working..

0
Further commenting has been locked.