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

posted Apr 28, 2017, 10:07 AM by Sachchida Ojha   [ updated Apr 28, 2017, 10:07 AM ]
Greenplum Database returns NaN (not a number) if the results of aggregates 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. 
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.

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.

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.