Null Handling in Greatest Function in Oracle
I want to compare two dates from two columns and get the largest value and then compare with the date value. NULL values can also be stored in two columns. For example, I want below OUTPUT. How do I use the largest function or if there is something else. I am using the output again to compare against a different date.
Col A Col B OUTPUT
---------------------------------------
NULL NULL NULL
09/21/2013 01/02/2012 09/21/2013
NULL 01/03/2013 01/03/2013
01/03/2013 NULL 01/03/2013
source to share
Use the Oracle framework of your choice: CASE... WHEN
SELECT COLA, COLB, CASE
WHEN (COLA > COLB OR COLB IS NULL)
THEN COLA
WHEN (COLA < COLB OR COLA IS NULL)
THEN COLB
WHEN (COLA = COLB)
THEN COLA
ELSE NULL
END
AS OUTPUT
FROM ...
source to share
Your specific question has two columns involved, but I've run into situations where I needed GREATEST
/ LEAST
from more than two columns. In these scenarios, you can use COALESCE
and deploy the solution to as many columns as you want.
Here's an example with three columns a
, b
and c
:
GREATEST( COALESCE(a, b, c), COALESCE(b, c, a), COALESCE(c, a, b) )
Note that the ordering of the columns is COALESCE
changed so that each input column is the first element COALESCE
at least once. The only time this will return NULL is when all the input columns are NULL.
In the "general solution", the number of operators COALESCE
will be equal to the number of input columns:
GREATEST( COALESCE(col1, col2, col3, col4, ....), COALESCE(col2, col3, col4, ...., col1), COALESCE(col3, col4, ...., col1, col2), COALESCE(col4, ...., col1, col2, col3), COALESCE(...., col1, col2, col3, col4), ... )
source to share
Another version using a case expression
to process values null
:
select cola, colb,
case when cola is null and colb is null then null
when cola is null then colb
when colb is null then cola
else greatest(cola, colb)
end as output
from <table>;
COLA COLB OUTPUT
---------- ---------- ----------
09/21/2013 01/02/2012 09/21/2013
01/03/2013 01/03/2013
01/03/2013 01/03/2013
source to share
If you have many columns to compare (more than 2 or 3), then handling all the different CASE combinations can become cumbersome. You can try (11g):
with x as (
select 1 as id, sysdate - 30 as col1, sysdate-50 as col2, sysdate-20 as col3,null as col4, sysdate-1 as col5 from dual
union
select 2 as id, sysdate - 10 as col1, sysdate-20 as col2, null as col3,null as col4, sysdate-35 as col5 from dual
union
select 3 as id, null as col1, null as col2, null as col3, null as col4, null as col5 from dual
)
select id, max(dates)
from x
UNPIVOT INCLUDE NULLS
(dates FOR colname IN (col1,col2,col3,col4,col5))
group by id
source to share
You can remove the capability of any NULL column with the NVL function. Substitute any NULL values with a date that precedes any date that may occur in your tables.
SELECT GREATEST(NVL(A,TO_DATE('01/01/1800','MM/DD/YYYY')),
NVL(B,TO_DATE('01/01/1800','MM/DD/YYYY'))) AS OUTPUT
FROM ...
The GREATEST function will return the most recent date (maximum date) from the list of provided dates without inadvertently returning NULL if one or more columns contain NULL.
source to share
I tried this ... after googling
WITH ABC AS ( SELECT NULL AS col1 , NULL AS col2 FROM dual UNION
SELECT NULL , DATE'2013-08-12' FROM dual UNION
SELECT DATE'2013-08-12' , NULL FROM dual UNION
SELECT DATE'2013-08-12', DATE'2013-09-12' FROM dual)
SELECT col1, col2 , substr(greatest('x'||col1,'x'||col2),2)
FROM ABC;
source to share