Expansion of Search-condition

Predicates and Search Condition › Expansion of Search-condition

Expansion of Search-condition

The search-condition represents a truth value in an SQL statement.

Syntax

Expansion of search-condition

►►─┬───────┬─┬─ between-predicate ────┬───────────────────────────────────────►
   └─ NOT ─┘ ├─ comparison-predicate ─┤
             ├─ exists-predicate ─────┤
             ├─ in-predicate ─────────┤
             ├─ like-predicate ───────┤
             ├─ null-predicate ───────┤
             ├─ quantified-predicate ─┤
             └─ ( search-condition ) ─┘

 ►─┬────────────────────────────────────────────────────┬─────────────────────►◄
   │ ┌────────────────────────────────────────────────┐ │
   └─▼─┬─ AND ─┬─┬───────┬─┬─ between-predicate ────┬─┴─┘
       └─ OR ──┘ └─ NOT ─┘ ├─ comparison-predicate ─┤
                           ├─ exists-predicate ─────┤
                           ├─ in-predicate ─────────┤
                           ├─ like-predicate ───────┤
                           ├─ null-predicate ───────┤
                           ├─ quantified-predicate ─┤
                           └─ ( search-condition ) ─┘

Parameters

NOT

Reverses the truth value, if known, of the operand that follows; that is:

A true value becomes false
A false value becomes true
An unknown value remains unknown

between-predicate

Represents the truth value resulting from the evaluation of a BETWEEN predicate. For expanded between-predicate syntax, see Expansion of Between-predicate.

comparison-predicate

Represents the truth value resulting from the evaluation of a comparison predicate. For expanded comparison-predicate syntax, see Expansion of Comparison-predicate.

exists-predicate

Represents the truth value resulting from the evaluation of an EXISTS predicate. For expanded exists-predicate syntax, see Expansion of Exists-predicate.

in-predicate

Represents the truth value resulting from the evaluation of an IN predicate. For expanded in-predicate syntax, see Expansion of In-predicate.

like-predicate

Represents the truth value resulting from the evaluation of a LIKE predicate. For expanded like-predicate syntax, see Expansion of Like-predicate.

null-predicate

Represents the truth value resulting from the evaluation of a NULL predicate. For expanded null-predicate syntax, see Expansion of Null-predicate.

quantified-predicate

Represents the truth value resulting from the evaluation of a quantified predicate. For expanded quantified-predicate syntax, see Expansion of Quantified-predicate.

(search-condition)

Specifies another search condition to be used as a single operand in the search condition. To be manipulated as a single operand, the search condition must be enclosed in parentheses.

AND

Specifies that both the operand preceding the operator and the operand following the operator must be true for the search condition to be true.

OR

Specifies that either the operand preceding the operator, the operand following the operator, or both operands must be true for the search condition to be true.

Usage

A search condition in an SQL statement specifies criteria used to restrict the data processed by the statement:

In a WHERE parameter, the search condition restricts the rows processed by the statement.
The WHERE parameter occurs in query-specification and in the DELETE, SELECT, and UPDATE statements.
In a HAVING parameter, the search condition restricts the table groupings processed by the statement.
The HAVING parameter occurs in query-specification and the SELECT statement.
In a CHECK or ADD CHECK parameter, the search condition restricts the data that can be stored in a table. A search condition in a CHECK or ADD CHECK parameter is also called a check constraint.
The CHECK parameter occurs in the CREATE TABLE statement. The ADD CHECK parameter occurs in the ALTER TABLE statement.

Restrictions on search-condition in a CHECK or ADD CHECK Parameter

In the CHECK parameter of the CREATE TABLE statement or the ADD CHECK parameter of the ALTER TABLE statement:

The search condition cannot include any host variables, routine parameters, local variables, user-defined-functions, aggregate functions, EXISTS predicates, quantified predicates, subqueries, or dynamic parameter markers.
Column references in the search condition must identify columns in the table being created or altered.

Truth Values

The result of a search condition is one of three possible truth values: true, false, or unknown. The unknown value occurs only when the search condition includes one or more null values.

CA IDMS obtains the result by evaluating the search condition for a particular row in a table or a particular table grouping. Processing occurs according to the results, as described in the following table:

If the result is:	CA IDMS:
True	Continues processing the statement for the row or group
False	CA IDMS does not process the statement for the row or group
Unknown	Does not process the row when the search condition occurs in the WHERE or HAVING parameters of a SELECT, UPDATE, or DELETE statement, or query specification Processes the row when the search condition occurs in a CHECK clause that is tested during an INSERT or UPDATE operation

If the result is:

CA IDMS:

True

Continues processing the statement for the row or group

False

CA IDMS does not process the statement for the row or group

Unknown

Does not process the row when the search condition occurs in the WHERE or HAVING parameters of a SELECT, UPDATE, or DELETE statement, or query specification
Processes the row when the search condition occurs in a CHECK clause that is tested during an INSERT or UPDATE operation

Truth Table for AND

The result of the AND operation for each possible combination of operands is given by the following truth table:

AND	True	False	Unknown
True	True	False	Unknown
False	False	False	False
Unknown	Unknown	False	Unknown

Truth Table for OR

The result of the OR operation for each possible combination of operands is given by the following truth table:

OR	True	False	Unknown
True	True	True	True
False	True	False	Unknown
Unknown	True	Unknown	Unknown

Order of Evaluation

After evaluating the individual operands, CA IDMS performs the operations in a search condition in the following order:

The unary operation NOT from left to right
AND from left to right
OR from left to right

You can use parentheses to override the default order of evaluation. Operations in parentheses are performed first.

For example, assuming the value in :SALARY is 35,000, the result of the following search condition is true:

:salary > 20000 or :salary = 0 and :salary < 30,000

When the OR operation is enclosed in parentheses, the result of the expression is false:

(:salary > 20000 or :salary = 0) and :salary < 30,000

Examples

A Single Operand

The following ALTER TABLE statement directs CA IDMS to store only values less than or equal to 10 in the BONUS_PERCENT column of the POSITION table. The search condition in the ADD CHECK parameter consists of a single operand (a comparison predicate).

alter table position
   add check (bonus_percent <= 10);

Two Operands with OR

The following SELECT statement returns the number of employees in each department that has either five or more employees or no employees:

select dept_id, count(emp_id)
   from employee
   group by dept_id
   having count(emp_id) >= 5
      or count(emp_id) = 0;

Multiple Operands

The following SELECT statement identifies the project leaders of projects that were scheduled to have started by now but have not and that have no assigned employees. The search condition in the first WHERE parameter includes three operands. The first is a comparison predicate, the second is a NULL predicate, and the third is an EXISTS predicate with the unary operator NOT.

select proj_leader_id
   from project p
   where est_start_date < current date
      and act_start_date is null
      and not exists
         (select emp_id
            from employee e
            where e.proj_id = p.proj_id);

More Information

For more information about the WHERE parameter, see:
- Expansion of Subquery
- Expansion of Query-specification
- DELETE, SELECT, UPDATE
For more information about the HAVING parameter, see:
- Expansion of Subquery
- Expansion of Query-specification
- SELECT or UPDATE
For more information about the CHECK parameter, see CREATE TABLE.
For more information about the ADD CHECK parameter, see ALTER TABLE.