Numeric overflow

Tools
Enthusiast

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!

12 REPLIES
Enthusiast

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
Enthusiast

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!
Enthusiast

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
Enthusiast

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
Enthusiast

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

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
Enthusiast

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

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

Supporter

Re: Numeric overflow

what might be the datatype of 1?

Don't you controll the max recurisve dept?