Calculations with DECIMAL Values ​

This section explains the expected behavior when SQL functions and operators are applied to DECIMAL values.

Resulting Precision and Scale ​

The following table shows how precision and scale are computed for mathematical operations that return DECIMAL results.

• p1 and s1: precision and scale of the first operand in a calculation
• p2 and s2: precision and scale of the second operand in a calculation

For the results of all calculations, the maximum precision is 38 and the maximum scale is 38.

Resulting Scale for POWER ​

The following table shows how the scale is computed in a POWER function result. The precision is always 18, but the scale may be 15, 8, or 6. A minimum scale of 6 is always preserved. p1 and s1 are the precision and scale of the first argument to the function, and p2 and s2 are the precision and scale of the second argument.

For example, the following result is a DECIMAL(38,8) because MAX(p1 - s1, s1) = 3 and p2-s2 = <=1:

premdb=# select pow(10.999,0.9) from sys.const;
   pow     
------------
 8.65401975
(1 row)

The following result is a DECIMAL(38,15) because MAX(p1 - s1, s1) = 2 and p2-s2 = <=1:

premdb=# select pow(10.99,0.9) from sys.const;
       pow        
-------------------
 8.647646401207339
(1 row)

Overflow Conditions ​

Overflow conditions with DECIMAL calculations are managed (and where possible, prevented) as follows. See the previous table for related information about these calculations.

Addition (+) and Subtraction (-)

An addition or subtraction on two DECIMAL operands does not reduce the scale of the result in order to preserve the overall precision. The resulting scale is always MAX(s1, s2). This behavior means that calculations are more likely to return overflow errors but will not automatically and silently round down results. In many cases, you can safeguard overflow conditions by adding explicit casts.

When precision >38, return an overflow error if the integral part of the result still has more than p - s number of digits.

Multiplication (*)

• When the scale of the result is <=6, retain that scale.

• When the scale is >6, reduce it to tighten overall precision and try to prevent overflow.
• Compute (p1 + p2, s1 + s2):
• Return an overflow error if the integral part of the result still has more than p - s number of digits.
• If no overflow occurs, round the result to scale (s1 + s2).

Division (/)

• When the scale of the result is <=6, set the scale to 6.

• When the scale is >6, reduce it to tighten overall precision and try to prevent overflow.
• Compute (p1 - s1 + s2 + max(6, s1 + p2 + 1), max(6, s1 + p2 + 1)):
• Return an overflow error if the integral part of the result still has more than p - s digits.
• If no overflow occurs but the scale goes over the value of (MAX(6, s1 + p2 + 1), truncate (do not round) the scale.

Modulo (%)

Return an overflow error if the value of the integral part of either operand does not fit into the integral part of the result.

UNION, INTERSECT, EXCEPT operators

The behavior for DECIMAL values propagated by set operations is similar to the behavior for addition and subtraction:

• Reduce scale to tighten overall precision and try to prevent overflow.
• Return an overflow error if the integral part of the result has more than p - s digits.
• If no overflow occurs, round the result to scale MAX(s1, s2).

CASE expressions

The behavior for DECIMAL values propagated by CASE expressions is similar to the behavior for addition and subtraction.

• Reduce scale to tighten overall precision and try to prevent overflow.
• Round the result to scale MAX(s1, s2) before propagation.
• Return an overflow error if the integral part of the result has more than p - s digits.

POWER function

If the integral part of the result is greater than 38 minus the scale of the first argument, the function may return an overflow error.

Parent topic:DECIMAL