NULL is not getting matched in case expression in sql

May 4, 2023

I want to write a query where for my date parameter ‘P_D_IN_END_DT’ if either ’01/01/1900′ or ’01/01/1′ or NULL is passed then i will take ‘sysdate’ else i will take parameter i.e ‘P_D_IN_END_DT’ value only.

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;

For ’01/01/1900′ or ’01/01/1′ is working fine . But when i taking NULL in parameter then i am not getting sysdate rather i am getting 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;

>Solution :

NULL is not equal to NULL; NULL is not un-equal to NULL. A comparison with NULL returns NULL.

Therefore:

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;