This section describes the behavior of various operators when using the NUMERIC data type.
Arithmetic operators
If an arithmetic operator has two expressions of the same type, the result has the same data type with the precision and scale defined for that type. If an operator has two expressions with different numeric data types, the rules of data type precedence define the data type of the result. The result has the precision and scale defined for its data type.
The following table defines how the precision and scale of the result are calculated when the result of an operation is of the NUMERIC data type. The result is numeric when either of the following is true:
• Both expressions are numeric.
• One expression is numeric and the other is a data type with a lower precedence than numeric.
The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not numeric is the precision and scale defined for the data type of the expression or it is calculated from its literal value.
Operation
Result precision
Result scale
e1+e2
max(s1, s2) + max(p1-s1, p2-s2) + 1
max(s1,s2)
e1-e2
max(s1, s2) + max(p1-s1, p2-s2) + 1
max(s1,s2)
e1*e2
p1+p2+1
s1+s2
e1/e2
p1 - s1 + s2 + max(MIN_SCALE, s1 + p2 + 1)
max(MIN_SCALE, s1 + p2 + 1)
When the result precision is greater than 127, the precision resets to 127 and the corresponding scale is reduced to accommodate the mantissa.
However, if the reduced scale is less than MINIMUM SCALE, the scale resets to:
• MINIMUM SCALE for DIVISION
• MIN (MINIMUM SCALE, computed scale) for ADDITION, SUBTRACTION, and MULTIPLICATION
Note: If you do not specify a value for MINIMUM SCALE in IP:
• A default MINIMUM SCALE of 3 is used for ADDITION, SUBTRACTION, and MULTIPLICATION.
• A default MINIMUM SCALE of 6 is used for DIVISION.
Example
num_col1 NUMERIC (70, 6) --> P = 70, S = 6, M = 70-6 = 64
num_col2 NUMERIC (60, 6) --> P = 60, S = 6, M = 60-6 = 54
num_col1* num_col2 --> P = 131, S = 12
In this example, as the result precision is greater than 127, it resets to 127 and the corresponding scale is reduced to prevent the integral part of a result from being truncated. Therefore, P = 127 and S = 12 – (131 - 127) = 8.
However, if MINIMUM SCALE specified in IP is 10, the scale resets to 10 instead of 8. Therefore, the result precision and scale will be 127 and 10 respectively.
num_col1* num_col2 --> P = 127, S = 10
UNION operator
When two expressions of numeric data type having different lengths are compared using UNION, the resulting length is the maximum length of the two expressions.
