SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-18
Examples of CASE
For example, if result-expression-1 and result-expression-2 have data
type NUMERIC(5) and result-expression-3 has data type NUMERIC(8,5), the
result data type is NUMERIC(10,5).
If any data type of the result expressions is approximate numeric, the result data type
is approximate numeric with precision equal to the maximum of the precisions of the
result expressions.
Datetime Data Type
If the data type of the result expressions is datetime, the result data type is the same
datetime data type.
Interval Data Type
If the data type of the result expressions is interval, the result data type is the same
interval data type (either year-month or day-time) with the start field being the most
significant of the start fields of the result expressions and the end field being the least
significant of the end fields of the result expressions.
Examples of CASE
•
Use a simple CASE to decode JOBCODE and return NULL if JOBCODE does not
match any of the listed values:
SELECT last_name, first_name,
CASE jobcode
WHEN 100 THEN 'MANAGER'
WHEN 200 THEN 'PRODUCTION SUPV'
WHEN 250 THEN 'ASSEMBLER'
WHEN 300 THEN 'SALESREP'
WHEN 400 THEN 'SYSTEM ANALYST'
WHEN 420 THEN 'ENGINEER'
WHEN 450 THEN 'PROGRAMMER'
WHEN 500 THEN 'ACCOUNTANT'
WHEN 600 THEN 'ADMINISTRATOR ANALYST'
WHEN 900 THEN 'SECRETARY'
ELSE NULL
END
FROM persnl.employee;
LAST_NAME FIRST_NAME (EXPR)
-------------------- --------------- -----------------
GREEN ROGER MANAGER
HOWARD JERRY MANAGER
RAYMOND JANE MANAGER
...
CHOU JOHN SECRETARY
CONRAD MANFRED PROGRAMMER
HERMAN JIM SALESREP
CLARK LARRY ACCOUNTANT