All Forums UDA
samvs 6 posts Joined 07/09
14 Nov 2009
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,5order by 1,2,3,4,5Pls help!

Jimm 298 posts Joined 09/07
19 Nov 2009

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 GSame for H/ I

20 Nov 2009

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.

05 Dec 2009

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...

Alexandra 1 post Joined 04/10
13 Apr 2010

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';

ganmku 9 posts Joined 04/10
19 Apr 2010

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.

Ganmku

parampp1986 1 post Joined 04/10
28 Apr 2010

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

/Param

SSG_TDUSER 3 posts Joined 04/10
08 Jul 2010

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.

Jimm 298 posts Joined 09/07
08 Jul 2010

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!

thakurrabikant 2 posts Joined 09/09
22 Jul 2010

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.......

Rabikant Thakur
Email:thakurrabikant@gmail.com

spriggy 2 posts Joined 05/13
06 May 2013

If the above did not work as it did not work for me with my AVG aggregate, cast it as follows:
SELECT AVG( CAST( column_name AS DECIMAL(16,0))) AS new_nameĀ 

You must sign in to leave a comment.