Examples: Rounding - Teradata Vantage NewSQL Engine - 16.20

Teradata Vantage™ Data Types and Literals

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

The following SELECT statement returns 48 and 48.

SELECT 47.5(FORMAT 'zzzz'), 48.5(FORMAT('zzzz')) ;

A FORMAT clause can cause the number to be rounded; for example, consider the following:

SELECT 1.3451 (FORMAT 'zz.z');

The FORMAT clause operates on 1.3451 and returns 1.3.

SELECT 13451 / 10000.000

1.345 is returned as expected.

SELECT 13451 / 10000.000 (FORMAT 'zz.z');

The arithmetic expression is evaluated first (yielding 1.345), then the FORMAT clause is applied, returning 1.3.

SELECT 13451 / 10000.00 (FORMAT 'zz.z');

Note that two ‘roundings’ occur; first to 1.35 when evaluating the expression, then a second rounding to 1.4 as a result of the FORMAT statement.

To avoid double ‘roundings’, extend the precision of the arithmetic expression two decimal places beyond that of the FORMAT clause.