17.10 - DECIMAL Result Data Type - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1145-171K
Language
English (United States)

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