Best fit considerations

Once the function is selected, there are still possible reasons why the use of the function may not be permitted. Each function is defined to return a result with a specific data type. If this result data type is not compatible within the context in which the function is invoked, an error will occur.

For example, given functions named STEP defined with different data types as the result:

   STEP(SMALLINT) RETURNS DATE)
   STEP(DOUBLE) RETURNS INTEGER

and the following function reference (where S is a SMALLINT column): 

   SELECT ... 3 +STEP(S)

then, because there is an exact match on argument type, the first STEP is chosen. An error occurs on the statement because the result type is DATE instead of a numeric type as required for an argument of the addition operator.

In cases where the arguments of the function invocation were not an exact match to the data types of the parameters of the selected function, the arguments are converted to the data type of the parameter at execution using the same rules as assignment to columns (see Assignments and comparisons). This includes the case where precision, scale, length, or CCSID differs between the argument and the parameter.

An error also occurs in the following examples:

  • The function is referenced in the TABLE clause of a FROM clause, but the function selected by the function resolution step is a scalar or aggregate function.
  • The function referenced in an SQL statement requires a scalar or aggregate function, but the function selected by the function resolution step is a table function.