MS-SQL - case when
/SQL/MS-SQL - case when.sql
-- case <value> :
SELECT foo
, CASE copo_type
WHEN 1 then 'Mandat'
WHEN 2 then 'Bail'
WHEN 3 then 'Quasi'
ELSE 'copo_type non reconnu'
END as copo_type_txt
FROM ...
-- case <no value> == "if"
SELECT co_id,
CASE
WHEN isNull(co_nom, '')='' then isNull(co_denomination, '')
ELSE co_nom+' '+isNull(co_prenom, '')
END as nom
FROM contact
-------------------------------------------------------------------------------
/* [[ http://www.craigsmullins.com/ssu_0899.htm ]]
There are two basic formulations that a CASE expression can take: simple CASE expressions and searched CASE expressions. A simple CASE expression checks one expression against multiple values. Within a SELECT statement, a simple CASE expression allows only an equality check; no other comparisons are made. A simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.
The basic syntax for a simple CASE expressions is shown below: */
CASE <*expression*>
WHEN expression1 THEN expression1
[[WHEN expression2 THEN expression2] [...]]
[ELSE expressionN]
END
/* A searched CASE expression is more feature-laden. It allows comparison operators, and the use of AND and/or OR between each Boolean expression. The simple CASE expression checks only for equivalent values and can not contain Boolean expressions. The basic syntax for a searched CASE expressions is shown below: */
CASE
WHEN Boolean_expression1 THEN expression1
[[WHEN Boolean_expression2 THEN expression2] [...]]
[ELSE expressionN]
END
*/