01-02-2017 12:09 PM
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!
01-02-2017 12:26 PM
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.
01-02-2017 12:26 PM
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.
01-02-2017 2:39 PM
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.
01-02-2017 2:40 PM
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 ).
01-02-2017 3:45 PM
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.
01-03-2017 8:50 AM
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,
01-03-2017 1:07 PM
You might also consider the usage of a subquery in the WHERE clause ...