Numeric Overflow error

UDA
Enthusiast

Numeric Overflow error

Hi,

while firing the below sql, I am getting 'Numeric Overflow Occured during computation'

Select
A
,B
,C
,D
,E
,sum(F) AS G
,sum(H) AS I
from dbname.tblname
WHERE D <> 'R'
group by 1,2,3,4,5
order by 1,2,3,4,5

Pls help!
10 REPLIES
Enthusiast

Re: Numeric Overflow error

The total columns will have the same datatype and size as the columns you are totalling.
So if column F is smallint and the total does not fit in a smallint, you get numeric overflow.

As a last resort, you can make G and I into float - then numeric overflow is very unlikely but you could lose some significant digits. Get a realistic (and futureproof) size for the total and use this.
It will then be something like:

, Sum(F) (Float) AS G

Same for H/ I
Enthusiast

Re: Numeric Overflow error

Good point, or you can CAST it to a DECIMAL of (15,0). This should take care of this issue. But knowing your data demographics is the only solution.

Re: Numeric Overflow error

you will get the same kind of error(Numeric Over Flow Computation) if you try to cast a value which have a length of more than 8 characters.
An integer accomodates maximum length of 8 digits...

Re: Numeric Overflow error

Hi,
While running the below sql, I am also getting 'Numeric Overflow Occured during computation'
in Teradata.
Select
A
,B
,C
,D
,E
,F AS G
,H AS I
from dbname.tblname
WHERE D='2010-04-14'
group by 1,2,3,4,5,6,7;

but I remove the 'group by..' clause and add 'distinct' after select, and can solve this problem, pls see the below sql, I do not know why.

Select distinct
A
,B
,C
,D
,E
,F AS G
,H AS I
from dbname.tblname
WHERE D='2010-04-14';

Enthusiast

Re: Numeric Overflow error

No you wont get the result with distinct.
Better you cast the column which you are going to sum.this will definitely solve your problem.

Re: Numeric Overflow error

HI,
what is the datatype of the F&H column?

Re: Numeric Overflow error

Hi,

I am facing same kind of issue for below query.

select count(*) from tablename;

then i modified above query as select count(*) float from tablename;
it worked fine in teradata database using queryman.

but problem is that i want to get table count using oracle toad application.,for that i am using teradata db link from oracle database.

whenever i am running above sql in TOAD application it is giving error " FROM keyword not found where expected ".

can anyone of you provide reason behind this error and what could be its solution ?

thanks in advance.
Enthusiast

Re: Numeric Overflow error

Oracle does not recognise the same implicit datatype conversion as Teradata.
Try:
Select Cast(Count(*) As Float) From Tablename;

It is ANSI standard, so even Oracle should cope!

Re: Numeric Overflow error

In Teradata to avoid this error you can use:

select cast(count(*) as decimal(18,0)) from Databasename.Tablename

It will work, I had same problem but using this problem is solved.
Please try it.......