Record loops: Difference between revisions

From m204wiki
Jump to navigation Jump to search
 
(50 intermediate revisions by 5 users not shown)
Line 17: Line 17:
===Processing records in order===
===Processing records in order===
<p>
<p>
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.</p>
The <var>In Order</var> option of the <var>For Each Record</var> statement processes records in index order, sort order, or sort key order, without requiring a sort of the records.</p>
   
   
===The prefetch feature===
===The prefetch feature===
<p>
<p>
<var class="product">Model&nbsp;204</var> supports a [[#The_prefetch (look-ahead_read) feature|prefetch feature]] (look-ahead read), which can provide performance improvements for applications containing FOR EACH RECORD statements. </p>
<var class="product">Model&nbsp;204</var> supports a [[#The_prefetch (look-ahead_read) feature|prefetch feature]] (look-ahead read), which can provide performance improvements for applications containing <var>For Each Record</var> statements. </p>


===Processing a single record or limited number of records===
===Processing a single record or limited number of records===
<p>
<p>
The FOR RECORD NUMBER statement provides a very efficient method to retrieve a specified record and initiate a record loop.</p>
The <var>For Record Number</var> statement provides a very efficient method to retrieve a specified record and initiate a record loop.</p>
<p>
<p>
The FOR <var class="term">k</var> RECORDS statement allows you to indicate a maximum number of times the loop is to be repeated.</p>
The <var>For <i>k</i> Records</var> statement allows you to indicate a maximum number of times the loop is to be repeated.</p>


===Skip processing===
===Skip processing===
<p>
<p>
When you are processing a found set of records in a FOR EACH RECORD loop, you can instruct <var class="product">Model&nbsp;204</var> 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.</p>
When you are processing a found set of records in a <var>For Each Record</var> loop, you can instruct <var class="product">Model&nbsp;204</var> 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.</p>
<p>
<p>
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.</p>
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.</p>
   
   
===Using a Note statement===
<p>
The <var>Note</var> statement (described further in [[#Note statement|Note statement]], below) causes <var class="product">Model&nbsp;204</var> to store the value of any field in a retrieved set, permitting the use of that set in subsequent retrievals within the same request.</p>
<p>
You can use the <var>Note</var> statement and the <var>Value In</var> clause of a <var>Find</var> statement to perform cross-referencing. You can also use the <var>Note</var> statement to set a value for later reference.</p>
===Saving and recalling position in a FOR loop===
===Saving and recalling position in a FOR loop===
<p>
<p>
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 [[Global features#Saving and recalling a POSITION in a FOR loop|Saving and recalling a POSITION in a FOR loop]] for a detailed discussion of this topic.</p>
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 [[Global features#Saving and recalling a POSITION in a FOR loop|Saving and recalling a POSITION in a FOR loop]] for a detailed discussion of this topic.</p>
   
   
===PAI INTO statement===
===PAI Into statement===
<p>
<p>
The PAI INTO statement provides a convenient method to determine field names and values encountered within a FOR EACH RECORD loop.</p>
The <var>[[#paiInto|PAI Into]]</var> statement provides a convenient method to determine field names and values encountered within a <var>For Each Record</var> loop. </p>


==FOR EACH RECORD statement==
==For Each Record statement==
<p>
<p>
You can use the FOR EACH RECORD statement to perform: </p>
You can use the <var>For Each Record</var> statement to perform: </p>
<ul>
<ul>
<li>Retrieval and looping</li>
<li>Retrieval and looping</li>
<li>Looping only</li>
<li>Looping only</li>
</ul>
</ul>
<b>Syntax</b>
<p>
<p class="syntax">FOR <span class="squareb">{</span>EACH <span class="squareb">|</span> <span class="term">n</span><span class="squareb">}</span> <span class="squareb">{</span>RECORD <span class="squareb">|</span> RECORDS<span class="squareb">}</span> -
<b>Syntax</b> </p>
[IN <span class="term">label</span> <span class="squareb">|</span> ON [LIST] <span class="term">listname</span>] -
{{Template:For Each Record statement syntax}}  
[IN [ASCENDING <span class="squareb">|</span> DESCENDING] [SORTKEY] ORDER -
Where:
[BY [EACH] <span class="term">fieldname</span>] -
[FROM <span class="term">value1</span>] [TO <span class="term">value2</span>]
[BY {<span class="term">%variable</span> <span class="squareb">|</span> <span class="term">literal</span>}] -
[OPTIMIZING FNV]
[{WHERE <span class="squareb">|</span> WITH} <span class="term">retrieval-conditions</span>]
</p>
 
<b>Where</b>
<ul>
<ul>
<li><var class="term">n</var> limits the number of records to retrieve. For example, if <var class="term">n</var> equals 17, the first 17 records in the file are retrieved.</li>
<li><var class="term">n</var> limits the number of records to retrieve. For example, if <var class="term">n</var> equals 17, the first 17 records in the file are retrieved.</li>


<li><var class="term">label</var> specifies the label of a preceding FIND statement. </li>
<li><var class="term">label</var> specifies the label of a preceding <var>Find</var> statement. </li>


<li><var class="term">listname</var> specifies a list from a preceding FIND statement.</li>
<li><var class="term">listname</var> specifies a list from a preceding <var>Find</var> statement.</li>


<li><var class="term">fieldname</var> identifies the field from which to retrieve values.</li>
<li><var class="term">fieldname</var> identifies the field from which to retrieve values.</li>


<li><var class="term">value1</var> and <var class="term">value2</var> specify the beginning and ending range of values to retrieve.</li>
<li><var class="term">value1</var> or <var class="term">expression1</var>, and <var class="term">value2</var> or <var class="term">expression2</var>, specify the beginning and ending of a range of values to retrieve. An [[Using variables and values in computation#Expressions|expression]] must be enclosed in parentheses. </li>
 
<li><var class="term">%variable</var> or <var class="term">literal</var> specify a comparison value</li>
 
<li><var class="term">retrieval-conditions</var> can be:
<p class="syntax"><span class="squareb">[</span><span class="term">fieldname</span> <span class="squareb">{</span>EQ <span class="squareb">|</span> NE<span class="squareb">}</span> VALUE IN <span class="term">value_set</span><span class="squareb">]</span>
</p>
<p>
or</p>
<p class="syntax"><span class="squareb">[</span><span class="term">fieldname</span> <span class="squareb">{</span>EQ <span class="squareb">|</span> NE<span class="squareb">}</span> VALUE (<span class="term">expression</span>)<span class="squareb">]</span>
</p>


<ul>
<li><var class="term">%variable</var> or <var class="term">literal</var> specify a numerical increment, which must be greater than or equal to 1. This increment minus 1 is the number of records skipped at each loop iteration.</li>
<li>The <var class="term">value_set</var> label represents an existing value set. Values of the value set in the <code>EQ VALUE IN <i>value_set</i></code> clause are treated as boolean OR, for example:
<p class="code"><i>field</i> = <i>value1</i> OR <i>field</i> = <i>value2</i> OR....
</p></li>
<li>The <code>NE VALUE IN <i>value_set</i> clause</code> is likewise treated as boolean AND, for example:
<p class="code"><i>field</i> <> <i>value1</i> AND <i>field</i> <> <i>value2</i> AND....
</p>
<p>
See also [[Basic SOUL statements and commands#Processing a VALUE IN clause|Processing a VALUE IN clause]].</p></li>


<li><var class="term">expression</var> is enclosed in parentheses and can be of any type listed in [[Using variables and values in computation#Using expressions for value retrieval|Using expressions for value retrieval]].</li>
<li><var class="term">retrieval-conditions</var> is a simple or arbitrarily complex combination of filtering phrases. For full details, see [[Statement syntax#Retrieval condition syntax|Retrieval condition syntax]]. </li>
</ul>
</ul>
</ul>
   
   
===Retrieval and looping===
===Retrieval and looping===
<p>
<p>
When the FOR EACH RECORD statement is specified without the IN option, it functions as a FIND statement as well as initiating a loop.</p>
When the <var>For Each Record</var> statement is specified <em>without</em> the <var>In</var> option, it functions as a <var>Find</var> statement as well as initiating a loop.</p>
<p>
<p>
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. </p>
This form of the <var>For Each Record</var> statement is more efficient than when <var>For Each Record</var> is used for looping only, because records are locked until they are processed. </p>
<p>
<p>
Use this form when the set of records being processed is not referred to by subsequent statements outside the loop.       </p>
Use this form when the set of records being processed is not referred to by subsequent statements outside the loop. </p>
   
   
====Example====
====Example====
<p class="code">BEGIN
<p class="code">Begin
     FOR EACH RECORD
     For Each Record
         PRINT FULLNAME WITH AGENT AT COLUMN 30
         Print FULLNAME With AGENT At Column 30
     END FOR
     End For
END
End
</p>
</p>


===Looping only===
===Looping only===
<p>
<p>
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.</p>
When the <var>For Each Record</var> statement is specified with the <var>In</var> or <var>On</var> option, it functions as a loop statement only, and it refers back to a previously found set in order to process the records.</p>
<p>
<p>
Use this form when the set of records being processed is referred to by subsequent statements outside the loop.</p>
Use this form when the set of records being processed is referred to by subsequent statements outside the loop.</p>
Line 137: Line 104:
===Handling empty found sets===
===Handling empty found sets===
<p>
<p>
Before doing any FOR EACH RECORD processing, <var class="product">Model&nbsp;204</var> checks to see if the referenced found set is empty. If the found set is empty, no processing takes place.</p>
Before doing any <var>For Each Record</var> processing, <var class="product">Model&nbsp;204</var> checks to see if the referenced found set is empty. If the found set is empty, no processing takes place.</p>
   
   
===Using expressions for value retrieval===
===Using expressions for value retrieval===
Line 143: Line 110:
You can use expressions to provide the values in:</p>
You can use expressions to provide the values in:</p>
<ul>
<ul>
<li>the FROM ... TO clause of a FOR EACH RECORD IN ORDER BY statement</li>
<li>The <var>From ... To</var> clause of a <var>For Each Record In Order By</var> statement</li>


<li>the EQ VALUE clause of a FOR EACH RECORD WHERE statement</li>
<li>The <var>Eq Value</var> clause of a <var>For Each Record Where</var> statement</li>
</ul>
</ul>
   
   
===FOR EACH RECORD IN ORDER BY===
===FOR EACH RECORD IN ORDER BY===
<b>Syntax</b>
<b>Syntax</b>
<p class="syntax">FOR EACH RECORD IN ORDER BY <span class="term">fieldname</span> FROM (<span class="term">expression1</span>) TO (<span class="term">expression2</span>)
<p class="syntax">For Each Record In Order By <span class="term">fieldname</span> From (<span class="term">expression1</span>) To (<span class="term">expression2</span>)
</p>
</p>
<b>Where</b>
<p>
<p>
<var class="term">expression1</var> and <var class="term">expression2</var> are enclosed in parentheses and can be of the following types: function call, string concatenation, arithmetic operation, User Language construct, or Boolean expression.</p>
Where:</p>
<p>
<var class="term">expression1</var> and <var class="term">expression2</var> are enclosed in parentheses and can be of the following types: function call, string concatenation, arithmetic operation, SOUL construct, or Boolean expression.</p>
   
   
====Example====
====Example====
Line 174: Line 142:
   
   
===FOR EACH RECORD WHERE===
===FOR EACH RECORD WHERE===
A <var>Where</var> clause in a <var>For Each Record</var> statement acts as an implied <var>Find</var> statement.
<b>Syntax</b>
<b>Syntax</b>
<p class="syntax">FOR EACH RECORD WHERE <span class="term">fieldname</span> EQ VALUE (<span class="term">expression</span>)
<p class="syntax">For Each Record Where <span class="term">fieldname</span> Eq Value(<span class="term">expression</span>)
</p>
</p>
<b>Where</b>
<p>
<p>
<var class="term">expression</var> is enclosed in parentheses and is one of the following types: function call, string concatenation, arithmetic operation, User Language construct, or Boolean expression.</p>
Where: </p>
<p>
<var class="term">expression</var> is enclosed in parentheses and is one of the following types: function call, string concatenation, arithmetic operation, SOUL construct, or Boolean expression.</p>
   
   
====Example====
====Example====
Line 195: Line 166:
END
END
</p>
</p>
 
===Using $LOBLEN to get the size of a Large Object data===
===Using $LOBLEN to get the size of a Large Object data===
<p>
<p>
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).</p>
You can tell how large a Large Object field is by issuing the <var>$Loblen</var> function using the Large Object field name in a <var>For Record</var> loop. The <var>$Loblen</var> function takes one argument that can be a field name or a field name variable (%%variable).</p>
   
   
====Example====
====Example====
Line 212: Line 183:
===Using the $LOBLEN and $LOBRESERVE functions===
===Using the $LOBLEN and $LOBRESERVE functions===
<p>
<p>
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.</p>
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.
</p>
<p class="code">BEGIN
<p class="code">BEGIN
  %S IS STRING LEN 100 ARRAY(3)
  %S IS STRING LEN 100 ARRAY(3)
Line 238: Line 210:
<p>
<p>
This is the output from the previous procedure:</p>
This is the output from the previous procedure:</p>
<p class="code">$LOBLEN=100
<p class="output">$LOBLEN=100
$LOBRESERVE=1000
$LOBRESERVE=1000
BLOB = (length 100) (reserve 1000)
BLOB = (length 100) (reserve 1000)
Line 256: Line 228:
===Other FOR EACH RECORD options===
===Other FOR EACH RECORD options===
<p>
<p>
The FOR EACH RECORD statement without any options specified processes every record in the current file or group.</p>
The <var>For Each Record</var> statement without any options specified processes every record in the current file or group.</p>
<p>
<p>
Other options available for the FOR EACH RECORD statement are described in detail on this page.</p>
Other options available for the <var>For Each Record</var> statement are described in detail on this page. </p>


==Restricting FOR EACH RECORD processing==
==Restricting For Each Record processing==
<b>Syntax</b>
<b>Syntax</b>
<p>
<p>
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:</p>
Use this form of the <var>For Each Record</var> statement to place one or more restrictions on the retrieval of records during <var>For Each Record</var> processing:</p>
<p class="syntax">FOR EACH RECORD <span class="squareb">[</span>IN <span class="term">label</span><span class="squareb">]</span> <span class="squareb">[</span>OPTIMIZING FNV<span class="squareb">]</span>  
<p class="syntax">For Each Record <span class="squareb">[</span>In <span class="term">label</span><span class="squareb">]</span> <span class="squareb">[</span>Optimizing FNV<span class="squareb">]</span>  
  [{WHERE <span class="squareb">|</span> WITH} <span class="term">retrieval-conditions</span>]
  [{Where <span class="squareb">|</span> With} <span class="term">retrieval-conditions</span>]
</p>
</p>
<p>
<p>
where <var class="term">retrieval-conditions</var> are any conditions that are valid for the FIND statement, such as:</p>
where <var class="term">retrieval-conditions</var> are any conditions that are valid for the <var>Find</var> statement, such as:</p>
<p class="syntax"><span class="squareb">[</span><span class="term">fieldname</span><span class="squareb">]</span> <span class="squareb">{</span>EQ <span class="squareb">|</span> NE<span class="squareb">}</span> VALUE IN <span class="term">value-set</span>
<p class="syntax"><span class="squareb">[</span><span class="term">fieldname</span><span class="squareb">]</span> <span class="squareb">{</span>Eq <span class="squareb">|</span> Ne<span class="squareb">}</span> Value In <span class="term">value-set</span>
</p>
</p>
<p>
<p>
Refer to [[Record retrievals|"Record retrievals"]] for more information about retrieval conditions.</p>
Refer to [[Record retrievals]] for more information about retrieval conditions.</p>
   
   
===Field name variable optimization===
===Field name variable optimization===
<p>
<p>
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.</p>
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 <var>For Each Record</var> 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.</p>
<p>
<p>
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.</p>
With the <var>For Each Record</var> 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.</p>
<p>
<p>
OPTIMIZING FNV can be abbreviated to OPT FNV.</p>
OPTIMIZING FNV can be abbreviated to OPT FNV.</p>
Line 288: Line 260:
See [[Using variables and values in computation#Field name variables|Field name variables]] for a detailed discussion.</p>
See [[Using variables and values in computation#Field name variables|Field name variables]] for a detailed discussion.</p>
<p>
<p>
You can also use OPTIMIZING FNV with the FOR RECORD NUMBER statement. See [[#Processing a single record|Processing a single record]] for a discussion of this statement.</p>
You can also use OPTIMIZING FNV with the <var>For Record Number</var> statement. See [[#Processing a single record|Processing a single record]] for a discussion of this statement.</p>
   
   
===Using WHERE or WITH clause with other options===
===<b id="WhereWith"></b>Using WHERE or WITH clause with other options===
<p>
<p>
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.</p>
A <var>Where</var> or <var>With</var> clause in a <var>For Each Record</var> statement acts as an implied <var>Find</var> statement. If such a clause is used with any other option on the <var>For Each Record</var> statement, the <var>Where</var> or <var>With</var> clause must be the last option specified. </p>
<p>
<p>
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. </p>
You can combine the <var>In</var> option with the <var>Where</var> or <var>With</var> clauses in a <var>For Each Record</var> statement to further filter a previously found set. </p>
   
   
====Example====
====Example====
<p class="code">BEGIN
<p class="code">begin
GETPOL: FIND ALL RECORDS FOR WHICH
getpol: find all records for which
             POLICY NO IS GREATER THAN 100320
             policy no is greater than 100320
         END FIND
         end find
         FOR EACH RECORD IN GETPOL WHERE
         for each record in getpol where
                 (TOTAL PREMIUM IS GREATER THAN 393
                 (total premium is greater than 393
                 AGENT IS GREATER THAN CASOLA)
                 agent is greater than 'CASOLA')
             PRINT POLICY NO WITH AGENT AT COLUMN 30
             print policy no with agent at column 30
         END FOR
         end for
END
</p>
</p>
<p class="note"><b>Note:</b> 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.  </p>
<p class="note"><b>Note:</b> If the retrieval conditions specified in the <var>Where</var> or <var>With</var> clause results in a direct search of the data (Table B), it is more efficient to use the <var>For Each Record</var> statement without retrieval conditions specified and use the <var>If</var> statement within the loop to specify the criteria.  </p>
 
===Specify retrieval criteria on one logical line===
===Specify retrieval criteria on one logical line===
<p>
<p>
Line 317: Line 288:
<p>
<p>
Use parentheses to continue a line. Bear in mind that <var class="product">Model&nbsp;204</var> treats the unstated Boolean operator as an implied AND.</p>
Use parentheses to continue a line. Bear in mind that <var class="product">Model&nbsp;204</var> treats the unstated Boolean operator as an implied AND.</p>
<p class="note"><b>Note:</b> 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 [[Record retrievals#Interpretation of Boolean operators in retrievals|Interpretation of Boolean operators in retrievals]]).</p>
<p class="note"><b>Note:</b> The rule for using parentheses with the <var>For Each Record Where</var> construct is different from the rule for using parentheses with the <var>Find</var> construct (see [[Record retrievals#Interpretation of Boolean operators in retrievals|Interpretation of Boolean operators in retrievals]]).</p>
   
   
====Example====
====Example====
<p>
<p>
This statement is considered one logical line:</p>
This statement is considered one logical line:</p>
<p class="code">FOR EACH RECORD WHERE
<p class="code">for each record where
   (TOTAL PREMIUM IS GREATER THAN 393
   (total premium is greater than 393
     AGENT IS GREATER THAN CASOLA) OR INCIDENT IS T1
     agent is greater than 'CASOLA') or incident is T1</p>
</p>
<p>
<p>
In this example, an implied AND is generated at the end of the first select criteria. </p>
In this example, an implied AND is generated at the end of the first select criteria. </p>
Line 331: Line 301:
==Processing records in order==
==Processing records in order==
<p>
<p>
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.         </p>
The <var>In Order</var> option of the <var>For Each Record</var> 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. </p>
<p>
<p>
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.       </p>
You can use index order processing with fields that were defined with the <var>ORDERED</var> attribute. Record loops in index order execute more slowly than record loops in sort order. </p>
<p>
<p>
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. </p>
You can use sort order processing for records in a file that 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.  
<p class="note"><b>Note:</b> 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.         </p>
</p>
<p class="note"><b>Note:</b> If the <var>In Order</var> option processes a field that is a sort key but also has the <var>ORDERED</var> attribute, and if <var>Sortkey</var> is not specified, index order processing is performed. </p>
<p>
<p>
If you want to produce a sort order which is not provided for by the syntax of the IN ORDER option, see [[Sorting#Sorting|Sorting]] for information about the SORT statement.</p>
If you want to produce a sort order that is not provided for by the syntax of the <var>In Order</var> option, see [[Sorting#Sorting|Sorting]] for information about the <var>Sort</var> statement.</p>
   
   
===Reference context===
===Reference context===
<p>
<p>
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:</p>
Index order and sort order processing can be performed only on records from a single file. Therefore, do not use the <var>In Order</var> option with groups in <var>For Each Record</var> loops. Such use of the <var>In Order</var> option with groups results in a counting error and this error message:</p>
<p class="code">M204.0243 'IN ORDER' NOT VALID IN GROUP CONTEXT
<p class="code">M204.0243 'IN ORDER' NOT VALID IN GROUP CONTEXT
</p>
</p>
<p>
<p>
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.)</p>
If you want to process records from a group in sorted order, use the <var>Sort</var> statement. (<var>Sort</var> statement processing typically is slower than index order or sort order processing.) </p>
<p>
<p>
The IN ORDER and VALUE IN options are supported in remote file context. See [[#Referring to the current value|Referring to the current value]] for a discussion of the VALUE IN option.</p>
The <var>In Order</var> and <var>value In</var> options are supported in remote file context. See [[#Referring to the current value|Referring to the current value]] for a discussion of the <var>Value In</var> option. </p>
   
   
===Specifying processing order===
===Specifying processing order===
<p>
<p>
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.</p>
If the fields processed by the <var>For Each Record</var> statement have the <var>ORDERED</var> attribute, or if the file is a sorted file, ordered processing can occur without requiring a separate sort of the found records.</p>
<p>
<p>
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.</p>
If the file processed is a sorted file, the <var>Sortkey</var> option forces records to be processed in order according to the values of the sort key field. </p>
<b>Syntax</b>
<b>Syntax</b>
<p>
<p>
This form of the FOR EACH RECORD statement demonstrates how you specify processing order:</p>
This form of the <var>For Each Record</var> statement demonstrates how you specify processing order:</p>
<p class="syntax">FOR EACH RECORD <span class="squareb">[</span>IN <span class="term">label</span> <span class="squareb">|</span> ON <span class="term">list</span><span class="squareb">]</span> IN
<p class="syntax">For Each Record <span class="squareb">[</span>In <span class="term">label</span> <span class="squareb">|</span> On <span class="term">list</span><span class="squareb">]</span> In
                 [ASCENDING <span class="squareb">|</span> DESCENDING]  
                 [Ascending <span class="squareb">|</span> Descending]  
                 [SORTKEY] ORDER [BY <span class="term">fieldname</span>]  
                 [Sortkey] Order [By <span class="term">fieldname</span>]  
                 [FROM <span class="term">value1</span>] [TO <span class="term">value2</span>]
                 [From <span class="term">value1</span>] [To <span class="term">value2</span>]
</p>
</p>
<b>Where</b>
Where:
<ul>
<ul>
<li>ASCENDING and DESCENDING indicate the order in which the record set is sorted. ASCENDING order is the default.     </li>
<li><var>Ascending</var> and <var>Descending</var> indicate the order in which the record set is sorted. Ascending order is the default. </li>


<li>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. </li>
<li><var>Sortkey</var> forces records to be processed in sort key order when a sorted file's sort key also has the <var>ORDERED</var> attribute. If <var>Sortkey</var> is omitted, and the specified field is both a sort key and an <var>ORDERED</var> field, processing order defaults to Ordered Index order. </li>


<li>BY <var class="term">fieldname</var> specifies the ORDERED field to be used to establish processing order. For sorted files, BY is optional.</li>
<li>BY <var class="term">fieldname</var> specifies the <var>ORDERED</var> field to be used to establish processing order. For sorted files, <var>By</var> is optional. </li>


<li>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.     </li>
<li>The <var>From</var> and <var>To</var> clauses specify the range of values to be processed. <var>From</var> indicates the beginning value; <var>To</var> indicates an ending value. You can explicitly define a range by using both <var>From</var> and <var>To</var>, or you can implicitly define a range by specifying only one of these options. Care must be taken in specifying values for <var>From</var> and <var>To</var>; sort key processing behaves differently from Ordered Index processing when you use value ranges. </li>
</ul>
</ul>


===Specifying value ranges for FOR EACH RECORD processing===
===Specifying value ranges for For Each Record processing===
   
   
====Records in which the BY field is not present====
====Records in which the BY field is not present====
<p>
<p>
If a range is specified (that is either FROM <var class="term">value1</var> TO <var class="term">value2</var> or just FROM <var class="term">value1</var> or just TO <var class="term">value2</var>), records that do not have the field present are not processed.</p>
If a range is specified (that is <code>From <i>value1</i> To <i>value2</i></code>, or just <code>From <i>value1</i></code>, or just <code>To <i>value2</i></code>), records that do not have the field present are not processed.</p>
<p>
<p>
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.</p>
If no range limits are specified and a pattern is not specified using the <var>Where</var> or <var>With</var> option, records for which the field is not present are processed at the end of the ordered set.</p>


====Order of FROM and TO values====
====Order of FROM and TO values====
<p>
<p>
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:</p>
With sort key order processing, the values for <var>From</var> and <var>To</var> can be presented in either order, for both of the <var>Ascending</var> or the <var>Descending</var> options. For example, the two code fragments below each produce the same result:</p>
<p class="code"> ...
<p class="code"> ...
FOR EACH RECORD IN FD1 IN ASCENDING SORTKEY ORDER BY FULLNAME -
FOR EACH RECORD IN FD1 IN ASCENDING SORTKEY ORDER BY FULLNAME -
Line 398: Line 369:
</p>
</p>
<p>
<p>
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):</p>
With Ordered Index processing (when <var>Sortkey</var> is not specified), the order in which you specify the <var>From</var> and <var>To</var> values is sensitive to whether you are doing ascending or descending order processing. For example, if you are doing <var>Descending</var> order processing, the <var>From</var> value must be higher than the <var>To</var> value, as in the following example, which produces output for the year 1990 in descending <code>INCIDENT DATE</code> order (assuming <code>INCIDENT DATE</code> is defined as an <var>ORDERED</var> field):</p>
<p class="code"> ...
<p class="code"> ...
FOR EACH RECORD IN FOUNDSET IN DESCENDING ORDER BY INCIDENT DATE
FOR EACH RECORD IN FOUNDSET IN DESCENDING ORDER BY INCIDENT DATE
Line 408: Line 379:
</p>
</p>
<p>
<p>
If, in the preceding example, you had specified DESCENDING order, but had given the range as <code>FROM 900101 TO 901231</code>, the request would compile without producing errors, but no records would be printed. </p>
If, in the preceding example, you had specified <var>Descending</var> order, but had given the range as <code>FROM 900101 TO 901231</code>, the request would compile without producing errors, but no records would be printed. </p>


====Processing behavior with implicitly specified ranges====
====Processing behavior with implicitly specified ranges====
<p>
<p>
You can specify a value range for FOR EACH RECORD processing implicitly by using only one of the FROM and TO options. <var class="product">Model&nbsp;204</var> 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. </p>
You can specify a value range for <var>For Each Record</var> processing implicitly by using only one of the <var>From</var> and <var>To</var> options. <var class="product">Model&nbsp;204</var> 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 <var>Ascending</var> or <var>Descending</var> processing, and whether the processing is based on Ordered Index or sort key (for sorted files) order. </p>
<p>
<p>
Sort key processing behavior for DESCENDING order with implicitly specified value ranges is the opposite of the corresponding behavior for Ordered Index processing.</p>
Sort key processing behavior for descending order with implicitly specified value ranges is the opposite of the corresponding behavior for Ordered Index processing. </p>
<table>
<table>
<caption>FOR EACH RECORD processing behavior with implicitly defined value ranges</caption>
<caption>For Each Record processing behavior with implicitly defined value ranges</caption>
<tr class="head">
<tr class="head">
<th rowspan="2" style="vertical-align:top">Processing direction</th>
<th rowspan="2" style="vertical-align:top">Processing direction</th>
Line 422: Line 393:
<th colspan="2">Value range processed</th>
<th colspan="2">Value range processed</th>
</tr>
</tr>
<tr>
<tr>
<th>Ordered Index
<th>Ordered Index processing</th>
processing</th>
<th>SORTKEY processing</th>
<th>SORTKEY
processing</th>
</tr>
</tr>
<tr>
<tr>
<td rowspan="2">ASCENDING</td>
<td rowspan="2">ASCENDING</td>
<td>FROM only</td>
<td>FROM only</td>
<td>From specified value
<td>From specified value to highest field value</td>
to highest field value</td>
<td>From specified value to highest field value</td>
<td>From specified value
to highest field value</td>
</tr>
</tr>
<tr>
<tr>
<td>TO only</td>
<td>TO only</td>
Line 441: Line 411:
<td>From lowest field value to specified value</td>
<td>From lowest field value to specified value</td>
</tr>
</tr>
<tr>
<tr>
<td rowspan="2">DESCENDING</td>
<td rowspan="2">DESCENDING</td>
<td>FROM only</td>
<td>FROM only</td>
<td>From specified value
<td>From specified value to lowest field value</td>
to lowest field value</td>
<td>From highest field value to specified value</td>
<td>From highest field value to specified value</td>
</tr>
</tr>
<tr>
<tr>
<td>TO only</td>
<td>TO only</td>
<td>From highest field value
<td>From highest field value to specified value</td>
to specified value</td>
<td>From specified value to lowest field value</td>
<td>From specified value
to lowest field value</td>
</tr>
</tr>
</table>
</table>
Line 459: Line 428:
===Optimization of FOR statement LIKE option===
===Optimization of FOR statement LIKE option===
<p>
<p>
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. <var class="product">Model&nbsp;204</var> 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.</p>
Whenever the <var>Like</var> option is used with the <var>For Each Record</var> or <var>For Each Value</var> statement which is performing Ordered index processing against an existing found set or list, an attempt is made to create pattern driven terms. <var class="product">Model&nbsp;204</var> optimizes the <var>From</var> and <var>To</var> values with the pattern terms to minimize Ordered Index processing. This optimization creates the most restrictive <var>From</var> and <var>To</var> value based on the specified <var>From</var> and <var>To</var> value (if any) and the current pattern term.
<p class="note"><b>Note:</b> 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|Retrieval and looping]]).</p>
</p>
<p class="note"><b>Note:</b> This optimization does not take place when the <var>For</var> statement is performing both retrieval and looping (when it is used without the <var>In</var> option; see [[#Retrieval and looping|Retrieval and looping]]). </p>
<p>
<p>
For example (data = 'ABCG' 'ABDG' 'ABEG' 'ABFG' 'ADFG' 'AEFG'): </p>
For example (data = 'ABCG' 'ABDG' 'ABEG' 'ABFG' 'ADFG' 'AEFG'): </p>
Line 468: Line 438:
</p>
</p>
<p>
<p>
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. </p>
The optimization changes the <var>From</var> value to <code>ABE</code> (more restrictive than <code>A</code>), and it changes the <var>To</var> value to <code>ABF</code> (more restrictive than <code>ABFG</code>). This results in searching just two values (<code>ABEG</code> and <code>ABFG</code>) to print <code>ABEG</code>. </p>
<p>
<p>
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.</p>
If the <var>From</var> or <var>To</var> value is omitted, the optimization creates an implied <var>From</var> or <var>To</var> value based on the pattern specified after the <var>Like</var> keyword. In the example above, if the <var>From</var> value were omitted, the results would be the same. The optimization would create an implied <var>From</var> value of <code>ABE</code> while changing the <var>To</var> value to <code>ABF</code>.</p>


===Referring to the current value===
===Referring to the current value===
<p>
<p>
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.)</p>
The current value of the <var>ORDERED</var> field can be referred to by using a VALUE IN phrase if the <var>For Each Record</var> 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.)</p>
<p class="code">FORNAME: FOR EACH RECORD IN ORDER BY FULLNAME
<p class="code">FORNAME: FOR EACH RECORD IN ORDER BY FULLNAME
             %X = VALUE IN FORNAME
             %X = VALUE IN FORNAME
Line 483: Line 453:
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.
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===
===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:
Individual fields within field groups can cause syntax errors. In the following example, <code>INCIDENT_TYPE</code> is a field in a field group:
<p class="code"><nowiki>
<p class="code"><nowiki>*** M204.0542: EDIT COMPLETE - GO
*** M204.0542: EDIT COMPLETE - GO
*** 1 M204.2845: INVALID FIELDGROUP CONTEXT FOR
*** 1 M204.2845: INVALID FIELDGROUP CONTEXT FOR
INCIDENT_TYPE
INCIDENT_TYPE
Line 493: Line 462:
></nowiki>
></nowiki>
</p>
</p>
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:
The previous example is <em>invalid</em>, because the field group context for the <var>Sort</var> 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:
<ul>
<ul>
<li>In an outer level field group, which is true in the previous example</li>
<li>In an outer level field group, which is true in the previous example</li>
<li>Defined as EXACTLY-ONE or AT-MOST-ONE, which is not true in the previous example</li>
 
<li>Defined as <var>EXACTLY-ONE</var> or <var>AT-MOST-ONE</var>, which is not true in the previous example</li>
</ul>
</ul>
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.
Without these restrictions it is ambiguous as to which occurrence a field group field reference is referring. In the previous case, <code>OTHER_DRIVER</code> is in field group <code>DRIVER</code>, but is defined as <code>REPEATABLE</code>. The <var>REPEATABLE</var> attribute makes <code>OTHER_DRIVER</code> ineligible to be a sort key.
<p class="code">
<p class="code">BEGIN
BEGIN
FD: IN POLICIES FD POLICY_NUMBER = 100095 OR 100096
FD: IN POLICIES FD POLICY_NUMBER = 100095 OR 100096
END FIND
END FIND
Line 511: Line 480:


<p>Output:</p>
<p>Output:</p>
<p class="code">
<p class="output">MODELS: ENVOY TACOMA
MODELS: ENVOY TACOMA
MAKES: GMC TOYOTA
MAKES: GMC TOYOTA
MODELS: NEW BEETLE ECLIPSE SUBURBAN
MODELS: NEW BEETLE ECLIPSE SUBURBAN
Line 519: Line 487:


===Usage notes for SORT processing and field groups===
===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.
The <var>Each</var> 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.
<p class="code"><nowiki>
<p class="code"><nowiki>BEGIN
BEGIN
FD: IN POLICIES FD POLICY_NUMBER = 100095 OR 100096
FD: IN POLICIES FD POLICY_NUMBER = 100095 OR 100096
END FIND
END FIND
SR: SORT RECORDS IN FD BY EACH MODEL
SR: SORT RECORDS IN FD BY EACH MODEL
FR SR
FR SR
PRINT ’MODELS:’ AND EACH MODEL
  PRINT ’MODELS:’ AND EACH MODEL
PRINT ’MAKES:’ AND EACH MAKE
  PRINT ’MAKES:’ AND EACH MAKE
END FOR
END FOR
END
END
*** 1 M204.2845: INVALID FIELDGROUP CONTEXT FOR MODEL
*** 1 M204.2845: INVALID FIELDGROUP CONTEXT FOR MODEL
SR: SORT RECORDS IN FD BY EACH MODEL
SR: SORT RECORDS IN FD BY EACH MODEL
Line 535: Line 503:
*** M204.1042: COMPILATION ERRORS
*** M204.1042: COMPILATION ERRORS
</nowiki></p>
</nowiki></p>
An unsubscripted reference to a field in a field group in a SORT statement is allowed.
<p>
An unsubscripted reference to a field in a field group in a <var>Sort</var> statement is allowed. </p>


==The prefetch (look-ahead read) feature==
==The prefetch (look-ahead read) feature==
<p>
<p>
The prefetch feature improves performance of <var class="product">Model&nbsp;204</var> 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. </p>
The prefetch feature improves performance of <var class="product">Model&nbsp;204</var> <var>For Record Number</var> order retrieval of a record set, particularly in a batch environment. Prefetch is for SOUL applications only, and it applies only to Table B. It is not supported for Host Language Interface applications. </p>
<p>
<p>
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. </p>
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 <var>For Each Record</var> sequential record retrieval mode. Look-ahead read is suppressed if the <var>FR</var> statement contains an <var>In Order</var> clause, or if it references a sorted set. </p>
   
   
===Performance considerations===
===Performance considerations===
Line 547: Line 516:
<li>You can use the prefetch feature during large, multiuser <var class="product">Model&nbsp;204</var> runs, but it is most suitable in a batch environment.</li>
<li>You can use the prefetch feature during large, multiuser <var class="product">Model&nbsp;204</var> runs, but it is most suitable in a batch environment.</li>


<li>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).</li>
<li>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, <var>Find</var> record conflicts, file level locking, buffer constraints, and so on).</li>


<li>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 [[#perfgains|Performance gains using the prefetch feature]]). A variety of performance tests have resulted in performance gains in the range of 10-40%.
<li>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 [[#perfgains|Performance gains using the prefetch feature]]). A variety of performance tests have resulted in performance gains in the range of 10-40%.
Line 569: Line 538:
The prefetch feature can be enabled only by users with system manager privileges.</p>
The prefetch feature can be enabled only by users with system manager privileges.</p>
<p>
<p>
Before you enable the prefetch feature, the MAXBUF parameter must be set, based on this formula:  </p>
Before you enable the prefetch feature, ensure sufficient above the bar or below the bar storage by setting a large enough value for the <var>[[NUMBUFG parameter|NUMBUFG]]</var> or the <var>[[MAXBUF parameter|MAXBUF]]</var> parameter, respectively. </p>
<p class="code">MAXBUF = NUSERS * (4 + 2 * (Maximum FOR EACH RECORD loop nest level))
</p>
<p>
<p>
To enable the prefetch feature, the system parameter SEQOPT must be set to 1 (the default is 0).  </p>
To enable the prefetch feature, the system parameter <var>[[SEQOPT parameter|SEQOPT]]</var> must be set to 1 (the default is 0).  </p>


==Processing a limited number of records==
==Processing a limited number of records==
Line 587: Line 554:
<li><var class="term">k</var> is an integer value of the sample size.</li>
<li><var class="term">k</var> is an integer value of the sample size.</li>


<li><var class="term">options</var> is any of the options that can be specified on a <var>FOR EACH RECORD</var> statement. </li>
<li><var class="term">options</var> is any of the options that can be specified on a <var>For Each Record</var> statement. </li>
</ul>
</ul>
   
   
Line 609: Line 576:
===Processing a single record===
===Processing a single record===
<p>
<p>
As discussed in [[Record retrievals|"Record retrievals"]], the POINT$ condition can be used to retrieve a range of record numbers. </p>
As discussed in [[Record retrievals]], the <var>Point$</var> condition can be used to retrieve a range of record numbers. </p>
<p>
<p>
If single records must be retrieved by record number, a more efficient method of retrieval is with the FOR RECORD NUMBER statement.</p>
If single records must be retrieved by record number, a more efficient method of retrieval is with the <var>For Record Number</var> statement.</p>
   
   
===FOR RECORD NUMBER processing===
===For Record Number processing===
<p>
<p>
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. </p>
The <var>For Record Number</var> statement retrieves the specified record and initiates a record loop. The record loop functions in a manner identical to a loop initiated by a <var>For Each Record</var> statement. </p>
<p>
<p>
The FOR RECORD NUMBER statement is supported in remote file and scattered group contexts.</p>
The <var>For Record Number</var> statement is supported in remote file and scattered group contexts.</p>
<b>Syntax</b>
<b>Syntax</b>
<p>
<p>
The format of the FOR RECORD NUMBER statement is:</p>
The format of the <var>For Record Number</var> statement is:</p>
<p class="syntax">FOR RECORD NUMBER <span class="squareb">{</span><span class="term">value</span> <span class="squareb">|</span> IN <span class="term">label</span><span class="squareb">}</span> <span class="squareb">[</span>OPTIMIZING FNV<span class="squareb">]</span>
<p class="syntax">For Record Number <span class="squareb">{</span><span class="term">value</span> <span class="squareb">|</span> IN <span class="term">label</span><span class="squareb">}</span> <span class="squareb">[</span>OPTIMIZING FNV<span class="squareb">]</span>
</p>
</p>
<b>Where</b>
<b>Where</b>
<ul>
<ul>
<li><var class="term">value</var> 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.
<li><var class="term">value</var> specifies the number of the record to be processed. The value can be a literal number or string, a %variable, or a <var>Value In</var> phrase. (The <var>Value In</var> 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 <var>For Record Number</var> statement.


<p class="note"><b>Note:</b>
<p class="note"><b>Note:</b>
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.   </p>
If this form of the <var>For Record Number</var> statement is used in group context, it must be preceded by an <var>In</var> clause that refers to a file. [[Files, groups, and reference context]] discusses the <var>In</var> clause in detail. </p>


<li>The IN label clause specifies the label of a previous FOR EACH RECORD or STORE RECORD statement. (Refer to [[Data maintenance#STORE RECORD statement|STORE RECORD statement]] for more information.)
<li>The <var>In <i>label</i></var> clause specifies the label of a previous <var>For Each Record</var> or <var>Store Record</var> statement. (Refer to [[Data maintenance#STORE RECORD statement|Store Record statement]] for more information.)


<p class="note"><b>Note:</b>
<p class="note"><b>Note:</b>
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. </p></li>
This form of the <var>For Record Number</var> statement automatically assumes the file or group context of the statement to which the label refers. Therefore, it cannot be preceded by an <var>In</var> clause. </p></li>


<li>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.  
<li>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 <var>For Record Number</var> statement, OPTIMIZING FNV must be specified at the end of the statement.  
<p>
<p>
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|Field name variable optimization]] for a more detailed discussion.</p>
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|Field name variable optimization]] for a more detailed discussion.</p>
Line 643: Line 610:
====Example====
====Example====
<p>
<p>
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:</p>
In the following example, the <var>In <i>label</i></var> form of the <var>For Record Number</var> statement is used to create one line of output from data obtained from two different found sets in different files:</p>
<p class="code">BEGIN
<p class="code">BEGIN
FIND1: IN MASTER FIND ALL RECORDS
FIND1: IN MASTER FIND ALL RECORDS
Line 661: Line 628:
END
END
</p>
</p>
 
===Returning the record number of the current record===
===Returning the record number of the current record===
<p>
<p>
The <var>[[$CURREC]]</var> function can be used to return the record number of the record being processed. </p>
The <var>[[$Currec]]</var> function can be used to return the record number of the record being processed. </p>
   
   
===Using an expression with FOR RECORD NUMBER===
===Using an expression with For Record Number===
<p>
<p>
The FOR RECORD NUMBER statement (abbreviation: FRN) accepts an expression to supply its value.</p>
The <var>For Record Number</var> statement (abbreviation: <var>FRN</var>) accepts an expression to supply its value.</p>
<b>Syntax</b>
<b>Syntax</b>
<p class="syntax">FRN <span class="term">expression</span>
<p class="syntax">FRN <span class="term">expression</span>
Line 678: Line 645:
<b>Where</b>
<b>Where</b>
<p>
<p>
<var class="term">expression</var> is one of the following types: function call, string concatenation, arithmetic operation, User Language construct, or Boolean expression.</p>
<var class="term">expression</var> is one of the following types: function call, string concatenation, arithmetic operation, SOUL construct, or Boolean expression.</p>
   
   
===Example===
===Example===
Line 691: Line 658:
==Skip processing==
==Skip processing==
<p>
<p>
You can direct <var class="product">Model&nbsp;204</var> 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. </p>
You can direct <var class="product">Model&nbsp;204</var> to process a user-specified fraction of a record set (for example, every third record) with the <var>For Each Record</var> statement. This is called skip processing, and the skipping can be forward (from the beginning of the record set to the end) or backward. </p>
<p>
<p>
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|Descending record number processing]]), within each Ordered Index value. </p>
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|Descending record number processing]]), within each Ordered Index value. </p>
Line 697: Line 664:
===Forward or backward skip processing===
===Forward or backward skip processing===
<p>
<p>
Skip processing is invoked for a <var>FOR EACH RECORD</var> or <var>FOR EACH VALUE</var> statement by including a <var>BY</var> clause with the following syntax:</p>
Skip processing is invoked for a <var>For Each Record</var> or <var>For Each Value</var> statement by including a <var>By</var> clause with the following syntax:</p>
<p class="syntax"><span class="literal">BY</span> <span class="squareb">{</span><span class="term">%variable</span> <span class="squareb">|</span> <span class="term">literal</span><span class="squareb"><span class="squareb">}</span></span>
<p class="syntax"><span class="literal">By</span> <span class="squareb">{</span><span class="term">%variable</span> <span class="squareb">|</span> <span class="term">literal</span><span class="squareb"><span class="squareb">}</span></span>
</p>
</p>
<b>Where</b>
Where:
<ul>
<ul>
<li><var class="term">%variable</var> or <var class="term">literal</var> indicates how many records to skip before retrieving the record to process in the next iteration of the <var>FOR</var> loop. If set to <var class="term">n</var>, every <var class="term">n</var>th record is processed (and <var class="term">n</var>-1 records are skipped in between). The value of <var class="term">%variable</var> or <var class="term">literal</var> must be an integer between and including positive or negative 2,147,483,647. A setting of zero is not allowed. </li>
<li><var class="term">%variable</var> or <var class="term">literal</var> indicates how many records to skip before retrieving the record to process in the next iteration of the <var>FOR</var> loop. If set to <var class="term">n</var>, every <var class="term">n</var>th record is processed (and <var class="term">n</var>-1 records are skipped in between). The value of <var class="term">%variable</var> or <var class="term">literal</var> must be an integer between and including positive or negative 2,147,483,647. A setting of zero is not allowed. </li>
Line 710: Line 677:
<li>You can use backward processing with Ordered Index record sets only if one of the following is true:
<li>You can use backward processing with Ordered Index record sets only if one of the following is true:
<ul>
<ul>
<li><var>EACH</var> is specified in the <var>FOR</var> statement.</li>
<li><var>Each</var> is specified in the <var>For</var> statement.</li>
<li>The field in question is defined with <var>OCCURS 1</var>.</li>
<li>The field in question is defined with <var>OCCURS 1</var>.</li>
<li>The field in question is defined with <var>AT-MOST-ONE</var>.</li>
<li>The field in question is defined with <var>AT-MOST-ONE</var>.</li>
Line 718: Line 685:
===Specifying ascending or descending order with ORDERED fields===
===Specifying ascending or descending order with ORDERED fields===
<p>
<p>
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.</p>
If you are processing <var>ORDERED</var> fields, use the following <var>For Each Record</var> syntax to get skip processing in ascending or descending order. The placement of the skip processing BY clause in the <var>For Each Value</var> statement syntax is similar.</p>
<p class="syntax">FOR EACH RECORD <span class="squareb">[</span>IN <span class="term">label</span> <span class="squareb">|</span> ON <span class="term">list</span><span class="squareb">]</span>
<p class="syntax">For Each Record <span class="squareb">[</span>In <span class="term">label</span> <span class="squareb">|</span> On <span class="term">list</span><span class="squareb">]</span>
  IN <span class="squareb">[</span>ASCENDING <span class="squareb">|</span> DESCENDING<span class="squareb">]</span> ORDER BY <span class="term">fieldname</span>
  In <span class="squareb">[</span>Ascending <span class="squareb">|</span> Descending<span class="squareb">]</span> Order By <span class="term">fieldname</span>
  <span class="squareb">[</span>FROM <span class="term">value1</span><span class="squareb">]</span> <span class="squareb">[</span>TO <span class="term">value2</span><span class="squareb">]</span> <span class="squareb">[</span>BY <span class="squareb">{</span><span class="term">%variable</span> <span class="squareb">|</span> <span class="term">literal</span><span class="squareb">}</span><span class="squareb">]</span>
  <span class="squareb">[</span>From <span class="term">value1</span><span class="squareb">]</span> <span class="squareb">[</span>To <span class="term">value2</span><span class="squareb">]</span> <span class="squareb">[</span>By <span class="squareb">{</span><span class="term">%variable</span> <span class="squareb">|</span> <span class="term">literal</span><span class="squareb">}</span><span class="squareb">]</span>
  <span class="squareb">[</span>WHERE <span class="term">fieldname</span> IS LIKE <span class="term">pattern</span><span class="squareb">]</span>
  <span class="squareb">[</span>Where <span class="term">fieldname</span> Is Like <span class="term">pattern</span><span class="squareb">]</span>
</p>
</p>
<p>
<p>
Use the following syntax for unordered and sorted record sets:</p>
Use the following syntax for unordered and sorted record sets:</p>
<p class="syntax">FOR EACH <span class="squareb">[</span>RECORD <span class="squareb">|</span> VALUE<span class="squareb">]</span> IN <span class="term">foundset</span>
<p class="syntax">For Each <span class="squareb">[</span>Record <span class="squareb">|</span> Value<span class="squareb">]</span> In <span class="term">foundset</span>
         BY <span class="squareb">{</span><span class="term">%variable</span> <span class="squareb">|</span> <span class="term">literal</span><span class="squareb">}</span>
         By <span class="squareb">{</span><span class="term">%variable</span> <span class="squareb">|</span> <span class="term">literal</span><span class="squareb">}</span>
</p>
</p>
<p>
<p>
Use the following syntax for record sets from sorted files:</p>
Use the following syntax for record sets from sorted files:</p>
<p class="syntax">FOR EACH RECORD <span class="squareb">[</span>IN <span class="term">label</span> <span class="squareb">|</span> ON <span class="term">list</span><span class="squareb">]</span> IN SORTKEY ORDER
<p class="syntax">For Each Record <span class="squareb">[</span>In <span class="term">label</span> <span class="squareb">|</span> On <span class="term">list</span><span class="squareb">]</span> In Sortkey Order
  BY <span class="term">fieldname</span> BY <span class="squareb">{</span><span class="term">%variable</span> <span class="squareb">|</span> <span class="term">literal</span><span class="squareb">}</span>
  By <span class="term">fieldname</span> By <span class="squareb">{</span><span class="term">%variable</span> <span class="squareb">|</span> <span class="term">literal</span><span class="squareb">}</span>
</p>
</p>
<b>Where</b>
Where:
<ul>
<ul>
<li><var>SORTKEY</var> invokes skip processing in order according to the values of the sort key field. The sort key field is <var class="term">fieldname</var> in the first <var>BY</var> clause.</li>
<li><var>Sortkey</var> invokes skip processing in order according to the values of the sort key field. The sort key field is <var class="term">fieldname</var> in the first <var>BY</var> clause.</li>


<li>If you do not specify <var>SORTKEY</var>, and <var class="term">fieldname</var> in the first <var>BY</var> clause is both a sort key and an <var>ORDERED</var> field, <var class="product">Model&nbsp;204</var> uses skip processing for the Ordered Index ordering of the <var class="term">fieldname</var> values.</li>
<li>If you do not specify <var>Sortkey</var>, and <var class="term">fieldname</var> in the first <var>By</var> clause is both a sort key and an <var>ORDERED</var> field, <var class="product">Model&nbsp;204</var> uses skip processing for the Ordered Index ordering of the <var class="term">fieldname</var> values.</li>
</ul>
</ul>


Line 798: Line 765:
===Descending record number processing===
===Descending record number processing===
<p>
<p>
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. <i>This extension is only valuable for processing record sets in which a field has duplicate data values.</i> </p>
You can process Ordered Index record sets in descending record number order per field value. The <var>For Each Record</var> statement skip processing option is extended to permit descending record number ordering. Previously, <var>In Order By</var> processing was always in ascending record number order. <i>This extension is only valuable for processing record sets in which a field has duplicate data values.</i> </p>
<p>
<p>
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. </p>
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. </p>


<p class="syntax">FOR EACH RECORD <span class="squareb">[</span>IN <span class="term">label</span> <span class="squareb">|</span> ON <span class="term">list</span><span class="squareb">]</span>
<p class="syntax">For Each Record <span class="squareb">[</span>In <span class="term">label</span> <span class="squareb">|</span> On <span class="term">list</span><span class="squareb">]</span>
  IN [ASCENDING <span class="squareb">|</span> DESCENDING] ORDER BY <span class="term">fieldname</span> [FROM <span class="term">value1</span>] [TO <span class="term">value2</span>]
  In [Ascending <span class="squareb">|</span> Descending] Order By <span class="term">fieldname</span> [From <span class="term">value1</span>] [To <span class="term">value2</span>]
  [BY [<b><u>ASCENDING RECORD</u></b> <span class="squareb">|</span> <b>DESCENDING RECORD</b>] {<span class="term">%variable</span> <span class="squareb">|</span> <span class="term">literal</span>}]
  [By [<b><u>Ascending Record</u></b> <span class="squareb">|</span> <b>Descending Record</b>] {<span class="term">%variable</span> <span class="squareb">|</span> <span class="term">literal</span>}]
  [WHERE <span class="term">fieldname</span> IS LIKE <span class="term">pattern</span>]
  [Where <span class="term">fieldname</span> Is Like <span class="term">pattern</span>]
</p>
</p>
<b>Where</b>
Where:
<ul>
<ul>
<li><var>ASCENDING RECORD</var> or <var>DESCENDING RECORD</var> indicates the records are to be processed in ascending or descending record number within <var class="term">fieldname</var> value. If neither <var>ASCENDING</var> nor <var>DESCENDING</var> is specified, the default is <var>ASCENDING RECORD</var>.</li>
<li><var>Ascending Record</var> or <var>Descending Record</var> indicates the records are to be processed in ascending or descending record number within <var class="term">fieldname</var> value. If neither <var>Ascending</var> nor <var>Descending</var> is specified, the default is <var>Ascending Record</var>.</li>


<li>If specified with the <var>IN ORDER BY</var> clause, a <var class="term">%variable</var> or <var class="term">literal</var> value is required when the skip processing <var>BY</var> is specified. </li>
<li>If specified with the <var>In Order By</var> clause, a <var class="term">%variable</var> or <var class="term">literal</var> value is required when the skip processing <var>By</var> is specified. </li>
</ul>
</ul>


===Examples===
===Examples===
<p>
<p>
In [[#Example 1|Example 1]], the record set below is processed using the default behavior of the <var>FOR EACH RECORD</var> statement. In [[#Example 2|Example 2]], it is processed using a <var>FOR EACH RECORD</var> statement with record number ordering. </p>
In [[#Example 1|Example 1]], the record set below is processed using the default behavior of the <var>For Each Record</var> statement. In [[#Example 2|Example 2]], it is processed using a <var>For Each Record</var> statement with record number ordering. </p>
<table>
<table>
<tr class="head">
<tr class="head">
Line 822: Line 789:
<th>Value of field ORDCHAR</th>
<th>Value of field ORDCHAR</th>
</tr>
</tr>
<tr>
<tr>
<td>01</td>
<td>01</td>
<td>A</td>
<td>A</td>
</tr>
</tr>
<tr>
<tr>
<td>02</td>
<td>02</td>
<td>A</td>
<td>A</td>
</tr>
</tr>
<tr>
<tr>
<td>03</td>
<td>03</td>
<td>B</td>
<td>B</td>
</tr>
</tr>
<tr>
<tr>
<td>04</td>
<td>04</td>
<td>B</td>
<td>B</td>
</tr>
</tr>
<tr>
<tr>
<td>05</td>
<td>05</td>
<td>C</td>
<td>C</td>
</tr>
</tr>
<tr>
<tr>
<td>06</td>
<td>06</td>
<td>C</td>
<td>C</td>
</tr>
</tr>
<tr>
<tr>
<td>07</td>
<td>07</td>
<td>A</td>
<td>A</td>
</tr>
</tr>
<tr>
<tr>
<td>08</td>
<td>08</td>
<td>A</td>
<td>A</td>
</tr>
</tr>
<tr>
<tr>
<td>09</td>
<td>09</td>
<td>B</td>
<td>B</td>
</tr>
</tr>
<tr>
<tr>
<td>10</td>
<td>10</td>
<td>B</td>
<td>B</td>
</tr>
</tr>
<tr>
<tr>
<td>11</td>
<td>11</td>
<td>C</td>
<td>C</td>
</tr>
</tr>
<tr>
<tr>
<td>12</td>
<td>12</td>
Line 873: Line 852:


====Example 1====
====Example 1====
Prior to skip processing and record number ordering, the <var>FOR EACH RECORD</var> statement ordering by field name was always in ascending record number order, as the output from the following statements show:
Prior to skip processing and record number ordering, the <var>For Each Record</var> statement ordering by field name was always in ascending record number order, as the output from the following statements show:


<p class="code">PRINT 'ORDCHAR' AND 'RECNO'
<p class="code">PRINT 'ORDCHAR' AND 'RECNO'
Line 882: Line 861:
<p>
<p>
These statements print ascending record numbers within the descending ORDCHAR values: </p>
These statements print ascending record numbers within the descending ORDCHAR values: </p>
<p class="code">ORDCHAR RECNO
<p class="output">ORDCHAR RECNO
C      05
C      05
C      06
C      06
Line 898: Line 877:


====Example 2====
====Example 2====
In this example, the <var>FOR EACH RECORD</var> statement specifies record number ordering:
In this example, the <var>For Each Record</var> statement specifies record number ordering:
<p class="code">PRINT 'ORDCHAR' AND 'RECNO'
<p class="code">PRINT 'ORDCHAR' AND 'RECNO'
FR IN label IN DESCENDING ORDER BY ORDCHAR -
FR IN label IN DESCENDING ORDER BY ORDCHAR -
Line 906: Line 885:
</p>
</p>
<p>
<p>
These statements print descending record numbers within the descending <var>ORDCHAR</var> values:</p>
These statements print descending record numbers within the descending <code>ORDCHAR</code> values:</p>
<p class="code">ORDCHAR RECNO
<p class="output">ORDCHAR RECNO
C      12
C      12
C      11
C      11
Line 922: Line 901:
</p>
</p>


<div id="noteStmt"></div>
==<b id="noteStmt"></b>Note statement==
 
==Note statement==
<p>
<p>
As described in [[Introduction to User Language#Fields|Fields]], <var class="product">Model&nbsp;204</var> retrieves sets of records by means of one or more fields contained in each record. </p>
As described in [[Introduction to User Language#Fields|Fields]], <var class="product">Model&nbsp;204</var> retrieves sets of records by means of one or more fields contained in each record. </p>
<p>
<p>
By using the <var>Note</var> statement, you can cause <var class="product">Model&nbsp;204</var> to temporarily store the value of any field in the retrieved set, permitting its use in subsequent retrievals within the same request.</p>
By using the <var>Note</var> statement, you can cause <var class="product">Model&nbsp;204</var> to temporarily store the value of any field in the retrieved set, permitting its use in subsequent retrievals within the same request.</p>
<p class="note"><b>Note:</b> The <var>Note</var> statement has been deprecated in favor of using [[#Alternative: use assignment statements|assignment statements]] for subsequent retrievals and cross referencing.</p>


<b>Syntax</b>
<b>Syntax</b>
Line 938: Line 917:
===Note processing===
===Note processing===
<p>
<p>
A <var>Note <i>fieldname</i></var> statement can be used only within a <var>FOR EACH RECORD</var> or <var>FOR RECORD NUMBER</var> loop. </p>
A <var>Note <i>fieldname</i></var> statement can be used only within a <var>For Each Record</var> or <var>For Record Number</var> loop. </p>
<p>
<p>
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 <var>Find</var> statement by using the <var>Value In</var> phrase to refer to the noted value.  </p>
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 <var>Find</var> statement by using the <var>Value In</var> phrase to refer to the noted value.  </p>
Line 945: Line 924:
<p>
<p>
The following request finds all current and potential policyholders in cities covered by the agent <code>GOODRICH</code>:</p>
The following request finds all current and potential policyholders in cities covered by the agent <code>GOODRICH</code>:</p>
<p class="code">BEGIN
<p class="code">begin
GET.AGENT:    FIND ALL RECORDS FOR WHICH
get.agent:    find all records for which
                   AGENT = GOODRICH
                   agent = 'GOODRICH'
               END FIND
               end find
PROCESS:      FOR EACH RECORD IN GET.AGENT
process:      for each record in get.agent
CITY.NAME:        NOTE CITY
city.name:        note city
CITY.VAL:          FIND ALL RECORDS FOR WHICH
city.val:          find all records for which
                       CITY = VALUE IN CITY.NAME
                       city = value in city.name
                   END FIND
                   end find
PRINT.INFO:        FOR EACH RECORD IN CITY.VAL
print.info:        for each record in city.val
                       PRINT CITY WITH AGENT AT COLUMN 20 -
                       print city with agent at column 20 -
                           WITH POLICY NO AT COLUMN 40
                           with policy no at column 40
                   END FOR
                   end for
               END FOR
               end for
TOT.GOODRICHCOUNT RECORDS IN GET.AGENT
tot.goodrichcount records in get.agent
               PRINT COUNT IN TOT.GOODRICH
               print count in tot.goodrich
END
end
</p>
</p>
<p>
<p>
In this example, the <code>PROCESS</code> label begins a loop. Cross-referencing (see the next section) is repeated until all records with <code>AGENT = GOODRICH</code> have been processed. </p>
In this example, the <code>process</code> label begins a loop. Cross-referencing (see the next section) is repeated until all records with <code>agent = 'GOODRICH'</code> have been processed. </p>
<p>
<p>
The <code>CITY</code> label notes the city of each record for agent <code>GOODRICH</code>. The <code>CITY.VAL</code> label retrieves all records in the file, regardless of agent, that have a value equal to the noted city. The <code>PRINT.INFO</code> statement prints the city, agent, and policy number, and then <var class="product">Model&nbsp;204</var> loops back to the <code>PROCESS</code> label and notes the city on the next <code>AGENT = GOODRICH</code> record, and so on. When all <code>AGENT = GOODRICH</code> records have been exhausted, execution continues with the <code>TOT.GOODRICH</code> label.</p>
The <code>city</code> label notes the city of each record for agent <code>GOODRICH</code>. The <code>city.val</code> label retrieves all records in the file, regardless of agent, that have a value equal to the noted city. The <code>print.info</code> statement prints the city, agent, and policy number, and then <var class="product">Model&nbsp;204</var> loops back to the <code>process</code> label and notes the city on the next <code>agent = 'GOODRICH'</code> record, and so on. When all <code>agent = 'GOODRICH'</code> records have been exhausted, execution continues with the <code>tot.goodrich</code> label.</p>
 
===Alternative: Use assignment statements===
===Alternative: Use assignment statements===
<p>
<p>
Line 977: Line 956:
</ul>
</ul>
<p>
<p>
In the preceding example, an assignment statement could be used by replacing <code>NOTE OWNER.POLICY</code> with <code>%OWNER.POLICY = OWNER POLICY</code>, and by replacing <code>POLICY NO = VALUE IN POLICY</code> with <code>POLICY NO = %OWNER.POLICY</code>.   </p>
In the preceding example, an assignment statement could be used by replacing <code>note city</code> with <code>%city = city</code>, as in the following example:</p>
<p class="code">begin
%city is string len 255
get.agent:    find all records for which
                  agent = 'GOODRICH'
              end find
process:      for each record in get.agent
                  %city = city
city.val:          find all records for which
                      city = %city
                  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</p>
 
===Noting character strings===
===Noting character strings===
<p>
<p>
The following form of the <var>Note</var> statement can be used to set a value for later reference by the <var>Value In</var> phrase: </p>
The following form of the <var>Note</var> statement can be used to set a value for later reference by the <var>Value In</var> phrase: </p>
<p class="code">label: NOTE 'string'
<p class="code">label: note 'string'
</p>
</p>
<p>
<p>
For example:</p>
For example:</p>
<p class="code">BEGIN
<p class="code">Begin
NAME:      NOTE 'ABBOTT, FRANKLIN G'
name:      note 'ABBOTT, FRANKLIN G'
FIND.RECS: FIND ALL RECORDS FOR WHICH
find.recs: find all records for which
               FULLNAME = VALUE IN NAME
               fullname = value in name
           END FIND
           end find
CT:        COUNT RECORDS IN FIND.RECS
ct:        count records in find.recs
           PRINT COUNT IN CT -
           print count in ct -
               WITH ' RECORDS FOR ' -
               with ' records for ' -
               WITH VALUE IN NAME
               with value in name
END
end
</p>
</p>
<p>
<p>
results in the output:</p>
results in the output:</p>
<p class="output">2 RECORDS FOR ABBOTT, FRANKLIN G
<p class="output">2 records for ABBOTT, FRANKLIN G
</p>
</p>
<p>
<p>
Line 1,012: Line 1,009:
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: </p>
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: </p>
<p class="code">BEGIN
<p class="code">BEGIN
%OWNER_POLICY IS STRING LEN 6
SAABS:      IN VEHICLES FIND ALL RECORDS FOR WHICH
SAABS:      IN VEHICLES FIND ALL RECORDS FOR WHICH
                 MAKE = SAAB
                 MAKE = SAAB
             END FIND
             END FIND
             FOR EACH RECORD IN SAABS
             FOR EACH RECORD IN SAABS
POLICY:          NOTE OWNER POLICY
                %OWNER_POLICY = OWNER POLICY
POLICY.VAL:      IN CLIENTS FIND ALL RECORDS FOR WHICH
POLICY.VAL:      IN CLIENTS FIND ALL RECORDS FOR WHICH
                     POLICY NO = VALUE IN POLICY
                     POLICY NO = %OWNER_POLICY
                     RECTYPE = POLICYHOLDER
                     RECTYPE = POLICYHOLDER
                 END FIND
                 END FIND
Line 1,028: Line 1,026:
END
END
</p>
</p>
 
===Moving constant retrieval outside FOR loop===
===Moving constant retrieval outside FOR loop===
<p>
<p>
If the first statement retrieves 500 records with MAKE = SAAB, the FIND statement in POLICY.VAL is executed 500 times. </p>
If the first statement retrieves 500 records with <code>MAKE=SAAB</code>, the <var>Find</var> statement in <code>POLICY.VAL</code> is executed 500 times. </p>
<p>
<p>
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. </p>
The cost of a <var>Find</var> is directly related to the number of retrieval conditions specified. In Example 1, the retrieval condition <code>RECTYPE=POLICYHOLDER</code> is the same for each repetition of <code>POLICY.VAL</code>. </p>
<p>
<p>
Greater efficiency is gained by moving constant retrieval conditions outside the FOR loop. For example:</p>
Greater efficiency is gained by moving constant retrieval conditions outside the FOR loop. For example:</p>
Line 1,060: Line 1,058:
The preceding technique might not be appropriate for an application in which many users retrieve data from and update the same file concurrently. </p>
The preceding technique might not be appropriate for an application in which many users retrieve data from and update the same file concurrently. </p>
<p>
<p>
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 level locking and concurrency control#Record locking and release statements|Record locking and release statements]] for more discussion.</p>
The <code>POLICYHOLDER</code> records in the second request are locked out from updates for the duration of the request. In the first request, the <code>POLICYHOLDER</code> records would be accessible for updating between repetitions of the outer loop. Refer to [[Record level locking and concurrency control#Record locking and release statements|Record locking and release statements]] for more discussion.</p>
   
   
===Nested loops===
===Nested loops===
Line 1,109: Line 1,107:
===Issues===
===Issues===
<p>
<p>
Records retrieved by a FOR EACH RECORD statement cannot be counted using the COUNT RECORDS statement. </p>
Records retrieved by a <var>For Each Record</var> statement cannot be counted using the <var>Count Records</var> statement. </p>
<p>
<p>
In addition, counting records retrieved by a FIND statement and counted within a record loop can produce unexpected results. </p>
In addition, counting records retrieved by a <var>Find</var> statement and counted within a record loop can produce unexpected results. </p>
   
   
===Example===
===Example===
<p>
<p>
Consider the problem of trying to count the total number of records retrieved by the FIND.RECS statement below:      </p>
Consider the problem of trying to count the total number of records retrieved by the FIND.RECS statement below:      </p>
<p class="code">GET.AGENT: FIND ALL RECORDS FOR WHICH
<p class="code">%zip is string len 5
               AGENT = CASOLA
get.agent: find all records for which
           END FIND
               agent = 'CASOLA'
           FOR EACH RECORD IN GET.AGENT
           end find
KEEP.ZIP:      NOTE ZIP
           for each record in get.agent
FIND.RECS:    FIND ALL RECORDS FOR WHICH
              %zip = zip
                   ZIP = VALUE IN KEEP.ZIP
find.recs:    find all records for which
               END FIND
                   zip = %zip
               end find
</p>
</p>
<p>
<p>
Line 1,130: Line 1,129:
               .
               .
               .
               .
CT.RECS:      COUNT RECORDS IN FIND.RECS
ct.recs:      count records in find.recs
               PRINT COUNT IN CT.RECS
               print count in ct.recs
           END FOR
           end for
</p>
</p>
<p>
<p>
Line 1,146: Line 1,145:
               .
               .
               .
               .
CT.RECS:      COUNT RECORDS IN FIND.RECS
ct.recs:      count records in find.recs
           END FOR
           end for
PRINT.CTPRINT COUNT IN CT.RECS
print.ctprint count in ct.recs
</p>
</p>
<p>
<p>
Line 1,154: Line 1,153:
<p class="code">25
<p class="code">25
</p>
</p>
 
===Solution===
===Solution===
   
   
Line 1,161: Line 1,160:
The correct total could be obtained by the following statements (which use the assignment statement discussed in [[Using variables and values in computation#Assigning values to %variables|Assigning values to %variables]]):</p>
The correct total could be obtained by the following statements (which use the assignment statement discussed in [[Using variables and values in computation#Assigning values to %variables|Assigning values to %variables]]):</p>
   
   
<p class="code">               .
<p class="code">               .
                .
                .
   
   
              .
ct.recs:      count records in find.recs
   
   
               .
               %total = %total + count in ct.recs
   
   
CT.RECS:      COUNT RECORDS IN FIND.RECS
          end for
   
   
              %TOTAL = %TOTAL + COUNT IN CT.RECS
print.ctprint %total
          END FOR
PRINT.CTPRINT %TOTAL
</p>
</p>
<p>
<p>
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#Lists|Lists]] describes the statements used to create lists.      </p>
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#Lists|Lists]] describes the statements used to create lists.      </p>
 
==PRINT ALL INFORMATION (or PAI) INTO statement==
==<b id="paiInto"></b>Print All Information (or PAI) Into statement==
<p>
<p>
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.</p>
The <var>Print All Information</var> (<var>PAI</var>) <var>Into</var> 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 <var>PAI Into</var> statement include an effective copy-record capability, and also a convenient way to determine field names and values encountered within a <var>For Each Record</var> loop. </p>
<b>Syntax</b>
<b>Syntax</b>
<p>
<p>
The form of the PAI INTO statement is:</p>
The format of the <var>PAI Into</var> statement is:</p>
<p class="code">PRINT ALL INFORMATION option INTO
<p class="syntax">Print All Information <span class="term">option</span> Into
                       array_1, array_2, [array_3]
                       <span class="term">array_1</span>, <span class="term">array_2</span>, [<span class="term">array_3</span>]
                       [FROM start] [COUNT ct]
                       [From <span class="term">start</span>] [Count <span class="term">ct</span>]
</p>
</p>
<b>Where</b>
Where:
<ul>
<li><var class="term">option</var> can be one of the following, specifying how Large Object fields are handled:
<ul>
<ul>
<li>option can be one of the following and specifies how Large Object fields are handled:
<li><var>Lob_Data</var>
<p>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, <var>$Status</var> is set to 2, <var>$StatusD</var> is set to 3, and the entire record is output in <var>PAI</var> format to the output device. </p>
<p>
<p>
LOB_DATA &mdash; 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.</p>
<var>Lob_Data</var> is the default option for the <var>PAI Into</var> statement. </p></li>
 
<li><var>Lob_Short_Data</var>  
<p>
<p>
The default option for the PAI INTO statement is LOB_DATA.</p>
Copies the first 255 bytes of the LOB data to the field value array element. </p></li>
 
<li><var>Lob_No_Data</var>
<p>
<p>
LOB_SHORT_DATA &mdash; Copies the first 255 bytes of the LOB data to the field value array element.</p>
No LOB data is copied to the field value array. The field name and LOB descriptor are copied to <var class="term">array_1</var> and <var class="term">array_3</var>, respectively. </p></li>
 
<li><var>Lob_None</var>  
<p>
<p>
LOB_NO_DATA &mdash; 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.</p>
No LOB information (field name, LOB data, nor LOB descriptor) is copied to the output arrays. </p></li>
<p>
</ul></li>
LOB_NONE &mdash; No LOB information--field name, LOB data, nor LOB descriptor--is copied to the output arrays.</p>
 
<li>array_1 through array_3 are string %variable or image item arrays. This form of the PAI statement fills:</li>
<li><var class="term">array_1</var> through <var class="term">array_3</var> are string %variable or image item arrays. This form of the <var>PAI</var> statement fills: </li>
<p>
<ul>
array_1 with field names</p>
<li><var class="term">array_1</var> with field names</li>
<p>
 
array_2 with field values</p>
<li><var class="term">array_2</var> with field values</li>
<p>
 
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.</p>
<li><var class="term">array_3</var> 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. </li>
</ul>
<p>
<p>
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|PRINT ALL INFORMATION (or PAI) INTO statement]].</p>
When writing requests using the <var>PAI Into</var> 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 <var>PAI Into</var> 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 below in [[#paiIntoExample|Example]]. </p>
<p>
<p>
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.</p></li>
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 <code>$STATUSD=4</code> return code. </p></li>


<li>start is a scalar %variable. If supplied, start is a relative field pointer indicating where to start extracting field values. The default is 1.</li>
<li><var class="term">start</var> is a scalar %variable. If supplied, start is a relative field pointer indicating where to start extracting field values. The default is 1.</li>


<li>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, <var class="product">Model&nbsp;204</var> uses the %variable as a counter to keep track of the number of fields extracted. </li>
<li><var class="term">ct</var> is a scalar %variable that indicates the number of fields that have been extracted from the record after the <var>PAI Into</var> statement executes. If you specify ct as a %variable, <var class="product">Model&nbsp;204</var> uses the %variable as a counter to keep track of the number of fields extracted. </li>
</ul>
</ul>
   
   
===Usage===
===Usage===
<p>
<p>
The PAI INTO statement writes only to the arrays, not to the terminal.</p>
The <var>PAI Into</var> statement writes only to the arrays, not to the terminal.</p>
   
   
===Error messages===
===Error messages===
<p>
<p>
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. </p>
Error messages are issued if the <var>Into</var> arguments are not string arrays, or if <var class="term">ct</var> is a literal. Since <var>PAI Into</var> can fail for a variety of storage-related reasons, error conditions are stored in <var>$Status</var> and <var>$StatusD</var>. </p>
<p>
<p>
The possible $STATUS values are:</p>
The possible <var>$Status</var> values are: </p>
<table>
<table>
<tr>
<tr><td>0</td>
<td align="right">0</td>
<td><var>PAI Into</var> completed successfully.</td></tr>
<td>PAI INTO completed successfully.</td>
 
</tr>
<tr><td>2</td>
<tr>
<td><var>PAI Into</var> failed, see <var>$StatusD</var> for details. </td></tr>
<td align="right">2</td>
<td>PAI INTO failed, see $STATUSD for details.</td>
</tr>
</table>
</table>
<p>
<p>
$STATUSD values are:</p>
<var>$StatusD</var> values are:</p>
<table>
<table>
<tr>
<tr>
<td align="right">0</td>
<td>0</td>
<td>PAI INTO completed successfully.</td>
<td><var>PAI Into</var> completed successfully.</td>
</tr>
</tr>
<tr><td>1</td>
<td><var>PAI Into</var> 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. </td></tr>
<tr>
<tr>
<td align="right">1</td>
<td>2</td>
<td>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.</td>
<td><var>PAI Into</var> failed because a field name was too large for an array element.</td>
</tr>
</tr>
<tr>
<tr>
<td align="right">2</td>
<td>3</td>
<td>PAI INTO failed because a field name was too large for an array element.</td>
<td><var>PAI Into</var> failed because a field value was too large for an array element.</td>
</tr>
</tr>
<tr>
<tr>
<td align="right">3</td>
<td>4</td>
<td>PAI INTO failed because a field value was too large for an array element.</td>
<td><var>PAI Into</var> failed because a Large Object descriptor was too large for an array element. </td>
</tr>
<tr>
<td align="right">4</td>
<td>PAI INTO failed because a Large Object descriptor was too large for an array element.</td>
</tr>
</tr>
</table>
</table>
<p>
<p>
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. </p>
If <var>$StatusD</var> is nonzero, the value of the <var class="term">ct</var> %variable contains the index value (as in the arrays shown below in [[#paiIntoExample|Example]]) of the last field name and value successfully extracted. </p>
   
   
===Record locking with PAI processing===
===Record locking with PAI processing===
<p>
<p>
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, <var class="product">Model&nbsp;204</var> 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.</p>
If you are processing unlocked records (such as records on lists or in sets produced via <var>Find Without Locks</var>) through a <var>PAI</var> loop in local context, <var class="product">Model&nbsp;204</var> 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.</p>
<p>
<p>
To help catch such conflicts, add an ON RECORD LOCKING CONFLICT unit to User Language procedures using PAI against unlocked records.</p>
To help catch such conflicts, add an <var>On Record Locking Conflict</var> unit to SOUL procedures using <var>PAI</var> against unlocked records. </p>
<p>
<p>
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 <var class="product">Model&nbsp;204</var> works from a local copy of the remote records.</p>
If you are using [[PQO|Parallel Query Option/204]], and you are working with a remote file, no locks are issued against the records in the remote file since <var class="product">Model&nbsp;204</var> works from a local copy of the remote records. </p>
   
   
===Example===
===<b id="paiIntoExample"></b>Example===
<p>
<p>
The following schematic example shows one way to set up a PAI INTO statement to perform a "copy record" function.</p>
The following schematic example shows one way to set up a <var>PAI Into</var> statement to perform a "copy record" function: </p>
<p class="code">BEGIN
<p class="code">BEGIN
DECLARE %FIELDNAM IS STRING LEN 20 ARRAY(100)
DECLARE %FIELDNAM IS STRING LEN 20 ARRAY(100)
Line 1,307: Line 1,313:
===Null field values===
===Null field values===
<p>
<p>
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.           </p>
Notice that the <var>Add</var> statement logic in the previous example includes an <var>Else</var> 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. </p>
   
   
</div> <!-- end of toc limit div -->
</div> <!-- end of toc limit div -->


[[Category:SOUL]]
[[Category:SOUL]]

Latest revision as of 14:16, 29 November 2018

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.

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.

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 | (expression1)}] [To {value2 | (expression2)}] [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 or expression1, and value2 or expression2, specify the beginning and ending of a range of values to retrieve. An expression must be enclosed in parentheses.
  • %variable or literal specify a numerical increment, which must be greater than or equal to 1. This increment minus 1 is the number of records skipped at each loop iteration.
  • retrieval-conditions is a simple or arbitrarily complex combination of filtering phrases. For full details, see Retrieval condition syntax.

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 or On option, it functions as a loop statement only, and it 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, SOUL 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

A Where clause in a For Each Record statement acts as an implied Find statement.

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, SOUL 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

A Where or With clause in a For Each Record statement acts as an implied Find statement. If such a 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

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 that 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 that 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 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 it 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 SOUL applications only, and it 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, ensure sufficient above the bar or below the bar storage by setting a large enough value for the NUMBUFG or the MAXBUF parameter, respectively.

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 that 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, SOUL 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.

Note: The Note statement has been deprecated in favor of using assignment statements for subsequent retrievals and cross referencing.

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:

In the preceding example, an assignment statement could be used by replacing note city with %city = city, as in the following example:

begin %city is string len 255 get.agent: find all records for which agent = 'GOODRICH' end find process: for each record in get.agent %city = city city.val: find all records for which city = %city 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

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 %OWNER_POLICY IS STRING LEN 6 SAABS: IN VEHICLES FIND ALL RECORDS FOR WHICH MAKE = SAAB END FIND FOR EACH RECORD IN SAABS %OWNER_POLICY = OWNER POLICY POLICY.VAL: IN CLIENTS FIND ALL RECORDS FOR WHICH POLICY NO = %OWNER_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:

%zip is string len 5 get.agent: find all records for which agent = 'CASOLA' end find for each record in get.agent %zip = zip find.recs: find all records for which zip = %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 format 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, specifying 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.

      Lob_Data is the default option for the PAI Into statement.

    • 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 below in Example.

    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, the value of the ct %variable contains the index value (as in the arrays shown below in Example) of the last field name and value successfully extracted.

Record locking with PAI processing

If 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 SOUL procedures using PAI against unlocked records.

If you are using 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.