16.20 - DECIMAL Result Data Type - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Functions, Expressions, and Predicates

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

The result data type for binary arithmetic operations involving DECIMAL operands is as follows:

When the operand on the left is … And the operand on the right is … And the operator is … Then the result data type is …
DATE DECIMAL(k,j) * / MOD DECIMAL(p,j)

These operations on DATE do not report an error, but results are generally not meaningful.

BYTEINT

SMALLINT

INTEGER

BIGINT

* / + - MOD DECIMAL(p,j)
DECIMAL(m,n) BYTEINT

SMALLINT

INTEGER

BIGINT

+ - * DECIMAL(p,n)
DATE * DECIMAL(p,n)

These operations on DATE do not report an error, but results are generally not meaningful.

In these cases the value of p, the number of digits in the decimal result, depends on:
  • The value specified for MaxDecimal in DBSControl.

    For more information on DBSControl and MaxDecimal, see “DBS Control utility” in Teradata Vantage™ - Database Utilities , B035-1102 .

  • The number of digits in the decimal operand, where the number of digits is k for a DECIMAL(k,j) operand on the right side of the operator or m for a DECIMAL(m,n) operand on the left side of the operator.
IF MaxDecimal is … AND the number of digits in the decimal operand is … THEN p is …
0 or 15 ≤ 15 15
> 15 and ≤18 18
> 18 38
18 ≤ 18 18
> 18 38
38 any value 38
When the operand on the left is … And the operand on the right is … And the operator is … Then the result data type is …
DECIMAL(m,n) DECIMAL(k,j) + - DECIMAL(min(p, (1+max(n,j)+max(m -n,k -j))),max(n,j))
* DECIMAL(min(p,m +k),(n +j))
/ MOD DECIMAL(p,max(n,j))

In these cases, the value of p in the definition of the decimal result data type depends on the value specified for MaxDecimal in DBSControl and the number of digits in the DECIMAL(m,n) and DECIMAL(k,j) operands.

IF MaxDecimal is … AND … THEN p is …
0 or 15 m and k ≤ 15 15
(m or k > 15) and (m and k ≤18) 18
m or k > 18 38
18 m and k ≤ 18 18
m or k > 18 38
38 m and k = any value 38