Expansion of Search-condition

The search-condition represents a truth value in an SQL statement.
idmscu19
The search-condition represents a truth value in an SQL statement.
This article describes the following information:
2
2
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
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:
  1. The unary operation NOT from left to right
  2. AND from left to right
  3. 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