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

02-09-2018
01:07 AM

02-09-2018
01:07 AM

I am trying to figure out what I am doing wrong in the following multiplication:

SELECT CAST(1234.60001 AS DECIMAL(9,5)) AS a, CAST(123456.34 AS DECIMAL(8,2)) AS b, a*b AS c

gives [2616] Numeric overflow occured during computation.

However, if I understand de documentation correctly, Teradata should decide that the outcome must be a DECIMAL(17,7).

9,5 + 8,2 = 17,7

The result of the computation should fit in DECIMAL(17,7):

SELECT cast(152419198.5985634 as DECIMAL(17,7)) works fine without error

Now when I do this:

SELECT CAST(1234.60001 AS DECIMAL(16,5)) AS a, CAST(123456.34 AS DECIMAL(8,2)) AS b, a*b AS c

The query works fine too and the result is a DECIMAL(18,7).

So why does Teradata give the original error and why does Teradata make the type DECIMAL(18,7) while the result should fit in DECIMAL(17,7) ?

Solved! Go to Solution.

Accepted Solutions

Highlighted

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

02-09-2018
02:44 AM

02-09-2018
02:44 AM

Understanding multiplication of decimals

I am trying to figure out what I am doing wrong in the following multiplication:

SELECT CAST(1234.60001 AS DECIMAL(9,5)) AS a, CAST(123456.34 AS DECIMAL(8,2)) AS b, a*b AS c

gives [2616] Numeric overflow occured during computation.

However, if I understand de documentation correctly, Teradata should decide that the outcome must be a DECIMAL(17,7).

9,5 + 8,2 = 17,7

The result of the computation should fit in DECIMAL(17,7):

SELECT cast(152419198.5985634 as DECIMAL(17,7)) works fine without error

Now when I do this:

SELECT CAST(1234.60001 AS DECIMAL(16,5)) AS a, CAST(123456.34 AS DECIMAL(8,2)) AS b, a*b AS c

The query works fine too and the result is a DECIMAL(18,7).

So why does Teradata give the original error and why does Teradata make the type DECIMAL(18,7) while the result should fit in DECIMAL(17,7) ?

Martijn,

By your own logic, the 2nd query should have result of 16,5 + 8,2 = 24,7

But instead you get result as 18,7

Looking in the manual, the default resulting type for your 1st query is DECIMAL(15,7).

Assuming your MaxDecimal in DBSControl is set to 15.

This may cause the overflow error, depending on the values.

(SQL Functions, Operators, Expressions, and Predicates - Chapter 3)

To avoid the error, cast one of the colums to 16,2 or 16,7 to force result of 18,7.

Teradata Frank, Certified Master

1 ACCEPTED SOLUTION

1 REPLY

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

02-09-2018
02:44 AM

02-09-2018
02:44 AM

Understanding multiplication of decimals

I am trying to figure out what I am doing wrong in the following multiplication:

SELECT CAST(1234.60001 AS DECIMAL(9,5)) AS a, CAST(123456.34 AS DECIMAL(8,2)) AS b, a*b AS c

gives [2616] Numeric overflow occured during computation.

However, if I understand de documentation correctly, Teradata should decide that the outcome must be a DECIMAL(17,7).

9,5 + 8,2 = 17,7

The result of the computation should fit in DECIMAL(17,7):

SELECT cast(152419198.5985634 as DECIMAL(17,7)) works fine without error

Now when I do this:

SELECT CAST(1234.60001 AS DECIMAL(16,5)) AS a, CAST(123456.34 AS DECIMAL(8,2)) AS b, a*b AS c

The query works fine too and the result is a DECIMAL(18,7).

So why does Teradata give the original error and why does Teradata make the type DECIMAL(18,7) while the result should fit in DECIMAL(17,7) ?

Martijn,

By your own logic, the 2nd query should have result of 16,5 + 8,2 = 24,7

But instead you get result as 18,7

Looking in the manual, the default resulting type for your 1st query is DECIMAL(15,7).

Assuming your MaxDecimal in DBSControl is set to 15.

This may cause the overflow error, depending on the values.

(SQL Functions, Operators, Expressions, and Predicates - Chapter 3)

To avoid the error, cast one of the colums to 16,2 or 16,7 to force result of 18,7.

Teradata Frank, Certified Master