Search Condition

A search condition is used to choose rows from tables or views, depending on the result of applying the condition to rows. The condition is a Boolean expression consisting of predicates combined together with OR, AND, and NOT operators.

You can use a search condition in:

  • The WHERE clause of a SELECT, DELETE, or UPDATE statement

  • The HAVING clause of a SELECT statement

  • The searched form of a CASE expression

  • The ON clause of a SELECT statement that involves a join

  • A CHECK constraint

  • A ROWS SINCE sequence function

UDFs may be used in certain forms of the WHERE clause of SELECT or DELETE statements. The use of user defined functions in UPDATE statements is not supported.

Predicate

Predicate is a BETWEEN, comparison, EXISTS, IN, LIKE, NULL, or quantified comparison predicate. A predicate specifies conditions that must be satisfied for a row to be chosen. UDFs may be used in BETWEEN and comparison predicates in SELECT and DELETE statements.

Examples of Search Condition

Select rows by using a search condition containing a UDF in a comparison predicate joined to another comparison predicate by an AND operator: 
select * from datatable where i = factorial(i2) and i >= 3;

I                    I2
-------------------  ------------------

                 24                   4

--- 1 row(s) selected.
Delete rows by using a search condition containing a UDF in a comparison predicate: 
delete from datatable where factorial(i2) >= 120;;

--- 2 row(s) deleted.