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.
1 – Format
(B)0[mrse =
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
1.1 – first-clause
(B)0[mfirst-clause =
qqqqqq> [4mFIRST[m 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.
1.1.1 – 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.
1.2 – relation-clause
(B)0[mrelation-clause =
qq> context-var qqq> [4mIN[m 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.
1.3 – cross-clause
(B)0[mcross-clause =
qwq> [4mCROSS[m qq> relation-clause qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqq>
x mqq> [4mOVER[m 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.
1.3.1 – 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");
}
1.4 – with-clause
(B)0[mwith-clause =
qqqqqq> [4mWITH[m 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.
1.5 – reduce-clause
(B)0[mreduce-clause =
qqqqq> [4mREDUCED[m [4mTO[m 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.
1.6 – sort-clause
(B)0[msort-clause =
qqq> [4mSORTED[m [4mBY[m qqqwqwqqqqqqqqqqqqqqqqwqq> value-expr qqwqq>
x tqq> [4mASCENDING[m qqu x
x mqq> [4mDESCENDING[m 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.
2.1 – 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
.
.
.
2.2 – 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
2.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.
2.4 – 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.
2.5 – 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.
2.6 – 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.