Error 2616 while doing an insert

General

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! 

2 REPLIES
Senior Apprentice

Re: Error 2616 while doing an insert

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.

Teradata Employee

Re: Error 2616 while doing an insert

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