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_part
This 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.
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!