SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
9-50
DECODE Function
DECODE Function
The functionality of DECODE is similar to the CASE-WHEN-THEN-ELSE-END
expression. The DECODE function compares the expr with each test_expr one by
one in the order specified. If expr is equal to a test_expr, the function returns the
corresponding retval value. If no match is found, default is returned. If no match is
found and default is omitted, NULL is returned.
expr,test_expr,test_expr2 are SQL value expressions of comparable data
types.
retval is a SQL value expression.
default, retval, retval2,.. are SQL value expressions of comparable data
types.
DECODE (expr,test_expr,retval [,test_expr2,retval2 ... ] [ ,
default]) is logically equivalent to the following:
CASE WHEN (expr IS NULL AND test_expr IS NULL) OR
expr = test_expr THEN retval
WHEN (expr IS NULL AND test_expr2 IS NULL) OR
expr = test_expr2 THEN retval2
...
ELSE default /* or ELSE NULL if default not specified */
END
Considerations
•
In a DECODE function, two NULLs are considered to be equivalent. If expr is
NULL, then the returned value is the retval of the first test_expr that is also
NULL.
•
The arguments can be any of the numeric types or character types. However,
expr and each test_expr value must be of comparable data types. If expr and
test_expr values are character types, they must be in the same character set (to
be comparable types).
•
All the default and retval value, if any, must be of comparable types.
•
If expr and a test_expr value are character data, the comparison is made using
nonpadded comparison semantics.
•
If expr and a test_expr value are numeric data, the comparison is made with a
temporary copy of one of the numbers, according to the NonStop SQL/MX defined
rules of conversion. For example, if one number is integer and the other is decimal,
the comparison is made with a temporary copy of the integer converted to a
decimal.
DECODE (expr,test_expr,retval [,test_expr2,retval2 ... ] [ ,
default] )