Home » Questions » Computers [ Ask a new question ]

SQL Case Expression Syntax?

SQL Case Expression Syntax?

What is the complete and correct syntax for the SQL Case expression?

Asked by: Guest | Views: 53
Total answers/comments: 4
Guest [Entry]

"The complete syntax depends on the database engine you're working with:

For SQL Server:

CASE case-expression
WHEN when-expression-1 THEN value-1
[ WHEN when-expression-n THEN value-n ... ]
[ ELSE else-value ]
END

or:

CASE
WHEN boolean-when-expression-1 THEN value-1
[ WHEN boolean-when-expression-n THEN value-n ... ]
[ ELSE else-value ]
END

expressions, etc:

case-expression - something that produces a value
when-expression-x - something that is compared against the case-expression
value-1 - the result of the CASE statement if:
the when-expression == case-expression
OR the boolean-when-expression == TRUE
boolean-when-exp.. - something that produces a TRUE/FALSE answer

Link: CASE (Transact-SQL)

Also note that the ordering of the WHEN statements is important. You can easily write multiple WHEN clauses that overlap, and the first one that matches is used.

Note: If no ELSE clause is specified, and no matching WHEN-condition is found, the value of the CASE expression will be NULL."
Guest [Entry]

"Considering you tagged multiple products, I'd say the full correct syntax would be the one found in the ISO/ANSI SQL-92 standard:

<case expression> ::=
<case abbreviation>
| <case specification>

<case abbreviation> ::=
NULLIF <left paren> <value expression> <comma>
<value expression> <right paren>
| COALESCE <left paren> <value expression>
{ <comma> <value expression> }... <right paren>

<case specification> ::=
<simple case>
| <searched case>

<simple case> ::=
CASE <case operand>
<simple when clause>...
[ <else clause> ]
END

<searched case> ::=
CASE
<searched when clause>...
[ <else clause> ]
END

<simple when clause> ::= WHEN <when operand> THEN <result>

<searched when clause> ::= WHEN <search condition> THEN <result>

<else clause> ::= ELSE <result>

<case operand> ::= <value expression>

<when operand> ::= <value expression>

<result> ::= <result expression> | NULL

<result expression> ::= <value expression>

Syntax Rules

1) NULLIF (V1, V2) is equivalent to the following <case specification>:

CASE WHEN V1=V2 THEN NULL ELSE V1 END

2) COALESCE (V1, V2) is equivalent to the following <case specification>:

CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END

3) COALESCE (V1, V2, . . . ,n ), for n >= 3, is equivalent to the
following <case specification>:

CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE (V2, . . . ,n )
END

4) If a <case specification> specifies a <simple case>, then let CO
be the <case operand>:

a) The data type of each <when operand> WO shall be comparable
with the data type of the <case operand>.

b) The <case specification> is equivalent to a <searched case>
in which each <searched when clause> specifies a <search
condition> of the form ""CO=WO"".

5) At least one <result> in a <case specification> shall specify a
<result expression>.

6) If an <else clause> is not specified, then ELSE NULL is im-
plicit.

7) The data type of a <case specification> is determined by ap-
plying Subclause 9.3, ""Set operation result data types"", to the
data types of all <result expression>s in the <case specifica-
tion>.

Access Rules

None.

General Rules

1) Case:

a) If a <result> specifies NULL, then its value is the null
value.

b) If a <result> specifies a <value expression>, then its value
is the value of that <value expression>.

2) Case:

a) If the <search condition> of some <searched when clause> in
a <case specification> is true, then the value of the <case
specification> is the value of the <result> of the first
(leftmost) <searched when clause> whose <search condition> is
true, cast as the data type of the <case specification>.

b) If no <search condition> in a <case specification> is true,
then the value of the <case expression> is the value of the
<result> of the explicit or implicit <else clause>, cast as
the data type of the <case specification>."
Guest [Entry]

"Here are the CASE statement examples from the PostgreSQL docs (Postgres follows the SQL standard here):

SELECT a,
CASE WHEN a=1 THEN 'one'
WHEN a=2 THEN 'two'
ELSE 'other'
END
FROM test;

or

SELECT a,
CASE a WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'other'
END
FROM test;

Obviously the second form is cleaner when you are just checking one field against a list of possible values. The first form allows more complicated expressions."
Guest [Entry]

"Sybase has the same case syntax as SQL Server:

Description

Supports conditional SQL expressions; can be used anywhere a value expression can be used.

Syntax

case
when search_condition then expression
[when search_condition then expression]...
[else expression]
end

Case and values syntax

case expression
when expression then expression
[when expression then expression]...
[else expression]
end

Parameters

case

begins the case expression.

when

precedes the search condition or the expression to be compared.

search_condition

is used to set conditions for the results that are selected. Search conditions for case expressions are similar to the search conditions in a where clause. Search conditions are detailed in the Transact-SQL User’s Guide.

then

precedes the expression that specifies a result value of case.

expression

is a column name, a constant, a function, a subquery, or any combination of column names, constants, and functions connected by arithmetic or bitwise operators. For more information about expressions, see “Expressions” in.

Example

select disaster,
case
when disaster = ""earthquake""
then ""stand in doorway""
when disaster = ""nuclear apocalypse""
then ""hide in basement""
when monster = ""zombie apocalypse""
then ""hide with Chuck Norris""
else
then ""ask mom""
end
from endoftheworld"