Handling Null in Greatest function in Oracle

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

Use Oracle CASE... WHEN structure in your select:

SELECT COLA, COLB, CASE
  WHEN (COLA >= COLB OR COLB IS NULL)
    THEN COLA
  ELSE COLB
  END
  AS OUTPUT
FROM ...

Advertisement

Answer