16.20.16.20 - 16.20 - Using CAST To Avoid Numeric Overflow - Teradata Vantage NewSQL Engine

Teradata Vantage™ Data Types and Literals

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

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.