skip to main content
Basic elements of SQL : Working with Numeric and Decimal data types : Calculating precision, scale, length : Scalar functions
 

Scalar functions

This section describes the behavior of some scalar functions when using the NUMERIC data type.
If IP does not specify MINIMUM SCALE, default value of 3 will be used.
Numeric functions
This section describes the behavior of some Numeric functions when using the NUMERIC data type.
ABS
For ABS, the result precision and scale are same as of the input expression.
CEILING and FLOOR
For CEILING and FLOOR, the scale is zero and precision is same as that of the input expression.
DEGREES
For DEGREES, the precision is 127 and scale is MINIMUM SCALE.
POWER
For POWER, the precision is 127 and scale is MINIMUM SCALE.
RADIANS
For RADIANS, the precision is 127 and scale is MINIMUM SCALE ROUND AND TRUNCATE.
Precision is same as that of the input expression and scale is same as that of the value specified by the second input parameter.
Aggregate Functions
This section describes the behavior of some Aggregate functions when using the NUMERIC data type.
MIN and MAX
For MIN and MAX, the result precision and scale are same as that of the input expression.
SUM
For SUM, the result precision has the maximum precision (P=127), whereas the scale remains same as that of the input expression.
VAR and VARP
For VAR and VARP, the result precision and scale are calculated as described below.
P = 2*input_precision + 1
S = 2*input_scale
When the result precision is greater than 127, the precision is set to 127 and the corresponding scale is reduced to preserve the mantissa. If the reduced scale is less than MINIMUM SCALE, the scale resets to MIN(MINIMUM SCALE, computed scale).
Example:
Let num_col1 be a column of type NUMERIC (66, 6) --> P = 66, S = 6, M = 66-6 = 60, and a MINIMUM SCALE of 10 is specified in IP .
For VAR(num_col1)and VARP(num_col1), scale and precision are calculated as described below.
P = 2 * 66 + 1 = 133
S = 2 * 6 = 12
As the result precision is greater than 127, the precision resets to 127 and the corresponding scale is reduced to prevent the integral part of the result from being truncated. Therefore, P = 127 and S = 12 – (133 - 127) = 6.
As the resultant scale (6) is less than the MINIMUM SCALE specified in IP, that is, 10 the scale resets to min(10,12) instead of 6. Therefore, the result precision and scale will be 127 and 10 respectively.
SQRT, AVG, STDEV, STDDEVP
For SQRT, AVG, STDDEV, and STDDEVP, the precision and scale are calculated as described below.
• Scale is MAX(MINIMUM SCALE, input_scale).
• Precision is same as the input precision if input_scale > MINIMUM SCALE. Otherwise, the precision is computed as shown below.
Precision = input_precision + (MINIMUM SCALE – input_scale).
If the result precision is greater than 127, the precision resets to 127 and scale is set to MINIMUM SCALE.
Note: The operations that involve SQRT, AVG, STDEV, and STDDEVP have “scale >= MINIMUM SCALE”.
Example:
Assume that num_col1 be a column of type NUMERIC (127, 4) --> P = 127, S = 4, M = 127-4 = 123, and the MINIMUM SCALE specified by IP is 10.
For AVG(num_col1), the precision and scale are calculated as described below.
S = MAX(10,input_expression_scale) = MAX(10,4) = 10.
In this example, the precision needs to be increased to accommodate the scale.
Therefore, P = P + (10-input_expression_scale) = P + (10-4) = P + 6 = 127 + 6 = 129.
As P > 127, precision resets to 127.
Therefore, P = 127 and S = 10.