Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-03-2010
04:50 AM

08-03-2010
04:50 AM

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!

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!

5 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-03-2010
06:15 AM

08-03-2010
06:15 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-03-2010
08:02 AM

08-03-2010
08:02 AM

Thank you very much!!!!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-23-2014
08:25 AM

09-23-2014
08:25 AM

This is very helpful. Thanks.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-12-2017
03:08 PM

06-12-2017
03:08 PM

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

Hope this helps someone.

Cheers!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-13-2017
01:50 PM

06-13-2017
01:50 PM

Of course Floats work, but you loose precision.