Database

turn on suggestions

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

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

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

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.