Condition

Use a condition to test whether data meets certain qualifications. Use this segment wherever you see a reference to a condition in a syntax diagram.

Syntax

(1)
Condition

1 + Logical_Operator?  NOT
2.1  %Comparison Conditions1
2.1  %Condition
with Subquery2
2.1  %User-Defined Function3
Notes:

Element Description Restrictions Syntax
Logical _Operator Combines two conditions Valid options are OR ( = logical union) or AND ( = logical intersection) Conditions with AND or OR

Usage

A condition is a search criterion, optionally connected by the logical operators AND or OR. Conditions can be classified into the following categories:
  • Comparison conditions (also called filters or Boolean expressions)
  • Conditions with a subquery
  • User-defined functions (HCL OneDB™ only)

A condition can contain an aggregate function only if it is used in the HAVING clause of a SELECT statement or in the HAVING clause of a subquery.

No aggregate function can appear in a condition in the WHERE clause of a DELETE, SELECT, or UPDATE statement unless both of the following are TRUE:
  • Aggregate is on a correlated column originating from a parent query.
  • The WHERE clause appears in a subquery within a HAVING clause.
In OneDB, user-defined functions are not valid as conditions in the following contexts:
  • In the HAVING clause of a SELECT statement
  • In the definition of a check constraint
SPL routines are not valid as conditions in the following contexts:
  • In the definition of a check constraint
  • In the ON clause of a SELECT statement
  • In the WHERE clause of a DELETE, SELECT, or UPDATE statement
External routines are not valid as conditions in the following contexts:
  • In the definition of a check constraint
  • In the ON clause of a SELECT statement
  • In the WHERE clause of a DELETE, SELECT, or UPDATE statement
  • In the WHEN clause of CREATE TRIGGER
  • In the IF, CASE, or WHILE statements of SPL