Two tables A and B, each with a field, update time A.MODIFIED_TM and B.modified_tm,a table as the main table, update time is not empty, but the B table update time may be empty, now to take a, B two table time the newest one, b.modified

Problem Description:

Two Tables A and B, each with a field, update time A.MODIFIED_TM and B.modified_tm,a table as the main table, update time is not empty, but the B table update time may be empty, now to take a, B two table time the newest one, b.modified_ If the TM is empty, take A.modified_tm, for example:

Table A
ID Modified_tm
1 2013/3/10 18:07:12
2 2013/4/10 18:07:12
3 2013/5/10 18:07:12

Table B
ID Modified_tm
1 2014/3/11 18:07:12
NULL NULL
3 null

Results

1 2014/3/11 18:07:12
2 2013/4/10 18:07:12
3 2013/5/10 18:07:12

Left connection

Way One:

SELECT a.ID as ID,
DECODE (B.MODIFIED_TM,
Null
A.MODIFIED_TM,
Greatest (A.modified_tm, B.modified_tm)) as Modified_tm
From A
Left JOIN B
on a.id = b.ID;

Way two:

SELECT a.ID as ID,
Case
When B.modified_tm are not NULL and then
Greatest (A.modified_tm, B.MODIFIED_TM)
ELSE
A.modified_tm
END as Modified_tm
From A
Left JOIN B

on a.id = b.ID;

Way three:

SELECT a.ID,
Greatest (NVL (B.modified_tm, DATE ' 1900-01-01 '),
NVL (A.modified_tm, DATE ' 1900-01-01 '))
From A, B
WHERE a.id = b.id (+)
ORDER by a.ID;

Two tables A and B, each with a field, update time A.MODIFIED_TM and B.modified_tm,a table as the main table, update time is not empty, but the B table update time may be empty, now to take a, B two table time the newest one, b.modified