Pivotal Knowledge Base

Follow

Query with NVL function results in "ERROR: function nvl(bigint, integer) does not exist"

Environment

Product Version
Pivotal Greenplum All versions

Symptom

NVL function reports "ERROR: function nvl(bigint, integer) does not exist"

For example

testdb=# select nvl(sum(0), 0);
ERROR: function nvl(bigint, integer) does not exist
LINE 1: select nvl(sum(0), 0);
^
HINT: No function matches the given name and argument types. You may need to add explicit type casts.

Cause

The function "nvl" accepts equivalence of types for both parameters as described in the definition of the function

testdb=# \df+ oracompat.nvl
                                                                 List of functions
  Schema   | Name | Result data type |  Argument data types   |  Type  | Data access | Volatility |  Owner  | Language | Source code | Description 
-----------+------+------------------+------------------------+--------+-------------+------------+---------+----------+-------------+-------------
 oracompat | nvl  | anyelement       | anyelement, anyelement | normal | no sql      | immutable  | lpetrov | c        | ora_nvl     | 
(1 row)

The point is that in order for this to work, you will have to substitute the expected with a value for the same type. For eg.s  in the above error 0 (zero) is returns as int datatype and sum(0) is bigint

Resolution

You can cast the type with the result as explained below

testdb=# select oracompat.nvl(sum(0), 0::bigint);
 nvl 
-----
   0
(1 row)

OR

Users can define their own NVL function (wraparound) to support bigint and smallint as the input/output parameter.

testdb=# CREATE OR REPLACE FUNCTION NVL(bigint, anyelement) returns bigint
AS 'select coalesce($1, $2::bigint)'
LANGUAGE SQL
IMMUTABLE;
testdb=# select oracompat.nvl(sum(0), 0); nvl ----- 0 (1 row)

Comments

Powered by Zendesk