We have migrated to new Google sites!
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.