CODEWITHSUNDEEP
sqloracle-database
Dreamer
Dreamer

Reputation: 7551

Oracle case when NOT null?

I see a lot of queries made for Oracle using CASE columnX WHEN NULL

how to design query if

CASE columnX WHEN NOT NULL ?

SQL developer throws an error on the query so how to make condition query using CASE... WHEN...?

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;

above query gives error when I change when null to when not null

Upvotes: 9

Views: 32475

Answers (2)

Punnerud
Punnerud

Reputation: 8021

You now have a function NVL that can turn this:

CASE WHEN columnX IS NOT NULL THEN columnX ELSE 'No value' END

Into this:

NVL(columnX, 'No value')

If columnX is a select, it make everything a lot cleaner

Upvotes: 1

Alex Zen
Alex Zen

Reputation: 926

You can't use the CASE sentence in that way with NULL values, because columnX is never "equal" to NULL. You should use instead:

CASE WHEN columnX IS NULL THEN

or

CASE WHEN columnX IS NOT NULL THEN

Upvotes: 14

Related Questions