coalesce is supported in both Oracle and SQL Server and serves essentially the same function as nvl and isnull. (There are some important differences, coalesce can take an arbitrary number of arguments, and returns the first non-null one. The return type for isnull matches the type of the first argument, that is not true for coalesce, at least on SQL Server.)
Videos
coalesce is supported in both Oracle and SQL Server and serves essentially the same function as nvl and isnull. (There are some important differences, coalesce can take an arbitrary number of arguments, and returns the first non-null one. The return type for isnull matches the type of the first argument, that is not true for coalesce, at least on SQL Server.)
Instead of ISNULL(), use NVL().
T-SQL:
SELECT ISNULL(SomeNullableField, 'If null, this value') FROM SomeTable
PL/SQL:
SELECT NVL(SomeNullableField, 'If null, this value') FROM SomeTable
Your first CASE expression won't even compile, but if we make a small change then it will:
CASE s.COURSE_SCHEDULED_ID
WHEN NULL THEN 'false'
ELSE 'true'
END AS "Is scheduled?"
This will be evaluated as this:
CASE WHEN s.COURSE_SCHEDULED_ID = NULL
THEN 'false'
ELSE 'true'
END AS "Is scheduled?"
Note very carefully that the COURSE_SCHEDULED_ID is being compared to NULL using the equality operator. This won't work as expected with NULL. Instead, for NULL checks the second verbose version always must be used as it allows IS NULL and IS NOT NULL checks.
From the documentaion:
In a simple
CASEexpression, Oracle Database searches for the firstWHEN ... THENpair for which expr is equal to comparison_expr and returns return_expr. If none of theWHEN ... THENpairs meet this condition, and anELSEclause exists, then Oracle returns else_expr. Otherwise, Oracle returns null.
In you first version you have
CASE s.COURSE_SCHEDULED_ID WHEN IS NULL THEN
which will throw "ORA-00936: missing expression" because IS NULL is a condition, not a value or expression. So then you might think you could do:
CASE s.COURSE_SCHEDULED_ID WHEN NULL THEN
but that will go to the else value because the comparison is "expr is equal to comparison_expr", and nothing is equal to (or not equal to) null (documentation).
In your second version you now have a searched case expression:
In a searched
CASEexpression, Oracle searches from left to right until it finds an occurrence of condition that is true, ...
Now it is expecting a condition (rather than a value or expression), so s.COURSE_SCHEDULED_ID IS NULL can be evaluated and is true.
COUNT(expresion) returns the count of of rows where expresion is not null. So SELECT COUNT (COL_NAME) FROM TABLE WHERE COL_NAME IS NULL will return 0, because you are only counting col_name where col_name is null, and a count of nothing but nulls is zero. COUNT(*) will return the number of rows of the query:
SELECT COUNT (*) FROM TABLE WHERE COL_NAME IS NULL
The other two queries are probably not returning any rows, since they are trying to match against strings with one blank character, and your dump query indicates that the column is actually holding nulls.
If you have rows with variable strings of space characters that you want included in the count, use:
SELECT COUNT (*) FROM TABLE WHERE trim(COL_NAME) IS NULL
trim(COL_NAME) will remove beginning and ending spaces. If the string is nothing but spaces, then the string becomes '' (empty string), which is equivalent to null in Oracle.
A NULL column is not countable, however a row that has a NULL column is. So, this should do what you're looking for:
SELECT COUNT (*) FROM TABLE WHERE COL_NAME IS NULL OR LENGTH(TRIM (COL_NAME)) = 0
Note, that there are non-printing characters that this will not address. For example U+00A0 is the non-breaking space character and a line containing that will visually appear empty, but will not be found by the tests above.