sql - 甲骨文的情况下，如果不为空？

Question

我看到很多使用CASE columnX WHEN NULL对Oracle进行的查询

如何设计查询
CASE columnX WHEN NOT NULL？

SQL开发人员在查询上引发错误，因此如何使用CASE ...何时...进行条件查询？

WITH dates_list AS
  (SELECT TO_DATE('02-19-2018','MM-dd-yyyy') + ROWNUM - 1 AS DAY,
    rownum                                                AS row_num
  FROM dual
    CONNECT BY LEVEL <= (TO_DATE('03-29-2018','MM-dd-yyyy') - TO_DATE('02-19-2018','MM-dd-yyyy')+1)
  )
SELECT dates1.day, dates2.*, count(case dates2.day when null then 1 else 0 end) 
              over (partition by dates2.day order by dates1.day) as cnt
FROM dates_list dates1
LEFT JOIN
  (SELECT *
  FROM dates_list
  WHERE TO_CHAR(DAY,'D') NOT IN (7,1)
  ) dates2 ON dates1.day = dates2.day
ORDER BY dates1.day;

当我将 when null更改为 when not null时，上述查询给出了错误

Answer 1

您不能以这种方式将CASE语句与NULL值一起使用，因为columnX永远不会“等于” NULL。您应该改用:

CASE WHEN columnX IS NULL THEN

或者

CASE WHEN columnX IS NOT NULL THEN