Understanding multiplication of decimals

Database
Highlighted
Teradata Employee

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) ?

 

 

 

 


Accepted Solutions
Junior Supporter

Re: Understanding multiplication of decimals

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
Junior Supporter

Re: Understanding multiplication of decimals

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