Format

      [first-clause] relation-clause [cross-clause] [with-clause] [reduced-clause] [sort-clause]

1  –  Parameters

1.1  –  first-clause

    FIRST value-expr

    Specifies how many records are in the record stream formed by
    the record selection expression (RSE). The value expression,
    value-expr, is a symbol or string of symbols used to calculate
    a value. The value expression in a FIRST clause must either
    be a positive number or a value expression that evaluates to a
    positive integer. The record stream cannot contain more records
    than the number specified by the value expression.

1.2  –  relation-clause

    context-var IN relation-name

    Declares context variables for a record stream or a loop. The
    context variable specifies a temporary name that identifies the
    record stream to the product evaluating the clause. You then use
    the context variable to refer to fields from that relation. The
    relation name specifies the relation from which CDO will take the
    records in the record stream.

1.3  –  cross-clause

    { CROSS relation-clause } ...

    Allows you to combine records from two or more record streams.
    You join these records in combinations based on the relationship
    between the values of fields in each record stream. This
    combination is called a relational join.

    The relation clause declares context variables for a record
    stream or loop.

1.4  –  with-clause

    WITH cond-expr

    Allows you to specify conditions that must be true for CDO to
    include a record in a record stream. You specify any conditional
    expression in this clause.

    The record becomes part of a record stream only when its
    values satisfy the conditions you specified in the conditional
    expression (that is, only when the conditional expression is
    true). If the conditional expression evaluates to false or
    missing for a record, that record is not included in the record
    stream.

1.5  –  reduced-clause

    REDUCED TO value-expr ,...

    Allows you to eliminate duplicate values for fields in a record
    stream and to group the records in a relation according to
    unique field values. However, only using the REDUCED clause
    does not guarantee the sort order within groups and the results
    are unpredictable. To ensure specific order, use the SORTED BY
    clause.

    The value expression, value-expr, specifies a symbol or string of
    symbols used to calculate a value.

1.6  –  sort-clause

              {                           }
    SORTED BY { [ ASCENDING  ] value-expr } ,...
              { [ DESCENDING ]            }
              {                           }

    Allows you to sort the records in the record stream by the values
    of specific fields. The value expression, or sort key, determines
    the order in which CDO returns records. The default for an
    initial sort key is ASCENDING. The default for subsequent keys
    is the specification for the initial key.

    The value expression, value-expr, specifies the value to sort by;
    this value is called the sort key.

2  –  Description

    A record selection expression (RSE) is a clause that products use
    at run time to include specific records for processing. The RSE
    defines the conditions that individual records must meet before
    CDO includes them in a record stream.

3  –  Examples

    1.FIRST 5 C IN CURRENT_SALARY
      SORTED BY DESCENDING SALARY_AMOUNT IN C

      You can use FIRST and SORTED BY clauses to find the maximum
      values for a field. In this example, the FIRST clause finds the
      five highest paid employees.

    2.E IN EMPLOYEES

      In this example, the RELATION clause retrieves all records from
      the EMPLOYEES relation.

    3.COUNT OF E IN EMPLOYEES WITH STATE IN E = "NY"

      In this example, the RELATION clause declares E as the context
      variable for the stream of records from the EMPLOYEES relation.

    4.E IN EMPLOYEES CROSS JH IN JOB_HISTORY
      WITH EMP_ID IN E = EMP_ID IN JH

      In this example, the CROSS clause finds all employees for whom
      data is stored in the JOB_HISTORY relation.

    5.E IN EMPLOYEES CROSS J IN JOBS

      In this example, the CROSS clause retrieves information on all
      employees and their job descriptions.

    6.E IN EMPLOYEES WITH JOB_CODE IN E = "R"

      In this example, the WITH clause returns all employees whose
      JOB_CODE equals R.

    7.REDUCED TO JOB_CODE IN J

      In this example, the REDUCED clause lists all active job codes
      once.

    8.EMPLOYEES SORTED BY EMPLOYEE_ID IN E

      In this clause, the SORTED BY clause sorts EMPLOYEES by
      EMPLOYEE_ID.

    9.SORTED BY DESCENDING STATUS_CODE IN E
      ASCENDING LAST_NAME IN E, EMPLOYEE_ID IN E

      In this example, the SORTED BY clause sorts first by STATUS_
      CODE in descending order. Within each STATUS_CODE group, SORTED
      BY sorts by LAST_NAME in ascending order. Finally, within
      groups of employees with the same last name, SORTED BY sorts
      by EMPLOYEE_ID. The order for this last sort is also ascending,
      because it adopts the order from the previous sort key.