-583   THE USE OF FUNCTION OR EXPRESSION name IS INVALID BECAUSE IT IS NOT DETERMINISTIC OR HAS AN EXTERNAL ACTION

Explanation

The function name is defined as a non-deterministic function or a function with an external action, or the expression name is defined as a non-deterministic expression. This type of function or expression is not supported in the context in which it is used. The contexts in which these are not valid are:

  • In the expression prior to the first WHEN keyword in a simple-case-expression.
  • In the WHERE clause of the fullselect in a CREATE VIEW statement if the WITH CHECK OPTION is specified.
  • In an expression of an ORDER BY clause
  • In a key-expression of an index definition
  • In an expression of a GROUP BY clause

System action

The statement cannot be processed.

Programmer response

If the use of a non-deterministic or external action function or expression was not intended, substitute a function or expression without these characteristics. If the behavior associated with the non-deterministic or external action function or expression is intentional, use the alternate form of the statements that make that intent explicit.

  • Instead of a simple-when-clause, use the corresponding searched-when-clause where the function or expression would be specified in each search-condition.
  • Remove the WITH CHECK OPTION from the CREATE VIEW statement.
  • Remove the function or expression from the ORDER BY clause. If the column is part of the result set of the query, change the expression in the ORDER BY clause to the simple-integer or simple-column-name form of the sort key.
  • Remove the function or expression from the GROUP BY clause.

SQLSTATE

42845