A special form of the FOR statement sets up a record stream
consisting of segments from a segmented string field. Because
a single segmented string field value is made up of multiple
segments, a record stream that includes a segmented string field
is "nested." The outer loop retrieves records that include the
field and the inner loop retrieves the segments of each field
value one at a time. Therefore, a FOR statement that retrieves
segmented strings looks like a set of nested FOR statements.
Example:
RDO> FOR R IN RESUMES
cont> FOR S IN R.RESUME
cont> PRINT S.RDB$LENGTH, S.RDB$VALUE
cont> END_FOR
cont> END_FOR
1 – Format
(B)0[m[4mFOR[m qq> context-var qqq> IN qq> ss-field qqwqqqqqqq>qqqqqqwqk
mq> on-error qqj x
lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqwq> get-statement qwqqq> [4mEND_FOR[m qqq>
mqqqqqqqq<qqqqqqqqqj
1.1 – context-var
A context variable.
1.2 – ss-field
A qualified field name that refers to a field defined with the
SEGMENTED STRING data type. Note that this field name, like all
field names in a FOR statement, must be qualified by its own
context variable. This second context variable must match the
variable declared in the outer FOR statement. See the Examples
topic.
1.3 – on-error
The ON ERROR clause. This clause specifies the action to be
taken if an Oracle Rdb error occurs while Oracle Rdb is trying to set
up retrieval of the next segmented string. For more details,
request HELP on ON_ERROR.
1.4 – get-statement
Any valid Oracle Rdb data manipulation language or host language
statement except INVOKE, COMMIT, or ROLLBACK. The GET statement
can reference only the RDB$VALUE and RDB$LENGTH fields.
2 – More
If you have invoked a database, you have the necessary privileges
to use the FOR Statement with Segmented Strings.
Oracle Rdb defines a special name to refer to the segments of a
segmented string. This value expression is equivalent to a
field name; it names the "fields" or segments of the string.
Furthermore, because segments can vary in length, Oracle Rdb also
defines a name for the length of a segment. The statement inside
the segmented string FOR loop must use these names to refer to
the segments of the string. These names are:
RDB$VALUE The value stored in a segment of a segmented
string
RDB$LENGTH The length in bytes of a segment
Within a single context, such as the context of a single request, if
an arithmetic expression contains the MISSING operator, the resulting
expression will evaluate to MISSING. In the following example,
A.FIELD_1 contains missing (unknown) values, and the query correctly
interprets the values in A.FIELD_1 as missing (unknown), causing the
expression A.FIELD_3 = VARIABLE + A.FIELD_1 to evaluate to MISSING:
RDO> FOR A IN RELATION_A
cont> MODIFY A USING
cont> A.FIELD_3 = VARIABLE + A.FIELD_1
cont> END_MODIFY
cont> END_FOR
However, in nested queries that use multiple database requests, such
as the following example, if B.FIELD_2 contains missing (unknown)
values, the expression A.FIELD_3 = VARIABLE + B.FIELD_2 returns
different results. The second query (which begins with FOR A)
retrieves a value, in this case the value defined as the field's
MISSING_VALUE, from B.FIELD_2 for its record selection expression
(RSE). However, because of RDO language limitations, the second query
cannot use the fact that the field B.FIELD_2 has an unknown value and
instead uses the missing value defined for the field with the DEFINE
FIELD or CHANGE FIELD statement. Using this value for B.FIELD_2 instead
of treating the value as unknown means that the A.FIELD_3 = VARIABLE +
B.FIELD_2 expression does not evaluate to MISSING.
RDO> FOR B IN RELATION_B
cont> FOR A IN RELATION_A WITH A.FIELD_1 = B.FIELD_1
cont> MODIFY A USING
cont> A.FIELD_3 = VARIABLE + B.FIELD_2
cont> END_MODIFY
cont> END_FOR
cont> END_FOR
The workaround is to use the SQL interface to Oracle Rdb. You can use
the SQL indicator variables to detect the NULL attribute of the column
(field) and therefore set the appropriate value for the column.
3 – Example
Create a stream whose records contain segmented string fields:
RDO> FOR R IN RECORD
cont> FOR S IN R.SS_FIELD
cont> PRINT S.RDB$LENGTH, S.RDB$VALUE
cont> END_FOR
cont> END_FOR
This statement looks like a nested FOR loop.
o The outer loop sets up a record stream using the context
variable R. The same context variable qualifies the field
name, SS_FIELD, as in every FOR statement.
o The inner loop retrieves the segments of the string field one
at a time.
o The context variable S identifies the segments.
o The special segmented string value expressions, RDB$VALUE and
RDB$LENGTH are qualified by S, the context variable associated
with the field.