I need to update a row with a formula based on the largest value of two DATETIME columns. I would normally do this:
GREATEST(date_one, date_two)
However, both columns are allowed to be NULL. I need the greatest date even when the other is NULL (of course, I expect NULL when both are NULL) and GREATEST() returns NULL when one of the columns is NULL.
This seems to work:
GREATEST(COALESCE(date_one, date_two), COALESCE(date_two, date_one))
But I wonder... am I missing a more straightforward method?
COALESCE(GREATEST(date_one, date_two), date_one, date_two)
EDIT Here's one possible n-column generalization in which the additional effort to add extra columns remains constant as the number of columns grows.
First, a table with test data:
create table MY_TABLE
( MY_KEY number
, COL_1 number
, COL_2 number
, COL_3 number
, COL_4 number
, primary key ( MY_KEY )
)
;
insert into MY_TABLE
select 0, null, null, null, null from dual union all
select 1, null, null, null, 5 from dual union all
select 2, null, null, 7, null from dual union all
select 3, null, null, 7, 5 from dual union all
select 4, null, 2, null, null from dual union all
select 5, null, 2, null, 5 from dual union all
select 6, null, 2, 7, null from dual union all
select 7, null, 2, 7, 5 from dual union all
select 8, 4, null, null, null from dual union all
select 9, 4, null, null, 5 from dual union all
select 10, 4, null, 7, null from dual union all
select 11, 4, null, 7, 5 from dual union all
select 12, 4, 2, null, null from dual union all
select 13, 4, 2, null, 5 from dual union all
select 14, 4, 2, 7, null from dual union all
select 15, 4, 2, 7, 5 from dual
;
commit
;
Then, a query:
with coalesced_columns
as (
select MY_KEY
, coalesce
( aa.COL_1
, aa.COL_2
, aa.COL_3
, aa.COL_4
-- , ... and so on, and so on, and so on
)
as FIRST_NON_NULL
from MY_TABLE
aa
)
select greatest
( nvl( aa.COL_1, zz.FIRST_NON_NULL )
, nvl( aa.COL_2, zz.FIRST_NON_NULL )
, nvl( aa.COL_3, zz.FIRST_NON_NULL )
, nvl( aa.COL_4, zz.FIRST_NON_NULL )
-- , ... and so on, and so on, and so on
)
as GREATEST_NON_NULL
, zz.FIRST_NON_NULL -- from "with" clause
, aa.COL_1
, aa.COL_2
, aa.COL_3
, aa.COL_4
from MY_TABLE
aa
join coalesced_columns
zz
on zz.MY_KEY = aa.MY_KEY
;
As always, the proof is in the pudding:
|-------------------+----------------+-------+-------+-------+-------|
| GREATEST_NON_NULL | FIRST_NON_NULL | COL_1 | COL_2 | COL_3 | COL_4 |
|-------------------+----------------+-------+-------+-------+-------|
| - | - | - | - | - | - |
| 5 | 5 | - | - | - | 5 |
| 7 | 7 | - | - | 7 | - |
| 7 | 7 | - | - | 7 | 5 |
| 2 | 2 | - | 2 | - | - |
| 5 | 2 | - | 2 | - | 5 |
| 7 | 2 | - | 2 | 7 | - |
| 7 | 2 | - | 2 | 7 | 5 |
| 4 | 4 | 4 | - | - | - |
| 5 | 4 | 4 | - | - | 5 |
| 7 | 4 | 4 | - | 7 | - |
| 7 | 4 | 4 | - | 7 | 5 |
| 4 | 4 | 4 | 2 | - | - |
| 5 | 4 | 4 | 2 | - | 5 |
| 7 | 4 | 4 | 2 | 7 | - |
| 7 | 4 | 4 | 2 | 7 | 5 |
|-------------------+----------------+-------+-------+-------+-------|