All Forums General
anithin1 1 post Joined 04/16
04 Apr 2016
Error 2616 while doing an insert

Hello,
 
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! 

dnoeth 4628 posts Joined 11/04
05 Apr 2016

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.

Dieter

AtardecerR0j0 71 posts Joined 09/12
06 Apr 2016

Hi anithin1
Have a look to this piece of SQL code to undertand the problem:

drop table MyNumbers;
create multiset volatile table MyNumbers
(
numero integer
)primary index( numero )
on commit preserve rows;
select * from MyNumbers

insert into MyNumbers values(2147483647);--This is the max integer value

--Other values
insert into MyNumbers 
select random(0,2147483647)
from sys_calendar.calendar
sample 100;

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

Be More!!

You must sign in to leave a comment.