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.

asked 16 Nov '16, 11:40

Tom%20Mangano's gravatar image

Tom Mangano
accept rate: 6%

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

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%20Carter's gravatar image

Breck Carter
accept rate: 21%

Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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

Question tags:


question asked: 16 Nov '16, 11:40

question was seen: 190 times

last updated: 17 Nov '16, 03:40