I am recently running into the 2616 error (Numeric overflow during computation). This happens only when i try to do an insert - Select into a table. If i run it as a stand alone select, it works fine. What's more confusing is, the insert-select is scheduled to run on a daily basis and has been running fine, but over the last few days i am running into this.
Any insight on this would be great, thanks!
The result of a calculation exceeds the defined range of one of the target columns.
You could do a CREATE VOLATILE TABLE vt AS (SELECT ....) WITH NO DATA; to get the resulting data types and compare to the target table. Of course this is just a hint as you might have a DEC(38,4) in the SELECT, but the result still fits into a DEC(10,4).
Or you do a MAX on the numeric columns and check if it fits the definition.
Have a look to this piece of SQL code to undertand the problem:
drop table MyNumbers;
create multiset volatile table MyNumbers
)primary index( numero )
on commit preserve rows;
select * from MyNumbers
insert into MyNumbers values(2147483647);--This is the max integer value
insert into MyNumbers
select sum(numero) from MyNumbers;--Error 2616
select sum(numero (bigint)) from MyNumbers;--No Error
So first of all you should identify what column is getting out of the data type range