All Forums Tools
Amit 8 posts Joined 02/06
30 Mar 2006
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 comesCode = 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!

Barry-1604 176 posts Joined 07/05
31 Mar 2006

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

Amit 8 posts Joined 02/06
03 Apr 2006

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!

Barry-1604 176 posts Joined 07/05
04 Apr 2006

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 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_partThis is not straightforward at all, so hopefully you don't have to do this with very many columns. Good luck.Barry

Amit 8 posts Joined 02/06
05 Apr 2006

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

Jim Chapman 449 posts Joined 09/04
05 Apr 2006

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

vuduthala 10 posts Joined 12/10
05 Aug 2011

when we are running below query
scr.pgi,fsh.cust_type_id,fsh.chnl_id,fsh .sub_sgmnt_cd,
SUM(sd.wghtd_orgnl_val) (DECIMAL(18,6)) AS drvr_val,
COUNT(sd.srv_accs_id) (DECIMAL(18,6)) cnt_ctn
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

VandeBergB 182 posts Joined 09/06
05 Aug 2011

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

Some drink from the fountain of knowledge, others just gargle.

murthy1234 4 posts Joined 06/12
07 Jun 2012


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


 *** Failure 2616 Numeric overflow occurred during computation.
                Statement# 1, Info =0
 *** Total elapsed time was 17 seconds.




           ( col1,


          , id




 WITH RECURSIVE  recursive_tbl

               ( col 1,

                 col 2


    ,col 3




               ( SELECT  Table B

             col1 ,


                 1  ,

                 '' ,

                    FROM  Table c


               UNION ALL



                    Col1 ,

                                Col 2,


                                 r.recurselevel + 1,



                     FROM recursive_tbl AS r





                Col 2,

                 recurselevel    ,



           FROM  recursive_tb


ulrich 816 posts Joined 09/09
07 Jun 2012

what might be the datatype of 1?

Don't you controll the max recurisve dept?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

TamilTiger 9 posts Joined 12/13
09 Dec 2013

need urgent help:
column data type is decimal(8,2),i was trying to insert below value
SELECT CAST(9159742.9700 AS NUMERIC(8,2)),i m getting numeric overfloe error.but before decimal i have only 7 digits
please help me

M.Saeed Khurram 544 posts Joined 09/12
09 Dec 2013

Hi Parkash,


The data type DECIMAL(8,2) means that the value can include total 8 digits, including the decimal digits.


for example, in this case you can only have 6 digits left to the decimal point :)


TamilTiger 9 posts Joined 12/13
09 Dec 2013

thanks khurram

You must sign in to leave a comment.