Scale for decimal arithmetic
Derby Reference Manual
188
range of the target DECIMAL/NUMERIC(5,2) datatype.
An attempt to put an integer value of a larger storage size into a location of a smaller size
fails if the value cannot be stored in the smaller-size location. For example:
INSERT INTO mytable (int_column) values 2147483648;
ERROR 22003: The resulting value is outside the range for the
data type INTEGER.
Note: When truncating trailing digits from a NUMERIC value, Derby rounds down.
Scale for decimal arithmetic
SQL statements can involve arithmetic expressions that use decimal data types of
different precisions (the total number of digits, both to the left and to the right of the
decimal point) and scales (the number of digits of the fractional component). The
precision and scale of the resulting decimal type depend on the precision and scale of the
operands.
Given an arithmetic expression that involves two decimal operands:
· lp stands for the precision of the left operand
· rp stands for the precision of the right operand
· ls stands for the scale of the left operand
· rs stands for the scale of the right operand
Use the following formulas to determine the scale of the resulting data type for the
following kinds of arithmetical expressions:
· multiplication
ls + rs
· division
31 - lp + ls - rs
· AVG()
max(max(ls, rs), 4)
· all others
max(ls, rs)
For example, the scale of the resulting data type of the following expression is 27:
11.0/1111.33
// 31 - 3 + 1 - 2 = 27
Use the following formulas to determine the precision of the resulting data type for the
following kinds of arithmetical expressions:
· multiplication
lp + rp
· addition
2 * (p - s) + s
· division
lp - ls + rp + max(ls + rp - rs + 1, 4)
· all others
max(lp - ls, rp - rs) + 1 + max(ls, rs)
Data type assignments and comparison, sorting, and ordering
The following table displays valid assignments between data types in Derby. A "Y"
indicates that the assignment is valid.
