17.10 - Example: Comparing Floating Point Values - 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)

Calculations involving floating point values often produce results that are not what you expect. If you perform a floating point calculation and then compare the results against some expected value, it is unlikely that you get the intended result. Consider the results of Example: Non-Associativity of Floating Point Arithmetic.

Instead of comparing the results of a floating point calculation, make sure that the result is greater or less than what is needed, with a given error. Here is an example of how to rewrite the statement in Example: Non-Associativity of Floating Point Arithmetic and achieve the desired results:

SELECT i, SUM(a) as sum_a, SUM(b) as sum_b
FROM t1
GROUP BY i
HAVING ABS(sum_a - sum_b) > 1E-10;