Numeric overflow occurred during computation?

Database

Numeric overflow occurred during computation?

Hi, I have two doubt about the sql statements below:

first:
select cast(9999999999999999.9 as decimal(17,1))*1.2;
select cast(9999999999999999.9 as decimal(18,1))*1.2;

the result will be like "Numeric overflow occurred during computation"

but"select cast(9999999999999999.9 as decimal(19,1))*1.2 "will work without error report.

second:
select SUM(CAST(a AS DECIMAL(18,2)) * CAST( b AS DECIMAL(18,2))) from t;

the result will be like "Numeric overflow occurred during computation"

but

"select SUM(CAST(a * b AS DECIMAL(18,2)) from t;"

work without error report.

Could anyone tell me why? Thanks!

Tags (1)
5 REPLIES
Enthusiast

Re: Numeric overflow occurred during computation?

The explanation for your first example can be found in the SQL Reference: Functions and Operators on page 47 and footnote 7 on page 48. I will try my best to summarize my interpretation here:

I believe the data type on the right hand side of your equation is implied as DECIMAL(5,0). If that is the case then the resulting data type in your equation is DECIMAL(18,2) but the actual result is DECIMAL(19,2). By explicitly casting to DECIMAL(19,1) the resulting number of digits in the calculation is increased to DECIMAL(38).

Without the actual values of A or B in your second example it makes it more difficult to determine how Teradata may be handling the computation. But by explicitly casting your operands to DECIMAL(18,2) before multiplying them your resulting data type will be DECIMAL(18,4). If the values being combined exceed DECIMAL(18,4) then you are going to have a problem. (See the SQL Reference: Functions and Operators section referenced above for how that works out.)

Furthermore, casting them after the computation is going to GAIN you two orders of magnitude for the result to fit in.

Hope this helps.

Re: Numeric overflow occurred during computation?

Thank you very much!!!!

Re: Numeric overflow occurred during computation?

This is very helpful. Thanks.

Visitor

Re: Numeric overflow occurred during computation?

Had the same issue today. Casting it to a float instead of decimal worked! : )

 

Hope this helps someone.

 

Cheers!

Senior Apprentice

Re: Numeric overflow occurred during computation?

Of course Floats work, but you loose precision.