Neoview SQL Reference Manual (R2.4 SP2)

If all the possible return values are of numeric types, none are NUMERIC, REAL, FLOAT, or
DOUBLE PRECISION, and at least one is of type DECIMAL, then the returned value will be of
type DECIMAL.
If the returned value is of type NUMERIC or DECIMAL, it has a precision equal to the sum of:
• the maximum scale of all the possible return value types and
• the maximum value of (precision - scale) for all the possible return value types.
However, the precision will not exceed 18.
The scale of the returned value is the minimum of:
• the maximum scale of all the possible return value types and
• 18 - (the maximum value of (precision - scale) for all the possible return value types).
The number of components in the DECODE function, including expr, test_exprs, retvals,
and default, has no limit other than the general limit of how big an SQL expression can be.
However, large lists do not perform well.
The syntax:
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
There is no special conversion of expr, test_exprN, or retvalN other than what a CASE
statement normally does.
Examples of DECODE
• Example of the DECODE function:
SELECT emp_name,
decode(CAST (( yrs_of_service + 3) / 4 AS INT ) ,
0,0.04,
1,0.04,
0.06) as perc_value
FROM employees;
SELECT supplier_name,
decode(supplier_id, 10000, 'Company A',
10001, 'Company B',
10002, 'Company C',
'Company D') as result
FROM suppliers;
• This example shows a different way of handling NULL specified as default and not specified
as default explicitly:
SELECT decode( (?p1 || ?p2), trim(?p1), ‘Hi’, ?p3, null )
from emp;
..
*** ERROR[4049] A CASE expression cannot have a result data type of both CHAR(2)
and NUMERIC(18,6).
*** ERROR[4062] The preceding error actually occurred in function
DECODE((?P1 || ?P2),(‘ ’ TRIM ?P1), ‘Hi’, ?P3, NULL)
*** ERROR[8822] The statement was not prepared.
The last ret-val is an explicit NULL. When Neoview SQL encounters this situation, it
assumes that the return value will be NUMERIC(18,6). Once Neoview SQL determines that
the return values are numeric, it determines that all possible return values must be numeric.
DECODE Function 381