NaN - Not a Numeric or Not a Number error in Greenplum

Post date: Nov 17, 2014 6:44:0 PM

Greenplum Database returns NaN (not a number) if the results of agregates are undefined. This can happen if there is a very small amount of data.

nanvl : Oracle-compliant function to substitute a value for a floating point number when a non-number value (NaN) is encountered.

Synopsis

nanvl(float1, float2)

Description

This Oracle-compatible function evaluates a floating point number (float1) such as BINARY_FLOAT or BINARY_DOUBLE. If it is a non-number (‘not a number’, NaN), the function returns float2. This function is most commonly used to convert non-number values into either NULL or 0.

Parameters

float1 ->The BINARY_FLOAT or BINARY_NUMBER to evaluate.

float2 -> The value to return if float1 is not a number.

float1 and float2 can be any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function determines the

argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

Example

SELECT nanvl(binary1, 0) FROM sachi;

Returns 0 if the binary1 field contained a non-number value. Otherwise, it would return the binary1 value.

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.