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

posted Nov 17, 2014, 10:44 AM by Sachchida Ojha   [ updated Nov 17, 2014, 10:44 AM ]
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.
The gadget spec URL could not be found
nanvl : Oracle-compliant function to substitute a value for a floating point number when a non-number value (NaN) is encountered. 

nanvl(float1, float2)

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.
The gadget spec URL could not be found
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.

The gadget spec URL could not be found
SELECT nanvl(binary1, 0) FROM sachi;

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

This command is compatible with Oracle syntax and is provided for convenience. 
The gadget spec URL could not be found