Posted by Hilarion on 10/04/99 11:26

> Works like a charm. Sadly, what Andy suggested as the standard way, didn't
> work. MSSQL doesn't support such syntax. F*cking Microsoft piece of crap.
> Gotta do it the wrong way then, as long as it works.


Checked on MS SQL Server 2000 and it does work.

Hilarion

PS.: From "Microsoft SQL Server 2000 Books Online (Updated)":

CASE

Evaluates a list of conditions and returns one of multiple possible result
expressions.

CASE has two formats:
+ The simple CASE function compares an expression to a set of simple
expressions to determine the result.
+ The searched CASE function evaluates a set of Boolean expressions
to determine the result.
Both formats support an optional ELSE argument.

Syntax

Simple CASE function:

CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

Searched CASE function:

CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

Arguments

input_expression
Is the expression evaluated when using the simple CASE format.
input_expression is any valid MicrosoftR SQL ServerT expression.
WHEN when_expression
Is a simple expression to which input_expression is compared when
using the simple CASE format. when_expression is any valid SQL
Server expression. The data types of input_expression and each
when_expression must be the same or must be an implicit conversion.
n
Is a placeholder indicating that multiple WHEN when_expression
THEN result_expression clauses, or multiple WHEN Boolean_expression
THEN result_expression clauses can be used.
THEN result_expression
Is the expression returned when input_expression equals
when_expression evaluates to TRUE, or Boolean_expression evaluates
to TRUE. result expression is any valid SQL Server expression.
ELSE else_result_expression
Is the expression returned if no comparison operation evaluates to
TRUE. If this argument is omitted and no comparison operation
evaluates to TRUE, CASE returns NULL. else_result_expression is
any valid SQL Server expression. The data types of
else_result_expression and any result_expression must be the same
or must be an implicit conversion.
WHEN Boolean_expression
Is the Boolean expression evaluated when using the searched CASE
format. Boolean_expression is any valid Boolean expression.


Result Types

Returns the highest precedence type from the set of types in
result_expressions and the optional else_result_expression. For more
information, see Data Type Precedence.


Result Values

Simple CASE function:
+ Evaluates input_expression, and then, in the order specified, evaluates
input_expression = when_expression for each WHEN clause.
+ Returns the result_expression of the first
(input_expression = when_expression) that evaluates to TRUE.
+ If no input_expression = when_expression evaluates to TRUE, SQL Server
returns the else_result_expression if an ELSE clause is specified, or
a NULL value if no ELSE clause is specified.

Searched CASE function:
+ Evaluates, in the order specified, Boolean_expression for each WHEN clause.
+ Returns result_expression of the first Boolean_expression that evaluates
to TRUE.
+ If no Boolean_expression evaluates to TRUE, SQL Server returns the
else_result_expression if an ELSE clause is specified, or a NULL value
if no ELSE clause is specified.

[Back to original message]