Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

SELECT: Use condition from another table

former_member310342
Participant
0 Kudos

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!

1 ACCEPTED SOLUTION

salah_zinet
Participant

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.

6 REPLIES 6

salah_zinet
Participant

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.

salah_zinet
Participant

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.

0 Kudos

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 ).

raghug
Active Contributor

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.

0 Kudos

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,

horst_keller
Product and Topic Expert
Product and Topic Expert

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