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

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.

if-expression

asked 16 Nov '16, 11:40

Tom Mangano
672●24●27●38
accept rate: 8%

edited 16 Nov '16, 13:53

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

(16 Nov '16, 13:30) Mark Culp

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.

(16 Nov '16, 13:56) Tom Mangano

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

(16 Nov '16, 14:52) Tom Mangano

Replies hidden

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...

(17 Nov '16, 03:39) Volker Barth

flat view

One Answer:

active answers oldest answers newest answers popular answers

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.

permanent link

answered 16 Nov '16, 14:28

Breck Carter
32.5k●541●726●1050
accept rate: 20%

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

if-expression ×3

question asked: 16 Nov '16, 11:40

question was seen: 2,204 times

last updated: 17 Nov '16, 03:40

SAP SQL Anywhere

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

Follow this question

Related questions