CODEWITHSUNDEEP
sqloracle-database
Rajiv A
Rajiv A

Reputation: 943

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.

Upvotes: 23

Views: 42057

Answers (11)

chatran
chatran

Reputation: 221

The most "elegant" way to solve it is using the syntax:

SELECT col_1, ... , col_l,
  (SELECT MAX(column_value) FROM TABLE(sys.odcidatelist(col_m, ... , col_n))) as max_value
FROM my_table;

Upvotes: 1

Rob
Rob

Reputation: 2472

You can also use NVL if oracle. GREATEST(NVL(foo,'01-jan-1980'), NVL(bar,'01-jan-1980))....

This will return foo, bar, or 1980. In my case I knew for sure that the date was > 1980.

Upvotes: 0

Maxime Pacary
Maxime Pacary

Reputation: 23041

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 ...

Upvotes: 6

HRawashdeh
HRawashdeh

Reputation: 1

use NVL to solve this however complicity will be increased based of number of compared columns :

select A.*, *greatest("COL A", "COL B") "DIRECT COMPARE"*, **greatest(nvl("COL A", "COL B"), nvl("COL B", "COL A")) "NVL COMPARE"**
from (
SELECT NULL "COL A", SYSDATE "COL B", SYSDATE "NEEDED RESULT" FROM DUAL UNION
SELECT SYSDATE - 180 , NULL  , SYSDATE - 180 FROM DUAL UNION
SELECT SYSDATE - 180 , SYSDATE , SYSDATE FROM DUAL ) A;

Upvotes: 0

Dennis A.
Dennis A.

Reputation: 81

You could remove the possibility of any of the columns returning NULL by using the NVL function. Substitute any NULL values with a date that is earlier than any date that is likely to 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 then return the most recent date (maximum date) from the list of supplied dates without inadvertently returning NULL if one or more of the columns contains NULL.

Upvotes: 8

Mr. Llama
Mr. Llama

Reputation: 20889

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),
    ...
)

Upvotes: 39

David Faber
David Faber

Reputation: 12485

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.

Upvotes: 27

tbone
tbone

Reputation: 15473

If you have many columns to compare (more than 2 or 3), then handling all the various CASE combinations might get unwieldy. You could 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

Upvotes: 3

Rajiv A
Rajiv A

Reputation: 943

I tried this..found 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;

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191265

Another version using a case expression to handle the null values:

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

Upvotes: 7

Hedinn
Hedinn

Reputation: 864

Something like

SELECT CASE WHEN ColA is NULL and ColB is NULL then NULL
WHEN coalesce(ColA, '01/01/1753')>coalesce(ColB, '01/01/1753') then ColA
ELSE ColB END as Output

Upvotes: 0

Related Questions