Returning a boolean value in a case statement

Question

I am trying to return a boolean value from a case statement but the compiler is complaining about a ORA-00936: missing expression error:

SELECT
  CASE MYCOLUMN
     WHEN NULL THEN  true
  ELSE
       false
  END,
FROM MYTABLE;

I also tried the following but it doesn't work:

SELECT
  CASE MYCOLUMN
     WHEN NULL THEN  SELECT true
  ELSE
       SELECT false
  END,
FROM MYTABLE;

Is it possible to do this?

Answer 1

You need the IS operator for NULL checks

SELECT CASE WHEN MYCOLUMN IS NULL
            THEN 1
            ELSE 0
       END
FROM MYTABLE;

Answer 2

BOOLEAN type is not supported in SQL. It is supported only in procedural PL/SQL