difference between if...then...else and case when

[t1950]

select ...,
  ( if table1.column1 > '' then table1.bin_location else table2.bin_location end if ) as bin1,
  ( Case
    when table1.column1 > '' then table1.bin_location
    else table2.bin_location
    End ) as bin2,
  ... 
From ...

I'm using SA 16, EBF 1761. The IF statement does NOT return a value. I don't remember if it was '' or null. The CASE statement returns what I expect, i.e. it returns the values I'm expecting. why the different behavior.

[MarkCulp]

Please elaborate. What does the IF statement return? What version are you using?

[t1950]

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.

[t1950]

Thank you for your answer. I don't see a way to accept your answer.

[VolkerBarth]

I don't see a way to accept your answer.

See the check mark before Breck's answer...

Just two more notes:

• Syntatically, there's no need to put the IF and CASE expressions inside brackets.
• If you want to have the same semantics for both expressions, make sure the test condition can only return TRUE or FALSE, not UNKNOWN, such as

   ...
   if isnull(table1.column1, '') > '' then ... end if as bin1,
   case when isnull(table1.column1, '') > '' then ... end case as bin2, ...

Of course, that would not be necessary when the tested column is declared as NOT NULL but I'm sure it isn't here...

[Breck_Carter · Accepted solution]

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.