I am learning Oracle SQL :)

1.

CASE s.COURSE_SCHEDULED_ID
    WHEN IS NULL THEN 'false'
    ELSE 'true'
END AS "Is scheduled?"

CASE 
    WHEN s.COURSE_SCHEDULED_ID IS NULL THEN 'false'
    ELSE 'true'
END AS "Is scheduled?"

Why 1 is not working?

CodePudding user response：

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.

CodePudding user response：

From the documentaion:

In a simple CASE expression, Oracle Database searches for the first WHEN ... THEN pair for which expr is equal to comparison_expr and returns return_expr. If none of the WHEN ... THEN pairs meet this condition, and an ELSE clause 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 CASE expression, 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.

CodePudding user response：

you can check multiple column situations in case when statements. for example

case 
     when s.COURSE_SCHEDULED_ID is null and s.COURSE_ID = 4 
     then true 
     else false 
 end as is_scheduled 

if you try to show in the 1st way, which value will asign to which columns? Or you can write different cases:

case 
     when s.COURSE_SCHEDULED_ID is null  
     then true 
     when s.COURSE_ID = 4
     then null 
     else false 
 end as is_scheduled