Reputation: 14306
IF purpose = null THEN
v_purpose := '';
ELSE
v_purpose := ' for ' || purpose;
END IF;
When purpose is null, it still goes to the else...why?!
Upvotes: 3
Views: 514
Reputation: 57804
The correct test is
IF purpose IS NULL THEN
This is because NULL
is not a value stored in a field. It is an attribute about the field stored elsewhere (but within in the row).
Setting a field to NULL
appears to be an ordinary assignment so it is seems perfectly orthogonal to expect testing for it by direct comparison. However, for it to work as it does, I surmise the SQL assignment primitive has a magic hidden aspect which diverts assignment of the special symbol NULL
into setting an attribute and not the field.
Upvotes: 5
Reputation: 4845
The result of the expression purpose = null
is unknown, regardless of what purpose
is (see @Paul Sasik's answer for more details). Since it's unknown, it's not necessarily true, so execution bypasses the inside of the IF
block and falls into the ELSE
block.
Upvotes: 1
Reputation: 81567
NULL is a special value in SQL that cannot be compared using the equality operator. You need to use special operators IS
and IS NOT
when testing if a value is NULL.
Here's a good overview of the idea. And an excerpt:
NOTE: Null In Oracle is an absence of information. A null can be assigned but it cannot be equated with anything, including itself. NULL values represent missing or unknown data. NULL values are not an integer, a character, or any other specific data type. Note that NULL is not the same as an empty data string or the numerical value '0'.
Upvotes: 5