Hi,
I have a procedure which does some mathematical calculations and returns the data. I am getting the arithmetic Overflow Error. The details are below.
Please let me know how to resolve it.


DETAILS:
1) It uses a function which takes input parameter of decimal(26,7) and and return value of same form decimal(26,7)
2) The qty field of tablea is of type decimal (31,9)
3) The fields from Tablec are character fields and I cast them to decimal
4) The price from tableb is decimal(15,6)

Query:
select a.qty,
coalesce(b.price)
CASE WHEN c.cola LIKE '3%' OR c.cola LIKE '8%'
THEN DECIMAL(a.qty
* DECIMAL(function(DECIMAL(COALESCE(b.price,0),26,7) ),31,4)
* DECIMAL(c.colb,26,7)
* (CASE WHEN b.cola IN ( '05','06','07','A' ) THEN 0.01
ELSE 1
END )
,31,4)
WHEN c.cola LIKE '5%' OR c.cola LIKE '6%' OR c.cola LIKE '7%'
THEN DECIMAL(a.qty
* DECIMAL(function(DECIMAL(COALESCE(b.price,0),26,7) ,31,4)
* (CASE WHEN DECIMAL(c.colc,26,7) = 0 THEN 1
ELSE DECIMAL(c.colc,26,7) * 0.01
END )
* (CASE WHEN b.cola IN ( '05','06','07','A' ) THEN 0.01
ELSE 1
END )
,31,4)

ELSE DECIMAL(a.qty
* DECIMAL(function(DECIMAL(COALESCE(b.price,0),26,7) ),31,4)
, 31,4)
end
from tablea a,
tableb b,
table c
-- all the join and filter conditions



------------------------------------------------------------------
I tried various cast options .But it breaks for some or the other value..
On manual check example value is 1233333332.22222222222222222
I guess this way in the limit of 31.4 (the decimal will be truncated and thats ok with me)

If after multiplication a value larger than 1e27 is produced, the final cast to DECIMAL(31,4) will give the "overflow" error.

A simpler example, to verify this behaviour:So, even where both 23 and 10 are valid DECIMAL(4,2) values, 230 isn't, so you see the overflow; the following variant would not have the overflow:So it really depends on the order of magnitude of a.qty, c.colc, *and* of the value returned by function().
Actually, even the following wouldn't overflow (rather "underflow", but that's allowed):

Thats right Peter...
But consider the following example.
SELECT DECIMAL((1234578909999.000000000 * 3000.000000000),31,9)
FROM SYSIBM.SYSDUMMY1
This give me overflow error.
But if you calculate this should turn out to be
3703736729997000 without decimal which is less then 1e22 (31-9)....
So why does it give the overflow error..

Thanks.

Good example, which clearly accentuates the most important feature of the DECIMAL datatype, and where it differs from datatypes like FLOAT or DECFLOAT:
by specifying the 9 digits after the decimal point, even if they are 0, you indicate that you *want* to keep the precision to 9 positions after the decimal point. Otherwise said, a number like 9.00 is *not* the same as e.g. 9.0, that is: both are stored differently because they have different datatypes.
This would not be the case for datatypes FLOAT and DECFLOAT, where both would be stored identically.
As a consequence, and regardless of the content of the arguments, multiplying a DEC(22,9) with a DEC(13,9) will *always* produce a DEC(35,18): this is the only way to guarantee that no digits get lost, i.e., that there is no precision loss, which is exactly why people are using DECIMAL in the first place.
Why does DB2 not look at the content first, to decide that the result could be a DEC(17,0) and need not be a DEC(35,18)? That's of course not the way DB2 works: at "compile time", it's decided that the multiplication of a DEC(22,9) with a DEC(13,9) needs to be a DEC(35,18); indeed, normally the result of a SELECT...FROM returns several rows, all of which will have the same datatype DEC(35,18). The user chose the input datatypes to be DEC, so decided that loss of precision is not acceptable.
To deviate from this "blind" datatype selection, type casting must be done.
so, either cast both arguments to FLOAT (or REAL or DOUBLE) in order to perform the multiplication as floating point numbers (with possible loss of precision), then cast the result back to DEC(22,0) or whatever is wanted.
Or better, to avoid rounding errors caused by back-and-forth converting between binary and decimal representations, cast both arguments to DECFLOAT, multiply as DECFLOATs, then cast to DECIMAL of the wanted precision (or do not cast at all).
In both cases you risk to loose or mutilate low-order digits, but that's your choice. Of course you'll never loose high-order digits: that would cause a -406 error instead of just a +802 warning.

An example:will return 975461059740893000. while without the casts to DOUBLE one would have the overflow warning. The same holds for your example:
returns the expected 3703736729997000.000000000 (note the 9 zeros at the end: the datatype was decided at "compile time").
But the truncation risk is always present, *both* during the multiplication (binary truncation because of a binary multiplication) *and* because of the final cast to DECIMAL.