SUM of a DOUBLE column might return SQL0802N if an intermediate
floating point calculation returns a value that is
outside the range for DOUBLE, even though a reasonable result is
expected.
The following example could reasonably return a SUM of 0, but
instead
generates an error.
db2 connect to sample
db2 "drop table mbj"
db2 "CREATE TABLE mbj(value INTEGER,number DOUBLE)"
db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, 3.98000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
db2 "SELECT SUM(number) FROM mbj"
Since the sum of the negative rows appears to be equivalent to
the sum
of the positive rows, a result of zero would be expected.
>db2 "SELECT SUM(number) FROM mbj"
1
------------------------
SQL0802N Arithmetic overflow or other arithmetic exception
occurred.
SQLSTATE=22003
This error might not occur with similar examples against other
database Servers.
This behavior is working as designed since double is not a
precise data type, but we will accommodate requirements from
applications like SAP and return 0 instead of SQL0802N in this
scenario when SAP is used.
Workaround:
Cast column to DECFLOAT or DECIMAL:
db2 "select SUM( DECFLOAT(number)) from mbj"
or
Use DECFLOAT or DECIMAL data type instead. |
