1  RSE
   A phrase that defines specific conditions that individual records
   must meet before Oracle Rdb includes them in a record stream. A
   record stream is a temporary group of related records that
   satisfy the conditions you specify in the record selection
   expression. A record stream can consist of:

   o  All the records of one or more relations

   o  A subset of the records in one or more relations

   The following sections describe how to use the record selection
   expression to form record streams.
 

2  Format

 (B)0rse =
 
 qqwqqqqqqqqqqqqqqqqqqqwqqq> relation-clause qqwqqqqqqqqqqqqqqqqqqwqk
   mqq> first-clause qqj                       mq> cross-clause qqj x
                                                                    x
  lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
  mwqqqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqwqq>
   mqq> with-clause qqj mqq> reduce-clause qqj mqq> sort-clause qqj

 

3  first-clause

 (B)0first-clause =
 
   qqqqqq>  FIRST  qqqq>  value-expr  qqqqqq>

   Specifies how many records are in the record stream formed by
   the record selection expression. If you include the element FIRST
   n, the record stream has no more than n records. The argument n
   should be a positive integer or a value expression that evaluates
   to a positive integer. For more information on value expressions,
   ask for HELP on Value_expr.
 

4  More
   Note the following rules:

   o  Oracle Rdb does not guarantee the order of records in a record
      stream unless you specify a sort order. For this reason, when
      you use FIRST n in the RSE, the actual records in the record
      stream are unpredictable. Therefore, you should always specify
      SORT when you use FIRST n.

   o  If n evaluates to zero or a negative number, the record stream
      will be empty.

   o  If you specify FIRST n and n is greater than the number
      of records satisfying those conditions, the record stream
      consists of all records meeting the conditions of the record
      selection expression.

   o  If n is not an integer, Oracle Rdb rounds any fractional part
      of the value and uses the remaining integer as the number of
      records in the record stream.
 

3  relation-clause

 (B)0relation-clause =
 
 qq> context-var qqq> IN qqwqqqqqqqqqqqqqqqqqqqqqwqq> relation-name qq>
                           mqq> db-handle qq> . qj

   In the relation-clause, you must declare context variables for a
   record stream or a loop. A context variable is a temporary name
   that identifies the record stream to Oracle Rdb.

   Once you have associated a context variable with a relation, you
   use the context variable to refer to fields from that relation.
   In this way, Oracle Rdb always knows which field from which relation
   you are referring to.

   You must use a context variable in every data manipulation
   statement and in every data definition statement that uses an
   RSE.

   If you access several record streams at once, the context
   variable lets you distinguish between fields from different
   record streams, even if different fields have the same name.

   In all programs except RDBPRE, RDML, SQL$PRE, and SQL$MOD
   programs, context variables must be unique within a request.
 

3  cross-clause

 (B)0cross-clause =
 
 qwq> CROSS qq> relation-clause qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqq>
  x                              mqq> OVER qwq> field-name qwqj x
  x                                         mqqqqqq , <qqqqqj   x
  mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj

   Lets you combine records from two or more record streams. You
   can join these records in combinations based on the relationship
   between the values of fields in the separate record streams. This
   combining of records is called a relational join.

4  More

   The RDO documentation indicates that views do not allow
   updates to returned data. They do not specify this
   restriction on a cross of two relations from the same
   database. Oracle Rdb allows it to work if you use a WITH
   clause.

   Any field that appears in the RSE (record selection
   expression) is marked as read-only. You cannot update these
   fields as this may remove the record from the join, or add
   a new record to the join that will not be recognized.

   In general, the record stream created by a join (CROSS)
   cannot be updated. A workaround is to fetch the RDB$DB_KEY
   in the loop and generate a separate FOR loop to modify the
   value. The following example shows this situation:

   #include stdio

   DATABASE MAPB = FILENAME "[rdml]PERSONNEL";

   main()
   {
    READY MAPB;
    START_TRANSACTION READ_WRITE;

    printf("Entering EMPLOYEES & JOB_HISTORY relations:\n\n");

    FOR E IN EMPLOYEES
       CROSS JH IN JOB_HISTORY OVER EMPLOYEE_ID
       WITH (JH.EMPLOYEE_ID > "00000" OR E.FIRST_NAME STARTING
       WITH "A-")
       SORTED BY E.LAST_NAME

       FOR E1 IN EMPLOYEES WITH E1.RDB$DB_KEY = E.RDB$DB_KEY
       printf("\n\n%s %s < found  ",E1.FIRST_NAME ,E1.LAST_NAME);

       MODIFY E1 USING
       strcpy(E1.FIRST_NAME,"Xavier    ");

       printf("\n%s %s < changed",E1.FIRST_NAME,E.LAST_NAME);
       END_MODIFY;
       END_FOR;

    END_FOR;

    printf("\n\nRolling Back the modifys");
    ROLLBACK;

    FINISH;
    printf("\n\nFinished");
   }
 

3  with-clause

 (B)0with-clause =
 
   qqqqqq> WITH qqq> conditional-expr qqq>

   Allows you to specify conditions that must be true for a record
   to be included in a record stream. Ask for HELP on Cond_expr for
   more details.
 

3  reduce-clause

 (B)0reduce-clause =
 
   qqqqq> REDUCED TO qqqwqq> value-expr qqqqqwqqq>
                        mqqqqqq<qqqq , qq<qqqj

   Lets you eliminate duplicate values for fields in a record
   stream. You can use this expression to eliminate redundancy in
   the results of a query and to group the records in a relation
   according to a field value. Oracle Rdb does not guarantee the
   sorting order of the resulting record stream. To assure the
   desired order use the SORTED BY clause. If you use the REDUCED
   clause, you should retrieve only the fields that you specify in
   the list of value expressions. If you retrieve other fields, the
   results are unpredictable.
 

3  sort-clause

 (B)0sort-clause =
 
 qqq> SORTED BY qqqwqwqqqqqqqqqqqqqqqqwqq> value-expr qqwqq>
                   x tqq> ASCENDING qqu                 x
                   x mqq> DESCENDING qj                 x
                   mqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqj

   Lets you sort the records in the record stream on one or more
   value expressions (sort keys). The default order is ASCENDING.
   Oracle Rdb uses sort keys in the order you name them. Missing field
   values are placed last. A sort key can be any Oracle Rdb value
   expression. For more information on value expressions, as for
   HELP on Value_expr.
 

2  Examples
   Choose the clause of the RSE you want to see from the following
   list.
 

3  FIRST
   Example 1

   Print the first ten records in a relation:


   RDO> FOR FIRST 10 E IN EMPLOYEES
   cont>   SORTED BY E.LAST_NAME
   cont> PRINT E.*
   cont> END_FOR

   Example 2

   Use FIRST and SORTED BY to find the maximum values for a field:


   RDO> FOR FIRST 5 C IN CURRENT-SALARY
   cont>    SORTED BY C.SALARY_AMOUNT
   cont> PRINT
   cont>    C.FIRST-NAME,
   cont>    C.LAST-NAME
   cont> END_FOR

   Example 3

   Use FIRST with a value expression:



   &RDB&  GET TENTH = ( COUNT OF E IN EMPLOYEES / 10 )

   &RDB&  START_TRANSACTION READ_WRITE
   &RDB&  FOR FIRST TENTH E IN EMPLOYEES
                SORTED BY E. LAST_NAME
                 .
                 .
                 .
 

3  relation-clause
   Example 1

   Use ERASE:


   RDO> FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = "00334"
   cont> ERASE E
   cont> END_FOR

   Example 2

   Use MODIFY:


   &RDB&  FOR SH IN SALARY_HISTORY
   &RDB&        WITH SH.EMPLOYEE_ID = ID-NUMBER
   &RDB&        AND SH.SALARY_END MISSING
   &RDB&        MODIFY SH USING
   &RDB&          SH.SALARY_AMOUNT =
   &RDB&          ( SH.SALARY_AMOUNT + ( 1000 * RATING ) )
   &RDB&        END_MODIFY
   &RDB&      END_FOR

   Example 3

   Use STORE:

   &RDB&  STORE D IN DEPARTMENTS USING
   &RDB&          D.DEPARTMENT_NAME = "Recreation";
   &RDB&          D.DEPARTMENT_CODE = "RECR";
   &RDB&          D.MANAGER_ID = "00445"
   &RDB&      END_STORE

   Example 4

   Use two record streams:


   &RDB&  FOR E IN EMPLOYEES CROSS
   &RDB&        J IN JOB_HISTORY WITH
   &RDB&        E.EMPLOYEE_ID = J.EMPLOYEE_ID
 

3  SORT

   RDO> FOR E IN EMPLOYEES SORTED BY E.BIRTHDAY

   Because this example did not specify the sort order, Oracle Rdb
   automatically sorts the EMPLOYEES records in ASCENDING order by
   BIRTHDAY.


   FOR E IN EMPLOYEES
       SORTED BY DESCENDING E.STATUS_CODE,
                 ASCENDING E.LAST_NAME, E.SOCIAL_SECURITY

   If you do not specify ASCENDING or DESCENDING for the second or
   subsequent sort keys, Oracle Rdb uses the order you specified for
   the preceding sort key.
 

3  REDUCED

   FOR J IN JOB_HISTORY
      WITH J.JOB_END MISSING
      REDUCED TO J.JOB_CODE
      SORTED BY J.JOB_CODE
   PRINT J.JOB_CODE
   END_FOR

   This example lists all the currently active job codes. It
   includes each value in the record stream only once.
 

3  CROSS
   Create a list of employees and their current salary.


   FOR E IN EMPLOYEES CROSS
     SH IN SALARY_HISTORY
       OVER EMPLOYEE_ID WITH
         SH.SALARY_END MISSING
   PRINT E.LAST_NAME,
         SH.SALARY_AMOUNT
   END_FOR

   The EMPLOYEES relation contains, among other things, the name
   and employee ID of each employee. The SALARY_HISTORY relation
   contains a SALARY_AMOUNT for each salary level each employee
   has attained and the starting and ending date for each current
   salary. For the current salary, the salary ending date is
   missing. These relations share the common field, EMPLOYEE_ID.
   Therefore, you can use CROSS to join them over this field.
 

3  VIEWS
   This view definition incorporates an RSE that includes a CROSS
   operation.

   DEFINE VIEW CURRENT_JOB OF JH IN JOB_HISTORY
     CROSS E IN EMPLOYEES OVER EMPLOYEE_ID
        WITH JH.JOB_END MISSING.
         E.LAST_NAME.
         E.FIRST_NAME.
         E.EMPLOYEE_ID.
         JH.JOB_CODE.
         JH.DEPARTMENT_CODE.
         JH.SUPERVISOR_ID.
         JH.JOB_START.
         JH.JOB_END.
   END VIEW.