Possible Result Overflow with SELECT Sum - Advanced SQL Engine - Teradata Database

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/djk1612415574830.ditamap
dita:ditavalPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/wrg1590696035526.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
Teradata Vantage™

Possible Result Overflow with SELECT Sum

When using this function, the result can create an overflow when the data type and format are not in sync. For a column defined as:

Salary Decimal(15,2) Format ‘$ZZZ,ZZ9.99’

The following query:

SELECT SUM (Salary) FROM Employee;

causes an overflow because the decimal operand and the format are not in sync.

To avoid possible overflows, explicitly specify the format for decimal sum to specify a format large enough to accommodate the decimal sum resultant data type.

SELECT Sum(Salary) (format ‘$Z,ZZZ,ZZZ,ZZ9.99) FROM Employee;