Function resolution and input argument casting

In considering the best fit of a candidate function, Db2 determines if the input arguments can be implicitly cast to the data type of the corresponding parameter for function resolution.

The castable process determines the best fit of a function, first considering if the input arguments in the function invocation match or can be promoted to the data type of the corresponding parameter of the function definition, and then if the input arguments can be implicitly cast to the data type of the corresponding parameter for function resolution. For the set of candidate functions, Db2 compares the parameter lists from left to right, using the following process:

  • The data type of the argument in the function invocation is compared to the data type of the corresponding parameter in the definition of the candidate function to ensure that each argument can be promoted or cast to the corresponding parameter. If not, an error is returned.

If a function is selected, its successful use depends on it being invoked in a context in which the returned result is allowed. For example, if the function returns a table where a table is not allowed, an error is returned.

Implicit casting for function resolution: Implicit casting for function resolution is not supported for arguments with a user-defined type, binary, ROWID, or XML data type. It is also not supported for built-in or user-defined cast functions. Implicit casting is supported for the following cases:

  • A numeric data type can be cast to a value of another numeric data type that is not in the data type promotion list for the source data type. This includes casting a numeric value to a numeric data type that is lower in the promotion list.
  • A numeric data type can be cast to a character or graphic string data type, except for a LOB.
  • A character or graphic string data type, except for a LOB, can be cast to a numeric data type.
  • A character or graphic string data type, except for a LOB, can be cast to a date, time, or timestamp data type.
  • A varying length character string data type, except for a LOB, can be cast to a fixed length character data type.