17.10 - Using CAST To Avoid Numeric Overflow - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Data Types and Literals

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

Because of the way the Teradata SQL compiler works, it is essential that you CAST the arguments of your expressions whenever large values are expected.

For example, suppose f1 is defined as DECIMAL(14,2) and you are going to multiply by an integer or get SUM(f1).

In this case, the following operations:

CAST(f1 AS DECIMAL(18,2))*100

or

SUM(CAST(f1 AS DECIMAL(18,2)))

are proper techniques for ensuring correct answer sets.

On the other hand, if you were to cast the results of the expressions, such as the following:

CAST(f1*100 AS DECIMAL(18,2))

or

CAST(SUM(f1) AS DECIMAL(18,2)

then you will likely experience overflow during the computations (and before the CAST is made)—not the desired result.