Record loops

Overview

This page identifies the statements used to create and process record loops. The For Each Record statement initiates a record loop to refer to each record within a set of records, such as one located by a Find statement.

A record loop is a portion of a request that executes a series of statements once for each record in a record set before proceeding to other statements in the request.

Record loops can be nested (embedded) any number of levels deep.

You can also use value loops, index loops, and repeat loops to execute a series of statements.

• Value loops perform a loop on a set of values and are discussed in detail in Value loops.
• Index and repeat index loops (discussed in Flow of control in User Language) perform a loop on a set of statements.

Processing records in order

The IN ORDER option of the FOR EACH RECORD statement processes records in index order, sort order, or sort key order, without requiring a sort of the records.

The prefetch feature

Model 204 supports a prefetch feature (look-ahead read), which can provide performance improvements for applications containing FOR EACH RECORD statements.

Processing a single record or limited number of records

The FOR RECORD NUMBER statement provides a very efficient method to retrieve a specified record and initiate a record loop.

The FOR k RECORDS statement allows you to indicate a maximum number of times the loop is to be repeated.

Skip processing

When you are processing a found set of records in a FOR EACH RECORD loop, you can instruct Model 204 to skip a specified number of records between successive iterations of the loop. This feature also allows you to process records in a forward or backward direction and, for ordered loops, allows you to process records in descending record number order within each value range.

The skip processing feature also gives you the flexibility to vary the number of records skipped, and the direction of the skip, between successive iterations of the loop. For example, between the first and second times through the loop you can skip five records forward. Then, you can again specify the number of records to skip so that between the second and third times through the loop, you skip three records backward, and so on.

Using a Note statement

The Note statement (described further in Note statement, below) causes Model 204 to store the value of any field in a retrieved set, permitting the use of that set in subsequent retrievals within the same request.

You can use the Note statement and the Value In clause of a Find statement to perform cross-referencing. You can also use the Note statement to set a value for later reference.

Saving and recalling position in a FOR loop

The REMEMBER and POSITION statements allow you to store the current processing position in a FOR loop, then recall it at a later time and resume FOR processing where you left off. This technique was developed primarily for use with global found sets and lists, but can be used with non-global found sets and lists as well. See Saving and recalling a POSITION in a FOR loop for a detailed discussion of this topic.

PAI INTO statement

The PAI INTO statement provides a convenient method to determine field names and values encountered within a FOR EACH RECORD loop.

FOR EACH RECORD statement

You can use the FOR EACH RECORD statement to perform:

• Retrieval and looping
• Looping only

Syntax

FOR {EACH | n} {RECORD | RECORDS} - [IN label | ON [LIST] listname] - [IN [ASCENDING | DESCENDING] [SORTKEY] ORDER - [BY [EACH] fieldname] - [FROM value1] [TO value2] [BY {%variable | literal}] - [OPTIMIZING FNV] [{WHERE | WITH} retrieval-conditions]

Where

• n limits the number of records to retrieve. For example, if n equals 17, the first 17 records in the file are retrieved.
• label specifies the label of a preceding FIND statement.
• listname specifies a list from a preceding FIND statement.
• fieldname identifies the field from which to retrieve values.
• value1 and value2 specify the beginning and ending range of values to retrieve.
• %variable or literal specify a comparison value
• retrieval-conditions can be:

  [fieldname {EQ | NE} VALUE IN value_set]

  or

  [fieldname {EQ | NE} VALUE (expression)]

  • The value_set label represents an existing value set. Values of the value set in the EQ VALUE IN value_set clause are treated as boolean OR, for example:

    field = value1 OR field = value2 OR....

  • The NE VALUE IN value_set clause is likewise treated as boolean AND, for example:

    field <> value1 AND field <> value2 AND....

    See also Processing a VALUE IN clause.

  • expression is enclosed in parentheses and can be of any type listed in Using expressions for value retrieval.

Retrieval and looping

When the FOR EACH RECORD statement is specified without the IN option, it functions as a FIND statement as well as initiating a loop.

This form of the FOR EACH RECORD statement is more efficient than when FOR EACH RECORD is used for looping only, because records are locked until they are processed.

Use this form when the set of records being processed is not referred to by subsequent statements outside the loop.

Example

BEGIN FOR EACH RECORD PRINT FULLNAME WITH AGENT AT COLUMN 30 END FOR END

Looping only

When the FOR EACH RECORD statement is specified with the IN/ON option, it functions as a loop statement only and refers back to a previously found set in order to process the records.

Use this form when the set of records being processed is referred to by subsequent statements outside the loop.

Example

BEGIN GET.RECS: FIND ALL RECORDS END FIND FOR EACH RECORD IN GET.RECS PRINT FULLNAME WITH AGENT AT COLUMN 30 END FOR END

Handling empty found sets

Before doing any FOR EACH RECORD processing, Model 204 checks to see if the referenced found set is empty. If the found set is empty, no processing takes place.

Using expressions for value retrieval

You can use expressions to provide the values in:

• the FROM ... TO clause of a FOR EACH RECORD IN ORDER BY statement
• the EQ VALUE clause of a FOR EACH RECORD WHERE statement

FOR EACH RECORD IN ORDER BY

Syntax

FOR EACH RECORD IN ORDER BY fieldname FROM (expression1) TO (expression2)

Where

expression1 and expression2 are enclosed in parentheses and can be of the following types: function call, string concatenation, arithmetic operation, User Language construct, or Boolean expression.

Example

B %MM IS STRING LEN 2 %DD IS STRING LEN 2 %MM = '09' %DD1 = '05' %DD2 = '30' PRINT 'FR1' FR1: IN CLIENTS FOR EACH RECORD IN ORDER BY ANNIV DATE - FROM (%MM WITH %DD1) TO (%MM WITH %DD2) PAI PRINT END FOR END

FOR EACH RECORD WHERE

Syntax

FOR EACH RECORD WHERE fieldname EQ VALUE (expression)

Where

expression is enclosed in parentheses and is one of the following types: function call, string concatenation, arithmetic operation, User Language construct, or Boolean expression.

Example

B %MM IS STRING LEN 2 %DD IS STRING LEN 2 %MM = '09' %DD = '17' PRINT 'FR1' FR1: IN CLIENTS FOR EACH RECORD WHERE ANNIV DATE EQ VALUE(%MM WITH %DD) PAI PRINT END FOR END

Using $LOBLEN to get the size of a Large Object data

You can tell how large a Large Object field is by issuing the $LOBLEN function using the Large Object field name in a FOR RECORD loop. The $LOBLEN function takes one argument that can be a field name or a field name variable (%%variable).

Example

%x=$LOBLEN(NOVEL)

or, to determine the length of a specific occurrence of the field:

%x=$LOBLEN(NOVEL(n))

%x is set to the length, in bytes, of the Large Object field.

Using the $LOBLEN and $LOBRESERVE functions

The following procedure shows how you can store some data, but have additional space to accommodate an increase in the size of the Large Object data up to the RESERVE number of bytes.

BEGIN %S IS STRING LEN 100 ARRAY(3) %S(1) = 'OBJECT DATA, MAX = 1000' %S(2) = 'OBJECT DATA, MAX = 2000' %S(3) = 'OBJECT DATA, MAX = 3000' IMAGE LOB.IMAGE S IS STRING LEN 100 END IMAGE PREPARE IMAGE LOB.IMAGE FOR %I FROM 1 TO 3 BY 1 %LOB.IMAGE:S = %S(%I) WRITE IMAGE LOB.IMAGE ON BUFFER POSITION=1 %RESERVE = %I * 1000 STORE RECORD BLOB = BUFFER,1,100 RESERVE %RESERVE BYTES END STORE END FOR FR PRINT '$LOBLEN=' WITH $LOBLEN(BLOB) PRINT '$LOBRESERVE=' WITH $LOBRESERVE(BLOB) PAI LOB_DATA END

This is the output from the previous procedure:

$LOBLEN=100 $LOBRESERVE=1000 BLOB = (length 100) (reserve 1000) OBJECT DATA, MAX = 1000 $LOBLEN=100 $LOBRESERVE=2000 BLOB = (length 100) (reserve 2000) OBJECT DATA, MAX = 2000 $LOBLEN=100 $LOBRESERVE=3000 BLOB = (length 100) (reserve 3000) OBJECT DATA, MAX = 3000

Other FOR EACH RECORD options

The FOR EACH RECORD statement without any options specified processes every record in the current file or group.

Other options available for the FOR EACH RECORD statement are described in detail on this page.

Restricting FOR EACH RECORD processing

Syntax

Use this form of the FOR EACH RECORD statement to place one or more restrictions on the retrieval of records during FOR EACH RECORD processing:

FOR EACH RECORD [IN label] [OPTIMIZING FNV] [{WHERE | WITH} retrieval-conditions]

where retrieval-conditions are any conditions that are valid for the FIND statement, such as:

[fieldname] {EQ | NE} VALUE IN value-set

Refer to "Record retrievals" for more information about retrieval conditions.

Field name variable optimization

The purpose of the OPTIMIZING FNV option is to prevent field name variables from triggering the retrieval of all fields and/or all occurrences of a field when used inside a FOR EACH RECORD loop. This option is valid only when you are using Parallel Query Option/204. If your site is not running Parallel Query Option/204, OPTIMIZING FNV compiles without generating errors, but is ignored.

With the FOR EACH RECORD statement, the OPTIMIZING FNV option must be specified immediately before the WHERE or WITH clause, or, when the WHERE/WITH clause is not used, at the end of the FOR statement.

OPTIMIZING FNV can be abbreviated to OPT FNV.

With the OPTIMIZING FNV option, only the initial value of the field name variable(s) at the start of the FOR loop is used to select the fields to be retrieved and sent to the client node. For this option to work properly, the field name variable must have the intended value at the time the FOR loop is first executed.

A side effect of this requirement is that if the field name variable is changed, inside the FOR loop, to the name of another field that was not explicitly referenced within the loop, that field is not retrieved. In this case, a default value of null is used for the field name variable.

See Field name variables for a detailed discussion.

You can also use OPTIMIZING FNV with the FOR RECORD NUMBER statement. See Processing a single record for a discussion of this statement.

Using WHERE or WITH clause with other options

If the WHERE or WITH clause is used with any other option on the FOR EACH RECORD statement, the WHERE or WITH clause must be the last option specified.

You can combine the IN option with the WHERE or WITH clauses in a FOR EACH RECORD statement to further filter a previously found set.

Example

BEGIN GETPOL: FIND ALL RECORDS FOR WHICH POLICY NO IS GREATER THAN 100320 END FIND FOR EACH RECORD IN GETPOL WHERE (TOTAL PREMIUM IS GREATER THAN 393 AGENT IS GREATER THAN CASOLA) PRINT POLICY NO WITH AGENT AT COLUMN 30 END FOR END

Note: If the retrieval conditions specified in the WHERE or WITH clause results in a direct search of the data (Table B), it is more efficient to use the FOR EACH RECORD statement without retrieval conditions specified and use the IF statement within the loop to specify the criteria.

Specify retrieval criteria on one logical line

You must specify the retrieval criteria on one logical line.

A hyphen can be used to note the continuation of the set of conditions.

Use parentheses to continue a line. Bear in mind that Model 204 treats the unstated Boolean operator as an implied AND.

Note: The rule for using parentheses with the FOR EACH RECORD WHERE construct is different from the rule for using parentheses with the FIND construct (see Interpretation of Boolean operators in retrievals).

Example

This statement is considered one logical line:

FOR EACH RECORD WHERE (TOTAL PREMIUM IS GREATER THAN 393 AGENT IS GREATER THAN CASOLA) OR INCIDENT IS T1

In this example, an implied AND is generated at the end of the first select criteria.

Processing records in order

The IN ORDER option of the FOR EACH RECORD statement processes records in index order or sort order, without requiring a sort of the records. When the IN ORDER option is not used, records are processed in entry order on a segment-by-segment basis.

You can use index order processing with fields that were defined with the ORDERED attribute. Record loops in index order execute more slowly than record loops in sort order.

You can use sort order processing for records in a file which were defined as a sorted file. The execution of a record loop in sort order might be slower than that of an unordered record loop (if you have many spilled records) but faster than that of an index order loop.

Note: If the IN ORDER option processes a field that is a sort key but also has the ORDERED attribute, and if SORTKEY is not specified, index order processing is performed.

If you want to produce a sort order which is not provided for by the syntax of the IN ORDER option, see Sorting for information about the SORT statement.

Reference context

Index order and sort order processing can be performed only on records from a single file. Therefore, do not use the IN ORDER option with groups in FOR EACH RECORD loops. Such use of the IN ORDER option with groups results in a counting error and this error message:

M204.0243 'IN ORDER' NOT VALID IN GROUP CONTEXT

If you want to process records from a group in sorted order, use the SORT statement. (SORT statement processing typically is slower than index order or sort order processing.)

The IN ORDER and VALUE IN options are supported in remote file context. See Referring to the current value for a discussion of the VALUE IN option.

Specifying processing order

If the fields processed by the FOR EACH RECORD statement have the ORDERED attribute, or if the file is a sorted file, ordered processing can occur without requiring a separate sort of the found records.

If the file processed is a sorted file, the SORTKEY option forces records to be processed in order according to the values of the sort key field.

Syntax

This form of the FOR EACH RECORD statement demonstrates how you specify processing order:

FOR EACH RECORD [IN label | ON list] IN [ASCENDING | DESCENDING] [SORTKEY] ORDER [BY fieldname] [FROM value1] [TO value2]

Where

• ASCENDING and DESCENDING indicate the order in which the record set is sorted. ASCENDING order is the default.
• SORTKEY forces records to be processed in sort key order when a sorted file's sort key also has the ORDERED attribute. If SORTKEY is omitted, and the specified field is both a sort key and an ORDERED field, processing order defaults to Ordered Index order.
• BY fieldname specifies the ORDERED field to be used to establish processing order. For sorted files, BY is optional.
• The FROM and TO clauses specify the range of values to be processed. FROM indicates the beginning value; TO indicates an ending value. You can explicitly define a range by using both FROM and TO, or you can implicitly define a range by specifying only one of these options. Care must be taken in specifying values for FROM and TO; sort key processing behaves differently from Ordered Index processing when you use value ranges.

Specifying value ranges for FOR EACH RECORD processing

Records in which the BY field is not present

If a range is specified (that is either FROM value1 TO value2 or just FROM value1 or just TO value2), records that do not have the field present are not processed.

If no range limits are specified and a pattern is not specified using the WHERE or WITH option, records for which the field is not present are processed at the end of the ordered set.

Order of FROM and TO values

With sort key order processing, the values for FROM and TO can be presented in either order, for both of the ASCENDING or the DESCENDING options. For example, the two code fragments below each produce the same result:

... FOR EACH RECORD IN FD1 IN ASCENDING SORTKEY ORDER BY FULLNAME - FROM 'ALTON, CHARLES Z' TO 'BAIN, THEODORE S' PRINT FULLNAME AND POLICY NO AND STATE AND MARITAL STATUS END FOR ... FOR EACH RECORD IN FD1 IN ASCENDING SORTKEY ORDER BY FULLNAME - FROM 'BAIN, THEODORE S' TO 'ALTON, CHARLES Z' PRINT FULLNAME AND POLICY NO AND STATE AND MARITAL STATUS END FOR ...

With Ordered Index processing (when SORTKEY is not specified), the order in which you specify the FROM and TO values is sensitive to whether you are doing ASCENDING or DESCENDING order processing. For example, if you are doing DESCENDING order processing, the FROM value must be higher than the TO value, as in the following example, which produces output for the year 1990 in descending INCIDENT DATE order (assuming INCIDENT DATE is defined as an ORDERED field):

... FOR EACH RECORD IN FOUNDSET IN DESCENDING ORDER BY INCIDENT DATE FROM 19901231 TO 19900101 PRINT 'DATE: ' WITH INCIDENT DATE WITH 'DRIVER NAME: ' - WITH FULLNAME END FOR ...

If, in the preceding example, you had specified DESCENDING order, but had given the range as FROM 900101 TO 901231, the request would compile without producing errors, but no records would be printed.

Processing behavior with implicitly specified ranges

You can specify a value range for FOR EACH RECORD processing implicitly by using only one of the FROM and TO options. Model 204 then makes an assumption about the unspecified end of the range before proceeding. The following table summarizes these assumptions and categorizes them based on your choice of ASCENDING or DESCENDING processing, and whether the processing is based on Ordered Index or sort key (for sorted files) order.

Sort key processing behavior for DESCENDING order with implicitly specified value ranges is the opposite of the corresponding behavior for Ordered Index processing.

FOR EACH RECORD processing behavior with implicitly defined value ranges

Processing direction	Range value specified	Value range processed
		Ordered Index processing	SORTKEY processing
ASCENDING	FROM only	From specified value to highest field value	From specified value to highest field value
	TO only	From lowest field value to specified value	From lowest field value to specified value
DESCENDING	FROM only	From specified value to lowest field value	From highest field value to specified value
	TO only	From highest field value to specified value	From specified value to lowest field value

Optimization of FOR statement LIKE option

Whenever the LIKE option is used with the FOR EACH RECORD or FOR EACH VALUE statement which is performing Ordered index processing against an existing found set or list, an attempt is made to create pattern driven terms. Model 204 optimizes the FROM and TO values with the pattern terms to minimize Ordered Index processing. This optimization creates the most restrictive FROM and TO value based on the specified FROM and TO value (if any) and the current pattern term.

Note: This optimization does not take place when the FOR statement is performing both retrieval and looping (when it is used without the IN option; see Retrieval and looping).

For example (data = 'ABCG' 'ABDG' 'ABEG' 'ABFG' 'ADFG' 'AEFG'):

FR IN ORDER BY ORD_FLD FROM 'A' TO 'ABFG' LIKE 'ABE*' PRINT ORD_FLD END FOR

The optimization changes the FROM value to ABE (more restrictive than A) and changes the TO value to ABF (more restrictive than ABFG). This results in searching just two values (ABEG and ABFG) to print ABEG.

If the FROM or TO value is omitted, the optimization creates an implied FROM or TO value based on the pattern specified after the LIKE keyword. In the example above, if the FROM value were omitted, the results would be the same. The optimization would create an implied FROM value of ABE while changing the TO value to ABF.

Referring to the current value

The current value of the ORDERED field can be referred to by using a VALUE IN phrase if the FOR EACH RECORD statement is labeled. In this example, the VALUE IN phrase is used to obtain the current value and assign it to a %variable. (See Using variables and values in computation for a discussion of %variables.)

FORNAME: FOR EACH RECORD IN ORDER BY FULLNAME %X = VALUE IN FORNAME ...

Field group SORT support

You can use field groups in sorted sets. SORT statement support for field groups not only allows records with field groups in them to be sorted, it lets you reference field groups in the sorted sets. For example, you can issue a FOR FIELDGROUP statement or FEO FIELDGROUP statement against the sorted set.

Restrictions sorting an individual field in a field group

Individual fields within field groups can cause syntax errors. In the following example INCIDENT_TYPE is a field in a field group:

*** M204.0542: EDIT COMPLETE - GO *** 1 M204.2845: INVALID FIELDGROUP CONTEXT FOR INCIDENT_TYPE 2 SORT RECORDS IN 1 BY INCIDENT_TYPE (FILE = CCATEMP , PROCEDURE = 0, LINE = 3) *** M204.1042: COMPILATION ERRORS >

The previous example is invalid because the field group context for the SORT statement is the record context. In the record context, you can reference fields inside a field group if, and only if, the field is both:

• In an outer level field group, which is true in the previous example
• Defined as EXACTLY-ONE or AT-MOST-ONE, which is not true in the previous example

Without these restrictions it is ambiguous as to which occurrence a field group field reference is referring. In the previous case, OTHER_DRIVER is in field group DRIVER, but is defined as REPEATABLE. The REPEATABLE attribute makes OTHER_DRIVER ineligible to be a sort key.

BEGIN FD: IN POLICIES FD POLICY_NUMBER = 100095 OR 100096 END FIND SR: SORT RECORDS IN FD BY MODEL FR SR PRINT ’MODELS:’ AND EACH MODEL PRINT ’MAKES:’ AND EACH MAKE END FOR END

Output:

MODELS: ENVOY TACOMA MAKES: GMC TOYOTA MODELS: NEW BEETLE ECLIPSE SUBURBAN MAKES: VOLKSWAGEN MITSUBISHI CHEVROLET

Usage notes for SORT processing and field groups

The EACH clause for fields inside field groups is not allowed. A compile error or run-time error, depending on whether a field name variable is used or not, will be returned.

BEGIN FD: IN POLICIES FD POLICY_NUMBER = 100095 OR 100096 END FIND SR: SORT RECORDS IN FD BY EACH MODEL FR SR PRINT ’MODELS:’ AND EACH MODEL PRINT ’MAKES:’ AND EACH MAKE END FOR END *** 1 M204.2845: INVALID FIELDGROUP CONTEXT FOR MODEL SR: SORT RECORDS IN FD BY EACH MODEL (FILE = CCATEMP, PROCEDURE = 0, LINE = 4) *** M204.1042: COMPILATION ERRORS

An unsubscripted reference to a field in a field group in a SORT statement is allowed.

The prefetch (look-ahead read) feature

The prefetch feature improves performance of Model 204 for record-number order retrieval of a record set, particularly in a batch environment. Prefetch is for User Language applications only and applies only to Table B. It is not supported for Host Language Interface applications.

The prefetch feature initiates a read of the next Table B page when a previous page is first declared to be current. The look-ahead reads are issued for the FOR EACH RECORD sequential record retrieval mode. Look-ahead read is suppressed if the FR statement contains an IN ORDER clause or if it references a sorted set.

Performance considerations

• You can use the prefetch feature during large, multiuser Model 204 runs, but it is most suitable in a batch environment.
• If you use the prefetch feature with many active users, and you are near the buffer limits (see below), you might get a decrease in throughput. To use this feature effectively, each user needs reserved resources, to avoid concurrency conflicts for resources (for example, FIND record conflicts, file level locking, buffer constraints, and so on).
• A major factor determining the impact of prefetch is the amount of processing per page I/O. If processing performed per page I/O cycle is high, then prefetch provides significant performance improvements. If processing per page I/O cycle is small, then performance gains are minimal (see Performance gains using the prefetch feature). A variety of performance tests have resulted in performance gains in the range of 10-40%.

  The first of each set of diagrams in Performance gains using the prefetch feature shows the relationship between page I/O and processing time when the prefetch feature is disabled. The second diagram of each set reflects this relationship when the prefetch feature is in operation. Note that as the amount of processing increases, the performance gains using prefetch also increase. If y (processing) is less than or equal to x (page I/O, including waits), then the gain from using the prefetch feature is roughly y * pages. If y is greater than x, then the gain is x * pages.

  Performance gains using the prefetch feature

  

  
  

• The prefetch feature involves a small CPU time cost, which is application dependent; you might want to test to determine if the additional overhead is an acceptable level for a specific application in your environment.
• If there is terminal I/O for each Table B page, then user delays in pressing Enter might cause a page that was preread into the buffer pool by the prefetch feature to be removed. In that case, the page would need to be read into the buffer pool again, resulting in an increased system I/O level.
• The interaction of the prefetch feature and the obsolete file skewing feature can affect performance positively or negatively, depending on the amount of CPU time consumed processing each Table B page.

Enabling the feature

The prefetch feature can be enabled only by users with system manager privileges.

Before you enable the prefetch feature, the MAXBUF parameter must be set, based on this formula:

MAXBUF = NUSERS * (4 + 2 * (Maximum FOR EACH RECORD loop nest level))

To enable the prefetch feature, the system parameter SEQOPT must be set to 1 (the default is 0).

Processing a limited number of records

You can process only a sample of the data by using the FOR k RECORDS statement.

Syntax

The form of the FOR k RECORDS statement is:

FOR k RECORDS [options]

Where

• k is an integer value of the sample size.
• options is any of the options that can be specified on a FOR EACH RECORD statement.

FOR k RECORDS processing

The FOR k RECORDS statement begins a loop that is repeated up to k times, depending on the statement. If k equals zero, the loop is skipped. If k is negative or if there are fewer than k records, then the loop is repeated for all the records in the found set (IN label).

Example

This request prints all information for the first five driver records in a file:

BEGIN DRIVERS: IN CLIENTS FIND ALL RECORDS FOR WHICH RECTYPE = DRIVER END FIND FOR 5 RECORDS IN DRIVERS PRINT ALL INFORMATION SKIP 1 LINE END FOR END

Processing a single record

As discussed in "Record retrievals", the POINT$ condition can be used to retrieve a range of record numbers.

If single records must be retrieved by record number, a more efficient method of retrieval is with the FOR RECORD NUMBER statement.

FOR RECORD NUMBER processing

The FOR RECORD NUMBER statement retrieves the specified record and initiates a record loop. The record loop functions in a manner identical to a loop initiated by a FOR EACH RECORD statement.

The FOR RECORD NUMBER statement is supported in remote file and scattered group contexts.

Syntax

The format of the FOR RECORD NUMBER statement is:

FOR RECORD NUMBER {value | IN label} [OPTIMIZING FNV]

Where

• value specifies the number of the record to be processed. The value can be a literal number or string, a %variable, or a VALUE IN phrase. (The VALUE IN phrase is discussed later in this section; %variables are discussed in Using variables and values in computation.) If the specified value is not numeric, or is not a valid record number in the current file, the body of the loop is bypassed and processing continues with the next statement that is at the same nesting level as the FOR RECORD NUMBER statement.

  Note: If this form of the FOR RECORD NUMBER statement is used in group context, it must be preceded by an IN clause which refers to a file. Files, groups, and reference context discusses the IN clause in detail.

• The IN label clause specifies the label of a previous FOR EACH RECORD or STORE RECORD statement. (Refer to STORE RECORD statement for more information.)

  Note: This form of the FOR RECORD NUMBER statement automatically assumes the file or group context of the statement to which the label refers. Therefore, it cannot be preceded by an IN clause.

• The OPTIMIZING FNV option is used in remote context to prevent field name variables from triggering the retrieval of all fields and/or all occurrences of a field. With the FOR RECORD NUMBER statement, OPTIMIZING FNV must be specified at the end of the statement.

  OPTIMIZING FNV is valid only in remote context; in local context, it is ignored. OPTIMIZING FNV can be abbreviated OPT FNV. See Field name variable optimization for a more detailed discussion.

Example

In the following example, the IN label form of the FOR RECORD NUMBER statement is used to create one line of output from data obtained from two different found sets in different files:

BEGIN FIND1: IN MASTER FIND ALL RECORDS END FIND FR1: FOR EACH RECORD IN FIND1 N1: NOTE SSNO FIND2: IN PAYROLL FIND ALL RECORDS FOR WHICH SSNO = VALUE IN N1 END FIND FOR 1 RECORD IN FIND2 FOR RECORD NUMBER IN FR1 PRINT SSNO AND NAME AND ... END FOR PRINT PAY AND DEDUCTIONS END FOR END FOR END

Returning the record number of the current record

The $CURREC function can be used to return the record number of the record being processed.

Using an expression with FOR RECORD NUMBER

The FOR RECORD NUMBER statement (abbreviation: FRN) accepts an expression to supply its value.

Syntax

FRN expression

A multi-part expression must be enclosed in parentheses; for example:

FRN (%A + %B)

Where

expression is one of the following types: function call, string concatenation, arithmetic operation, User Language construct, or Boolean expression.

Example

IN FILE CLAIMS STORE RECORD ... END STORE IN FILE CLAIMS FRN $CURREC ... END FOR

Skip processing

You can direct Model 204 to process a user-specified fraction of a record set (for example, every third record) with the FOR EACH RECORD statement. This is called skip processing, and the skipping can be forward (from the beginning of the record set to the end) or backward.

For Ordered Index record sets that have duplicate field values, you can invoke a skip processing option to process the record set by ascending or descending record number (as described in Descending record number processing), within each Ordered Index value.

Forward or backward skip processing

Skip processing is invoked for a FOR EACH RECORD or FOR EACH VALUE statement by including a BY clause with the following syntax:

BY {%variable | literal}

Where

• %variable or literal indicates how many records to skip before retrieving the record to process in the next iteration of the FOR loop. If set to n, every nth record is processed (and n-1 records are skipped in between). The value of %variable or literal must be an integer between and including positive or negative 2,147,483,647. A setting of zero is not allowed.
• Negative settings invoke backward skipping. However, you cannot skip backward in a given record set until you have first skipped forward in the record set. This is equivalent to saying that you cannot start at the beginning of the record set and skip backward.
• You can change the direction and number of records to skip each time through the loop by using a %variable in the BY clause and changing the value of the %variable inside the loop. Model 204 checks the value of the %variable immediately before beginning the next iteration of the loop.
• You can use backward processing with Ordered Index record sets only if one of the following is true:
  • EACH is specified in the FOR statement.
  • The field in question is defined with OCCURS 1.
  • The field in question is defined with AT-MOST-ONE.

Specifying ascending or descending order with ORDERED fields

If you are processing ORDERED fields, use the following FOR EACH RECORD syntax to get skip processing in ascending or descending order. The placement of the skip processing BY clause in the FOR EACH VALUE statement syntax is similar.

FOR EACH RECORD [IN label | ON list] IN [ASCENDING | DESCENDING] ORDER BY fieldname [FROM value1] [TO value2] [BY {%variable | literal}] [WHERE fieldname IS LIKE pattern]

Use the following syntax for unordered and sorted record sets:

FOR EACH [RECORD | VALUE] IN foundset BY {%variable | literal}

Use the following syntax for record sets from sorted files:

FOR EACH RECORD [IN label | ON list] IN SORTKEY ORDER BY fieldname BY {%variable | literal}

Where

• SORTKEY invokes skip processing in order according to the values of the sort key field. The sort key field is fieldname in the first BY clause.
• If you do not specify SORTKEY, and fieldname in the first BY clause is both a sort key and an ORDERED field, Model 204 uses skip processing for the Ordered Index ordering of the fieldname values.

Example

The following example demonstrates the flexibility of skip processing. Note that the value of the skip increment, %I in this example, is recomputed inside the FOR loop. This allows both the direction of and the interval of the skip to be different for each iteration of the loop.

*************************************************************** * THIS ROUTINE FINDS THE NUMBER OF A RECORD WHOSE VALUE * EQUALS OR PRECEDES THAT OF %LONGNAME. IT SETS USE.REC * TO THAT RECORD NUMBER. A BINARY SEARCH IMPLEMENTED WITH * SKIP PROCESSING IS USED TO DETERMINE THE RECORD NUMBER. *************************************************************** BEGIN * PROCEDURE TO PERFORM BINARY SEARCH IN SORTED FILES USING SKIP * BY %VAR * %V CONTAINS A VALUE TO BE FOUND %FOUND = 0 FND: FIND ALL RECORDS END FIND CT: COUNT RECORDS IN FND %COUNT = COUNT IN CT %I = %COUNT / 2 %BGN = 0 %END = %COUNT + 1 %C = %I * %C IS THE CENTER POINT REACHED BY THE NEW %I FOR EACH RECORD IN FND IN SORTKEY ORDER BY FIELD1 BY %I IF FIELD1 EQ %V THEN %FOUND = 1 JUMP TO GOT.NUM ELSEIF FIELD1 GT %V THEN * GOING BACKWARD %END = %C %I = -(%END - %BEG)/2 %C = %END + %I ELSE * GOING FORWARD %BEG = %C %I = (%END - %BEG) / 2 %C = %BEG + %I END IF IF %I EQ 0 THEN %I = 1 * SKIP VALUE MAY NOT BE ZERO EVEN WHEN WE LEAVE THE LOOP JUMP TO GOT.NUM END IF END FOR GOT.NUM: IF %FOUND = 0 THEN PRINT 'NOT FOUND: ' %V ELSE PRINT 'FOUND: ' %V END IF END

Descending record number processing

You can process Ordered Index record sets in descending record number order per field value. The FOR EACH RECORD statement skip processing option is extended to permit descending record number ordering. Previously, IN ORDER BY processing was always in ascending record number order. This extension is only valuable for processing record sets in which a field has duplicate data values.

For example, use the following syntax for Ordered Index skip processing with record number ordering. The extension to skip processing syntax is shown in bold.

FOR EACH RECORD [IN label | ON list] IN [ASCENDING | DESCENDING] ORDER BY fieldname [FROM value1] [TO value2] [BY [ASCENDING RECORD | DESCENDING RECORD] {%variable | literal}] [WHERE fieldname IS LIKE pattern]

Where

• ASCENDING RECORD or DESCENDING RECORD indicates the records are to be processed in ascending or descending record number within fieldname value. If neither ASCENDING nor DESCENDING is specified, the default is ASCENDING RECORD.
• If specified with the IN ORDER BY clause, a %variable or literal value is required when the skip processing BY is specified.

Examples

In Example 1, the record set below is processed using the default behavior of the FOR EACH RECORD statement. In Example 2, it is processed using a FOR EACH RECORD statement with record number ordering.

Record number (value of field RECNO)	Value of field ORDCHAR
01	A
02	A
03	B
04	B
05	C
06	C
07	A
08	A
09	B
10	B
11	C
12	C

Example 1

Prior to skip processing and record number ordering, the FOR EACH RECORD statement ordering by field name was always in ascending record number order, as the output from the following statements show:

PRINT 'ORDCHAR' AND 'RECNO' FR IN label IN DESCENDING ORDER BY ORDCHAR PRINT ORDCHAR WITH RECNO AT 9 END FOR

These statements print ascending record numbers within the descending ORDCHAR values:

ORDCHAR RECNO C 05 C 06 C 11 C 12 B 03 B 04 B 09 B 10 A 01 A 02 A 07 A 08

Example 2

In this example, the FOR EACH RECORD statement specifies record number ordering:

PRINT 'ORDCHAR' AND 'RECNO' FR IN label IN DESCENDING ORDER BY ORDCHAR - BY DESCENDING RECORD %variable PRINT ORDCHAR WITH RECNO AT 9 END FOR

These statements print descending record numbers within the descending ORDCHAR values:

ORDCHAR RECNO C 12 C 11 C 06 C 05 B 10 B 09 B 04 B 03 A 08 A 07 A 02 A 01

Note statement

As described in Fields, Model 204 retrieves sets of records by means of one or more fields contained in each record.

By using the Note statement, you can cause Model 204 to temporarily store the value of any field in the retrieved set, permitting its use in subsequent retrievals within the same request.

Syntax

This statement directs Model 204 to remember the value of a retrieved field:

label: Note fieldname

Note processing

A Note fieldname statement can be used only within a FOR EACH RECORD or FOR RECORD NUMBER loop.

Each time the loop is executed, another record in the retrieved set is processed and the value of the field in that record is saved. This value can then be used in a subsequent Find statement by using the Value In phrase to refer to the noted value.

Example

The following request finds all current and potential policyholders in cities covered by the agent GOODRICH:

BEGIN GET.AGENT: FIND ALL RECORDS FOR WHICH AGENT = GOODRICH END FIND PROCESS: FOR EACH RECORD IN GET.AGENT CITY.NAME: NOTE CITY CITY.VAL: FIND ALL RECORDS FOR WHICH CITY = VALUE IN CITY.NAME END FIND PRINT.INFO: FOR EACH RECORD IN CITY.VAL PRINT CITY WITH AGENT AT COLUMN 20 - WITH POLICY NO AT COLUMN 40 END FOR END FOR TOT.GOODRICH: COUNT RECORDS IN GET.AGENT PRINT COUNT IN TOT.GOODRICH END

In this example, the PROCESS label begins a loop. Cross-referencing (see the next section) is repeated until all records with AGENT = GOODRICH have been processed.

The CITY label notes the city of each record for agent GOODRICH. The CITY.VAL label retrieves all records in the file, regardless of agent, that have a value equal to the noted city. The PRINT.INFO statement prints the city, agent, and policy number, and then Model 204 loops back to the PROCESS label and notes the city on the next AGENT = GOODRICH record, and so on. When all AGENT = GOODRICH records have been exhausted, execution continues with the TOT.GOODRICH label.

Alternative: Use assignment statements

The functions of the Note statement can be performed more efficiently with the assignment statement because the assignment statement uses less user table space during compilation. See also:

• Assigning values to %variables
• Description of tables

In the preceding example, an assignment statement could be used by replacing NOTE OWNER.POLICY with %OWNER.POLICY = OWNER POLICY, and by replacing POLICY NO = VALUE IN POLICY with POLICY NO = %OWNER.POLICY.

Noting character strings

The following form of the Note statement can be used to set a value for later reference by the Value In phrase:

label: NOTE 'string'

For example:

BEGIN NAME: NOTE 'ABBOTT, FRANKLIN G' FIND.RECS: FIND ALL RECORDS FOR WHICH FULLNAME = VALUE IN NAME END FIND CT: COUNT RECORDS IN FIND.RECS PRINT COUNT IN CT - WITH ' RECORDS FOR ' - WITH VALUE IN NAME END

results in the output:

2 RECORDS FOR ABBOTT, FRANKLIN G

Refer to Nested loops for an additional example using the Value In phrase.

Cross-referencing

Cross-referencing involves using a field from one record to find another record or set of records.The following examples illustrate the use of cross-referencing.

Efficient cross-referencing

This example illustrates a technique for creating efficient cross-referencing requests. Consider the CLIENTS and VEHICLES files that contain records for people and records for cars, respectively. Records might be related by having a common field (for example, POLICY NO in one file and OWNER POLICY in the other). To find the total premium paid by each policyholder owning a SAAB, you could write this request:

BEGIN SAABS: IN VEHICLES FIND ALL RECORDS FOR WHICH MAKE = SAAB END FIND FOR EACH RECORD IN SAABS POLICY: NOTE OWNER POLICY POLICY.VAL: IN CLIENTS FIND ALL RECORDS FOR WHICH POLICY NO = VALUE IN POLICY RECTYPE = POLICYHOLDER END FIND FOR EACH RECORD IN POLICY.VAL PRINT POLICYHOLDER - WITH TOTAL PREMIUM TO COLUMN 25 END FOR END FOR SAABS END

Moving constant retrieval outside FOR loop

If the first statement retrieves 500 records with MAKE = SAAB, the FIND statement in POLICY.VAL is executed 500 times.

The cost of a FIND is directly related to the number of retrieval conditions specified. In Example 1, the retrieval condition RECTYPE = POLICYHOLDER is the same for each repetition of POLICY.VAL.

Greater efficiency is gained by moving constant retrieval conditions outside the FOR loop. For example:

BEGIN SAABS: IN VEHICLES FIND ALL RECORDS FOR WHICH MAKE = SAAB END FIND POL.HOLDERS: IN CLIENTS FIND ALL RECORDS FOR WHICH RECTYPE = POLICYHOLDER END FIND SAAB.LOOP: FOR EACH RECORD IN SAABS POLICY: NOTE OWNER POLICY POLICY.VAL: FIND ALL RECORDS IN POL.HOLDERS FOR WHICH POLICY NO = VALUE IN POLICY END FIND FOR EACH RECORD IN POLICY.VAL PRINT POLICYHOLDER - WITH TOTAL PREMIUM TO COLUMN 25 END FOR END FOR END

Record locking considerations

The preceding technique might not be appropriate for an application in which many users retrieve data from and update the same file concurrently.

The POLICYHOLDER records in the second request are locked out from updates for the duration of the request. In the first request, the POLICYHOLDER records would be accessible for updating between repetitions of the outer loop. Refer to Record locking and release statements for more discussion.

Nested loops

The following example illustrates cross-referencing with nested loops. This request finds all drivers of vehicles used for carpool purposes who also have a vehicle that is not used for carpools.

This example illustrates referring to noted values in output statements.

BEGIN SET HEADER 1 'PRINCIPLE DRIVER' - WITH 'POLICY NO.' AT COLUMN 20 SET HEADER 2 NEW PAGE FD.CARPOOLS: IN VEHICLES FIND ALL RECORDS FOR WHICH USAGE = CARPOOL END FIND FOR EACH RECORD IN FD.CARPOOLS NOTE.DRIVER: NOTE PRINCIPLE DRIVER FD.NO.CP: FIND ALL RECORDS FOR WHICH PRINCIPLE DRIVER = VALUE IN NOTE.DRIVER USAGE = NOT CARPOOL END FIND FOR EACH RECORD IN FD.NO.CP PRINT VALUE IN NOTE.DRIVER AT COLUMN 5 - WITH OWNER POLICY AT COLUMN 22 END FOR END FOR END

The VALUE IN phrase

The VALUE IN phrase can be used in place of an explicit field name in a PRINT statement. Each time the loop containing the PRINT statement is executed, the current value of the noted field is printed.

This output is generated by the PRINT statement in the previous request:

PRINCIPLE DRIVER POLICY NO. 100735 100304 102081 100865 101693 100761 . . . .

The VALUE IN phrase can be used in SET HEADER and SET TRAILER statements to print what was noted. VALUE IN also can be used to replace any value in a retrieval statement (FIND).

Counting records in a found set

Issues

Records retrieved by a FOR EACH RECORD statement cannot be counted using the COUNT RECORDS statement.

In addition, counting records retrieved by a FIND statement and counted within a record loop can produce unexpected results.

Example

Consider the problem of trying to count the total number of records retrieved by the FIND.RECS statement below:

GET.AGENT: FIND ALL RECORDS FOR WHICH AGENT = CASOLA END FIND FOR EACH RECORD IN GET.AGENT KEEP.ZIP: NOTE ZIP FIND.RECS: FIND ALL RECORDS FOR WHICH ZIP = VALUE IN KEEP.ZIP END FIND

Suppose 25 records were found each time through the loop, or 100 records after four iterations. Then the sequence:

. . . CT.RECS: COUNT RECORDS IN FIND.RECS PRINT COUNT IN CT.RECS END FOR

would print, not 100, but

25 25 25 25

and the sequence

. . . CT.RECS: COUNT RECORDS IN FIND.RECS END FOR PRINT.CT: PRINT COUNT IN CT.RECS

would print only the last of the four counts, or

25

Solution

The correct total could be obtained by the following statements (which use the assignment statement discussed in Assigning values to %variables):

. . . CT.RECS: COUNT RECORDS IN FIND.RECS %TOTAL = %TOTAL + COUNT IN CT.RECS END FOR PRINT.CT: PRINT %TOTAL

However, this technique would be accurate only if the record found by the FIND.RECS statement was not found in any earlier iteration. If the records were found earlier, then a list should be used to arrive at the correct total. Lists describes the statements used to create lists.

PRINT ALL INFORMATION (or PAI) INTO statement

The PRINT ALL INFORMATION (PAI) INTO statement lets you move a record's field names and values into a form of storage that can be easily manipulated. The benefits of the PAI INTO statement include an effective copy-record capability, and also a convenient way to determine field names and values encountered within a FOR EACH RECORD loop.

Syntax

The form of the PAI INTO statement is:

PRINT ALL INFORMATION option INTO array_1, array_2, [array_3] [FROM start] [COUNT ct]

Where

• option can be one of the following and specifies how Large Object fields are handled:

  LOB_DATA — If the LOB is 255 bytes or less and the field value array element is large enough, the LOB data is copied to the field value array element. Otherwise, $STATUS is set to 2, $STATUSD is set to 3, and the entire record is output in PAI format to the output device.

  The default option for the PAI INTO statement is LOB_DATA.

  LOB_SHORT_DATA — Copies the first 255 bytes of the LOB data to the field value array element.

  LOB_NO_DATA — No LOB data is copied to the field value array. The field name and LOB descriptor are copied to array_1 and array_3, respectively.

  LOB_NONE — No LOB information--field name, LOB data, nor LOB descriptor--is copied to the output arrays.

• array_1 through array_3 are string %variable or image item arrays. This form of the PAI statement fills:

array_1 with field names

array_2 with field values

array_3 with the Large Object descriptor, of at least 27 bytes starting at X'800000', from which you can extract the field length, reserve, and so on.

When writing requests using the PAI INTO statement, remember to add a subscript to the name of each array variable. The subscripts determine which element of the array receives the first value generated by the PAI INTO statement. The subscripts for the three arrays should be the same, and, unless you have a specific reason for wanting to reserve elements at the beginning of the arrays, the value of each subscript should be 1, as shown in PRINT ALL INFORMATION (or PAI) INTO statement.

The lengths of the field name and field value arrays must equal, respectively, the length of the longest known field name plus one and the length of the longest known field value plus one. The extra byte is required for the length byte which precedes each field name and field value. The length of the Large Object descriptor must be at least 27 bytes, otherwise you will get a $STATUSD=4 return code.

• start is a scalar %variable. If supplied, start is a relative field pointer indicating where to start extracting field values. The default is 1.
• ct is a scalar %variable that indicates the number of fields that have been extracted from the record after the PAI INTO statement executes. If you specify ct as a %variable, Model 204 uses the %variable as a counter to keep track of the number of fields extracted.

Usage

The PAI INTO statement writes only to the arrays, not to the terminal.

Error messages

Error messages are issued if the INTO arguments are not string arrays, or if ct is a literal. Since PAI INTO can fail for a variety of storage-related reasons, error conditions are stored in $STATUS and $STATUSD.

The possible $STATUS values are:

0	PAI INTO completed successfully.
2	PAI INTO failed, see $STATUSD for details.

$STATUSD values are:

0	PAI INTO completed successfully.
1	PAI INTO failed because either the field name array, the field value array, or the Large Object descriptor array was not large enough to contain all of the record's fields.
2	PAI INTO failed because a field name was too large for an array element.
3	PAI INTO failed because a field value was too large for an array element.
4	PAI INTO failed because a Large Object descriptor was too large for an array element.

If $STATUSD is nonzero, then the value of the ct %variable contains the index value (as in the arrays shown in the following example) of the last field name and value successfully extracted.

Record locking with PAI processing

When you are processing unlocked records (such as records on lists or in sets produced via FIND WITHOUT LOCKS) through a PAI loop in local context, Model 204 puts a share lock on each record as it is being processed. This lock prevents other updating users from changing records as they are being processed, but might cause record locking conflicts.

To help catch such conflicts, add an ON RECORD LOCKING CONFLICT unit to User Language procedures using PAI against unlocked records.

When you are using the Parallel Query Option/204 and you are working with a remote file, no locks are issued against the records in the remote file since Model 204 works from a local copy of the remote records.

Example

The following schematic example shows one way to set up a PAI INTO statement to perform a "copy record" function.

BEGIN DECLARE %FIELDNAM IS STRING LEN 20 ARRAY(100) DECLARE %FIELDVAL IS STRING LEN 80 ARRAY(100) NO FS DECLARE %FGO IS FIXED DECLARE %RET IS FIXED DECLARE %STAT IS FIXED DECLARE %INDX IS FIXED FINDLOOP: IN FILE1 FIND ALL RECORDS... FOR EACH RECORD IN FINDLOOP STORELOOP: IN FILE2 STORE RECORD END STORE %FGO = 1 PAI INTO %FIELDNAM(1), %FIELDVAL(1) FROM %FGO - COUNT %RET %STAT = $STATUS * (optional error processing goes here) FOR RECORD NUMBER IN STORELOOP FOR %INDX FROM 1 to %RET IF %FIELDVAL(%INDX) NE '' THEN ADD %%FIELDNAM(%INDX) = %FIELDVAL(%INDX) ELSE ADD %%FIELDNAM(%INDX) = '' END IF END FOR END FOR END FOR END

Null field values

Notice that the ADD statement logic in the previous example includes an ELSE clause that forces processing of null field values. This logic is necessary because assigning a null value from a %variable stores nothing in the output file. If you do not need to copy null field values, then omit this special processing.