getting data from table with nearest date

I want to create a query where any null values IN val COLUMN in table1
are replaced with value from table2 (VAL column) where the rules are:
join tabkle1 to table2 re: ID1 & ID2 and the value retuned from table2 (column VAL)
must be the row where the date in table2 is either equal or next date back
from that in table1 - see output table for desired result



Table1
Company       ID1      ID2     DATE          VAL
abc            123      1       01/03/2008     34
def            124      11      01/03/2009     NULL

Table2
ID1      ID2     DATE          VAL
123      1       01/03/2008     34
124      11      01/02/2004     50
124      11      01/02/2008     67
124      11      01/02/2011     12


SQL query required to produce ..............
output
cpy       ID1      ID2     DATE          VAL
abc      123      1       01/03/2008     34
def      124      11      01/03/2009     67 -- date returned from table1 and val 67
                                 -- is nearest date in table2 <= table1 date
philsivyerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
SELECT company,
       id1,
       id2,
       d,
       NVL(val, val2)
  FROM (SELECT t1.*,
               t2.val val2,
               ROW_NUMBER() OVER (PARTITION BY t1.id1, t1.id2 ORDER BY t1.d - t2.d) rn
          FROM table1 t1, table2 t2
         WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2 AND t2.d <= t1.d)
 WHERE rn = 1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
philsivyerAuthor Commented:
Thanks for this
0
philsivyerAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.