Value loops: Difference between revisions

From m204wiki
Jump to navigation Jump to search
m (Admin moved page Value Loops to Value loops without leaving a redirect)
(/* FROM and TO clause; added inclusive to FROM and exclusive to TO)
 
(41 intermediate revisions by 3 users not shown)
Line 1: Line 1:
===Overview===
<div class="toclimit-3">
<p>This chapter describes value loops, which retrieve, sort, and process sets of field values.</p>
 
<p class="note"><b>Note:</b> Because a value loop is driven by a list of values rather than by records, no records are involved until a FIND or FOR EACH RECORD statement is issued within the loop. A field reference (other than in a FIND statement) cannot be used in a value loop unless it is further embedded in a nested FOR EACH RECORD loop, as illustrated in this chapter.</p>
==Overview==
<p>To review the use of record loops to retrieve, sort, and process sets of records, see [[Record Loops#Record Loops|Record Loops]].</p>
<p>
<p>Repetitions of a value loop occur for all distinct values of an FRV (for-each-value) or ORDERED field, including any values referencing records that are logically deleted. </p>
This topic describes value loops, which retrieve, sort, and process sets of field values.
<p class="note"><b>Note:</b> INVISIBLE fields must be explicitly deleted (see [[Field Attributes#INVISIBLE attribute|INVISIBLE attribute]] for a discussion of INVISIBLE fields). </p>
====FOR EACH VALUE statement====
<p>The FOR EACH VALUE statement initiates a loop that is executed once for each unique value of the specified field. It is supported in remote file and scattered group contexts.</p>
<p>The basic syntax is:</p>
<p class="code">label: FOR EACH VALUE OF fieldname
</p>
</p>
<p>There are several variations of the FOR EACH VALUE statement, as described in this chapter.</p>
<p class="note"><b>Note:</b> Because a value loop is driven by a list of values rather than by records, no records are involved until a <var>FIND</var> or <var>FOR EACH RECORD</var> statement is issued within the loop. A field reference (other than in a <var>FIND</var> statement) cannot be used in a value loop unless it is further embedded in a nested <var>FOR EACH RECORD</var> loop, as illustrated in this topic.</p>
<p>You can use the FOR EACH VALUE statement to:</p>
<p>
To review the use of record loops to retrieve, sort, and process sets of records, see [[Record loops]].</p>
<p>
Repetitions of a value loop occur for all distinct values of an <var>FRV</var> (for-each-value) or <var>ORDERED</var> field, including any values referencing records that are logically deleted. </p>
<p class="note"><b>Note:</b> <var>INVISIBLE</var> fields must be explicitly deleted (see [[Field attributes#INVISIBLE attribute|INVISIBLE attribute]] for a discussion of <var>INVISIBLE</var> fields). </p>
===FOR EACH VALUE statement===
<p>
The <var>FOR EACH VALUE</var> statement initiates a loop that is executed once for each unique value of the specified field. It is supported in remote file and scattered group contexts.</p>
<p>
The basic syntax is:</p>
<p class="syntax"><span class="term">label</span>: FOR EACH VALUE OF <span class="term">fieldname</span>
</p>
<p>
There are several variations of the <var>FOR EACH VALUE</var> statement, as described in this page.</p>
<p>
You can use the <var>FOR EACH VALUE</var> statement to:</p>
<table>
<table>
<tr class="head">
<tr class="head">
Line 17: Line 28:
<th>Using...</th>
<th>Using...</th>
</tr>
</tr>
<tr>
<tr>
<td>Sort the retrieved values </td>
<td>Sort the retrieved values </td>
<td>IN ORDER clause</td>
<td><var>IN ORDER</var> clause</td>
</tr>
</tr>
<tr>
<tr>
<td>Specify a range of values to retrieve </td>
<td>Specify a range of values to retrieve </td>
<td><var>FROM value1</var> and <var>TO value2</var> clauses</td>
<td><var>FROM <i>value1</i></var> and <var>TO <i>value2</i></var> clauses</td>
</tr>
</tr>
<tr>
<tr>
<td>Specify a pattern to use to retrieve values </td>
<td>Specify a pattern to use to retrieve values </td>
<td><var class="term">pattern</var> clause</td>
<td><var class="term">pattern</var> clause</td>
</tr>
</tr>
<tr>
<tr>
<td>Process a sample of values </td>
<td>Process a sample of values </td>
<td>k clause</td>
<td><var class="term">k</var> clause</td>
</tr>
</tr>
<tr>
<tr>
<td>Process a fraction of the set of retrieved values, for example, every third value </td>
<td>Process a fraction of the set of retrieved values, for example, every third value </td>
<td>Skip processing feature. Skip processing concepts are explained beginning on [[Record Loops#Skip processing|Skip processing]].</td>
<td>[[Record loops#Skip processing|Skip processing]] feature. </td>
</tr>
</tr>
</table>
</table>
====FIND ALL VALUES statement====
<p>The FIND ALL VALUES statement provides an efficient value retrieval method that performs the FIND and SORT operations outside of the value loop. Each FIND ALL VALUES statement generates a value set header of 48 bytes.</p>
<b>Syntax</b>
<p>A complete FIND ALL VALUES statement format in User Language is as follows:</p>
<p class="code">label: FIND ALL VALUES


[IN {FILE filename | GROUP groupname}]-
===Find All Values statement===
<p>
The <var>Find All Values</var> statement provides an efficient value retrieval method that performs the <var>FIND</var> and <var>SORT</var> operations outside of the value loop. Each <var>Find All Values</var> statement generates a value set header of 48 bytes.</p>
====Syntax====
<p>
The complete syntax for the <var>Find All Values</var> statement in SOUL is as follows:</p>
 
<p>
Where:</p>
<ul>
{{Template: Find Values Of statement syntax}}
<li><var class="term">label</var> is necessary in order to use the result of the statement.</li>
 
<li><var class="term">filename</var> specifies the file that has the records you want to retrieve.</li>
 
<li><var class="term">groupname</var> specifies the group of files that has the records you want to retrieve.</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>


FIND ALL VALUES OF fieldname -
<li><var class="term">pattern</var> specifies a string to match or not match. See the syntax of [[Is Like pattern matching|Is Like patterns]].</li>


[FROM value1] [TO value2] [(NOT) LIKE pattern] -
<li><var class="term">value_set</var> is a label that identifies an existing value set. <var>NOT</var>, which is optional, is the only permissible operator.
<p>
See also [[Basic SOUL statements and commands#Processing a VALUE IN clause|Processing a VALUE IN clause]] for other statements that use a <var>VALUE IN</var> clause. </p></li>


[(NOT) VALUE IN value_set]
<li>Expressions can be used for <var class="term">value1</var> and <var class="term">value2</var>, for example:
</p>
<p class="code">Find All Values Of <i>fieldname</i> From (<i>expression1</i>) To (<i>expression2</i>)
<p>Where:</p>
<ul>
<li>filename specifies the file that has the records you want to retrieve.</li>
</li>
<li>groupname specifies the group of files that has the records you want to retrieve.</li>
</li>
<li>fieldname identifies the field from which to retrieve values.</li>
</li>
<li>value1 and value2 specify the beginning and ending range of values to retrieve.</li>
</li>
<li>pattern specifies a string to match or not match. See the syntax of [[Is Like pattern matching|Is Like patterns]].</li>
</li>
<li>The value_set label represents an existing value set. Values of the value set in the EQ VALUE IN value_set clause are treated as boolean OR, for example:</li>
<p class="code">field = value1 OR field = value2 OR....
</p>
<p>The NE VALUE IN value_set clause is likewise treated as boolean AND, for example:</p>
<p class="code">field <> value1 AND field <> value2 AND....
</p>
<p>See also [[Basic SOUL statements and commands#Processing a VALUE IN clause|Processing a VALUE IN clause]].</p>
</li>
<li>Expressions can be used for value1 and value2, for example:</li>
<p class="code">FIND ALL VALUES OF fieldname FROM (expression1) TO (expression2)
</p>
</p>
<p>where expression1 and expression2 are enclosed in parentheses and can be of the following types: function call, string concatenation, arithmetic operation, User Language construct, or Boolean expression. </p>
<p>
<p>Example:</p>
Where <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></li>
</li>
</ul>
</ul>
<p class="code">B
 
%MM IS STRING LEN 2
====Example====
%DD IS STRING LEN 2
<p>
%MM = '09'
In the following example, the values of <code>ANNIV DATE</code> (0905, 0909, 0913, 0917, 0921, 0925) are found, counted, and printed.
%DD1 = '05'
Leading zeroes for these values are stripped by <var>FDV</var>. The example also illustrates the use of expressions in the <var>FROM</var> and <var>TO</var> clause of <var>FDV</var>. </p>
%DD2 = '30'
 
PRINT 'FR1'
<p class="code">begin
FDV1:
%i    is fixed 
IN CLIENTS FIND ALL VALUES OF ANNIV DATE -
%mm    is string len 2  
  FROM (%MM WITH %DD1) TO (%MM WITH %DD2)
%dd    is string len 2  
FD1:
%mm = '09'
IN CLIENTS FPC ANNIV DATE = VALUE IN FDV1
%dd1 = '05'  
          END FIND
%dd2 = '30'  
FR FD1
fdv1: in clients fdv anniv date -  
   PAI
      from (%mm with %dd1) to (%mm with %dd2)  
   PRINT
/?--------------------------------------------?/
END FOR
/?  Print and count the values of anniv date  ?/
END
/?--------------------------------------------?/         
adate: frv in fdv1       
        print value in adate
        %i = %i + 1
      end for 
      print 'number of values of anniv date = ' with %i
fd1:   in clients fpc anniv date eq value in fdv1   
      end find 
/?----------------------------------?/
/?  fd1 above is equivalent to fd2: ?/
/?----------------------------------?/                           
fd2: in clients fpc anniv date = 0905 OR - 
                    anniv date = 0909 or -
                    anniv date = 0913 OR -
                    anniv date = 0917 OR -
                    anniv date = 0921 OR -
                    anniv date = 0925 
                end find    
end </p>
 
The request result is:
<p class="output">905 
909
913
917
921
925
number of values of anniv date = 6 
72
72                                               
</p>
</p>
====FRV or ORDERED field attribute required====
 
<p>You can only use value loops with fields that have the FRV (For Each Value) or ORDERED attribute. This is because <var class="product">Model&nbsp;204</var> maintains a list of all the unique values created for a field with the FRV attribute. For a field with the ORDERED attribute, <var class="product">Model&nbsp;204</var> can easily use the Ordered Index to retrieve all values created for the field.           </p>
===FRV or ORDERED field attribute required===
<p>The set of values retrieved differs depending upon the field attribute, as follows</p>
<p>
You can only use value loops with fields that have the FRV (For Each Value) or ORDERED attribute. This is because <var class="product">Model&nbsp;204</var> maintains a list of all the unique values created for a field with the FRV attribute. For a field with the ORDERED attribute, <var class="product">Model&nbsp;204</var> can easily use the Ordered Index to retrieve all values created for the field. </p>
<p>
The set of values retrieved differs depending upon the field attribute, as follows:</p>
<table>
<table>
<tr class="head">
<tr class="head">
Line 115: Line 157:
</tr>
</tr>
</table>
</table>
<b>To process fields without either the FRV or ORDERED attribute as though they did, see See Simulating a FOR EACH VALUE loop.</b>
<b>Rewriting applications to take advantage of VALUE IN clause processing</b>
To process fields that do not have either the FRV or ORDERED attribute as though they did, see [[#Simulating a FOR EACH VALUE loop|Simulating a FOR EACH VALUE loop]].
<p>If your pre-Version 5.1 applications use a nested loop join in User Language, you could rewrite your applications to take advantage of VALUE IN clause processing.</p>
<p>You are likely to see Improved performance for fields with the ORDERED attribute. These improvements, which result in a reduction of overall wall clock time, include:</p>
====Rewriting applications to take advantage of VALUE IN clause processing====
<p>
If your pre-Version 5.1 applications use a nested loop join in User Language, you could rewrite your applications to take advantage of VALUE IN clause processing.</p>
<p>
You are likely to see Improved performance for fields with the ORDERED attribute. These improvements, which result in a reduction of overall wall clock time, include:</p>
<ul>
<ul>
<li>A decrease in disk I/O</li>
<li>A decrease in disk I/O</li>
Line 125: Line 171:
</li>
</li>
</ul>
</ul>
<b>Coding example for an FD statement</b>
====Coding example for an FD statement====
<p class="code">OPEN CLAIMS90
<p class="code">OPEN CLAIMS90
FILEMGR
FILEMGR
Line 142: Line 189:
END
END
</p>
</p>
====Using value loops====
 
<p>The value loop is particularly useful for counting and printing records that have a given set of values and for detecting control breaks. </p>
===Using value loops===
<p>This example illustrates the use of a value loop. See [[#Solution using FRV fields|Solution using FRV fields]] to [[#Avoiding printing deleted field values|Avoiding printing deleted field values]] for additional examples illustrating uses for value loops.</p>
<p>
The value loop is particularly useful for counting and printing records that have a given set of values and for detecting control breaks. </p>
<p>
This example illustrates the use of a value loop. See [[#Solution using FRV fields|Solution using FRV fields]] to [[#Avoiding printing deleted field values|Avoiding printing deleted field values]] for additional examples illustrating uses for value loops.</p>
<p class="code">BEGIN
<p class="code">BEGIN
AGENT.VALUE: FOR EACH VALUE OF AGENT
AGENT.VALUE: FOR EACH VALUE OF AGENT
Line 159: Line 209:
                 END FOR
                 END FOR
             END FOR
             END FOR
END  
END
</p>
</p>
<p>The preceding request results in this output:</p>
<p>
The preceding request results in this output:</p>
<p class="code">AGENT: CASOLA
<p class="code">AGENT: CASOLA
 
       ANCH, HOWARD W
       ANCH, HOWARD W
       BARLEY, BOYD Y
       BARLEY, BOYD Y
Line 171: Line 222:
       OAKES, CHRISTOPHER L
       OAKES, CHRISTOPHER L
       OZOLINS, CARRIE M
       OZOLINS, CARRIE M
 
 
AGENT: GREEN
AGENT: GREEN
 
       DETWILER, WOODROW Q
       DETWILER, WOODROW Q
       HELM, HAZEL P
       HELM, HAZEL P
Line 181: Line 231:
       NATHAN, JANEL S
       NATHAN, JANEL S
       VIGNOLA, VERDELL D
       VIGNOLA, VERDELL D
  .
            .
  .
            .
  .  
            .
</p>
</p>
<p>In the first repetition of the FOR EACH VALUE loop, <var class="product">Model&nbsp;204</var> notes a specific value of AGENT that occurs in the file and prints that value. Then it finds all Massachusetts drivers for the noted agent and prints their names. In the next repetition, <var class="product">Model&nbsp;204</var> notes a second value of AGENT, prints it, finds all Massachusetts drivers for the agent, and prints their names. This continues until all values of AGENT have been processed.</p>
<p>
===Sorting retrieved values===
In the first repetition of the FOR EACH VALUE loop, <var class="product">Model&nbsp;204</var> notes a specific value of AGENT that occurs in the file and prints that value. Then it finds all Massachusetts drivers for the noted agent and prints their names. In the next repetition, <var class="product">Model&nbsp;204</var> notes a second value of AGENT, prints it, finds all Massachusetts drivers for the agent, and prints their names. This continues until all values of AGENT have been processed.</p>
====Field attributes affect default order====
 
<p>When using the FOR EACH VALUE statement, the default order in which <var class="product">Model&nbsp;204</var> returns retrieved values depends on the reference context and the attribute of the value field, according to the following principles:      </p>
==Sorting retrieved values==
===Field attributes affect default order===
<p>
When using the FOR EACH VALUE statement, the default order in which <var class="product">Model&nbsp;204</var> returns retrieved values depends on the reference context and the attribute of the value field, according to the following principles:      </p>
<ul>
<ul>
<li>When the FOR EACH VALUE statement refers to an individual file, the default order is determined by the attribute of the value field:</li>
<li>When the FOR EACH VALUE statement refers to an individual file, the default order is determined by the attribute of the value field:</li>
</li>
</li>
</ul>
<p>
<p>Ascending numeric order for numeric values followed by standard EBCDIC collating order for non-numeric values if the field is ORDERED NUMERIC</p>
Ascending numeric order for numeric values followed by standard EBCDIC collating order for non-numeric values if the field is ORDERED NUMERIC</p>
<p>Standard EBCDIC order if the field is ORDERED CHARACTER</p>
<p>
<p>Random sequence if the field is FRV  </p>
Standard EBCDIC order if the field is ORDERED CHARACTER</p>
<ul>
<p>
Random sequence if the field is FRV  </p>
 
<li>When the FOR EACH VALUE statement refers to a group, the default order is also determined by the attributes of the value field in the various files in the group:</li>
<li>When the FOR EACH VALUE statement refers to a group, the default order is also determined by the attributes of the value field in the various files in the group:</li>
</li>
<p>
Ascending numeric if the field is defined as ORDERED NUMERIC in any of the group members </p>
<p>
Otherwise, the standard EBCDIC collating sequence</p>
<p>
See [[Files, groups, and reference context#Group operations in User Language|Group operations in User Language]] for more information on operations on file groups.</p>
</ul>
</ul>
<p>Ascending numeric if the field is defined as ORDERED NUMERIC in any of the group members </p>
<p>Otherwise, the standard EBCDIC collating sequence</p>
<p>See [[Files, Groups, and Reference Context#Group operations in User Language|Group operations in User Language]] for more information on operations on file groups.</p>
====FOR EACH VALUE OF statement====
<p>If values must be obtained in a different sort sequence, the IN ORDER clause can be appended to the FOR EACH VALUE statement. In group context, this implies that the IN ORDER clause must be used to process values in any order other than standard EBCDIC sequence. The format is:</p>
<b>Syntax</b>
<p class="code">FOR EACH VALUE OF fieldname


  [IN [ASCENDING |DESCENDING]
===FOR EACH VALUE OF statement===
<p>
If values must be obtained in a different sort sequence, the <var>IN ORDER</var> clause can be appended to the <var>FOR EACH VALUE</var> statement. In group context, this implies that the <var>IN ORDER</var> clause must be used to process values in any order other than standard EBCDIC sequence. </p>
====Syntax====
<p class="syntax">FOR EACH VALUE OF <span class="term">fieldname</span>
  [IN [ASCENDING |DESCENDING] [CHARACTER | <b>NUMERIC</b>AL] | |RIGHT-ADJUSTED] ORDER]
</p>


[CHARACTER | NUMERICAL] | |RIGHT-ADJUSTED] ORDER]   
Where:
</p>
<b>Where</b>
<ul>
<ul>
<li>ASCENDING and DESCENDING indicate the order in which the value set is sorted. ASCENDING is the default.      </li>
<li>ASCENDING and DESCENDING indicate the order in which the value set is sorted. ASCENDING is the default.      </li>
</li>
</li>
<li>CHARACTER specifies EBCDIC collating sequence, with fields with null values appearing first. Fields with unequal lengths are left-justified before being sorted. The default is determined as indicated above, except when you are using a field name variable to indicate the field name, and you are working in group context. In this case, CHARACTER is the default.    </li>
<li>CHARACTER specifies EBCDIC collating sequence, with fields with null values appearing first. Fields with unequal lengths are left-justified before being sorted. The default is determined as indicated above, except when you are using a field name variable to indicate the field name, and you are working in group context. In this case, CHARACTER is the default.    </li>
</li>
</li>
<li>NUMERICAL specifies that non-numeric fields appear first, followed by proper number fields in numerical order.    </li>
<li>NUMERICAL specifies that non-numeric fields appear first, followed by proper number fields in numerical order.    </li>
</li>
</li>
<li>RIGHT-ADJUSTED specifies that fields are right-adjusted, with shorter lengths appearing first. Fields of equal length are in EBCDIC collating sequence.    </li>
<li>RIGHT-ADJUSTED specifies that fields are right-adjusted, with shorter lengths appearing first. Fields of equal length are in EBCDIC collating sequence.    </li>
</li>
</li>
</ul>
</ul>
<b>Usage</b>
 
<p>The FOR EACH VALUE statement is supported in remote file and scattered group contexts.</p>
====Usage====
<p>Each FOR EACH VALUE OF statement generates a value set header of 48 bytes. </p>
<p>
<b>Example</b>
The FOR EACH VALUE statement is supported in remote file and scattered group contexts.</p>
<p>This sample FOR EACH VALUE statement uses the IN ORDER option.</p>
<p>
<p class="code">GET.NAMES: FOR EACH VALUE OF AGENT -
Each FOR EACH VALUE OF statement generates a value set header of 48 bytes. </p>
                          IN DESCENDING ORDER
====Example====
<p>
This sample FOR EACH VALUE statement uses the IN ORDER option.</p>
<p class="code">GET.NAMES: FOR EACH VALUE OF AGENT IN DESCENDING ORDER
               PRINT VALUE IN GET.NAMES
               PRINT VALUE IN GET.NAMES
           END FOR  
           END FOR
</p>
</p>
====Performance considerations====
 
<p>Use of the FOR EACH VALUE statement might force an internal sort of the found values, which increases processing time. This section explains these conditions in detail. </p>
===Performance considerations===
<b>Impact of field attributes</b>
<p>
<p>For fields with the ORDERED attribute, if only the ASCENDING or DESCENDING option is specified, the Ordered Index is used to return the values. </p>
Use of the FOR EACH VALUE statement might force an internal sort of the found values, which increases processing time. This section explains these conditions in detail. </p>
<p>Fields with the ORDERED attribute and any other options specified require an internal sort of values, which requires more processing time.  </p>
<p>In addition, processing time requirements are extended further if an ORDERED NUMERIC field is processed in CHARACTER or RIGHT-ADJUSTED order, because the numeric values must be converted into character strings before the sort can be performed.</p>
====Impact of field attributes====
<p>A field with the FRV attribute requires an internal sort of the values, which requires more processing time.      </p>
<p>
<b>Use with groups</b>
For fields with the ORDERED attribute, if only the ASCENDING or DESCENDING option is specified, the Ordered Index is used to return the values. </p>
<p>Issuing the FOR EACH VALUE statement in reference to a file group always involves an internal sort, and therefore additional processing time. This is true whether values are being processed in default (EBCDIC) order, or in another order as specified in an IN ORDER clause.                </p>
<p>
===Specifying retrieval ranges for values===
Fields with the ORDERED attribute and any other options specified require an internal sort of values, which requires more processing time.  </p>
====FROM and TO clause====
<p>
<p>This form of the FOR EACH VALUE statement can be used to obtain a range of values:          </p>
In addition, processing time requirements are extended further if an ORDERED NUMERIC field is processed in CHARACTER or RIGHT-ADJUSTED order, because the numeric values must be converted into character strings before the sort can be performed.</p>
<p class="code">FOR EACH VALUE OF fieldname <var>[</var>FROM value1<var>]</var> <var>[</var>TO value2<var>]</var>  
<p>
A field with the FRV attribute requires an internal sort of the values, which requires more processing time.      </p>
====Use with groups====
<p>
Issuing the FOR EACH VALUE statement in reference to a file group always involves an internal sort, and therefore additional processing time. This is true whether values are being processed in default (EBCDIC) order, or in another order as specified in an IN ORDER clause.                </p>
==Specifying retrieval ranges for values==
===FROM and TO clause===
<p>
This form of the FOR EACH VALUE statement can be used to obtain a range of values:          </p>
<p class="code">FOR EACH VALUE OF <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>
</p>
</p>
<p>where the FROM and TO clauses specify the range of values to be processed. FROM indicates a beginning value; TO indicates an ending value.</p>
<p>
====Intended to be used with character strings====
where the <var>FROM</var> and <var>TO</var> clauses specify the range of values to be processed. <var>FROM</var> indicates a beginning value (inclusive); <var>TO</var> indicates an ending value (exclusive).</p>
<p>The range specification (FROM value1 TO value2) is intended for use with character strings. </p>
 
<p>If the range values (value1 and value2) are numeric, the range is still treated as a character string comparison. Thus, this statement:</p>
===Intended to be used with character strings===
<p class="code">FOR EACH VALUE OF AGE FROM 1 TO 100  
<p>
The range specification (FROM <var class="term">value1</var> TO <var class="term">value2</var>) is intended for use with character strings. </p>
<p>
If the range values (<var class="term">value1</var> and <var class="term">value2</var>) are numeric, the range is still treated as a character string comparison. Thus, this statement:</p>
<p class="code">FOR EACH VALUE OF AGE FROM 1 TO 100
</p>
</p>
<p>would only use the values 1, 10, and 100. To use ages 1 through 100, declare the AGE field to be ORDERED NUMERIC and use a FIND statement to perform the retrieval.</p>
<p>
====Use with the IN ORDER option====
would only use the values 1, 10, and 100. To use ages 1 through 100, declare the AGE field to be ORDERED NUMERIC and use a FIND statement to perform the retrieval.</p>
<p>If an inclusive range is specified along with the IN ORDER option, value1 and value2 must also reflect the specified sort order (ASCENDING or DESCENDING) in order for the loop to be executed. </p>
 
<p>If ASCENDING order is specified, or the default (ASCENDING) is assumed, value1 must be less than value2. For example:    </p>
===Use with the IN ORDER option===
<p>
If an inclusive range is specified along with the IN ORDER option, <var class="term">value1</var> and <var class="term">value2</var> must also reflect the specified sort order (ASCENDING or DESCENDING) in order for the loop to be executed. </p>
<p>
If ASCENDING order is specified, or the default (ASCENDING) is assumed, <var class="term">value1</var> must be less than <var class="term">value2</var>. For example:    </p>
<p class="code">GET.NAMES: FOR EACH VALUE OF AGENT -
<p class="code">GET.NAMES: FOR EACH VALUE OF AGENT -
               FROM 'B' TO 'J' IN ORDER
               FROM 'B' TO 'J' IN ORDER
               PRINT VALUE IN GET.NAMES
               PRINT VALUE IN GET.NAMES
           END FOR  
           END FOR
</p>
</p>
<p>If DESCENDING order is specified along with a range, value1 must be greater than value2. For example:  </p>
<p>
If DESCENDING order is specified along with a range, <var class="term">value1</var> must be greater than <var class="term">value2</var>. For example:  </p>
<p class="code">GET.NAMES: FOR EACH VALUE OF AGENT -
<p class="code">GET.NAMES: FOR EACH VALUE OF AGENT -
               FROM 'J' TO 'B' IN DESCENDING ORDER
               FROM 'J' TO 'B' IN DESCENDING ORDER
               PRINT VALUE IN GET.NAMES
               PRINT VALUE IN GET.NAMES
           END FOR              
           END FOR
</p>
</p>
====Using expressions with FOR EACH VALUE====
 
<p>You can use expressions to provide the values in a FOR EACH VALUE statement.</p>
===Using expressions with FOR EACH VALUE===
<p class="code">FOR EACH VALUE OF fieldname FROM (expression1) TO (expression2)
<p>
You can use expressions to provide the values in a FOR EACH VALUE statement.</p>
<p class="syntax">FOR EACH VALUE OF <span class="term">fieldname</span> FROM (<span class="term">expression1</span>) TO (<span class="term">expression2</span>)
</p>
</p>
<p>where expression1 and expression2 are enclosed in parentheses and can be of the following types: function call, string concatenation, arithmetic operation, User Language construct, or Boolean expression. </p>
<p>
<b>Example</b>
where <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>
====Example====
<p class="code">B
<p class="code">B
%MM IS STRING LEN 2
%MM IS STRING LEN 2
Line 288: Line 379:
END
END
</p>
</p>
===Specifying retrieval patterns for values===
 
====Syntax of the LIKE PATTERN clause====
==Specifying retrieval patterns for values==
<p>This form of the FOR EACH VALUE statement can be used to obtain a range of values based upon a pattern:                            </p>
<p class="code">FOR EACH VALUE OF fieldname <var>[</var>NOT<var>]</var> LIKE pattern  
===Syntax of the LIKE PATTERN clause===
<p>
This form of the FOR EACH VALUE statement can be used to obtain a range of values based upon a pattern:                            </p>
<p class="syntax">FOR EACH VALUE OF <span class="term">fieldname</span> <span class="squareb">[</span>NOT<span class="squareb">]</span> LIKE <span class="term">pattern</span>
</p>
</p>
<b>Where</b>
Where:
<ul>
<ul>
<li>fieldname is the name of the field for which values are retrieved. A FOR EACH VALUE retrieval based on a pattern is optimized if the field has the ORDERED attribute.</li>
<li><var class="term">fieldname</var> is the name of the field for which values are retrieved. A FOR EACH VALUE retrieval based on a pattern is optimized if the field has the ORDERED attribute.</li>
</li>
</li>
<li>pattern is any valid pattern that can be used in a retrieval condition. For more information on patterns, refer to the syntax of [[Is Like pattern matching|Is Like patterns]].</li>
 
<li><var class="term">pattern</var> is any valid pattern that can be used in a retrieval condition. For more information on patterns, refer to the syntax of [[Is Like pattern matching|Is Like patterns]].</li>
</li>
</li>
</ul>
</ul>
<b>Example</b>
====Example====
<p class="code">CAS.PREFIX: FOR EACH VALUE OF AGENT LIKE 'CAS*'
<p class="code">CAS.PREFIX: FOR EACH VALUE OF AGENT LIKE 'CAS*'
               PRINT VALUE IN CAS.PREFIX
               PRINT VALUE IN CAS.PREFIX
             END FOR            
             END FOR
</p>
</p>
====Pattern matching using the LANGLIKE operator====
 
<p>The User Language operator LANGLIKE supports parsing and evaluation of patterns according to the tables indicated with the LANGUSER and LANGFILE parameters. (See the Rocket <var class="product">Model&nbsp;204</var> Language Support Summary or the Rocket <var class="product">Model&nbsp;204</var> Parameter and Command Reference Manual.)</p>
===Pattern matching using the LANGLIKE operator===
<p>The LANGLIKE syntax is the same as LIKE syntax. If LANGFILE is set to US, then the parsing language is U.S. English, and the LIKE and LANGLIKE operators are equivalent. </p>
<p>
The <var class="product">SOUL</var> operator <var>LANGLIKE</var> supports parsing and evaluation of patterns according to the tables indicated with the <var>LANGUSER</var> and <var>LANGFILE</var> parameters. (See the
"Rocket <var class="product">Model&nbsp;204</var> Language Support Summary" or the
[[List of Model 204 parameters]] and [[List of Model 204 commands]].)</p>
<p>
The <var>LANGLIKE</var> syntax is the same as <var>LIKE</var> syntax. If <var>LANGFILE</var> is set to <var>US</var>, then the parsing language is U.S. English, and the <var>LIKE</var> and <var>LANGLIKE</var> operators are equivalent. </p>
<ul>
<ul>
<li>The LIKE operator employs U.S. English for parsing and the value of LANGFILE for evaluating the pattern.</li>
<li>The <var>LIKE</var> operator employs U.S. English for parsing and the value of <var>LANGFILE</var> for evaluating the pattern.</li>
</li>
</li>
<li>The LANGLIKE operator uses the value of LANGUSER for parsing the pattern and the value of LANGFILE for evaluating the pattern.</li>
 
<li>The <var>LANGLIKE</var> operator uses the value of <var>LANGUSER</var> for parsing the pattern and the value of <var>LANGFILE</var> for evaluating the pattern.</li>
</li>
</li>
</ul>
</ul>
<p>The parsing language, LANGUSER, is used for checking the syntax of the pattern and for determining the value of:</p>
<p>
The parsing language, <var>LANGUSER</var>, is used for checking the syntax of the pattern and for determining the value of:</p>
<ul>
<ul>
<li>Special pattern escape character</li>
<li>Special pattern escape character</li>
Line 323: Line 427:
</li>
</li>
</ul>
</ul>
<p>The evaluation language, LANGFILE, is used to match the pattern against the data. In particular, if a range of characters is defined in the pattern, then the collating sequence is determined by the evaluation language, LANGFILE.</p>
<p>
<b>Syntax</b>
The evaluation language, <var>LANGFILE</var>, is used to match the pattern against the data. In particular, if a range of characters is defined in the pattern, then the collating sequence is determined by the evaluation language, <var>LANGFILE</var>.</p>
<p>The format of the FIND statement used to perform pattern matching is:</p>
<p class="code">FIND <var>[</var>ALL<var>]</var> RECORDS {FOR WHICH | WITH} fieldname  
====Syntax====
<p>
The format of the FIND statement used to perform pattern matching is:</p>
<p class="syntax">FIND <span class="squareb">[</span>ALL<span class="squareb">]</span> RECORDS <span class="squareb">{</span> FOR WHICH <span class="squareb">|</span> WITH <span class="squareb">}</span> <span class="term">fieldname</span>
    IS <span class="squareb">[</span>NOT<span class="squareb">]</span> LANGLIKE'<span class="term">pattern</span>'
</p>
Where:
<p>
The <var>LANGLIKE</var> keyword indicates that <var class="term">pattern</var> is the set of characters to match.</p>
<p>
The <var class="term">pattern</var> argument must be enclosed in quotation marks. </p>


    IS <var>[</var>NOT<var>]</var> LANGLIKE'pattern' 
==Processing a sample of values==
===FOR k VALUES OF statement===
<p>
You can process only a sample of values with this statement:  </p>
<p class="syntax">FOR <span class="term">k</span> VALUES OF <span class="term">fieldname</span> <span class="squareb">[</span><span class="term">options</span><span class="squareb">]</span>
</p>
</p>
<b>Where</b>
<p>The LANGLIKE keyword indicates that pattern is the set of characters to match.</p>
Where:
<p>The pattern argument must be enclosed in quotation marks. </p>
===Processing a sample of values===
====FOR k VALUES OF statement====
<p>You can process only a sample of values with this statement:   </p>
<p class="code">FOR k VALUES OF fieldname <var>[</var>options<var>]</var>
</p>
<b>Where</b>
<ul>
<ul>
<li>k 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>
</li>
<li>options is any of the options that can be specified on a FOR EACH VALUES statement. </li>
<li><var class="term">options</var> is any of the options that can be specified on a <var>FOR EACH VALUES</var> statement. </li>
</li>
</li>
</ul>
</ul>
<p>The FOR k VALUES statement begins a loop that repeats up to k times, depending on the statement: </p>
<p>
The <var>FOR k VALUES</var> statement begins a loop that repeats up to <var class="term">k</var> times, depending on the statement: </p>
<ul>
<ul>
<li>If k equals zero, the loop is skipped. </li>
<li>If <var class="term">k</var> equals zero, the loop is skipped. </li>
</li>
</li>
<li>If k is negative, or if there are fewer than k values, then the loop is repeated for all the values of the field.</li>
<li>If <var class="term">k</var> is negative, or if there are fewer than <var class="term">k</var> values, the loop is repeated for all the values of the field.</li>
</li>
</li>
</ul>
</ul>
<p>The FOR k VALUES statement is supported in remote file and scattered group contexts.</p>
<p>
====Use with range specification====
The <var>FOR k VALUES</var> statement is supported in remote file and scattered group contexts.</p>
<p>The FOR k VALUES statement, combined with the FROM option, allows processing in a neighborhood of a value (to select a number of records associated with values centered around a given focal value). For example, the request shown below searches for the correct spelling of an insurance agent's name. This request uses a %variable. See [[Using Variables and Values in Computation#Using Variables and Values in Computation|Using Variables and Values in Computation]] for a detailed discussion of %variables.</p>
 
===Use with range specification===
<p>
The <var>FOR k VALUES</var> statement, combined with the <var>FROM</var> option, allows processing in a neighborhood of a value (to select a number of records associated with values centered around a given focal value). For example, the request shown below searches for the correct spelling of an insurance agent's name. This request uses a %variable. See [[Using variables and values in computation]] for a detailed discussion of %variables.</p>
<p class="code">V1: FOR 5 VALUES OF AGENT IN DESCENDING ORDER -
<p class="code">V1: FOR 5 VALUES OF AGENT IN DESCENDING ORDER -
       FROM GOODRIK
       FROM GOODRIK
Line 361: Line 479:
V2: FOR 10 VALUES OF AGENT IN ORDER FROM %START
V2: FOR 10 VALUES OF AGENT IN ORDER FROM %START
       PRINT VALUE IN V2
       PRINT VALUE IN V2
     END FOR  
     END FOR
</p>
</p>
====Order of the most recent loop defines the order of the values====
<p>The order of the most recent loop defines the order of the values. For example, suppose the AGENT field in the preceding example has the ORDERED CHARACTER attribute. Although the V1 loop has retrieved the values in descending order, the 10 agent names print in ascending order. This is because the most recent loop, V2, specifies only IN ORDER, which defaults to ascending character order.                    </p>
===Order of the most recent loop defines the order of the values===
===Retrieving values efficiently===
<p>
====FOR EACH VALUE retrieval====
The order of the most recent loop defines the order of the values. For example, suppose the <code>AGENT</code> field in the preceding example has the <var>ORDERED CHARACTER</var> attribute. Although the <code>V1</code> loop has retrieved the values in descending order, the 10 agent names print in ascending order. This is because the most recent loop, <code>V2</code>, specifies only <code>IN ORDER</code>, which defaults to ascending character order.                    </p>
<p>When a FOR EACH VALUE statement appears inside another loop, the portion of the statement that finds the values is executed repeatedly, even if the set values do not change. The internal sort also is repeated for FOR EACH VALUE IN ORDER or in group context. (See [[Files, Groups, and Reference Context#Files, Groups, and Reference Context|Files, Groups, and Reference Context]] for information about group context.)</p>
 
====Advantage of the FIND ALL VALUES statement====
==Retrieving values efficiently==
<p>By moving the FIND and SORT out of the loop, the cost of the inner FOR EACH VALUE loop can be reduced.</p>
===FOR EACH VALUE retrieval===
<p>
When a FOR EACH VALUE statement appears inside another loop, the portion of the statement that finds the values is executed repeatedly, even if the set values do not change. The internal sort also is repeated for FOR EACH VALUE IN ORDER or in group context. (See [[Files, groups, and reference context]] for information about group context.)</p>
===Advantage of the Find All Values statement===
<p>
By moving the FIND and SORT out of the loop, the cost of the inner FOR EACH VALUE loop can be reduced.</p>
<b>Syntax</b>
<b>Syntax</b>
<p>This statement performs the FIND portion of the FOR EACH VALUE statement:</p>
<p>
<p class="code">label: FIND <var>[</var>ALL<var>]</var> VALUES OF fieldname
This statement performs the FIND portion of the FOR EACH VALUE statement:</p>
 
<p class="syntax"><span class="term">label</span>: FIND <span class="squareb">[</span>ALL<span class="squareb">]</span> VALUES OF <span class="term">fieldname</span>
<var> [</var>FROM value1<var>]</var> <var>[</var>TO value2<var>]</var> <var>[ [</var>NOT<var>]</var> LIKE 'pattern'<var>]</var>
<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>NOT<span class="squareb">]</span> LIKE '<span class="term">pattern'</span><span class="squareb">]</span>
</p>
</p>
<p>The FIND ALL VALUES statement is supported in remote file and scattered group contexts.</p>
<p>
====FIND ALL VALUES options====
The Find All Values statement is supported in remote file and scattered group contexts.</p>
<p>Like other FIND statements, a range of values can be specified for the FIND ALL VALUES statement by using the FROM and TO clauses. </p>
<p>In addition, value selection can be made based upon a pattern by using the LIKE clause.</p>
===Find All Values options===
<p>You can use the IN GROUP MEMBER clause to restrict the FIND to one member of a group. See [[Files, Groups, and Reference Context#IN GROUP MEMBER clause|IN GROUP MEMBER clause]] for more information on using it.</p>
<p>
====No block end required====
Like other FIND statements, a range of values can be specified for the Find All Values statement by using the FROM and TO clauses. </p>
<p>Unlike other FIND statements, the FIND ALL VALUES statement must not have a block end statement because FIND ALL VALUES does not accept multiple conditions.</p>
<p>
====Sorting retrieved values====
In addition, value selection can be made based upon a pattern by using the LIKE clause.</p>
<p>The FIND ALL VALUES statement returns values in the default order. </p>
<p>
<p>If a different order is required, the SORT VALUES statement must be used. For a discussion, refer to [[#SORT VALUES statement|SORT VALUES statement]]. If a FIND ALL VALUES statement appears in group context, a sort is generated automatically. </p>
You can use the IN GROUP MEMBER clause to restrict the FIND to one member of a group. See [[Files, groups, and reference context#IN GROUP MEMBER clause|IN GROUP MEMBER clause]] for more information on using it.</p>
====Processing retrieved values====
<p>The value set resulting from a FIND ALL VALUES statement can be processed with this statement:</p>
===No block end required===
<p class="code">FOR EACH VALUE IN label  
<p>
Unlike other FIND statements, the Find All Values statement must not have a block end statement because Find All Values does not accept multiple conditions.</p>
===Sorting retrieved values===
<p>
The Find All Values statement returns values in the default order. </p>
<p>
If a different order is required, the SORT VALUES statement must be used. For a discussion, refer to [[#SORT VALUES statement|SORT VALUES statement]]. If a Find All Values statement appears in group context, a sort is generated automatically. </p>
===Processing retrieved values===
<p>
The value set resulting from a Find All Values statement can be processed with this statement:</p>
<p class="syntax">FOR EACH VALUE IN <span class="term">label</span>
</p>
</p>
<p>For example, the following request finds a selected range of agents in the CLIENTS file:</p>
<p>
For example, the following request finds a selected range of agents in the <code>CLIENTS</code> file:</p>
<p class="code">BEGIN
<p class="code">BEGIN
FIND.RECS:  FIND ALL VALUES OF AGENT -
FIND.RECS:  FIND ALL VALUES OF AGENT -
Line 397: Line 536:
                 PRINT VALUE IN PRINT.INFO
                 PRINT VALUE IN PRINT.INFO
             END FOR
             END FOR
END  
END
</p>
</p>
<p>The example below shows nested FOR EACH VALUE loops. In the example, the constant portion of the processing for COUNTY is executed once instead of fifty times. The CITY names are looked up once instead of hundreds or thousands of times.</p>
<p>
The example below shows nested FOR EACH VALUE loops. In the example, the constant portion of the processing for <code>COUNTY</code> is executed once instead of fifty times. The <code>CITY</code> names are looked up once instead of hundreds or thousands of times.</p>
<p class="code">EACH.COUNTY: FIND ALL VALUES OF COUNTY
<p class="code">EACH.COUNTY: FIND ALL VALUES OF COUNTY
EACH.CITY:  FIND ALL VALUES OF CITY
EACH.CITY:  FIND ALL VALUES OF CITY
Line 407: Line 547:
                         .
                         .
                         .
                         .
                         .  
                         .
</p>
</p>
<p>If new values are to be added within the body of a FRV loop, it might be incorrect to separate the FIND. The FIND must be re-executed to allow the new values to be processed by the loop. In this case, the FOR EACH VALUE OF statement should be used to process the values.                  </p>
<p>
===SORT VALUES statement===
If new values are to be added within the body of an FRV loop, it might be incorrect to separate the FIND. The FIND must be re-executed to allow the new values to be processed by the loop. In this case, the FOR EACH VALUE OF statement should be used to process the values.                  </p>
<p>If you need special ordering of the value set found by the FIND ALL VALUES statement, use the SORT VALUES statement.    </p>
 
<p>The SORT VALUES statement can refer to any FIND ALL VALUES statement. The sorted value set can then be processed with the FOR EACH VALUE IN statement.              </p>
==SORT VALUES statement==
<p>The FIND ALL VALUES statement is described in the previous section.</p>
<p>
<b>Syntax</b>
If you need special ordering of the value set found by the Find All Values statement, use the SORT VALUES statement.    </p>
<p>The format of the SORT VALUES statement is:</p>
<p>
<p class="code">SORT VALUES IN label [IN [ASCENDING | DESCENDING]
The SORT VALUES statement can refer to any Find All Values statement. The sorted value set can then be processed with the FOR EACH VALUE IN statement.              </p>
<p>
The Find All Values statement is described in the previous section.</p>


====Syntax====
<p>
The format of the SORT VALUES statement is:</p>
<p class="syntax">SORT VALUES IN <span class="term">label</span> [IN [ASCENDING | DESCENDING]
  [CHARACTER | NUMERICAL] | [RIGHT-ADJUSTED] ORDER]
  [CHARACTER | NUMERICAL] | [RIGHT-ADJUSTED] ORDER]
</p>
</p>
<p>Where the sort options are the same as those that can be specified for the FOR EACH VALUE statement (see [[#FOR EACH VALUE OF statement|FOR EACH VALUE OF statement]]). </p>
<p>
<p>The SORT VALUES statement is supported in remote file and scattered group contexts.</p>
Where the sort options are the same as those that can be specified for the FOR EACH VALUE statement (see [[#FOR EACH VALUE OF statement|FOR EACH VALUE OF statement]]). </p>
===Locating records missing a particular field===
<p>
<p>The IS NOT PRESENT retrieval condition, described in [[Record retrievals#Numeric range retrievals|Numeric range retrievals]], generally can be used to locate records that do not have a particular field. </p>
The SORT VALUES statement is supported in remote file and scattered group contexts.</p>
<p>However, there are times when you do not want to use IS NOT PRESENT because it requires a direct search of the data records which could result in a very large number of records being searched.      </p>
 
====Solution using FRV fields====
==Locating records missing a particular field==
<p>In such cases the following technique an be used if the field specified in the FOR EACH VALUE statement has the FRV attribute. This technique, however, increases processing overhead if the FRV field has many values.</p>
<p>
The IS NOT PRESENT retrieval condition, described in [[Record retrievals#Numeric range retrievals|Numeric range retrievals]], generally can be used to locate records that do not have a particular field. </p>
<p>
However, there are times when you do not want to use IS NOT PRESENT because it requires a direct search of the data records which could result in a very large number of records being searched.      </p>
===Solution using FRV fields===
<p>
In such cases the following technique can be used if the field specified in the FOR EACH VALUE statement has the FRV attribute. This technique, however, increases processing overhead if the FRV field has many values.</p>
<p class="code">BEGIN
<p class="code">BEGIN
ALL.RECS:  FIND ALL RECORDS
ALL.RECS:  FIND ALL RECORDS
Line 443: Line 597:
               PRINT ALL INFORMATION
               PRINT ALL INFORMATION
             END FOR
             END FOR
END  
END
</p>
</p>
====Solution for non-FRV fields====
 
<p>The problem of locating records that do not have a particular field also can be solved for a non-FRV field. In the code fragment below, the ZIP.MATCH statement removes records from the list as values are found. This avoids unnecessary processing of records with values that already have been found.</p>
===Solution for non-FRV fields===
<p>
The problem of locating records that do not have a particular field also can be solved for a non-FRV field. In the code fragment below, the ZIP.MATCH statement removes records from the list as values are found. This avoids unnecessary processing of records with values that already have been found.</p>
<p class="code">BEGIN
<p class="code">BEGIN
ALL.RECS:  FIND ALL RECORDS
ALL.RECS:  FIND ALL RECORDS
Line 456: Line 612:
                   ZIP = VALUE IN ZIP.VAL
                   ZIP = VALUE IN ZIP.VAL
               END FIND
               END FIND
               REMOVE RECORDS IN ZIP.MATCH FROM LIST NOZIP
               REMOVE RECORDS IN ZIP.MATCH FROM LIST NOZIP
</p>
</p>
===Creating control breaks===
<p>A control break is a change in value of a field from one set of records to another that must be accompanied by some special processing. </p>
==Creating control breaks==
<p>Each pass through a value loop corresponds to a control break. Statements that perform the special processing are placed within the body of the loop and are executed automatically with each value change.    </p>
<p>
====Using value loops for taking control breaks====
A control break is a change in value of a field from one set of records to another that must be accompanied by some special processing. </p>
<p>The following request illustrates the use of value loops for taking control breaks. This request adds together the premium amounts from individual records and prints the total amount for each automobile model. (%variables, which are illustrated in this request, are discussed in detail in [[Using Variables and Values in Computation#Using Variables and Values in Computation|Using Variables and Values in Computation]].)</p>
<p>
Each pass through a value loop corresponds to a control break. Statements that perform the special processing are placed within the body of the loop and are executed automatically with each value change.    </p>
===Using value loops for taking control breaks===
<p>
The following request illustrates the use of value loops for taking control breaks. This request adds together the premium amounts from individual records and prints the total amount for each automobile model. (%variables, which are illustrated in this request, are discussed in detail in [[Using variables and values in computation]].)</p>
<p class="code">BEGIN
<p class="code">BEGIN
MODEL.VALUE: FOR EACH VALUE OF MODEL
MODEL.VALUE: FOR EACH VALUE OF MODEL
Line 475: Line 636:
                 %TOTAMT = 0
                 %TOTAMT = 0
             END FOR
             END FOR
  END  
  END
</p>
</p>
<p>The resulting output would be:</p>
<p>
The resulting output would be:</p>
<p class="code">BEETLE          8053
<p class="code">BEETLE          8053
RAMBLER            0
RAMBLER            0
Line 484: Line 646:
   .              .
   .              .
   .              .
   .              .
   .              .  
   .              .
</p>
</p>
<p>In the preceding example, RAMBLER represents a value of the MODEL field that used to appear in the file but no longer occurs in any records. However, it still is retrieved as a value because the MODEL field has the FRV attribute.    </p>
<p>
===Simulating a FOR EACH VALUE loop===
In the preceding example, RAMBLER represents a value of the MODEL field that used to appear in the file but no longer occurs in any records. However, it still is retrieved as a value because the MODEL field has the FRV attribute.    </p>
<p>You cannot process all fields with the FOR EACH VALUE statement. Occasionally you must process non-FRV and non-ORDERED fields by value.        </p>
====Simulating a value loop for a non-FRV field====
==Simulating a FOR EACH VALUE loop==
<p>The following technique simulates a value loop for a field that is not multiply occurring. (See [[Operations on Multiply Occurring Fields#Operations on Multiply Occurring Fields|Operations on Multiply Occurring Fields]] for detailed information on simulating a value loop for a multiply occurring field.)  </p>
<p>
<p>Assume MODEL is a non-FRV field:</p>
You cannot process all fields with the FOR EACH VALUE statement. Occasionally you must process non-FRV and non-ORDERED fields by value.        </p>
===Simulating a value loop for a non-FRV field===
<p>
The following technique simulates a value loop for a field that is not multiply occurring. (See [[Processing multiply occurring fields and field groups]] for detailed information on simulating a value loop for a multiply occurring field.)  </p>
<p>
Assume that MODEL is a non-FRV field:</p>
<p class="code">BEGIN
<p class="code">BEGIN
ALL:        FIND ALL RECORDS
ALL:        FIND ALL RECORDS
Line 505: Line 673:
               REMOVE RECORDS IN MODEL.MATCH FROM LIST VLOOP
               REMOVE RECORDS IN MODEL.MATCH FROM LIST VLOOP
             END FOR
             END FOR
END  
END
</p>
</p>
<p>The REMOVE RECORDS statement eliminates value redundancies by removing all records with the current value from the list. </p>
<p>
<p>Note that in using this technique, only those records which have a MODEL field are processed.</p>
The REMOVE RECORDS statement eliminates value redundancies by removing all records with the current value from the list. </p>
<b>Usage guidelines</b>
<p>
<p>The previous technique is useful when you must process a list of records representing a portion of a file or group by value. The ALL statement could be replaced by one or more selection statements, with the MAKE.LIST statement producing the final list. </p>
Note that in using this technique, only those records which have a MODEL field are processed.</p>
<p>If the number of values occurring on the record list is less than the number of values occurring in the entire file or group, the simulated FOR EACH VALUE loop is more efficient. This is true even for an FRV field, because the inner FIND statement is executed fewer times.</p>
====Simulating an FRV loop on a non-ORDERED field====
====Usage guidelines====
<p>The following request simulates a FOR EACH VALUE loop on the STATE field in sorted order:</p>
<p>
The previous technique is useful when you must process a list of records representing a portion of a file or group by value. The ALL statement could be replaced by one or more selection statements, with the MAKE.LIST statement producing the final list. </p>
<p>
If the number of values occurring on the record list is less than the number of values occurring in the entire file or group, the simulated FOR EACH VALUE loop is more efficient. This is true even for an FRV field, because the inner FIND statement is executed fewer times.</p>
 
===Simulating an FRV loop on a non-ORDERED field===
<p>
The following request simulates a FOR EACH VALUE loop on the STATE field in sorted order:</p>
<p class="code">BEGIN
<p class="code">BEGIN
ALL:        FIND ALL RECORDS
ALL:        FIND ALL RECORDS
Line 530: Line 705:
               PRINT STATE
               PRINT STATE
             END FOR
             END FOR
END  
END
</p>
</p>
===Printing field values===
<p>A frequent use of the FOR EACH VALUE statement is to print all the values of a field for the entire file, for example, the MODEL field in the EACH.MODEL statement below:    </p>
==Printing field values==
<p>
A frequent use of the FOR EACH VALUE statement is to print all the values of a field for the entire file, for example, the MODEL field in the EACH.MODEL statement below:    </p>
<p class="code">EACH.MODEL: FOR EACH VALUE OF MODEL
<p class="code">EACH.MODEL: FOR EACH VALUE OF MODEL
               PRINT VALUE IN EACH.MODEL
               PRINT VALUE IN EACH.MODEL
             END FOR  
             END FOR
</p>
</p>
====Field names not included====
<p>This type of PRINT statement can contain format information but not field names; the FOR EACH VALUE statement could not have been followed by:</p>
===Field names not included===
<p class="code">PRINT VALUE IN EACH.MODEL AND MAKE  
<p>
This type of PRINT statement can contain format information but not field names; the FOR EACH VALUE statement could not have been followed by:</p>
<p class="code">PRINT VALUE IN EACH.MODEL AND MAKE
</p>
</p>
====Setting up a value loop on one field and printing a value of another====
<p>To set up a value loop on one field and then print the value of another, use a FIND statement and a nested FOR EACH RECORD loop, as illustrated in the following example:</p>
===Setting up a value loop on one field and printing a value of another===
<p>
To set up a value loop on one field and then print the value of another, use a FIND statement and a nested FOR EACH RECORD loop, as illustrated in the following example:</p>
<p class="code">BEGIN
<p class="code">BEGIN
MAKE.VALUE: IN VEHICLES FOR EACH VALUE OF MAKE
MAKE.VALUE: IN VEHICLES FOR EACH VALUE OF MAKE
Line 556: Line 737:
               END FOR
               END FOR
             END FOR
             END FOR
END  
END
</p>
</p>
====Avoiding printing deleted field values====
<p>To avoid printing deleted field values, you can use the technique illustrated in the following request:</p>
===Avoiding printing deleted field values===
<p>
To avoid printing deleted field values, you can use the technique illustrated in the following request:</p>
<p class="code">BEGIN
<p class="code">BEGIN
CHECK.AGENTS: FOR EACH VALUE OF AGENT
CHECK.AGENTS: FOR EACH VALUE OF AGENT
Line 577: Line 761:
COMMENT:        *COMMENT TO PROVIDE JUMP DESTINATION
COMMENT:        *COMMENT TO PROVIDE JUMP DESTINATION
               END FOR
               END FOR
END  
END
</p>
</p>
<p>See the [[Flow of Control in User Language#JUMP TO statement|JUMP TO statement]] for a more detailed discussion.</p>
<p>
See
the [[Flow of control in User Language#JUMP TO statement|JUMP TO statement]] for a more detailed discussion.</p>
 
</div> <!-- end of toc limit dev -->
 
[[Category:SOUL]]
[[Category:SOUL]]

Latest revision as of 00:26, 24 November 2021

Overview

This topic describes value loops, which retrieve, sort, and process sets of field values.

Note: Because a value loop is driven by a list of values rather than by records, no records are involved until a FIND or FOR EACH RECORD statement is issued within the loop. A field reference (other than in a FIND statement) cannot be used in a value loop unless it is further embedded in a nested FOR EACH RECORD loop, as illustrated in this topic.

To review the use of record loops to retrieve, sort, and process sets of records, see Record loops.

Repetitions of a value loop occur for all distinct values of an FRV (for-each-value) or ORDERED field, including any values referencing records that are logically deleted.

Note: INVISIBLE fields must be explicitly deleted (see INVISIBLE attribute for a discussion of INVISIBLE fields).

FOR EACH VALUE statement

The FOR EACH VALUE statement initiates a loop that is executed once for each unique value of the specified field. It is supported in remote file and scattered group contexts.

The basic syntax is:

label: FOR EACH VALUE OF fieldname

There are several variations of the FOR EACH VALUE statement, as described in this page.

You can use the FOR EACH VALUE statement to:

Task Using...
Sort the retrieved values IN ORDER clause
Specify a range of values to retrieve FROM value1 and TO value2 clauses
Specify a pattern to use to retrieve values pattern clause
Process a sample of values k clause
Process a fraction of the set of retrieved values, for example, every third value Skip processing feature.

Find All Values statement

The Find All Values statement provides an efficient value retrieval method that performs the FIND and SORT operations outside of the value loop. Each Find All Values statement generates a value set header of 48 bytes.

Syntax

The complete syntax for the Find All Values statement in SOUL is as follows:

Where:

    label:[In { File filename | Group groupname } ]
    { Find [All] Values Of | FDV } fieldname
    [From value1 | (expression1)] [To value2 | (expression2)]
    [(Not) Like pattern]
    [(Not) Value In value_set]

  • label is necessary in order to use the result of the statement.
  • filename specifies the file that has the records you want to retrieve.
  • groupname specifies the group of files that has the records you want to retrieve.
  • fieldname identifies the field from which to retrieve values.
  • value1 and value2 specify the beginning and ending range of values to retrieve.
  • pattern specifies a string to match or not match. See the syntax of Is Like patterns.
  • value_set is a label that identifies an existing value set. NOT, which is optional, is the only permissible operator.

    See also Processing a VALUE IN clause for other statements that use a VALUE IN clause.

  • Expressions can be used for value1 and value2, for example:

    Find All Values Of 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

In the following example, the values of ANNIV DATE (0905, 0909, 0913, 0917, 0921, 0925) are found, counted, and printed. Leading zeroes for these values are stripped by FDV. The example also illustrates the use of expressions in the FROM and TO clause of FDV.

begin %i is fixed %mm is string len 2 %dd is string len 2 %mm = '09' %dd1 = '05' %dd2 = '30' fdv1: in clients fdv anniv date - from (%mm with %dd1) to (%mm with %dd2) /?--------------------------------------------?/ /? Print and count the values of anniv date  ?/ /?--------------------------------------------?/ adate: frv in fdv1 print value in adate %i = %i + 1 end for print 'number of values of anniv date = ' with %i fd1: in clients fpc anniv date eq value in fdv1 end find /?----------------------------------?/ /? fd1 above is equivalent to fd2: ?/ /?----------------------------------?/ fd2: in clients fpc anniv date = 0905 OR - anniv date = 0909 or - anniv date = 0913 OR - anniv date = 0917 OR - anniv date = 0921 OR - anniv date = 0925 end find end

The request result is:

905 909 913 917 921 925 number of values of anniv date = 6 72 72

FRV or ORDERED field attribute required

You can only use value loops with fields that have the FRV (For Each Value) or ORDERED attribute. This is because Model 204 maintains a list of all the unique values created for a field with the FRV attribute. For a field with the ORDERED attribute, Model 204 can easily use the Ordered Index to retrieve all values created for the field.

The set of values retrieved differs depending upon the field attribute, as follows:

Field attribute Action
FRV Once a value is added to an FRV field list in Table A, the value remains on the list even if all occurrences of the value are removed from the database. Therefore, the value is retrieved by a FOR EACH VALUE statement.
ORDERED Values are deleted from the Ordered Index only when the last fieldname = value pair with that value is deleted from the file. Only values actually present for a field with the ORDERED attribute are retrieved using the FOR EACH VALUE statement.

To process fields that do not have either the FRV or ORDERED attribute as though they did, see Simulating a FOR EACH VALUE loop.

Rewriting applications to take advantage of VALUE IN clause processing

If your pre-Version 5.1 applications use a nested loop join in User Language, you could rewrite your applications to take advantage of VALUE IN clause processing.

You are likely to see Improved performance for fields with the ORDERED attribute. These improvements, which result in a reduction of overall wall clock time, include:

  • A decrease in disk I/O
  • Decreases in the number of FINDs and BXFINDs, causing an increase in BXNEXTs

Coding example for an FD statement

OPEN CLAIMS90 FILEMGR REDEFINE FIELD POLICY NO (ORD CHAR) OPEN CLIENTS FILEMGR REDEFINE FIELD POLICY NO (ORD CHAR) BEGIN /? FIND ALL CLIENTS WHO MADE CLAIMS ?/ CLAIM.POLICYS: IN CLAIMS90 FDV POLICY NO CLIENT.FD: IN CLIENTS FD POLICY NO EQ VALUE IN CLAIM.POLICYS END FIND FR CLIENT.FD PRINT POLICY NO AND FULLNAME END FOR END

Using value loops

The value loop is particularly useful for counting and printing records that have a given set of values and for detecting control breaks.

This example illustrates the use of a value loop. See Solution using FRV fields to Avoiding printing deleted field values for additional examples illustrating uses for value loops.

BEGIN AGENT.VALUE: FOR EACH VALUE OF AGENT SKIP 2 LINES PRINT 'AGENT: ' AND VALUE IN AGENT.VALUE SKIP 1 LINE MA.CLIENTS: FIND ALL RECORDS FOR WHICH STATE = MASSACHUSETTS RECTYPE = POLICYHOLDER AGENT = VALUE IN AGENT.VALUE END FIND FOR EACH RECORD IN MA.CLIENTS PRINT FULLNAME AT COLUMN 8 END FOR END FOR END

The preceding request results in this output:

AGENT: CASOLA ANCH, HOWARD W BARLEY, BOYD Y FENIG, CHRISTOPHER K JUDKINS, LINWOOD Y KOVACH, SPENCER J OAKES, CHRISTOPHER L OZOLINS, CARRIE M AGENT: GREEN DETWILER, WOODROW Q HELM, HAZEL P ISGRIG, REGINA E JULIN, FRED R NATHAN, JANEL S VIGNOLA, VERDELL D . . .

In the first repetition of the FOR EACH VALUE loop, Model 204 notes a specific value of AGENT that occurs in the file and prints that value. Then it finds all Massachusetts drivers for the noted agent and prints their names. In the next repetition, Model 204 notes a second value of AGENT, prints it, finds all Massachusetts drivers for the agent, and prints their names. This continues until all values of AGENT have been processed.

Sorting retrieved values

Field attributes affect default order

When using the FOR EACH VALUE statement, the default order in which Model 204 returns retrieved values depends on the reference context and the attribute of the value field, according to the following principles:

  • When the FOR EACH VALUE statement refers to an individual file, the default order is determined by the attribute of the value field:
  • Ascending numeric order for numeric values followed by standard EBCDIC collating order for non-numeric values if the field is ORDERED NUMERIC

    Standard EBCDIC order if the field is ORDERED CHARACTER

    Random sequence if the field is FRV

  • When the FOR EACH VALUE statement refers to a group, the default order is also determined by the attributes of the value field in the various files in the group:
  • Ascending numeric if the field is defined as ORDERED NUMERIC in any of the group members

    Otherwise, the standard EBCDIC collating sequence

    See Group operations in User Language for more information on operations on file groups.

FOR EACH VALUE OF statement

If values must be obtained in a different sort sequence, the IN ORDER clause can be appended to the FOR EACH VALUE statement. In group context, this implies that the IN ORDER clause must be used to process values in any order other than standard EBCDIC sequence.

Syntax

FOR EACH VALUE OF fieldname [IN [ASCENDING |DESCENDING] [CHARACTER | NUMERICAL] | |RIGHT-ADJUSTED] ORDER]

Where:

  • ASCENDING and DESCENDING indicate the order in which the value set is sorted. ASCENDING is the default.
  • CHARACTER specifies EBCDIC collating sequence, with fields with null values appearing first. Fields with unequal lengths are left-justified before being sorted. The default is determined as indicated above, except when you are using a field name variable to indicate the field name, and you are working in group context. In this case, CHARACTER is the default.
  • NUMERICAL specifies that non-numeric fields appear first, followed by proper number fields in numerical order.
  • RIGHT-ADJUSTED specifies that fields are right-adjusted, with shorter lengths appearing first. Fields of equal length are in EBCDIC collating sequence.

Usage

The FOR EACH VALUE statement is supported in remote file and scattered group contexts.

Each FOR EACH VALUE OF statement generates a value set header of 48 bytes.

Example

This sample FOR EACH VALUE statement uses the IN ORDER option.

GET.NAMES: FOR EACH VALUE OF AGENT IN DESCENDING ORDER PRINT VALUE IN GET.NAMES END FOR

Performance considerations

Use of the FOR EACH VALUE statement might force an internal sort of the found values, which increases processing time. This section explains these conditions in detail.

Impact of field attributes

For fields with the ORDERED attribute, if only the ASCENDING or DESCENDING option is specified, the Ordered Index is used to return the values.

Fields with the ORDERED attribute and any other options specified require an internal sort of values, which requires more processing time.

In addition, processing time requirements are extended further if an ORDERED NUMERIC field is processed in CHARACTER or RIGHT-ADJUSTED order, because the numeric values must be converted into character strings before the sort can be performed.

A field with the FRV attribute requires an internal sort of the values, which requires more processing time.

Use with groups

Issuing the FOR EACH VALUE statement in reference to a file group always involves an internal sort, and therefore additional processing time. This is true whether values are being processed in default (EBCDIC) order, or in another order as specified in an IN ORDER clause.

Specifying retrieval ranges for values

FROM and TO clause

This form of the FOR EACH VALUE statement can be used to obtain a range of values:

FOR EACH VALUE OF fieldname [FROM value1] [TO value2]

where the FROM and TO clauses specify the range of values to be processed. FROM indicates a beginning value (inclusive); TO indicates an ending value (exclusive).

Intended to be used with character strings

The range specification (FROM value1 TO value2) is intended for use with character strings.

If the range values (value1 and value2) are numeric, the range is still treated as a character string comparison. Thus, this statement:

FOR EACH VALUE OF AGE FROM 1 TO 100

would only use the values 1, 10, and 100. To use ages 1 through 100, declare the AGE field to be ORDERED NUMERIC and use a FIND statement to perform the retrieval.

Use with the IN ORDER option

If an inclusive range is specified along with the IN ORDER option, value1 and value2 must also reflect the specified sort order (ASCENDING or DESCENDING) in order for the loop to be executed.

If ASCENDING order is specified, or the default (ASCENDING) is assumed, value1 must be less than value2. For example:

GET.NAMES: FOR EACH VALUE OF AGENT - FROM 'B' TO 'J' IN ORDER PRINT VALUE IN GET.NAMES END FOR

If DESCENDING order is specified along with a range, value1 must be greater than value2. For example:

GET.NAMES: FOR EACH VALUE OF AGENT - FROM 'J' TO 'B' IN DESCENDING ORDER PRINT VALUE IN GET.NAMES END FOR

Using expressions with FOR EACH VALUE

You can use expressions to provide the values in a FOR EACH VALUE statement.

FOR EACH VALUE OF fieldname FROM (expression1) TO (expression2)

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

Example

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

Specifying retrieval patterns for values

Syntax of the LIKE PATTERN clause

This form of the FOR EACH VALUE statement can be used to obtain a range of values based upon a pattern:

FOR EACH VALUE OF fieldname [NOT] LIKE pattern

Where:

  • fieldname is the name of the field for which values are retrieved. A FOR EACH VALUE retrieval based on a pattern is optimized if the field has the ORDERED attribute.
  • pattern is any valid pattern that can be used in a retrieval condition. For more information on patterns, refer to the syntax of Is Like patterns.

Example

CAS.PREFIX: FOR EACH VALUE OF AGENT LIKE 'CAS*' PRINT VALUE IN CAS.PREFIX END FOR

Pattern matching using the LANGLIKE operator

The SOUL operator LANGLIKE supports parsing and evaluation of patterns according to the tables indicated with the LANGUSER and LANGFILE parameters. (See the "Rocket Model 204 Language Support Summary" or the List of Model 204 parameters and List of Model 204 commands.)

The LANGLIKE syntax is the same as LIKE syntax. If LANGFILE is set to US, then the parsing language is U.S. English, and the LIKE and LANGLIKE operators are equivalent.

  • The LIKE operator employs U.S. English for parsing and the value of LANGFILE for evaluating the pattern.
  • The LANGLIKE operator uses the value of LANGUSER for parsing the pattern and the value of LANGFILE for evaluating the pattern.

The parsing language, LANGUSER, is used for checking the syntax of the pattern and for determining the value of:

  • Special pattern escape character
  • Hexadecimal character
  • Alphabetic character

The evaluation language, LANGFILE, is used to match the pattern against the data. In particular, if a range of characters is defined in the pattern, then the collating sequence is determined by the evaluation language, LANGFILE.

Syntax

The format of the FIND statement used to perform pattern matching is:

FIND [ALL] RECORDS { FOR WHICH | WITH } fieldname IS [NOT] LANGLIKE'pattern'

Where:

The LANGLIKE keyword indicates that pattern is the set of characters to match.

The pattern argument must be enclosed in quotation marks.

Processing a sample of values

FOR k VALUES OF statement

You can process only a sample of values with this statement:

FOR k VALUES OF fieldname [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 VALUES statement.

The FOR k VALUES statement begins a loop that repeats 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 values, the loop is repeated for all the values of the field.

The FOR k VALUES statement is supported in remote file and scattered group contexts.

Use with range specification

The FOR k VALUES statement, combined with the FROM option, allows processing in a neighborhood of a value (to select a number of records associated with values centered around a given focal value). For example, the request shown below searches for the correct spelling of an insurance agent's name. This request uses a %variable. See Using variables and values in computation for a detailed discussion of %variables.

V1: FOR 5 VALUES OF AGENT IN DESCENDING ORDER - FROM GOODRIK %START = VALUE IN V1 END FOR V2: FOR 10 VALUES OF AGENT IN ORDER FROM %START PRINT VALUE IN V2 END FOR

Order of the most recent loop defines the order of the values

The order of the most recent loop defines the order of the values. For example, suppose the AGENT field in the preceding example has the ORDERED CHARACTER attribute. Although the V1 loop has retrieved the values in descending order, the 10 agent names print in ascending order. This is because the most recent loop, V2, specifies only IN ORDER, which defaults to ascending character order.

Retrieving values efficiently

FOR EACH VALUE retrieval

When a FOR EACH VALUE statement appears inside another loop, the portion of the statement that finds the values is executed repeatedly, even if the set values do not change. The internal sort also is repeated for FOR EACH VALUE IN ORDER or in group context. (See Files, groups, and reference context for information about group context.)

Advantage of the Find All Values statement

By moving the FIND and SORT out of the loop, the cost of the inner FOR EACH VALUE loop can be reduced.

Syntax

This statement performs the FIND portion of the FOR EACH VALUE statement:

label: FIND [ALL] VALUES OF fieldname [FROM value1] [TO value2] [ [NOT] LIKE 'pattern']

The Find All Values statement is supported in remote file and scattered group contexts.

Find All Values options

Like other FIND statements, a range of values can be specified for the Find All Values statement by using the FROM and TO clauses.

In addition, value selection can be made based upon a pattern by using the LIKE clause.

You can use the IN GROUP MEMBER clause to restrict the FIND to one member of a group. See IN GROUP MEMBER clause for more information on using it.

No block end required

Unlike other FIND statements, the Find All Values statement must not have a block end statement because Find All Values does not accept multiple conditions.

Sorting retrieved values

The Find All Values statement returns values in the default order.

If a different order is required, the SORT VALUES statement must be used. For a discussion, refer to SORT VALUES statement. If a Find All Values statement appears in group context, a sort is generated automatically.

Processing retrieved values

The value set resulting from a Find All Values statement can be processed with this statement:

FOR EACH VALUE IN label

For example, the following request finds a selected range of agents in the CLIENTS file:

BEGIN FIND.RECS: FIND ALL VALUES OF AGENT - FROM JACOBS TO LESTER PRINT.INFO: FOR EACH VALUE IN FIND.RECS PRINT VALUE IN PRINT.INFO END FOR END

The example below shows nested FOR EACH VALUE loops. In the example, the constant portion of the processing for COUNTY is executed once instead of fifty times. The CITY names are looked up once instead of hundreds or thousands of times.

EACH.COUNTY: FIND ALL VALUES OF COUNTY EACH.CITY: FIND ALL VALUES OF CITY EACH.STATE: FOR EACH VALUE OF STATE FOR EACH VALUE IN EACH.COUNTY FOR EACH VALUE IN EACH.CITY . . .

If new values are to be added within the body of an FRV loop, it might be incorrect to separate the FIND. The FIND must be re-executed to allow the new values to be processed by the loop. In this case, the FOR EACH VALUE OF statement should be used to process the values.

SORT VALUES statement

If you need special ordering of the value set found by the Find All Values statement, use the SORT VALUES statement.

The SORT VALUES statement can refer to any Find All Values statement. The sorted value set can then be processed with the FOR EACH VALUE IN statement.

The Find All Values statement is described in the previous section.

Syntax

The format of the SORT VALUES statement is:

SORT VALUES IN label [IN [ASCENDING | DESCENDING] [CHARACTER | NUMERICAL] | [RIGHT-ADJUSTED] ORDER]

Where the sort options are the same as those that can be specified for the FOR EACH VALUE statement (see FOR EACH VALUE OF statement).

The SORT VALUES statement is supported in remote file and scattered group contexts.

Locating records missing a particular field

The IS NOT PRESENT retrieval condition, described in Numeric range retrievals, generally can be used to locate records that do not have a particular field.

However, there are times when you do not want to use IS NOT PRESENT because it requires a direct search of the data records which could result in a very large number of records being searched.

Solution using FRV fields

In such cases the following technique can be used if the field specified in the FOR EACH VALUE statement has the FRV attribute. This technique, however, increases processing overhead if the FRV field has many values.

BEGIN ALL.RECS: FIND ALL RECORDS END FIND PLACE RECORDS IN ALL.RECS ON LIST NOAGENT EACH.AGENT: FOR EACH VALUE OF AGENT AGENT.VAL: FIND ALL RECORDS ON LIST NOAGENT FOR WHICH AGENT = VALUE IN EACH.AGENT END FIND REMOVE RECORDS IN AGENT.VAL FROM LIST NOAGENT END FOR SET HEADER 2 'RECORDS WITHOUT AN AGENT FIELD' NEW PAGE FOR EACH RECORD ON LIST NOAGENT SKIP 1 LINE PRINT ALL INFORMATION END FOR END

Solution for non-FRV fields

The problem of locating records that do not have a particular field also can be solved for a non-FRV field. In the code fragment below, the ZIP.MATCH statement removes records from the list as values are found. This avoids unnecessary processing of records with values that already have been found.

BEGIN ALL.RECS: FIND ALL RECORDS END FIND PLACE RECORDS IN ALL.RECS ON LIST NOZIP FOR EACH RECORD ON LIST NOZIP ZIP.VAL: NOTE ZIP ZIP.MATCH: FIND ALL RECORDS ON LIST NOZIP FOR WHICH ZIP = VALUE IN ZIP.VAL END FIND REMOVE RECORDS IN ZIP.MATCH FROM LIST NOZIP

Creating control breaks

A control break is a change in value of a field from one set of records to another that must be accompanied by some special processing.

Each pass through a value loop corresponds to a control break. Statements that perform the special processing are placed within the body of the loop and are executed automatically with each value change.

Using value loops for taking control breaks

The following request illustrates the use of value loops for taking control breaks. This request adds together the premium amounts from individual records and prints the total amount for each automobile model. (%variables, which are illustrated in this request, are discussed in detail in Using variables and values in computation.)

BEGIN MODEL.VALUE: FOR EACH VALUE OF MODEL MODEL: FIND ALL RECORDS FOR WHICH MODEL = VALUE IN MODEL.VALUE END FIND FOR EACH RECORD IN MODEL %TOTAMT = VEHICLE PREMIUM + %TOTAMT END FOR PRINT VALUE IN MODEL.VALUE - WITH %TOTAMT TO COLUMN 30 %TOTAMT = 0 END FOR END

The resulting output would be:

BEETLE 8053 RAMBLER 0 MAVERICK 57549 COLT 29350 . . . . . .

In the preceding example, RAMBLER represents a value of the MODEL field that used to appear in the file but no longer occurs in any records. However, it still is retrieved as a value because the MODEL field has the FRV attribute.

Simulating a FOR EACH VALUE loop

You cannot process all fields with the FOR EACH VALUE statement. Occasionally you must process non-FRV and non-ORDERED fields by value.

Simulating a value loop for a non-FRV field

The following technique simulates a value loop for a field that is not multiply occurring. (See Processing multiply occurring fields and field groups for detailed information on simulating a value loop for a multiply occurring field.)

Assume that MODEL is a non-FRV field:

BEGIN ALL: FIND ALL RECORDS END FIND MAKE.LIST: PLACE RECORDS IN ALL ON LIST VLOOP FOR EACH RECORD ON LIST VLOOP MODEL: NOTE MODEL MODEL.MATCH: FIND ALL RECORDS ON LIST VLOOP FOR WHICH MODEL = VALUE IN MODEL END FIND MODEL.CT: COUNT RECORDS IN MODEL.MATCH PRINT VALUE IN MODEL AND COUNT IN MODEL.CT REMOVE RECORDS IN MODEL.MATCH FROM LIST VLOOP END FOR END

The REMOVE RECORDS statement eliminates value redundancies by removing all records with the current value from the list.

Note that in using this technique, only those records which have a MODEL field are processed.

Usage guidelines

The previous technique is useful when you must process a list of records representing a portion of a file or group by value. The ALL statement could be replaced by one or more selection statements, with the MAKE.LIST statement producing the final list.

If the number of values occurring on the record list is less than the number of values occurring in the entire file or group, the simulated FOR EACH VALUE loop is more efficient. This is true even for an FRV field, because the inner FIND statement is executed fewer times.

Simulating an FRV loop on a non-ORDERED field

The following request simulates a FOR EACH VALUE loop on the STATE field in sorted order:

BEGIN ALL: FIND ALL RECORDS END FIND PLACE RECORDS IN ALL ON LIST X FOR EACH RECORD ON LIST X PLACE RECORD ON LIST Z X.STATE: NOTE STATE X.STATE.MATCH FIND ALL RECORDS ON LIST X FOR WHICH STATE = VALUE IN X.STATE END FIND REMOVE RECORDS IN X.STATE.MATCH FROM LIST X END FOR SORTED.Z: SORT RECORDS ON LIST Z BY STATE FOR EACH RECORD IN SORTED.Z PRINT STATE END FOR END

Printing field values

A frequent use of the FOR EACH VALUE statement is to print all the values of a field for the entire file, for example, the MODEL field in the EACH.MODEL statement below:

EACH.MODEL: FOR EACH VALUE OF MODEL PRINT VALUE IN EACH.MODEL END FOR

Field names not included

This type of PRINT statement can contain format information but not field names; the FOR EACH VALUE statement could not have been followed by:

PRINT VALUE IN EACH.MODEL AND MAKE

Setting up a value loop on one field and printing a value of another

To set up a value loop on one field and then print the value of another, use a FIND statement and a nested FOR EACH RECORD loop, as illustrated in the following example:

BEGIN MAKE.VALUE: IN VEHICLES FOR EACH VALUE OF MAKE MAKE: FIND ALL RECORDS FOR WHICH MAKE = VALUE IN MAKE.VALUE END FIND SKIP 1 LINE PRINT VALUE IN MAKE.VALUE PRINT '-----------' FOR EACH RECORD IN MAKE PRINT MODEL END FOR END FOR END

Avoiding printing deleted field values

To avoid printing deleted field values, you can use the technique illustrated in the following request:

BEGIN CHECK.AGENTS: FOR EACH VALUE OF AGENT MASS.CLIENTS: FIND ALL RECORDS FOR WHICH STATE = 'MASSACHUSETTS' AGENT = VALUE IN CHECK.AGENTS END FIND CT: COUNT RECORDS IN MASS.CLIENTS IF COUNT IN CT EQ 0 THEN JUMP TO COMMENT END IF SKIP 1 LINE PRINT 'AGENT ' WITH VALUE IN CHECK.AGENTS FOR EACH RECORD IN MASS.CLIENTS PRINT FULLNAME END FOR COMMENT: *COMMENT TO PROVIDE JUMP DESTINATION END FOR END

See the JUMP TO statement for a more detailed discussion.