How to avoid returning a NULL in CASE when one of the three values is NULL-CodePudding

I want to thank you in advance for your help on this matter.

I'm writing a SQL query in Oracle SQL Developer which needs to compare 3 dates and return the earliest one, avoiding blank values and writing that value somewhere along the lines.

My question is what do you think is the best way to avoid those NULL values, my take is below but I'm having some slight issues implementing this in my current setup.

Cheers!

SELECT Case When DATE1 < DATE2 And DATE1 < DATE3 Then DATE1
            When DATE2 < DATE1 And DATE2 < DATE3 Then DATE2
            Else DATE3
            End AS xxx, SOME.TABLE|| ' ' || TABLE.SOME INTO TABLE1, TABLE2
            
FROM (SELECT Case When DATE1 IS NULL Then DATEADD(year, 100, GETDATE()) Else DATE1 As DATE1,
             Case When DATE2 IS NULL Then DATEADD(year, 100, GETDATE()) Else DATE2 As DATE2,
             Case When DATE3 IS NULL Then DATEADD(year, 100, GETDATE())
             Else DATE3 As DATE3
        FROM SOME_TABLE2 ST2 WHERE SOME_TABLE3 = '#INTO_SOMETHING')

CodePudding user response：

You can use LEAST and COALESCE:

SELECT LEAST(
         COALESCE(date1, date2, date3, ADD_MONTHS(SYSDATE, 100*12)),
         COALESCE(date2, date3, date1, ADD_MONTHS(SYSDATE, 100*12)),
         COALESCE(date3, date1, date2, ADD_MONTHS(SYSDATE, 100*12))
       ) AS least_date
FROM   table_name;

or:

SELECT COALESCE(
         LEAST(
           COALESCE(date1, date2, date3),
           COALESCE(date2, date3, date1),
           COALESCE(date3, date1, date2)
         ),
         ADD_MONTHS(SYSDATE, 100*12)
       ) AS least_date
FROM   table_name;

or:

SELECT LEAST(
         COALESCE(date1, ADD_MONTHS(SYSDATE, 100*12)),
         COALESCE(date2, ADD_MONTHS(SYSDATE, 100*12)),
         COALESCE(date3, ADD_MONTHS(SYSDATE, 100*12))
       ) AS least_date
FROM   table_name;

Note: the first two queries will return the least non-NULL value from your table in preference to the default value 100 years from now. The final query may return the default value 100 years from now if all the other values are a mix of NULL and non-NULL values that are greater than 100 years from now. Which you use depends on the logic you want to implement and whether your table will ever contain dates more than 100 years in the future.

Which, for the sample data:

CREATE TABLE table_name (date1, date2, date3) AS
SELECT DATE '2022-01-01', DATE '2021-01-01', DATE '2020-01-01' FROM DUAL UNION ALL
SELECT DATE '2022-01-01', NULL, DATE '2020-01-01' FROM DUAL UNION ALL
SELECT DATE '2022-01-01', DATE '2021-01-01', NULL FROM DUAL UNION ALL
SELECT NULL, DATE '2021-01-01', NULL FROM DUAL UNION ALL
SELECT NULL, NULL, NULL FROM DUAL;

All output:

LEAST_DATE
2020-01-01 00:00:00
2020-01-01 00:00:00
2021-01-01 00:00:00
2021-01-01 00:00:00
2122-12-05 09:50:28

fiddle

If you do want a NULL value if, and only if, all 3 dates are NULL then use the first query without the default date:

SELECT LEAST(
         COALESCE(date1, date2, date3),
         COALESCE(date2, date3, date1),
         COALESCE(date3, date1, date2)
       ) AS least_date
FROM   table_name;

fiddle

CodePudding user response：

Here is a little paralel results Using LEAST() function alone, with Nvl() and CASE with Nvl. The Last one will return Null if all 3 date columns are Null.
Sample data:

WITH
    tbl As 
        (
            Select 1 "NMBR", 'A' "LTTR", SYSDATE "DATE1", SYSDATE-1 "DATE2", SYSDATE 1 "DATE3" From Dual Union All
            Select 2 "NMBR", 'B' "LTTR", SYSDATE "DATE1", SYSDATE-1 "DATE2", Null "DATE3" From Dual Union All
            Select 3 "NMBR", 'C' "LTTR", SYSDATE "DATE1", Null "DATE2", SYSDATE 1 "DATE3" From Dual Union All
            Select 4 "NMBR", 'D' "LTTR", Null "DATE1", SYSDATE-1 "DATE2", SYSDATE 1 "DATE3" From Dual Union All
            Select 5 "NMBR", 'E' "LTTR", Null "DATE1", Null "DATE2", SYSDATE 1 "DATE3" From Dual Union All
            Select 6 "NMBR", 'F' "LTTR", Null "DATE1", Null "DATE2", NULL "DATE3" From Dual 
        )

NMBR	LTTR	DATE1	DATE2	DATE3
1	A	05-DEC-22	04-DEC-22	06-DEC-22
2	B	05-DEC-22	04-DEC-22
3	C	05-DEC-22		06-DEC-22
4	D		04-DEC-22	06-DEC-22
5	E			06-DEC-22
6	F

SQL

SELECT 
    NMBR,
    LTTR,
    LEAST(DATE1, DATE2, DATE3) "LEAST_DATE",
    LEAST(Nvl(DATE1, TRUNC(SYSDATE) 10000), Nvl(DATE2, TRUNC(SYSDATE) 10000), Nvl(DATE3, TRUNC(SYSDATE) 10000)) "LEAST_NVL_DATE",
    CASE  
          WHEN LEAST(Nvl(DATE1, TRUNC(SYSDATE) 10000), Nvl(DATE2, TRUNC(SYSDATE) 10000), Nvl(DATE3, TRUNC(SYSDATE) 10000)) < TRUNC(SYSDATE 1000) 
          THEN LEAST(Nvl(DATE1, TRUNC(SYSDATE) 10000), Nvl(DATE2, TRUNC(SYSDATE) 10000), Nvl(DATE3, TRUNC(SYSDATE) 10000))
    END "LEAST_CASE_NVL_DATE"
FROM
  tbl

Result:

NMBR	LTTR	LEAST_DATE	LEAST_NVL_DATE	LEAST_CASE_NVL_DATE
1	A	04-DEC-22	04-DEC-22	04-DEC-22
2	B		04-DEC-22	04-DEC-22
3	C		05-DEC-22	05-DEC-22
4	D		04-DEC-22	04-DEC-22
5	E		06-DEC-22	06-DEC-22
6	F		22-APR-50

CodePudding user response：

I've been able to resolve the issue by using LEAST and COALESCE functions, since DATEADD and GETDATE are not Oracle functions.

Further to this, I've used @MT0 resolution query which worked well in my current setup.