(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 – 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 – 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.
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.
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.
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");
}
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.
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.
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.