Hi all,
I have the following tables
With TABLE1(eof, t_date, val1, val2) as (
select '315810302', to_date('01/01/2019'), null, null from dual union all
select '315810302', to_date('02/01/2019'), null, null from dual union all
select '315810302', to_date('12/05/2019'), null, null from dual union all
select '315810301', to_date('21/02/2019'), null, null from dual union all
select '315810301', to_date('05/03/2019'), null, null from dual )
select *
from TABLE1;
With TABLE2(eof, s_date, val1, val2) as (
select '315810302', to_date('02/01/2019'), 35, 16.2 from dual union all
select '315810302', to_date('20/01/2019'), 34, 19.2 from dual union all
select '315810301', to_date('04/03/2019'), 10, 14 from dual )
select *
from TABLE2;
I want to update the val1, val2 of table1 based on the following criteria :
For the same eof value I compare the t_date with s_date
- if t_date = s_date then I update table1.val1 with table2.val1 and table1.val2 with table2.val2
- if t_date > s_date then I update table1.val1 with table2.val1 and table1.val2 with table2.val2 of the maximum s_date
- if t_date < s_date not update will be performed
Result
With TABLE1(eof, t_date, val1, val2) as (
select '315810302', to_date('01/01/2019'), null, null from dual union all
select '315810302', to_date('02/01/2019'), 35, 16.2 from dual union all
select '315810302', to_date('12/05/2019'), 34, 19.2 from dual union all
select '315810301', to_date('21/02/2019'), null, null from dual union all
select '315810301', to_date('05/03/2019'), 10, 14 from dual )
select *
from TABLE1;
I am trying to get the values with something like that but I don;t know how to get the row with the maximum s_date
SELECT DISTINCT RT.EOF, RT.T_DATE, SUP.S_DATE, SUP.val1, SUP.val2
FROM TABLE1 RT
LEFT OUTER JOIN TABLE2 SUP ON ( RT.EOF = SUP.EOF
AND TRUNC( RT.T_DATE ) >= TRUNC( SUP.S_DATE ) )
WHERE RT.EOF IS NOT NULL;
Thanks in advance for your help