Value loops: Difference between revisions

From m204wiki
Jump to navigation Jump to search
(/* FROM and TO clause; added inclusive to FROM and exclusive to TO)
 
(24 intermediate revisions by 3 users not shown)
Line 1: Line 1:
<div class="toclimit-3">
==Overview==
==Overview==
<p>
<p>
This topic describes value loops, which retrieve, sort, and process sets of field values.</p>
This topic describes value loops, which retrieve, sort, and process sets of field values.
<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 topic.</p>
</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>
<p>
To review the use of record loops to retrieve, sort, and process sets of records, see [[Record Loops#Record Loops|Record Loops]].</p>
To review the use of record loops to retrieve, sort, and process sets of records, see [[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>
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> INVISIBLE fields must be explicitly deleted (see [[Field Attributes#INVISIBLE attribute|INVISIBLE attribute]] for a discussion of INVISIBLE fields). </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===
===FOR EACH VALUE statement===
<p>
<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>
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>
<p>
The basic syntax is:</p>
The basic syntax is:</p>
Line 17: Line 20:
</p>
</p>
<p>
<p>
There are several variations of the FOR EACH VALUE statement, as described in this page.</p>
There are several variations of the <var>FOR EACH VALUE</var> statement, as described in this page.</p>
<p>
<p>
You can use the FOR EACH VALUE statement to:</p>
You can use the <var>FOR EACH VALUE</var> statement to:</p>
<table>
<table>
<tr class="head">
<tr class="head">
Line 48: Line 51:
<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===
===Find All Values statement===
<p>
<p>
The <var>FIND ALL VALUES</var> statement provides an efficient value retrieval method that performs the FIND and SORT operations outside of the value loop. Each <var>FIND ALL VALUES</var> statement generates a value set header of 48 bytes.</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====
====Syntax====
<p>
<p>
A complete <var>FIND ALL VALUES</var> statement format in SOUL is as follows:</p>
The complete syntax for the <var>Find All Values</var> statement in SOUL is as follows:</p>
<p class="syntax"><span class="term">label</span>: FIND ALL VALUES
 
<span class="squareb">[</span>IN <span class="squareb">{ </span>FILE <span class="term">filename</span> <span class="squareb">|</span> GROUP <span class="term">groupname</span> <span class="squareb">}</span> <span class="squareb">]</span> -


FIND ALL VALUES 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> <span class="squareb">[</span>(NOT) LIKE <span class="term">pattern</span><span class="squareb">]</span> -
<span class="squareb">[</span>(NOT) VALUE IN <span class="term">value_set</span><span class="squareb">]</span>
</p>
<p>
<p>
Where:</p>
Where:</p>
<ul>
<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">filename</var> specifies the file that has the records you want to retrieve.</li>
</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">groupname</var> specifies the group of files that has the records you want to retrieve.</li>
</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>


<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> and <var class="term">value2</var> specify the beginning and ending range of values to retrieve.</li>
</li>


<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>
<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>
</li>


<li>The <var class="term">value_set</var> label represents an existing value set. Values of the value set in the <var>EQ VALUE IN <i>value_set</i></var> clause are treated as boolean OR, for example:</li>
<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 class="code"><i>field</i> = <i>value1</i> OR <i>field</i> = <i>value2</i> OR....
</p>
<p>
<p>
The <var>NE VALUE IN <i>value_set</i></var> clause is likewise treated as boolean AND, for example:</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>
<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>Expressions can be used for <var class="term">value1</var> and <var class="term">value2</var>, for example:</li>
<li>Expressions can be used for <var class="term">value1</var> and <var class="term">value2</var>, for example:
<p class="code">FIND ALL VALUES OF <i>fieldname</i> FROM (<i>expression1</i>) TO (<i>expression2</i>)
<p class="code">Find All Values Of <i>fieldname</i> From (<i>expression1</i>) To (<i>expression2</i>)
</p>
</p>
<p>
<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, User Language construct, or Boolean expression. </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>
====Example====
====Example====
<p class="code">B
<p>
%MM IS STRING LEN 2
In the following example, the values of <code>ANNIV DATE</code> (0905, 0909, 0913, 0917, 0921, 0925) are found, counted, and printed.
%DD IS STRING LEN 2
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>
%MM = '09'
 
%DD1 = '05'
<p class="code">begin
%DD2 = '30'
%i    is fixed 
PRINT 'FR1'
%mm    is string len 2  
FDV1:
%dd    is string len 2  
IN CLIENTS FIND ALL VALUES OF ANNIV DATE -
%mm = '09'
  FROM (%MM WITH %DD1) TO (%MM WITH %DD2)
%dd1 = '05'  
FD1:
%dd2 = '30'  
IN CLIENTS FPC ANNIV DATE = VALUE IN FDV1
fdv1: in clients fdv anniv date -  
          END FIND
      from (%mm with %dd1) to (%mm with %dd2)  
FR FD1
/?--------------------------------------------?/
   PAI
/?  Print and count the values of anniv date  ?/
   PRINT
/?--------------------------------------------?/         
END FOR
adate: frv in fdv1       
END
        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===
===FRV or ORDERED field attribute required===
<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>
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>
<p>
The set of values retrieved differs depending upon the field attribute, as follows</p>
The set of values retrieved differs depending upon the field attribute, as follows:</p>
<table>
<table>
<tr class="head">
<tr class="head">
Line 146: Line 158:
</table>
</table>
   
   
To process fields without either the FRV or ORDERED attribute as though they did, see See [[#Simulating a FOR EACH VALUE loop]].
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]].
   
   
====Rewriting applications to take advantage of VALUE IN clause processing====
====Rewriting applications to take advantage of VALUE IN clause processing====
Line 177: Line 189:
END
END
</p>
</p>
 
===Using value loops===
===Using value loops===
<p>
<p>
Line 247: Line 259:
Otherwise, the standard EBCDIC collating sequence</p>
Otherwise, the standard EBCDIC collating sequence</p>
<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>
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>


Line 314: Line 326:
</p>
</p>
<p>
<p>
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; <var>TO</var> indicates an ending value.</p>
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>


===Intended to be used with character strings===
===Intended to be used with character strings===
Line 373: Line 385:
<p>
<p>
This form of the FOR EACH VALUE statement can be used to obtain a range of values based upon a pattern:                            </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 <span class="term">fieldname</span> <var class="squareb">[</span>NOT<span class="squareb">]</span> LIKE <span class="term">pattern</span>
<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>
   
   
Line 393: Line 405:
===Pattern matching using the LANGLIKE operator===
===Pattern matching using the LANGLIKE operator===
<p>
<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
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
"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>
[[List of Model 204 parameters]] and [[List of Model 204 commands]].)</p>
<p>
<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>
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>
<p>
The parsing language, LANGUSER, is used for checking the syntax of the pattern and for determining the value of:</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 415: Line 428:
</ul>
</ul>
<p>
<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>
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>
   
   
====Syntax====
====Syntax====
<p>
<p>
The format of the FIND statement used to perform pattern matching is:</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
<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 <var>[</var>NOT<var>]</var> LANGLIKE'pattern'
     IS <span class="squareb">[</span>NOT<span class="squareb">]</span> LANGLIKE'<span class="term">pattern</span>'
</p>
</p>
   
   
Where:
Where:
<p>
<p>
The LANGLIKE keyword indicates that pattern is the set of characters to match.</p>
The <var>LANGLIKE</var> keyword indicates that <var class="term">pattern</var> is the set of characters to match.</p>
<p>
<p>
The pattern argument must be enclosed in quotation marks. </p>
The <var class="term">pattern</var> argument must be enclosed in quotation marks. </p>


==Processing a sample of values==
==Processing a sample of values==
Line 436: Line 449:
<p>
<p>
You can process only a sample of values with this 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 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>
   
   
Where:
Where:
<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>
<p>
The FOR k VALUES statement begins a loop that repeats up to k times, depending on the statement: </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>
<p>
The FOR k VALUES statement is supported in remote file and scattered group contexts.</p>
The <var>FOR k VALUES</var> statement is supported in remote file and scattered group contexts.</p>


===Use with range specification===
===Use with range specification===
<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>
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 471: Line 484:
===Order of the most recent loop defines the order of the values===
===Order of the most recent loop defines the order of the values===
<p>
<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>
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>
 
==Retrieving values efficiently==
==Retrieving values efficiently==
   
   
===FOR EACH VALUE retrieval===
===FOR EACH VALUE retrieval===
<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>
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===
===Advantage of the Find All Values statement===
<p>
<p>
By moving the FIND and SORT out of the loop, the cost of the inner FOR EACH VALUE loop can be reduced.</p>
By moving the FIND and SORT out of the loop, the cost of the inner FOR EACH VALUE loop can be reduced.</p>
Line 485: Line 498:
<p>
<p>
This statement performs the FIND portion of the FOR EACH VALUE statement:</p>
This statement performs the FIND portion of the FOR EACH VALUE statement:</p>
<p class="code">label: FIND <var>[</var>ALL<var>]</var> VALUES OF fieldname
<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>
<p>
The FIND ALL VALUES statement is supported in remote file and scattered group contexts.</p>
The Find All Values statement is supported in remote file and scattered group contexts.</p>
   
   
===FIND ALL VALUES options===
===Find All Values options===
<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>
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>
<p>
In addition, value selection can be made based upon a pattern by using the LIKE clause.</p>
In addition, value selection can be made based upon a pattern by using the LIKE clause.</p>
<p>
<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>
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>
   
   
===No block end required===
===No block end required===
<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>
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===
===Sorting retrieved values===
<p>
<p>
The FIND ALL VALUES statement returns values in the default order. </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>
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===
===Processing retrieved values===
<p>
<p>
The value set resulting from a FIND ALL VALUES statement can be processed with this statement:</p>
The value set resulting from a Find All Values statement can be processed with this statement:</p>
<p class="code">FOR EACH VALUE IN label
<p class="syntax">FOR EACH VALUE IN <span class="term">label</span>
</p>
</p>
<p>
<p>
For example, the following request finds a selected range of agents in the CLIENTS file:</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 526: Line 539:
</p>
</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>
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 537: Line 550:
</p>
</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>
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>
 
==SORT VALUES statement==
==SORT VALUES statement==
<p>
<p>
If you need special ordering of the value set found by the FIND ALL VALUES statement, use the SORT VALUES statement.    </p>
If you need special ordering of the value set found by the Find All Values statement, use the SORT VALUES statement.    </p>
<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>
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>
<p>
The FIND ALL VALUES statement is described in the previous section.</p>
The Find All Values statement is described in the previous section.</p>
<b>Syntax</b>
 
====Syntax====
<p>
<p>
The format of the SORT VALUES statement is:</p>
The format of the SORT VALUES statement is:</p>
<p class="code">SORT VALUES IN label [IN [ASCENDING | DESCENDING]
<p class="syntax">SORT VALUES IN <span class="term">label</span> [IN [ASCENDING | DESCENDING]
   
   
  [CHARACTER | NUMERICAL] | [RIGHT-ADJUSTED] ORDER]
  [CHARACTER | NUMERICAL] | [RIGHT-ADJUSTED] ORDER]
Line 557: Line 571:
<p>
<p>
The SORT VALUES statement is supported in remote file and scattered group contexts.</p>
The SORT VALUES statement is supported in remote file and scattered group contexts.</p>
 
==Locating records missing a particular field==
==Locating records missing a particular field==
<p>
<p>
Line 566: Line 580:
===Solution using FRV fields===
===Solution using FRV fields===
<p>
<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>
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 585: Line 599:
END
END
</p>
</p>
 
===Solution for non-FRV fields===
===Solution for non-FRV fields===
<p>
<p>
Line 609: Line 623:
===Using value loops for taking control breaks===
===Using value loops for taking control breaks===
<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>
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 643: Line 657:
===Simulating a value loop for a non-FRV field===
===Simulating a value loop for a non-FRV field===
<p>
<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>
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>
<p>
Assume MODEL is a non-FRV field:</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 671: Line 685:
<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>
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===
===Simulating an FRV loop on a non-ORDERED field===
<p>
<p>
Line 751: Line 765:
<p>
<p>
See
See
  the [[Flow of Control in User Language#JUMP TO statement|JUMP TO statement]] for a more detailed discussion.</p>
  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.