Tools & Utilities

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

03-30-2006
07:36 AM

03-30-2006
07:36 AM

Numeric overflow

I am running this query

SELECT 12345678901234567 (DECIMAL(18,2)) (FORMAT 'ZZ,ZZZ,ZZZ,ZZZ,ZZZ,ZZ9.99')

Error comes:

Code = 2616: Numeric overflow occurred during computation.

If I try to increase the Decimal from 18 to 19 then another error says

SELECT 12345678901234567 (DECIMAL(19,2)) (FORMAT 'ZZ,ZZZ,ZZZ,ZZZ,ZZZ,ZZ9.99')

Error comes

Code = 3784.3784: The number of digits specified must be between 1 and 18.

Could any one help me in displaying the numeric data of this length.

I can't cast it as charr here as in the actual query I am performing sum on this column.

Thanks!

SELECT 12345678901234567 (DECIMAL(18,2)) (FORMAT 'ZZ,ZZZ,ZZZ,ZZZ,ZZZ,ZZ9.99')

Error comes:

Code = 2616: Numeric overflow occurred during computation.

If I try to increase the Decimal from 18 to 19 then another error says

SELECT 12345678901234567 (DECIMAL(19,2)) (FORMAT 'ZZ,ZZZ,ZZZ,ZZZ,ZZZ,ZZ9.99')

Error comes

Code = 3784.3784: The number of digits specified must be between 1 and 18.

Could any one help me in displaying the numeric data of this length.

I can't cast it as charr here as in the actual query I am performing sum on this column.

Thanks!

12 REPLIES

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

03-31-2006
11:00 AM

03-31-2006
11:00 AM

Re: Numeric overflow

With DECIMAL(18,2), you can only have 16 digits to the left of the decimal. The number you are using has 17 digits to the left of the decimal. That's why you are getting the numeric overflow.

Good luck!

Barry

Good luck!

Barry

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

04-03-2006
04:51 AM

04-03-2006
04:51 AM

Re: Numeric overflow

Thanks for the reply. I know (18,2) will only have 16 digits to the left of the decimal. But my question is how can we treat a number having more than 16 digits to the left of the decimal. I hope I am making my self clear this time.

Cheers!

Cheers!

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

04-04-2006
08:32 AM

04-04-2006
08:32 AM

Re: Numeric overflow

The only way that I can think of that you can do this is to break the column apart into two numbers. Then, sum them up separately, and then put them back together again into a CHAR column. When you put them back together, you have to add the overflow digits from the second part back into the first part and remove them from the second part.

select sum((big_number_col - (big_number_col mod

1000000000)) / 1000000000) as first_part,

sum(big_number_col mod 1000000000)

as second_part,

second_part - (second_part mod 1000000000)

as overflow_digits,

first_part + overflow_digits

(format 'zzz,zzz,zzz,zz9,') (CHAR(16))

as sum_first_part,

second_part mod 1000000000

(format '999,999,999.99') (CHAR(15))

as sum_second_part,

sum_first_part || sum_second_part

This is not straightforward at all, so hopefully you don't have to do this with very many columns.

Good luck.

Barry

select sum((big_number_col - (big_number_col mod

1000000000)) / 1000000000) as first_part,

sum(big_number_col mod 1000000000)

as second_part,

second_part - (second_part mod 1000000000)

as overflow_digits,

first_part + overflow_digits

(format 'zzz,zzz,zzz,zz9,') (CHAR(16))

as sum_first_part,

second_part mod 1000000000

(format '999,999,999.99') (CHAR(15))

as sum_second_part,

sum_first_part || sum_second_part

This is not straightforward at all, so hopefully you don't have to do this with very many columns.

Good luck.

Barry

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

04-05-2006
03:50 AM

04-05-2006
03:50 AM

Re: Numeric overflow

Thanks Barry!

I thought there should be a straight forward way of doing it, but after looking at your reply I also believe that it could only be done this way in Teradata.

Amit

I thought there should be a straight forward way of doing it, but after looking at your reply I also believe that it could only be done this way in Teradata.

Amit

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

04-05-2006
01:34 PM

04-05-2006
01:34 PM

Re: Numeric overflow

It may be worth mentioning that the upcoming release V2R6.2 will support up to 38 digits for the DECIMAL data type.

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

08-05-2011
08:29 AM

08-05-2011
08:29 AM

Re: Numeric overflow

when we are running below query

SELECT

sd.fpd,

scr.pgi,fsh.cust_type_id,fsh.chnl_id,fsh .sub_sgmnt_cd,

sd.drvr_grp_fincl_id,

SUM(sd.wghtd_orgnl_val) (DECIMAL(18,6)) AS drvr_val,

COUNT(sd.srv_accs_id) (DECIMAL(18,6)) cnt_ctn

FROM db1.abc sd

INNER JOIN db1.efg fsh

ON sd.fpd BETWEEN fsh.fped AND fsh.fincl_period_end_dt

AND sd.srv_accs_id = fsh.srv_accs_id

LEFT OUTER join db1.klm

ON scr.sec_geo_id = fsh.grp_geo_id and scr.grrcd = '52'

WHERE sd.fpd = '2011-05-01'

AND grp_geo_id <> 7126

GROUP BY 1,2,3,4,5,6

i am geting error as

2616: Numeric overflow occurred during computation.

Output directed to Answerset window

may i know why iam geting this error

SELECT

sd.fpd,

scr.pgi,fsh.cust_type_id,fsh.chnl_id,fsh .sub_sgmnt_cd,

sd.drvr_grp_fincl_id,

SUM(sd.wghtd_orgnl_val) (DECIMAL(18,6)) AS drvr_val,

COUNT(sd.srv_accs_id) (DECIMAL(18,6)) cnt_ctn

FROM db1.abc sd

INNER JOIN db1.efg fsh

ON sd.fpd BETWEEN fsh.fped AND fsh.fincl_period_end_dt

AND sd.srv_accs_id = fsh.srv_accs_id

LEFT OUTER join db1.klm

ON scr.sec_geo_id = fsh.grp_geo_id and scr.grrcd = '52'

WHERE sd.fpd = '2011-05-01'

AND grp_geo_id <> 7126

GROUP BY 1,2,3,4,5,6

i am geting error as

2616: Numeric overflow occurred during computation.

Output directed to Answerset window

may i know why iam geting this error

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

08-05-2011
09:29 AM

08-05-2011
09:29 AM

Re: Numeric overflow

either your sum(sd.wghtd_orngl_val) exceeds 999999999999.999999

or the count(sd.srv_accs_id) exceeds the maximum value storeable in decimal(18,6)...try expanding your casted datatype targets from decimal(18,6) to that can hold larger values

or the count(sd.srv_accs_id) exceeds the maximum value storeable in decimal(18,6)...try expanding your casted datatype targets from decimal(18,6) to that can hold larger values

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

06-07-2012
03:20 AM

06-07-2012
03:20 AM

Re: Numeric overflow

Hi

I am facing the numeric overflow error with the below query.

**Error:**

*** Failure 2616 Numeric overflow occurred during computation.

Statement# 1, Info =0

*** Total elapsed time was 17 seconds.

**Query:**

**INSERT** **INTO** tableA

** ( col1,**

** col2**

** , id**

** ,col3**

** )**

** WITH RECURSIVE recursive_tbl**

** ( col 1,**

** col 2**

** ,recurselevel**

** ,col 3**

** )**

** AS**

** ( SELECT Table B**

** col1 ,**

** col2,**

** 1 ,**

** '' ,**

** FROM Table c**

** UNION ALL**

** SELECT**

** Col1 ,**

** Col 2,**

** **

** r.recurselevel + 1,**

** ''**

** FROM recursive_tbl AS r**

** **

** )**

** SEL**

** Col1,**

** Col 2,**

** recurselevel ,**

** col3**

** FROM recursive_tb**

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

06-07-2012
04:22 AM

06-07-2012
04:22 AM

Re: Numeric overflow

what might be the datatype of 1?

Don't you controll the max recurisve dept?