Skip to Content
-1

SELECT: Use condition from another table

Jan 02, 2017 at 12:09 PM

112

avatar image

Hey,

My select statement looks like this right now:

SELECT pernr reinr pdatv pdatb accdt pdvrs abrec FROM ptrv_perio
INTO CORRESPONDING FIELDS OF TABLE lt_ptrv_perio
WHERE ( abrec EQ '2'
AND pdatv GE pa_begda
AND pdatb LE pa_endda )
OR ( abrec EQ '3'
AND accdt GE pa_begda
AND accdt LE pa_endda ).

The problem is that the attribute accdt in my second condition is located in another table. How can I get that inside the select and directly use it?

Thanks for any hints!

10 |10000 characters needed characters left characters exceeded

You might also consider the usage of a subquery in the WHERE clause ...

1
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Salah ZINET Jan 02, 2017 at 12:26 PM
1

Hi Dominic,

you have to use INNER JOIN in your Select statement, that is, in your code:

SELECT pernr reinr pdatv pdatb accdt pdvrs abrec

FROM ptrv_perio INNER JOIN the_other_table ON ptrv_perio~rel_key = the_other_table~rel_key1 and ....

INTO CORRESPONDING FIELDS OF TABLE lt_ptrv_perio
WHERE (ptrv_perio~abrec EQ '2'
ANDptrv_perio~pdatv GE pa_begda
ANDptrv_perio~pdatb LE pa_endda )
OR (ptrv_perio~abrec EQ '3'
AND the_other_table~accdt GE pa_begda
AND the_other_table~accdt LE pa_endda ).

Regards.

Share
10 |10000 characters needed characters left characters exceeded
Salah ZINET Jan 02, 2017 at 02:39 PM
0

Good,

After INNER JOIN you have to use all the fields that make the relationship between the 2 tables,

In my opinion that will be better like that:

SELECTp~pernr p~reinr p~pdatv p~pdatb p~accdt p~pdvrs p~abrec
 FROM ptrv_perio AS p INNER JOIN ptrv_head AS h ON p~MANDT = h~MANDT and p~PERNR = h~PERNR and p~reinr = h~reinr 
 INTO CORRESPONDING FIELDS OF TABLE lt_ptrv_perio
 WHERE(p~abrec EQ'2'ANDp~accdt GE pa_begda
 ANDp~accdt LE pa_endda )OR(p~abrec EQ'3'AND h~dates GE pa_begda
 AND h~dates LE pa_endda ).

Regards.

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

Hey Salah,

Thanks for your help! It worked as intended.

My code now Looks as followed:

 SELECT p~pernr p~reinr p~pdatv p~pdatb p~accdt p~pdvrs p~abrec
 FROM ptrv_perio AS p INNER JOIN ptrv_head AS h ON p~reinr = h~reinr
 INTO CORRESPONDING FIELDS OF TABLE lt_ptrv_perio
 WHERE ( p~abrec EQ '2'
 AND p~accdt GE pa_begda
 AND p~accdt LE pa_endda )
 OR ( p~abrec EQ '3'
 AND h~dates GE pa_begda
 AND h~dates LE pa_endda ).
0

I said : In my opinion that will be better like that:

INNER JOIN ptrv_head AS h ON p~MANDT = h~MANDT and p~PERNR = h~PERNR and p~reinr = h~reinr 

:)

Regards,

0

Yes, definitely use all the fields in the relationship. There is one more field "Sequence number" that appears to be relevant too. That said MANDT does not have to be explicitly declared in the join condition.

1