Question:
I am trying to extract data of these 2 tables...but I have a problem.
This "inner join" is picking up data from table (pa2006) based on table (pa0000), but when it gets the data from table (pa0000), instead of getting the last record (the one with the most recent date) related to table (PA2006), it picks up the first one (the one with the oldest date). How do I solve this situation?
CODE:
select pa2006~pernr pa2006~ktart pa2006~begda
pa2006~endda pa2006~anzhl pa2006~kverb
FROM pa2006 inner JOIN pa0000
ON pa2006~pernr = pa0000~pernr
INTO corresponding fields of table tab
WHERE pa2006~pernr IN s_pernr
AND pa2006~begda IN s_begda
AND pa2006~endda IN s_endda
AND pa2006~ktart IN s_ktart
AND pa0000~stat2 IN s_stat2
GROUP BY pa2006~PERNR pa2006~ktart
pa2006~begda pa2006~endda pa2006~anzhl pa2006~kverb.
Answer:
would sound a bit out of theme, but... why you do an INNER JOIN without getting any field from the second table?
Not sure if I got the sense of the specs, but I would do something like:
SELECT INTO TABLE it_pa0 yadda yadda.
SORT it_pa0 BY date DESCENDING.
DELETE ADJACENT DUPLICATES FROM it_pa0.
SELECT INTO TABLE it_pa06 FOR ALL ENTRIES IN it_pa0 yadda yadda.(code provided "as is", without syntax check nor anything related)
I'm sorry to not be able to give you a better answer, but it's too early, and I'm sleeping
PD: try to use aliases for table names when you write a JOIN, will be easier to read (because the shorter names)... In my modest opinion
_________________
Brief History of the Tuly Idiots
Bad advices, wrong answers, bigotism
sapfans GD
Ad by Viiiiiic
Answer:
If you're joining PA0000 and PA2006 I would have thought you should use the complete key. Or at least include the key ENDDA and BEGDA dates in the join. Unless you do want data from PA0000 whether or not it matches the dates in PA2006.
_________________
Using SAP R/3 version 4.7 BASIS 6.20 under SunOS on an Oracle 10.2.0.2.0 database