oracle SQL中的case表达式不匹配NULL

zazmityj  于 2023-05-06  发布在  Oracle
关注(0)|答案(1)|浏览(211)

我想写一个查询,对于我的日期参数'P_D_IN_END_DT',如果传递了'01/01/1900'或'01/01/1'或NULL,则我将采用'sysdate',否则我将只采用参数即'P_D_IN_END_DT'值。

SELECT
     CASE
          WHEN  P_D_IN_END_DT IN ( TO_DATE ('01/01/1900', 'dd/mm/yyyy'),  TO_DATE ('01/01/1', 'dd/mm/yyyy'),  NULL)
          THEN
             TRUNC(SYSDATE)
          ELSE
             P_D_IN_END_DT
       END 
       AS END_DT
FROM DUAL;

对于“01/01/1900”或“01/01/1”,工作正常。但是当我在参数中取NULL时,我不会得到sysdate,而是得到NULL

SELECT CASE
          WHEN NULL IN
                  (TO_DATE ('01/01/1900', 'dd/mm/yyyy'),
                   TO_DATE ('01/01/1', 'dd/mm/yyyy'), NULL)         
          THEN
             TRUNC(SYSDATE)
          ELSE
             NULL
       END
    AS END_DT
FROM DUAL;
zfycwa2u

zfycwa2u1#

NULL不等于NULL;NULL不等于NULL。与NULL比较返回NULL。
因此:

SELECT
     CASE
          WHEN  P_D_IN_END_DT IS NULL
            OR  P_D_IN_END_DT IN ( 
             TO_DATE ('01/01/1900', 'dd/mm/yyyy')
          ,  TO_DATE ('01/01/1', 'dd/mm/yyyy')
          )   
          THEN
             TRUNC(SYSDATE)
          ELSE
             P_D_IN_END_DT
       END 
       AS END_DT
FROM indata;

相关问题