SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-4
Examples—CASE Expression
WHEN target-value = value-3 THEN result-3
...
WHEN target-value = value-n THEN result-n
ELSE value-x
END
Examples—CASE Expression

This example decodes movie_type and returns NULL if movie_type does not
match any of the listed values:
SELECT movie_name,
CASE movie_type
WHEN 1 THEN "Horror"
WHEN 2 THEN "Comedy"
WHEN 3 THEN "Drama"
ELSE NULL
END
FROM movies;

This example returns last_name, first_name, and a value based on salary that
depends on the value of employee.dept_num:
SELECT last_name, first_name,
CASE
WHEN dept_num = 9000 THEN salary * 1.10
WHEN dept_num = 1000 THEN salary * 1.12
ELSE salary
END
FROM employee;
CAST Function
The CAST expression converts data to the data type you specify.
expression
specifies the operand to convert to the data type data-type.
If the operand is an expression, data-type depends on the data type of
expression and follows the rules outlined under Valid Considerations—CAST on
page C-5.
If the value of the expression is null, the result of CAST is NULL, regardless of the
data type you specify.
data-type
specifies a data type to associate with the operand of CAST. For more information,
see Data Types on page D-1.
CAST ( expression AS data-type )