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

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!

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

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

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

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

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.

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

what might be the datatype of 1?

Don't you controll the max recurisve dept?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

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

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!