It is a bug feature :)
Here's an excerpt from The Book:
3.10.1 IF and CASE Expressions
The IF and CASE keywords can be used to create expressions as well as to code
IF-THEN-ELSE and CASE statements. The statements are discussed in Chapter
8, “Packaging,” and the expressions are described here.
<if_expression> ::= IF <boolean_expression>
THEN <expression>
[ ELSE <expression> ]
ENDIF
The IF expression evaluates the <boolean_expression> to determine if it is
TRUE, FALSE, or UNKNOWN. If the <boolean_expression> result is TRUE,
the THEN <expression> is returned as the result of the IF. If the
<boolean_expression> is FALSE, the ELSE <expression> is returned as the
result of the IF. If there is no ELSE <expression>, or if the <boolean_expression>
is UNKNOWN, then NULL is returned as the result of the IF.
...
The CASE expression comes in two forms:
<case_expression> ::= <basic_case_expression>
| <searched_case_expression>
<basic_case_expression> ::= CASE <basic_expression>
WHEN <expression> THEN <expression>
{ WHEN <expression> THEN <expression> }
[ ELSE <expression> ]
END
...
The second form of the CASE expression is more flexible; you are not limited
to the implicit equals “=” operator, nor are you limited to a single CASE comparison
value on the left side of all the WHEN comparisons.
<searched_case_expression> ::= CASE
WHEN <boolean_expression> THEN <expression>
{ WHEN <boolean_expression> THEN <expression> }
[ ELSE <expression> ]
END
Each WHEN <boolean_expression> is evaluated, in turn, to result in a TRUE,
FALSE, or UNKNOWN result. As soon as a TRUE result is encountered, the
search is over; the corresponding THEN <expression> is evaluated and returned
as the result of the CASE. If all the results are FALSE or UNKNOWN, then the
ELSE <expression> is evaluated and returned; if there is no ELSE <expression>,
then NULL is returned.
answered
16 Nov '16, 14:28
Breck Carter
32.5k●539●724●1050
accept rate:
20%
Please elaborate. What does the IF statement return? What version are you using?
I'm using SA 16 EBF 1761. I don't remember if the IF statement returned '' or null. The CASE statement returned the values I expected to see.
Thank you for your answer. I don't see a way to accept your answer.
See the check mark before Breck's answer...
Just two more notes:
Of course, that would not be necessary when the tested column is declared as NOT NULL but I'm sure it isn't here...