WITH Clauses

Purpose

Use WITH clauses to apply selection criteria to the selection of rows for the active found set. WITH clauses can be added to FIND statements and/or relationship clauses to qualify rows from any table read by the query for selection. Their use can improve performance. A WITH clause contains selection criteria stating the characteristics a row must have to qualify for selection. The selection criteria are expressed as one or more logical expressions that CA Dataquery evaluates during the selection process. See Multiple WITH Clauses for more information about multiple WITH clauses.

Syntax Diagrams

The following diagrams show the use of a WITH clause in a FIND statement and in a RELATED BY clause.

►►─ FIND ─┬─────────┬─ table-name ─┬────────────┬─┬────────┬─┬────────┬───────►
          └─ COUNT ─┘              └─ (status) ─┘ └─ ROWS ─┘ └─ WITH ─┘

 ►─ operand1 ─ comparison-operator ─ operand2 ─┬───────────┬──────────────────►◄
                                               └─┬─ AND ─┬─┘
                                                 └─ OR ──┘

Additional comparison expressions may be added using AND and OR. For example:

FIND all personnel rows

WITH name-column = smith

AND city-column = dallas

OR city-column = houston

►►─ FIND ─┬─────────┬─ table-name ─┬────────────┬─┬────────┬─ RELATED ────────►
          └─ COUNT ─┘              └─ (status) ─┘ └─ ROWS ─┘

 ►─┬──────┬─ key-name ─┬───────┬─┬──────┬─┬─────────┬─ table-name ────────────►
   └─ BY ─┘            └─ KEY ─┘ └─ TO ─┘ └─ FIRST ─┘

 ►─┬────────────┬─┬───────┬─┬────────┬─ operand1 ─ comparison-operator ───────►
   └─ (status) ─┘ └─ ROW ─┘ └─ WITH ─┘

 ►─ operand2 ─┬───────────┬───────────────────────────────────────────────────►◄
              └─┬─ AND ─┬─┘
                └─ OR ──┘

Additional comparison expressions may be added using AND and OR.

For example:

FIND all personnel ROWS

WITH dept-column = sales

RELATED BY social-security-column TO payroll

WITH payroll street-column NOT CONTAINING 'p.o.'

OR 'box'

Note: A series of values following an EQ (=) or NE (,=) cannot be separated with AND or OR. For example:

Right:
WITH FIELDA = 'TX' 'CA' 'NY' or
WITH FIELDA NE 'TX' 'CA' 'NY'
Wrong:
WITH FIELDA NE 'TX' and 'CA' and 'NY'

Only the keywords for the WITH clause are described in the following sections. See FIND Statement for a description of the FIND statement and Relationship Clauses for a description of relationship clauses.

Further information appears in Multiple WITH Clauses and Evaluation of Logical Expressions.