Handling Null in Greatest function in Oracle

Question

I want to compare two dates from two columns and get the greatest and then compare against a date value. The two column can hold NULL values too.

For example I want the below OUTPUT.

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 

How do I use the greatest function or if there is anything else? I am again using the output to compare against another date.

Answer 1

Your question specifically involves two columns, but I've run into situations where I needed GREATEST/LEAST of more than two columns. In those scenarios you can use COALESCE and expand the solution to as many columns you want.

Here is 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 column ordering of the COALESCE changes so that each input column is the first element COALESCE at least once. The only time this will return NULL is when all input columns are NULL.

In the "general solution" the number of COALESCE statements 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),
    ...
)

Answer 2

You might try the following:

SELECT cola, colb, COALESCE( GREATEST( cola, colb ), cola, colb ) AS output
  FROM yourtable;

The reason for COALESCE() is that GREATEST() returns NULL if either of the parameters is NULL.