How do I filter out NaN FLOAT values in Teradata SQL?
Posted
by
Paul Hooper
on Stack Overflow
See other posts from Stack Overflow
or by Paul Hooper
Published on 2012-11-20T22:57:11Z
Indexed on
2012/11/20
22:59 UTC
Read the original article
Hit count: 1048
With the Teradata database, it is possible to load values of NaN, -Inf, and +Inf into FLOAT columns through Java. Unfortunately, once those values get into the tables, they make life difficult when writing SQL that needs to filter them out. There is no IsNaN() function, nor can you "CAST ('NaN' as FLOAT)" and use an equality comparison.
What I would like to do is,
SELECT
SUM(VAL**2)
FROM
DTM
WHERE
NOT ABS(VAL) > 1e+21 AND
NOT VAL = CAST ('NaN' AS FLOAT)
but that fails with error 2620, "The format or data contains a bad character.", specifically on the CAST. I've tried simply "... AND NOT VAL = 'NaN'", which also fails for a similar reason (3535, "A character string failed conversion to a numeric value."). I cannot seem to figure out how to represent NaN within the SQL statement. Even if I could represent NaN successfully in an SQL statement, I would be concerned that the comparison would fail. According to the IEEE 754 spec, NaN = NaN should evaluate to false. What I really seem to need is an IsNaN() function. Yet that function does not seem to exist.
© Stack Overflow or respective owner