UDA

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-14-2009
11:44 AM

11-14-2009
11:44 AM

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!

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-19-2009
04:42 PM

11-19-2009
04:42 PM

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

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

Highlighted
##
##### Re: Numeric Overflow error

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-20-2009
12:53 AM

11-20-2009
12:53 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-05-2009
07:19 AM

12-05-2009
07:19 AM

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

An integer accomodates maximum length of 8 digits...

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-13-2010
08:52 PM

04-13-2010
08:52 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-19-2010
02:06 AM

04-19-2010
02:06 AM

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.

Better you cast the column which you are going to sum.this will definitely solve your problem.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-28-2010
02:49 AM

04-28-2010
02:49 AM

HI,

what is the datatype of the F&H column?

what is the datatype of the F&H column?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-08-2010
09:57 AM

07-08-2010
09:57 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-08-2010
10:09 AM

07-08-2010
10:09 AM

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!

Try:

Select Cast(Count(*) As Float) From Tablename;

It is ANSI standard, so even Oracle should cope!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-22-2010
06:16 AM

07-22-2010
06:16 AM

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

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