ODBC Server Reference Manual

CORE SQL Language
HP NonStop ODBC Server Reference Manual—429151-002
3-30
CASE Expression
expression-1 ... expression-n
specifies a value associated with each result-expression. If the value of an
expression in a WHEN clause matches case-expression, then simple CASE
returns the associated result-expression value. If there is no match, the
CASE expression returns the value expression specified in the ELSE clause, or
NULL if the ELSE value is not specified.
result-expression-1 ... result-expression-n
specifies the result value expression associated with each expression in a
WHEN clause of a simple CASE, or with each search-condition in a WHEN
clause of a searched CASE. All of the result-expressions must have
comparable data types, and at least one of the result-expressions must
return a nonnull.
result-expression
follows the ELSE keyword and specifies the value returned if none of the
expressions in the WHEN clause of a simple CASE are equal to the case
expression, or if none of the search conditions in the WHEN clause of a searched
CASE are true. If the ELSE result-expression clause is not specified, then
CASE returns NULL. The data type of result-expression must be
comparable to the other results.
search-condition-1 ... search-condition-n
specifies conditions to test for in a searched CASE. If a search-condition is
true, the CASE expression returns the associated result-expression value. If
no search-condition is true, the CASE expression returns the value
expression specified in the ELSE clause, or NULL if the ELSE value is not
specified.
Examples
SELECT last_name, first_name,
CASE jobcode
WHEN 100 THEN "Manager"
WHEN 250 THEN "Information Designer"
WHEN 420 THEN "Software Engineer"
WHEN 600 THEN "Database Administrator"
ELSE NULL
END
FROM employee
SELECT last_name, first_name,
CASE
WHEN deptnum=1000 THEN salary * 1.1
WHEN deptnum=2000 THEN salary * 1.5
WHEN deptnum=3000 THEN salary * 1.8
ELSE salary