oracle 如何避免在三个值之一为NULL的情况下返回NULL

xiozqbni 于 2022-12-11 发布在 Oracle

关注(0)|答案(3)|浏览(158)

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')

oracle

来源：https://stackoverflow.com/questions/74685869/how-to-avoid-returning-a-null-in-case-when-one-of-the-three-values-is-null

3条答案

按热度按时间

vhmi4jdf1#

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

赞(0）回复(0）举报 2022-12-11

0mkxixxg2#

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

赞(0）回复(0）举报 2022-12-11

w8rqjzmb3#

我已经能够通过使用LEAST和COALESCE函数解决这个问题，因为DATEADD和GETDATE不是Oracle函数。
此外，我还使用了@MT0解析查询，它在我当前的设置中运行良好。

赞(0）回复(0）举报 2022-12-11