FIND Statement

Purpose

The FIND statement specifies the information to be retrieved from the database. The FIND statement contains three major parts: the FIND, the selection criteria (WITH clause) and the relationship criteria (RELATE or outer join clause).

Operation

The FIND statement must be the first entry of each new query because it requests the data which later statements manipulate and display. When you execute a FIND statement, CA Dataquery establishes an active found set of data replacing any found set created from the execution of a previous FIND statement.

Syntax Diagram

Following is the syntax of the FIND statement.

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

 ►─┬───────────────┬─┬───────────────────────┬────────────────────────────────►◄
   └─ with-clause ─┘ └─ relationship-clause ─┘

Explanation

The following explains the keywords in the FIND statement.

count

Represents the maximum number of rows you want to select. You can specify ALL or a positive number. When you specify a positive number, CA Dataquery continues selecting rows until it can find no more rows or it finds the number of rows specified. If you do not specify any value for count, CA Dataquery defaults to ALL.

table-name

Represents the name of the table from which CA Dataquery is to retrieve the specified data. Only those tables shown in your Directory of Tables are available for your query access.

(status)

(Optional) If authorized, use to specify a Datadictionary definition of a table that is being tested by programmers responsible for its maintenance. If the FIND or COUNT statement specifies a TEST status for a table, all subsequent statements in the same query reference the same status and version of the table, unless a different status is specified. The following chart describes each status you can specify.

Note: The status must be enclosed in parentheses, and there must be no spaces between the table name and the left parenthesis.

TEST: Finds the named table in the first test version that meets the specifications.
Tnnn: (Where nnn refers to the version number of the test status you want.) Finds the named table in the specified test version.
PROD: PRODuction is the default status if not specified.

ROWS

Used for readability only.

with-clause

Represents one or more logical expressions which specify the criteria on which CA Dataquery is to select rows from the database. A WITH clause contains the criteria which qualify the rows. Only those rows which meet the specified criteria in the WITH clause are selected, as in the following example:

     FIND ALL CAI-SLSHST-REC ROWS
          WITH SHIP-QTY GT 0

The selection criteria can consist of multiple conditions which serve to limit the number of rows to those which meet all conditions. See WITH Clauses for more information.

You can decide not to include any selection criteria. This causes CA Dataquery to select all the rows in the specified table. However, this can cause the query selection process to run for a long period of time due to CA Dataquery selecting a potentially large amount of rows, depending on the size of the table involved.

relationship-clause

Represents a relationship expression which relates the data in two or more tables. A relationship clause establishes the relationship between tables. When you use RELATED BY to join tables, you can access all the data contained in each row of the relationship, as in the following example.

     FIND ALL CAI-SLSHST-REC ROWS
          WITH SHIP-QTY GT 0
     RELATED BY SLMN-ID TO CAI-ORDER-REC ROWS
          WITH ORD-YR LT '87'

When you use an outer join keyword, you can retrieve unrelated rows, when needed. See Outer Joins for details.

Multiple relationship expressions can exist in a FIND statement. In addition, you can specify selection criteria for each RELATED clause. See Relationship Clauses for more information.