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
ands1
: precision and scale of the first operand in a calculationp2
ands2
: 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.
Operation  Resulting Precision (p)  Resulting Scale (s) 

Addition (+) and Subtraction ()  MAX(p1  s1, p2  s2) + MAX(s1, s2) + 1 
MAX(s1, s2) 
Multiplication (*)  p1 + p2 
s1 + s2 
Division (/)  p1  s1 + s2 + MAX(6, s1 + p2 + 1) 
MAX(6, s1 + p2 + 1) 
Modulo (%)  MAX(p1, p2) 
MAX(s1, s2) 
UNION, INTERSECT, EXCEPT  MAX(s1, s2) + MAX(p1  s1, p2  s2) 
MAX(s1, s2) 
CASE expression  MAX(p1  s1, p2  s2) + MAX(s1, s2) 
MAX(s1, s2) 
SUM function  38  Scale of input value 
AVG function  Precision of input value  Scale of input value 
TRUNC, ROUND functions  Precision of input value  MIN(scale of input value, scale of second argument to function) 
POWER function  38  See the following table. 
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. MAX(p1  s1, s1)  p2  s2  Result type 

<= 2  <= 1  (38,15) 
3  <= 1  (38, 8) 
Any number  Any number  (38, 6) 
For example, the following result is a
DECIMAL(38,8)
because
MAX(p1  s1, s1) = 3
and p2s2 = <=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 p2s2 = <=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 alwaysMAX(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)
.
 Return an overflow error if the integral part of the result still has more
than
 When the scale of the result is
 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.
 Return an overflow error if the integral part of the result still has more
than
 When the scale of the result is
 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.