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. 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. |
|
|