FIND Statement (DQL)

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).
datacom151
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).
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, 
Dataquery
 establishes an active found set of data replacing any found set created from the execution of a previous FIND statement.
Topics discussed on this page include:
 
 
Syntax Diagram
Following is the syntax of the FIND statement.
►►─ FIND ─┬─────────┬─
table-name
─┬────────────┬─┬────────┬──────────────────► └─
count
─┘ └─ (
status
) ─┘ └─ ROWS ─┘ ►─┬───────────────┬─┬───────────────────────┬────────────────────────────────►◄ └─
with-clause
─┘ └─
relationship-clause
─┘
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, 
    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, 
    Dataquery
     defaults to ALL.
  •  
     
    table-name
     
    Represents the name of the table from which 
    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 
    Datacom 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.
     The status must be enclosed in parentheses, and there must be no spaces between the table name and the left parenthesis.
  •  
    ROWS
    Used for readability only.
  •  
     
    with-clause
     
    Represents one or more logical expressions which specify the criteria on which 
    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 (DQL) for more information.
    You can decide not to include any selection criteria. This causes 
    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 
    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 (DQL) for more information.
FIND Limitations
Observe the following important limitations when constructing a FIND or COUNT statement.
  • A maximum of 17 tables can be related or used in a FIND or COUNT statement. The number of tables is based on the following considerations:
    1. The total number of tables is developed after 
      Join Optimization
       processes the statement.
    2. Repeating relationships or complex WITH statements can cause an increase in the total number of tables the query uses.
    3. The record at a time (RAAT) Long Key feature of 
      Datacom
       15.0 cannot be used in a Dataquery Relate of two files.
  • Three parameters in the System Option Table limit the execution of a FIND or COUNT statement:
    • FNDBLKS: Maximum number of logical allocated blocks in the work table for this query.
    • MFTIME: Maximum number of seconds for the FIND or COUNT verb to execute.
    • SRCHLIM: Maximum number of DATACOM/DB physical I/Os allowed during the execution of a FIND or COUNT statement.