Processing multiply occurring fields and field groups: Difference between revisions

From m204wiki
Jump to navigation Jump to search
 
(106 intermediate revisions by 7 users not shown)
Line 1: Line 1:
===Overview===
<div class="toclimit-4">
<p>A single field name that has different values can be stored repeatedly in a record. For example, the field CHILD in the record shown below is an example of a multiply occurring field:</p>
 
==Overview==
<p>
A single field name that has different values can be stored repeatedly in a record. For example, the field <code>CHILD</code> in the record shown below is an example of a <b>multiply occurring field</b>:</p>
<p class="code">FATHER = JOHN DOE
<p class="code">FATHER = JOHN DOE
CHILD = ELIZABETH
CHILD = ELIZABETH
CHILD = ROBERT  
CHILD = ROBERT
</p>
</p>
<p>Any field name except for the following types of fields can be present more than once in a record: </p>
<p>
Any field name except for the following types of fields can be present more than once in a record: </p>
<ul>
<ul>
<li>A NUMERIC RANGE field </li>
<li>A <var>NUMERIC RANGE</var> field </li>
<li>A sort key </li>
<li>A sort key </li>
<li>A hash key       </li>
<li>A hash key </li>
</ul>
</ul>
====Special processing for multiply occurring fields====
<p>Certain User Language statements operate differently on multiply occurring fields than on singly occurring fields. User Language provides special statements and forms of statements that can be used with multiply occurring fields--specifically introducing the EACH modifier and subscripts. User Language also provides for the use of subscripted field references for accessing a particular occurrence of a multiply occurring field.</p>
<div id="FIELDGROUP overview"></div> <!-- This id referenced off the page, e.g., #REDIRECT on FIELDGROUP operations page-->
===FIND statement===
 
====Retrieval====
JOHN DOE might also have a medical record that recorded multiple surgeries, each of which is associated with a surgeon and a date. His record contains repeated instances of these three related fields:
<b>Example 1</b>
<p class="code">SURGERY_TYPE
<p>Assume that we are using this sample record: </p>
SURGEON
SURGERY_DATE </p>
 
As of version 7.5 of <var class="product">Model&nbsp;204</var>, you can define these fields as part of a "physical" <b>field group</b>, which means they are stored, retrieved, and updated as a single instance of, say, the <code>SURGERY</code> field group. You could define these fields as individual multiply occurring fields and group them logically in your SOUL code, but the physical field group structure has significant processing efficiencies.
 
Field groups may only be defined for records in files that have the <var>[[FILEORG parameter|FILEORG]]</var> parameter set to include <code>X'100'</code>. How to define a field group is described in [[Field group design#Defining a field group|Defining a field group]].
 
The remaining sections on this page describe how to use the basic and the special SOUL statements that operate on multiply occurring fields and field groups.
 
==Fields in the FIND and SORT statements==
====Retrieval by an exact occurrence value====
<p>
Assume this sample record: </p>
<p class="code">FATHER = JOHN DOE
<p class="code">FATHER = JOHN DOE
CHILD = ELIZABETH
CHILD = ELIZABETH
CHILD = ROBERT  
CHILD = ROBERT
</p>
</p>
<p>You can use either of these two statements to retrieve the record:    </p>
<p>
You can use either of these two statements to retrieve the record:    </p>
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
               CHILD = ELIZABETH
               CHILD = ELIZABETH
           END FIND  
           END FIND
 
FIND.RECS: FIND ALL RECORDS FOR WHICH
FIND.RECS: FIND ALL RECORDS FOR WHICH
               CHILD = ROBERT
               CHILD = ROBERT
           END FIND  
           END FIND
</p>
</p>
<b>Example 2</b>
<p>
<p>The record is not retrieved by either of these statements:</p>
The record is <i>not</i> retrieved by either of these statements:</p>
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
               CHILD = NOT ELIZABETH
               CHILD = NOT ELIZABETH
           END FIND
           END FIND
 
FIND.RECS: FIND ALL RECORDS FOR WHICH
FIND.RECS: FIND ALL RECORDS FOR WHICH
               CHILD = NOT ROBERT
               CHILD = NOT ROBERT
           END FIND  
           END FIND
</p>
</p>
====Multi-condition range retrievals====
====Multi-condition range retrievals====
<p>You should pay special attention to the effect of multiply occurring fields on multi-condition range retrievals. Undesirable results can occur if a range search is specified for a multiply occurring field. For example:   </p>
<p>
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
You should pay special attention to the effect of multiply occurring fields on multi-condition range retrievals. Undesirable results can occur if a range search is specified for a multiply occurring field. For example, this <var>FIND</var> retrieves the sample record even though neither child's name in the sample is between <code>KEN</code> and <code>PAUL</code>: </p>
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
               CHILD IS AFTER KEN AND BEFORE PAUL
               CHILD IS AFTER KEN AND BEFORE PAUL
           END FIND  
           END FIND
</p>
</p>
<p>retrieves the sample record even though neither child's name in the sample is between KEN and PAUL. <var class="product">Model&nbsp;204</var> evaluates each condition of the FIND separately, then combines the results of each evaluation to build the set of records satisfying all conditions. The BETWEEN operator behaves exactly like any other multi-condition range retrieval when used on a multiply occurring field.</p>
<p>
<p>If a range search must be performed on a multiply occurring field, use the IN RANGE clause of the FIND statement. Refer to [[Record retrievals#NUMERIC RANGE and ORDERED NUMERIC attributes|NUMERIC RANGE and ORDERED NUMERIC attributes]] for more information about the IN RANGE clause. Better code for the previous example would be:               </p>
<var class="product">Model&nbsp;204</var> evaluates each condition of the FIND separately, then combines the results of each evaluation to build the set of records satisfying all conditions. The <var>BETWEEN</var> operator behaves exactly like any other multi-condition range retrieval when used on a multiply occurring field.</p>
<p>
If a range search must be performed on a multiply occurring field, use the <var>IN RANGE</var> clause of the <var>FIND</var> statement. Refer to [[Record retrievals#NUMERIC RANGE and ORDERED NUMERIC attributes|NUMERIC RANGE and ORDERED NUMERIC attributes]] for more information about the <var>IN RANGE</var> clause. Better code for the previous example is: </p>
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
               CHILD IS ALPHA IN RANGE  
               CHILD IS ALPHA IN RANGE
               AFTER KEN AND BEFORE PAUL
               AFTER KEN AND BEFORE PAUL
           END FIND  
           END FIND
</p>
</p>
====Use of subscripts====
====Use of subscripts====
<p>You cannot use subscripted references with the FIND statement. Refer to [[#Subscripts|Subscripts]] for more information about using subscripts with multiply occurring fields.</p>
<p>
You cannot use subscripted references with the <var>FIND</var> statement. Refer to [[#Subscripts|Subscripts]] for more information about using subscripts with multiply occurring fields.</p>
===SORT RECORDS statement===
<p>
When a multiply occurring field is chosen as a sort key, each record in the set being sorted is processed once using the first occurrence of the field as the key. </p>
====EACH modifier with one sort field====
<p>
If the <var>EACH</var> modifier is present in the <var>SORT</var> statement, and if there are <var class="term">n</var> occurrences of the field in the record, similar records are created in the sorted copy of the original set. </p>
=====Example=====
<p>
The following statement:</p>
<p class="code">SORT RECORDS IN FIND.RECS BY EACH KEY
</p>
<p>
generates three temporary records for the single permanent record in which the field named <code>KEY</code> occurs three times:</p>
<p class="code">KEY = COMPUTER
KEY = CORPORATION
KEY = AMERICA
</p>
<p>
The records generated are:</p>
<p class="output">1 KEY = COMPUTER          2 KEY = CORPORATION
  KEY = CORPORATION        KEY = AMERICA
  KEY = AMERICA            KEY = COMPUTER
3 KEY = AMERICA
  KEY = COMPUTER
  KEY = CORPORATION
</p>
<p>
These correspond to the <var class="term">n</var> cyclic permutations of the set of field occurrences and, in this example, are sorted into the order 3, 1, 2 (if no other option is selected). Statements that refer to the sorted set, such as <var>PRINT</var>, <var>PRINT EACH</var>, <var>NOTE</var>, and <var>PRINT ALL INFORMATION</var> (<var>PAI</var>), reflect the permutation. No record is generated if <var class="term">n</var> is 0.</p>
 
====EACH modifier with several key fields====
<p>
When the <var>EACH</var> option is selected for several keys, <var class="term">n</var> occurrences of one key and <var class="term">m</var> of another produce differently permuted records. If either <var class="term">n</var> or <var class="term">m</var> equal 0, no records are generated. </p>
=====Example=====
<p>
For this request:</p>
<p class="code">BEGIN
FIND.RECS: IN CLIENTS FIND ALL RECORDS FOR WHICH
              INCIDENT = T1
          END FIND
SORT.RECS: SORT RECORDS IN FIND.RECS BY FULLNAME -
              AND EACH INCIDENT DATE
          FOR EACH RECORD IN SORT.RECS
              PRINT FULLNAME WITH INCIDENT DATE  -
                AT COLUMN 25
          END FOR
END
</p>
<p>
The printed output is:</p>
<p class="output">ABBOTT, FRANKLIN G    860323
ABBOTT, GAIL H        861022
ABRAMS, RUTH Z        861115
ABRAMS, RUSSELL Y      870218
ABBOTT, FRANKLIN G    870424
ABBOTT, GAIL H        871123
      .                  .
      .                  .
      .                  .
</p>
 
====If no occurrences are present====
<p>
A record that does not contain at least one occurrence of the <code>INCIDENT DATE</code> field produces no printed output. Similarly, the following statement produces nothing for those records that do not have at least one occurrence of both <code>A</code> and <code>B</code>: </p>
<p class="code">SORT RECORDS IN FIND.RECS BY EACH A AND EACH B
</p>
 
==Fields inside record loops==
This section describes by SOUL statement aspects of working with fields in a record loop (such as <var>For Each Record</var> or <var>For Record</var> context. Certain statements operate differently on multiply occurring fields than on singly occurring fields.
 
<p>
SOUL provides several statements for handling multiply occurring fields and [[Field group design|field groups]]. Field groups are supported as of Model 204 version 7.5. While all these statements can also be used with singly occurring fields, they are most useful for multiply occurring fields. They are:</p>
<ul>
<li><var>COUNT OCCURRENCES</var> (used with singly occurring fields, too)</li>
<li><var>FOR EACH OCCURRENCE</var> (used with singly occurring fields, too)</li>
<li><var>DELETE EACH OCCURRENCE</var></li>
</ul>
<p class="note"><b>Note:</b> See also the [[#INSERT statement|INSERT statement]], which you can use to add new occurrences of a field.</p>
===Field references in expressions===
<p>
A field's value can be references in a SOUL expression simply by specifying the field name with an optionl subscript in parentheses. If no subscript is specified, occurrence 1 is assumed. For example, the following assigns the value of field <var>Foo</var> to <var>%foo</var> and adds the first two occurrences of field <var>bar</var> to stringlist <var>%sl</var>:
</p>
<p class="output">%foo      is string len 31
%sl      is object stringlist
...
for each record in %recordset
  %foo = foo
  %sl = new
  %sl:add(bar(1))
  %sl:add(bar(2))
  ...
end for
</p>
 
===NOTE statement===
===NOTE statement===
====Only first occurrence is noted====
====Only first occurrence is noted====
<p>The NOTE statement notes only the first occurrence of a multiply occurring field. For example, only the first occurrence of the field (CHILD = ELIZABETH) are noted by:</p>
<p>
<p class="code">KEEP.CHILD: NOTE CHILD  
The <var>NOTE</var> statement notes only the first occurrence of a multiply occurring field. For example, only the first occurrence of the field (<code>CHILD = ELIZABETH</code>) are noted by:</p>
<p class="code">KEEP.CHILD: NOTE CHILD
</p>
</p>
====Use of subscripts====
====Use of subscripts====
<p>In order to note a particular occurrence of a multiply occurring field, the field name must be subscripted. Refer to the discussion of subscripts on [[#Subscripts|Subscripts]] for detailed information on subscripted field names and usage.</p>
<p>
===PRINT and PRINT n statements===
In order to note a particular occurrence of a multiply occurring field, the field name must be subscripted. Refer to [[#Subscripts|Subscripts]] for detailed information on subscripted field names and usage.</p>
====PRINT statement output format====
<p>The  PRINT statement prints only the first occurrence of a field in a record. </p>
===<b id="PRINT and PRINT n statements"></b>Print and Print <i>n</i> statements for fields===
<p>If there is more than one value of a field in a record, the special modifier, EACH, can be used in a PRINT statement to print out all the values on a single line, with a single space between values.</p>
<b>Example 1</b>
====Print statement====
<p class="code">PRINT EACH INCIDENT
<p>
The  <var>Print</var> statement prints only the first occurrence of a field in a record. </p>
<p>
If there is more than one value of a field in a record, you can use the modifier <var>Each</var> in a <var>Print</var> statement to print out all the values on a single line, with a single space between values.</p>
=====Example 1=====
<p class="code">print each incident
</p>
</p>
<p>yields:</p>
<p>
<p class="code">T1 T2 T1 T3 T2 T1  
yields:</p>
<p class="output">T1 T2 T1 T3 T2 T1
</p>
</p>
<b>Example 2</b>
<p>If the field is given a column position, as in:</p>
=====Example 2=====
<p class="code">PRINT FULLNAME WITH EACH INCIDENT AT COLUMN 18  
<p>
If the field is given a column position, as in:</p>
<p class="code">print fullname with each incident at column 18
</p>
</p>
<p>values are printed one to a line and positioned at the column specified. </p>
<p>
<p class="code">ABBOTT, GAIL H  T1                      -
values are printed one to a line and positioned at the column specified. </p>
<p class="output">ABBOTT, GAIL H  T1                      -
                 T3                      -
                 T3                      -
                 T1                      -
                 T1                      -
                 T3                      -
                 T3                      -
                 T2                      -
                 T2                      -
                 T1
                 T1  
</p>
   
   
=====Example 3=====
<p>
A field cited in a <var>Print</var> statement after a multiply occurring field is printed on the same line as the last value of the multiply occurring field. If you change the <var>Print</var> statement in the sample request as here:</p>
<p class="code">print fullname with each incident at column 18    -
      with policy no at column 23                -
      with state at column 32
</p>
</p>
<b>Example 3</b>
<p>
<p>A field cited in a PRINT statement after a multiply occurring field is printed on the same line as the last value of the multiply occurring field. If you were to change the PRINT statement in the sample request as here:</p>
Output like this results:</p>
<p class="code">PRINT FULLNAME WITH EACH INCIDENT AT COLUMN18 -
<p class="output">ABBOTT, GAIL H  T1                              -
      WITH POLICY NO AT COLUMN 23 -
                T3                              -
      WITH STATE AT COLUMN 32
                T1                              -
                T3                              -
                T2                              -
                T1  100340  CALIFORNIA
</p>
</p>
<p>this output results:</p>
 
<p class="code">ABBOTT, GAIL H T1                          -
=====Use of Print with subscripts=====
T3                                        -
<p>
T1                                        -
See the [[#PRINT statement rules|Print statement subscript rules]] for a description of using <var>Print</var> with field subscripts.</p>
T3                                        -
 
T2                                        -
====<b id="printN"></b>Print <i>n</i> statement====
T1 100340 CALIFORNIA 
<p>
"Long logical fields," such as abstracts, evaluations, or statements of purpose, can be stored as a multiply occurring field. Each occurrence can contain as many as 255 characters.  </p>
<p>
You can use this <var>PRINT</var> statement to print such a field as a paragraph:</p>
<p class="syntax">PRINT <span class="term">n fieldname</span>
</p>
</p>
====Use of subscripts====
<p>
<p>See [[#PRINT statement|PRINT statement]] for a description of using PRINT with subscripts.</p>
where <var class="term">n</var> must be a number less than 32,768.</p>
====PRINT n statement====
 
<p>Long fields, such as abstracts, evaluations, or statements of purpose, can be stored as a multiply occurring field. Each occurrence can contain as many as 255 characters.    </p>
If you do not require the "pieces" of a "long logical field" to be available as individual field occurrences, you can, instead, store such values in a single <var>[[Lob fields|BLOB/CLOB]]</var> field occurrence.
<b>Syntax</b>
<p>You can use this PRINT statement to print such a field as a paragraph:</p>
=====Output format=====
<p class="code">PRINT n fieldname
<p>
</p>
The <var>PRINT <i>n</i></var> statement prints up to <var class="term">n</var> lines of text, composed of all of the occurrences of the field concatenated in order. Nothing is inserted. At most, <var class="term">n</var> lines are printed; any extra lines are ignored. </p>
<p>where n must be a number less than 32,768.</p>
<p>
<b>Output format</b>
Ordinarily, a <var>PRINT</var> statement that produces more than one line inserts a hyphen in the continuation column of the output device. Instead of using the continuation column, this form of <var>PRINT</var> attempts to end each line with a complete word delimited by spaces. If there is insufficient space to fit the last word on a line, the word is hyphenated arbitrarily and continued on the next line. </p>
<p>The PRINT n statement prints up to n lines of text, composed of all of the occurrences of the field concatenated in order. Nothing is inserted. At most, n lines are printed; any extra lines are ignored.   </p>
<p>
<p>Ordinarily, a PRINT statement that produces more than one line inserts a hyphen in the continuation column of the output device. Instead of using the continuation column, this form of PRINT attempts to end each line with a complete word delimited by spaces. If there is insufficient space to fit the last word on a line, the word is hyphenated arbitrarily and continued on the next line. </p>
The <var>AT COLUMN</var> and <var>TO COLUMN</var> clauses can be used to adjust the output to a narrower column. When used together with <var>PRINT <i>n</i></var>, text is broken at word boundaries to fit within the column. The <var>AT</var> clause does not affect the first line, and the <var>TO</var> clause does not affect the last line (unless the line limit <var class="term">n</var> is exceeded). This allows indenting. </p>
<p>The AT COLUMN and TO COLUMN clauses can be used to adjust the output to a narrower column. When used together with PRINT n, text is broken at word boundaries to fit within the column. The AT clause does not affect the first line, and the TO clause does not affect the last line (unless the line limit n is exceeded). This allows indenting.     </p>
<p>
<p>The AT or TO column options cannot accept negative numbers or numbers greater than 32767 for the column. </p>
The <var>AT</var> or <var>TO</var> column options cannot accept negative numbers or numbers greater than 32767 for the column. </p>
<p>Procedures containing PRINT statements with negative numbers or numbers greater than 32767 fail at compile time with the following counting error message:</p>
<p>
Procedures containing <var>PRINT</var> statements with negative numbers or numbers greater than 32767 fail at compile time with the following counting error message:</p>
<p class="code">M204.0263: AT/TO MUST BE BETWEEN 1 AND 32767
<p class="code">M204.0263: AT/TO MUST BE BETWEEN 1 AND 32767
</p>
</p>
<b>Example</b>
<p>This example illustrates the use of the PRINT n statement.</p>
=====Example=====
<p>
This example illustrates the use of the <var>PRINT <i>n</i></var> statement:</p>
<p class="code">BEGIN
<p class="code">BEGIN
             PRINT '1234567890123456'
             PRINT '1234567890123456'
Line 130: Line 283:
             END FIND
             END FIND
             FOR 1 RECORD IN FD.REC
             FOR 1 RECORD IN FD.REC
 
PRINT.TEXT:    PRINT '' AT COLUMN 5 WITH 3 TEXT -
PRINT.TEXT:    PRINT &apos;&apos; AT COLUMN 5 WITH 3 TEXT -
                   AT COLUMN 3 TO 16
                   AT COLUMN 3 TO 16
             END FOR
             END FOR
END  
END
</p>
</p>
<p>The output produced is:</p>
<p>
<p class="code">1234567890123456
The output produced is:</p>
<p class="output">1234567890123456
     NOW IS THE
     NOW IS THE
   TIME FOR ALL
   TIME FOR ALL
   GOOD MEN TO  
   GOOD MEN TO
</p>
</p>
<p>The PRINT.TEXT statement concatenates a null value (two single quotes with no space between them) with the PRINT n form to indent the first line of text by using the location of the null value (column 5).</p>
<p>
<p>Note that the printing stops short of column 16 to avoid truncating TIME and GOOD.       </p>
The <code>PRINT.TEXT</code> statement concatenates a null value (two single quotes with no space between them) with the <var>PRINT <i>n</i></var> form to indent the first line of text by using the location of the null value (column 5).</p>
<b>Use with subscripts</b>
<p>
<p>You cannot use subscripted references with the PRINT n statement. Refer to [[#Subscripts|Subscripts]] for more information about using subscripts with multiply occurring fields.</p>
Note that the printing stops short of column 16 to avoid truncating <code>TIME</code> and <code>GOOD</code>. </p>
===ADD, CHANGE, and DELETE statements===
 
<p>The ADD and CHANGE statements, and both forms of the DELETE statement, are supported in remote file and scattered group contexts.</p>
=====Use with subscripts=====
<p>
You cannot use subscripted references with the <var>PRINT <i>n</i></var> statement. Refer to [[#Subscripts|Subscripts]] for more information about using subscripts with multiply occurring fields.</p>
 
===Is tests===
<p>
  There are three tests that can be applied to field names via <var>Is</var> operators:
  <table>
    <tr><th style="white-space:nowrap">Is Defined</th><td>Returns 1 if the field is defined in the current file or 0, if not. This operator really only useful in a record loop in group context where a field might be defined in some, but not all files. Subscripts are not allowed for <var>Is Defined</var>.</td></tr>
    <tr><th style="white-space:nowrap">Is Present</th><td>Returns 1 if the specified or implicit (1) occurrence of the field is present in the current record or 0, if not. Subscripts are allowed for <var>Is Present</var>.</td></tr>
    <tr><th style="white-space:nowrap">Is Visible</th><td>Returns 1 if the field is visible in the current file or 0, if not. This operator really only useful in a record loop in group context where a field might be visible in some, but not all files. Subscripts are not allowed for <var>Is Visible</var>.</td></tr>
  </table>
</p>
<p>
  The <var>Not</var> keyword can be specified after <var>Is</var> to invert the value returned by any of these operators. The following illustrates use of the <var>Is Defined</var> and <var>Is Present</var> tests in a loop in group context:
</p>
<p class="output">%rs      is object recordset in group foobar
...
for each record in %rs
  if foo is defined then
      add foo = "This is foo"
  end if
  if bar is not present then
      add bar = "This is bar"
  end if
  ...
end for
</p>
===ADD, INSERT, CHANGE, and DELETE statements===
<p>
The <var>ADD</var>, <var>INSERT</var>, and <var>CHANGE</var> statements, and both forms of the <var>DELETE</var> statement, are supported in remote file and scattered group contexts.</p>
====ADD statement====
====ADD statement====
<p>The ADD statement places new occurrences of a field after existing occurrences. For example, if new children are added to the sample record, the additions are placed last. Thus:    </p>
<p>
The <var>ADD</var> statement places new occurrences of a field after existing occurrences. For example, if new children are added to the sample record, the additions are placed last. Thus:    </p>
<p class="code">ADD CHILD = SARAH
<p class="code">ADD CHILD = SARAH
ADD CHILD = PATRICK  
ADD CHILD = PATRICK
</p>
</p>
<p>results in the record:</p>
<p>
results in this order in the record:</p>
<p class="code">FATHER = JOHN DOE
<p class="code">FATHER = JOHN DOE
CHILD = ELIZABETH
CHILD = ELIZABETH
CHILD = ROBERT
CHILD = ROBERT
CHILD = SARAH
CHILD = SARAH
CHILD = PATRICK  
CHILD = PATRICK
</p>
====INSERT statement====
<p>
The <var>INSERT</var> statement, like the <var>ADD</var> statement, is used for adding new occurrences of a field. <var>INSERT</var> is used to add occurrences when the order of the values is important and the values are added out of order.  </p>
<p>
<var>INSERT</var> is supported in remote file and scattered group contexts, but it is <b>not</b> supported for Large Object fields.</p>
=====Syntax=====
<p>
The format of the <var>INSERT</var> statement is:</p>
<p class="syntax">INSERT <span class="term">fieldname</span> [(<span class="term">subscript</span>)] = <span class="term">value</span>
</p>
=====Example=====
<p>
Assume a record with these fields:</p>
<p class="code">DEPT = PERSONNEL
DEPT = FINANCE
DEPT = MARKETING
</p>
<p>
The following statement:</p>
<p class="code">INSERT DEPT(3) = ACCOUNTING
</p>
<p>
results in this record:</p>
<p class="output">DEPT = PERSONNEL
DEPT = FINANCE
DEPT = ACCOUNTING
DEPT = MARKETING
</p>
</p>
<b>The INSERT statement</b>
 
<p>If the order of occurrence is important, the INSERT statement can be used to add new occurrences. See the discussion on [[#INSERT statement|INSERT statement]]. </p>
=====See also=====
====CHANGE statement====
See (below) the [[#INSERT statement rules|subscript validity rules for INSERT]].
<p>The CHANGE statement alters only the first occurrence of a field in a record. </p>
 
<b>Syntax</b>
====Change statement====
<p>You can use this form of the CHANGE statement if there is more than one occurrence:   </p>
<p>
<p class="code">CHANGE fieldname = value1 TO value2  
The [[Change statement|basic Change statement]] alters only the first occurrence of a field in a record. </p>
<p>
You can use the following form of the <var>Change</var> statement if there is more than one occurrence: </p>
<p class="syntax">Change <span class="term">fieldname</span> = <span class="term">value1</span> TO <span class="term">value2</span>
</p>
</p>
<p>This form, like the CHANGE fieldname statement, can be used only inside a FOR EACH RECORD loop. It deletes the first occurrence of the pair fieldname = value1 from a record, and adds the pair fieldname = value2 to the record. The new value is added either in the position occupied by the original value or at the end of the record, depending upon the update attribute specified for the field by the file manager. See the discussion in [[#UPDATE field attribute|UPDATE field attribute]].    </p>
<p>
<p>If the specified field, fieldname = value1, does not appear in the record, fieldname = value2 is simply added to the record. If the specified fieldname = value1 pair appears more than once in the record, only the first occurrence of it is deleted. The pair, fieldname = value2, is added just once. Occurrences of the field name that have other values are not altered by the statement.    </p>
This form, like the basic <var>Change</var> statement, can be used only inside a <var>For Each Record</var> loop. It deletes the first occurrence of the pair <var class="term">fieldname = value1</var> from a record, and adds the pair <var class="term">fieldname = value2</var> to the record. The new value is added either in the position occupied by the original value or at the end of the record, depending upon the update attribute specified for the field by the file manager. See the discussion in [[#UPDATE field attribute|UPDATE field attribute]].    </p>
<p>
If the specified field, <var class="term">fieldname</var> = <var class="term">value1</var>, does not appear in the record, <var class="term">fieldname</var> = <var class="term">value2</var> is simply added to the record. If the specified <var class="term">fieldname</var> = <var class="term">value1</var> pair appears more than once in the record, only the first occurrence of it is deleted. The pair, <var class="term">fieldname</var> = <var class="term">value2</var>, is added just once. Occurrences of the field name that have other values are not altered by the statement.    </p>
 
====DELETE statement====
====DELETE statement====
<p>The DELETE statement deletes only the first occurrence of the field in the record by default. </p>
<p>
<b>Two forms of DELETE statement</b>
The <var>[[Data maintenance#DELETE statement|DELETE]]</var> statement deletes <i>only the first occurrence of the field</i> in the record by default. </p>
<p>For multiply occurring fields, two forms of the DELETE statement are provided; however only for use inside a FOR EACH RECORD loop:    </p>
=====Two forms of DELETE statement=====
<p>
For multiply occurring fields, two forms below of the <var>DELETE</var> statement are provided. </p>
<p class="note"><b>Note:</b> These statements are only for use inside a <var>FOR EACH RECORD</var> loop</p>
<table>
<table>
<tr class="head">
<tr class="head">
Line 180: Line 408:
<th>Usage</th>
<th>Usage</th>
</tr>
</tr>
<tr>
<tr>
<td>A particular occurrence </td>
<td nowrap>A particular occurrence </td>
<td>DELETE fieldname = value </td>
<td nowrap>DELETE <i>fieldname</i> = <i>value</i> </td>
<td>
<td>To delete the first occurrence of the pair, <var class="term">fieldname</var> = <var class="term">value</var>, from a record.  
<p>To delete the first occurrence of the pair, fieldname = value, from a record. Occurrences of the field that have other values are not removed. If the field with the specified value:</p>
<p>
<p>Occurs more than once in the record, only the first occurrence is deleted. </p>
Occurrences of the field that have other values are not removed. If the field with the specified value occurs more than once in the record, only the first occurrence is deleted. If that field cannot be found, no deletion occurs.</p></td>
<p>Cannot be found, no deletion occurs.</p>
</td>
</tr>
</tr>
<tr>
<tr>
<td>Every occurrence of the field in the record </td>
<td>Every occurrence of the field in the record </td>
<td>DELETE EACH fieldname  </td>
<td>DELETE EACH <i>fieldname</i>   </td>
<td>To delete all occurrences of the specified field name. The field to be deleted cannot have the INVISIBLE attribute.     </td>
<td>To delete all occurrences of the specified field name. The field to be deleted cannot have the <var>INVISIBLE</var> attribute. </td>
</tr>
</tr>
</table>
</table>
<b>Use with the FOR EACH OCCURRENCE statement</b>
<p>See [[#Deleting occurrences|Deleting occurrences]] for a discussion of deleting occurrences using the FOR EACH OCCURRENCE OF (FEO) statement.</p>
<b>Use with subscripts</b>
<p>See [[#DELETE statement|DELETE statement]] for a discussion of using the DELETE statement with subscripts.</p>
===SORT RECORDS statement===
<p>When a multiply occurring field is chosen as a sort key, each record in the set being sorted is processed once using the first occurrence of the field as the key. </p>
====EACH modifier with one sort field====
<p>If the EACH modifier is present in the SORT statement and if there are n occurrences of the field in the record, similar records are created in the sorted copy of the original set. </p>
<b>Example</b>
<p>The following request:</p>
<p class="code">SORT RECORDS IN FIND.RECS BY EACH KEY   
</p>
<p>generates three temporary records for the single permanent record in which the field named KEY occurs three times:</p>
<p class="code">KEY = COMPUTER
KEY = CORPORATION
KEY = AMERICA
</p>
<p>The records generated are:</p>
<p class="code">1 KEY = COMPUTER          2 KEY = CORPORATION
  KEY = CORPORATION        KEY = AMERICA
  KEY = AMERICA            KEY = COMPUTER


3 KEY = AMERICA
=====See also=====
  KEY = COMPUTER
<ul>
  KEY = CORPORATION
<li>[[#Deleting occurrences of fields|Deleting occurrences of fields]] describes deleting occurrences using the <var>FOR EACH OCCURRENCE OF</var> (<var>FEO</var>) statement.</li>
</p>
 
<p>These correspond to the n cyclic permutations of the set of field occurrences and, in this example, are sorted into the order 3, 1, 2 (if no other option is selected). Statements that refer to the sorted set, such as PRINT, PRINT EACH, NOTE, and PRINT ALL INFORMATION, reflects the permutation. No record is generated if n = 0.</p>
<li>[[#DELETE statement rules|DELETE statement rules]] describes how to use the <var>DELETE</var> statement with subscripts.</li>
====EACH modifier with several key fields====
 
<p>When the EACH option is selected for several keys, n occurrences of one key and m of another produce differently permuted records. If either n or m equal 0, no records are generated. </p>
<li>[[#Deleting field groups|Deleting field groups]] discusses the <var>DELETE</var> statement for field groups.</li>
<b>Example</b>
</ul>
<p>This request:</p>
 
<p class="code">BEGIN
FIND.RECS: IN CLIENTS FIND ALL RECORDS FOR WHICH
              INCIDENT = T1
          END FIND
SORT.RECS: SORT RECORDS IN FIND.RECS BY FULLNAME -
              AND EACH INCIDENT DATE
          FOR EACH RECORD IN SORT.RECS
              PRINT FULLNAME WITH INCIDENT DATE -
                AT COLUMN 25
          END FOR
END
</p>
<p>produces printed output of:</p>
<p class="code">ABBOTT, FRANKLIN G    860323
ABBOTT, GAIL H        861022
ABRAMS, RUTH Z        861115
ABRAMS, RUSSELL Y      870218
ABBOTT, FRANKLIN G    870424
ABBOTT, GAIL H        871123
      .                  .
      .                  .
      .                   .
</p>
====If no occurrences are present====
<p>A record that does not contain at least one occurrence of the INCIDENT DATE field produces no printed output. Similarly:</p>
<p class="code">SORT RECORDS IN FIND.RECS BY EACH A AND EACH B
</p>
<p>would produce nothing for those records that do not have at least one occurrence of both A and B.                  </p>
===FOR EACH RECORD statement===
===FOR EACH RECORD statement===
<p>The FOR EACH RECORD IN ORDER BY statement retrieves and loops on only the first occurrence of a field in a record. </p>
<p>
The <var>FOR EACH RECORD IN ORDER BY</var> statement retrieves and loops on only the first occurrence of a field in a record. </p>
====EACH modifier====
====EACH modifier====
<p>If there is more than one value of a field in a record, the special modifier, EACH, can be used to retrieve and loop on all values of the field.       </p>
<p>
<p>The EACH modifier only can be used on a FOR EACH RECORD statement that specifies index order processing (the IN ORDER BY fieldname clause must be used and the field must have the ORDERED attribute). The VALUE IN phrase must be used to retrieve the current value of the ORDERED field driving the loop.     </p>
If there is more than one value of a field in a record, the special modifier, <var>EACH</var>, can be used to retrieve and loop on all values of the field. </p>
<b>Example</b>
<p>
<p>This example of the FOR EACH RECORD IN ORDER BY statement:</p>
The <var>EACH</var> modifier only can be used on a <var>FOR EACH RECORD</var> statement that specifies index order processing (the <var>IN ORDER BY <i>fieldname</i></var> clause must be used, and the field must have the <var>ORDERED</var> attribute). The <var>VALUE IN</var> phrase must be used to retrieve the current value of the <var>ORDERED</var> field driving the loop. </p>
=====Example=====
<p>
This example of the <var>FOR EACH RECORD IN ORDER BY</var> statement returns each record in order by each value of the <var>ORDERED</var> field:</p>
<p class="code">BEGIN
<p class="code">BEGIN
FR1:  FOR EACH RECORD IN ORDER BY EACH INCIDENT DATE
FR1:  FOR EACH RECORD IN ORDER BY EACH INCIDENT DATE
         PRINT VALUE IN FR1 WITH FULLNAME AT COLUMN 25
         PRINT VALUE IN FR1 WITH FULLNAME AT COLUMN 25
       END FOR
       END FOR
END  
END
</p>
</p>
<p>returns each record in order by each value of the ORDERED field, as follows:</p>
<p>
<p class="code">760323          ABBOTT, FRANKLIN G
Here is sample output:</p>
<p class="output">760323          ABBOTT, FRANKLIN G
761022          ABBOTT, GAIL H
761022          ABBOTT, GAIL H
761115          ABRAMS, RUTH Z
761115          ABRAMS, RUTH Z
Line 274: Line 460:
   .
   .
   .
   .
   .      
   .
</p>
</p>
===Special statements for multiply occurring fields and field groups===
 
<p>User Language provides several statements for handling multiply occurring fields and field groups. Field groups are supported as of Model 204 version 7.5.</p>
===COUNT OCCURRENCES OF statement===
<ul>
<p>
<li>COUNT OCCURRENCES (used with singly occurring fields, too)</li>
The <var>COUNT OCCURRENCES OF</var> (<var>CTO</var>) statement counts the number of occurrences of the named field in the current record. </p>
<li>FOR ALL OCCURRENCES (FAO) statement</li>
<li>FOR EACH OCCURRENCE (used with singly occurring fields, too)</li>
====Syntax====
<li>FOR FIELDGROUP</li>
<p>
<li>DELETE EACH OCCURRENCE</li>
The format of the <var>COUNT OCCURRENCES</var> statement is:</p>
</ul>
<p class="syntax"><span class="term">label</span>: COUNT OCCURRENCES OF <span class="term">fieldname</span>
<p class="note"><b>Note:</b> See [[#INSERT statement|INSERT statement]], which you can use to add new occurrences of a field.</p>
====COUNT OCCURRENCES OF statement====
<p>The COUNT OCCURRENCES OF (CTO) statement counts the number of occurrences of the named field in the current record. </p>
<b>Syntax</b>
<p>The format of the COUNT OCCURRENCES statement is:</p>
<p class="code">label: COUNT OCCURRENCES OF fieldname
</p>
</p>
<p>or</p>
<p>
<p class="code">label: CTO fieldname
or</p>
<p class="syntax"><span class="term">label</span>: CTO <span class="term">fieldname</span>
</p>
</p>
<p>The field name cannot be subscripted. The COUNT OCCURRENCES OF statement can appear only within a FOR EACH RECORD loop.</p>
<p>
<p>The COUNT OCCURRENCES statement is supported in remote file and scattered group contexts.</p>
The field name cannot be subscripted. The <var>COUNT OCCURRENCES OF</var> statement can appear <i>only</i> within a <var>FOR EACH RECORD</var> loop.</p>
<b>COUNT IN clause</b>
<p>
<p>The COUNT IN clause refers to the count obtained by the COUNT OCCURRENCES OF statement. This request:</p>
The <var>COUNT OCCURRENCES</var> statement is supported in remote file and scattered group contexts.</p>
====COUNT IN clause====
<p>
The <var>COUNT IN</var> clause refers to the count obtained by the <var>COUNT OCCURRENCES OF</var> statement. For example:</p>
<p class="code">BEGIN
<p class="code">BEGIN
FIND.RECS:  IN VEHICLES FIND ALL RECORDS
FIND.RECS:  IN VEHICLES FIND ALL RECORDS
Line 307: Line 492:
                   COUNT IN NO.OF.VINS WITH ' VEHICLE(S)'
                   COUNT IN NO.OF.VINS WITH ' VEHICLE(S)'
             END FOR
             END FOR
END  
END
</p>
</p>
<p>generates the following output:</p>
<p>
<p class="code">100025 INSURES 1 VEHICLE(S)
The request above generates the following output:</p>
<p class="output">100025 INSURES 1 VEHICLE(S)
100030 INSURES 1 VEHICLE(S)
100030 INSURES 1 VEHICLE(S)
100032 INSURES 1 VEHICLE(S)
100032 INSURES 1 VEHICLE(S)
100051 INSURES 1 VEHICLE(S)
100051 INSURES 1 VEHICLE(S)
100058 INSURES 1 VEHICLE(S)  
100058 INSURES 1 VEHICLE(S)
</p>
</p>
====FOR ALL OCCURRENCES OF FIELDGROUP (FAO FIELDGROUP) statement====
 
The FAO statement collects the field group IDs for all occurrences, then processes against that list.
===FOR EACH OCCURRENCE OF loops===
<p><b>Syntax</b> </p>
<p>
<p>The format of the statement is:</p>
On each loop of <var>FOR EACH OCCURRENCE OF</var> (<var>FEO</var>), the <var>VALUE IN</var> and <var>OCCURRENCE IN</var> labels refer to value and position, respectively, of the next field occurrence, starting with occurrence 1 and increasing by 1 for each pass through the loop. When the next field occurrence number is greater than the number of field occurrences in the record, the loop terminates. </p>
<p class="syntax">FOR ALL OCCURRENCES OF FIELDGROUP (<span class="term">fgname</span> | %%<span class="term">varGrp</span>)
====Syntax====
<p>
The format of the <var>FOR EACH OCCURRENCE</var> loop is:</p>
<p class="syntax"><span class="term">label</span>: FOR EACH OCCURRENCE OF <span class="term">fieldname</span>
</p>
</p>
<b>Where</b>
<p>
<ul>
Alternatively,</p>
<li><var class="term">fgname</var> specifies a field group name.</li>
<p class="syntax"><span class="term">label</span>: FEO <span class="term">fieldname</span>
<li>%%<var class="term">varGrp</var> specifies a field group name variable.</li>
</ul>
The difference between an FAO statement and an FEO statement is that the FAO statement is less affected by insertions or deletions of FIELDGROUP fgname within the loop, due to the semantics of field group IDs. This means that deleting the current occurrence within the loop will not affect occurrences referenced in subsequent iterations.
====FOR EACH OCCURRENCE OF loops====
<p>On each loop of FOR EACH OCCURRENCE OF (FEO), the VALUE IN and OCCURRENCE IN labels refer to value and position, respectively, of the next field occurrence, starting with occurrence 1 and increasing by 1 for each pass through the loop. When the next field occurrence number is greater than the number of field occurrences in the record, the loop terminates. </p>
<b>Syntax</b>
<p>The format of the FOR EACH OCCURRENCE loop is:</p>
<p class="code">label: FOR EACH OCCURRENCE OF fieldname  
</p>
</p>
<p>Alternatively,</p>
<p>
<p class="code">label: FEO fieldname
The <var>FOR EACH OCCURRENCE OF</var> statement is supported in remote file and scattered group contexts. </p>
</p>
<p>
<p>The FOR EACH OCCURRENCE OF statement is supported in remote file and scattered group contexts.</p>
To work with specific field values inside an <var>FEO</var> loop, two styles of reference are supported: <var>OCCURRENCE IN <i>label</i></var>, which is used as a field subscript, and <var>VALUE IN <i>label</i></var>, which is a direct reference to a field occurrence.</p>
<b>Example</b>
<p>Consider this record:</p>
====Example====
<p>
Consider this record:</p>
<p class="code">FIRST NAME = RICHARD
<p class="code">FIRST NAME = RICHARD
LAST NAME = SMITH
LAST NAME = SMITH
Line 345: Line 529:
CHILD = SALLY
CHILD = SALLY
CHILD = JANE
CHILD = JANE
ADDRESS = AVON DRIVE  
ADDRESS = AVON DRIVE
</p>
</p>
<p>This request creates a separate record for each child.</p>
<p>
This request creates a separate record for each child.</p>
<p class="code">BEGIN
<p class="code">BEGIN
FD.REC:    FIND ALL RECORDS FOR WHICH
FD.REC:    FIND ALL RECORDS FOR WHICH
Line 364: Line 549:
               END FOR
               END FOR
             END FOR
             END FOR
END  
END
</p>
</p>
<b>Simulating a FOR EACH VALUE loop</b>
<p>The FOR EACH OCCURRENCE OF statement can be used to simulate a FOR EACH VALUE loop if the field contains a static collection of known values. Consider the values of states. The user sets up a single record that has a multiply occurring field in sorted sequence as follows:  </p>
====Simulating a FOR EACH VALUE loop====
<p>
The <var>FOR EACH OCCURRENCE OF</var> statement can be used to simulate a <var>FOR EACH VALUE</var> loop if the field contains a static collection of known values. Consider the values of states. You set up a single record that has a multiply occurring field in sorted sequence as follows:  </p>
<p class="code">TYPE = STATE
<p class="code">TYPE = STATE
CODE = ALABAMA
CODE = ALABAMA
Line 373: Line 560:
     .
     .
     .
     .
CODE = WYOMING  
CODE = WYOMING
</p>
</p>
<p>These statements would begin a request to generate a report in order by state:</p>
<p>
These statements begin a request to generate a report in order by state:</p>
<p class="code">BEGIN
<p class="code">BEGIN
FIND.TYPE: FIND ALL RECORDS FOR WHICH
FIND.TYPE: FIND ALL RECORDS FOR WHICH
Line 386: Line 574:
                 END FIND
                 END FIND
                 FOR EACH RECORD IN FIND.STATE
                 FOR EACH RECORD IN FIND.STATE
                     .
                     . . .
                    .  
</p>
<p>
This method has the advantage of eliminating the internal sort required by <var>FOR EACH VALUE IN ORDER</var> and provides an easy way to simulate a <var>FOR EACH VALUE</var> loop for a field that does not have the <var>FRV</var> or <var>ORDERED</var> attribute. However, a change in the list of values can require a recreation of the <var>TYPE</var> record to keep the values in order. Recreation is not required if the <var>UPDATE IN PLACE</var> field attribute has been specified for the <var>CODE</var> field and if the new and old values occupy the same place in order. Recreation also is not required if the [[#INSERT statement|INSERT statement]] is used correctly. </p>
====VALUE IN with FOR EACH OCCURRENCE loops====
<p>
<var>VALUE IN</var> references to <var>FOR EACH OCCURRENCE</var> (<var>FEO</var>) statements from outside the <var>FEO</var> loop does not compile. Such references receive the following error message:</p>
<p class="code">M204.0311 UNACCEPTABLE STATEMENT REFERENCE
</p>
<p>
At runtime, the space occupied in [[Large request considerations#STBL (character string table)|STBL]] by the <var>FEO</var> value is reclaimed after each iteration of the <var>FEO</var> loop (including the last). This results in a reduction in the runtime STBL space requirements of some programs that use <var>FEO</var>.</p>
<p>
To avoid getting that error, move the value into a %variable inside the <var>FEO</var> loop, and then reference the %variable outside the <var>FEO</var> loop.</p>
 
====FOR EACH OCCURRENCE against INVISIBLE fields====
<p>
Using <var>FOR EACH OCCURRENCE</var> (<var>FEO</var>) syntax against <var>INVISIBLE</var> fields is a waste of processing time. An <var>FEO</var> causes a scan of the current Table B record, but <var>INVISIBLE</var> fields are not stored in Table B, so an <var>FEO</var> against an <var>INVISIBLE</var> field can never find any occurrences.  </p>
<p>
<var>FEO</var> syntax against an <var>INVISIBLE</var> field results in a compilation error:</p>
<p class="code">M204.0320 Field is Invisable. Field = <i>fieldname</i>
</p>
 
====UPDATE field attribute====
 
=====Impact of changing a value=====
<p>
When the user changes the value of a field, how <var class="product">Model&nbsp;204</var> changes the occurrence depends upon whether the field was defined with the <var>UPDATE IN PLACE</var> or <var>UPDATE AT END</var> attribute, as follows:</p>
<table>
<tr class="head">
<th>Attribute </th>
<th>How the update works...</th>
</tr>
 
<tr>
<td><var>UPDATE IN PLACE</var> (the default) </td>
<td>The value of the field occurrence is changed but its position in the record is preserved. To change the order of values, the user must delete the old value and add the new one in separate statements.  </td>
</tr>
 
<tr>
<td><var>UPDATE AT END</var> </td>
<td>The existing occurrence is deleted and the new one is automatically added at the end. <var>UPDATE AT END</var> is normally specified for applications that depend on value rotation to accomplish aging. </td>
</tr>
</table>
======Example======
<p>
This example includes both approaches to updating. Suppose a record has the following fields:</p>
<p class="code">NAME = RICHARD SMITH
CHILD = HENRY
CHILD = SALLY
CHILD = JANE
</p>
<p>
You could use the technique illustrated below to add a last name to each child. (This technique involves the use of %variables, which are discussed in [[Using variables and values in computation]].)</p>
<p class="code">BEGIN
FIND.RECS:  FIND ALL RECORDS FOR WHICH
              NAME = RICHARD SMITH
            END FIND
            FOR EACH RECORD IN FIND.RECS
EACH.CHILD    FOR EACH OCCURRENCE OF CHILD
                  %A = VALUE IN EACH.CHILD WITH ' SMITH'
                  CHANGE CHILD = VALUE IN EACH.CHILD TO %A
              END FOR
            END FOR
END
</p>
 
=====If the UPDATE IN PLACE option is specified=====
<p>
If the <var>UPDATE IN PLACE</var> option has been specified for the <code>CHILD</code> field, then the <var>FOR EACH OCCURRENCE</var> loop change each occurrence of <code>CHILD</code> in turn. </p>
<p>
On the first pass through the loop, the first value, <code>HENRY</code>, is selected and changed to <code>HENRY SMITH</code>. After the first pass, the record looks like this:</p>
<p class="code">NAME = RICHARD SMITH
CHILD = HENRY SMITH
CHILD = SALLY
CHILD = JANE
</p>
<p>
At the end of the second pass, SALLY is changed:</p>
<p class="code">NAME = RICHARD SMITH
CHILD = HENRY SMITH
CHILD = SALLY SMITH
CHILD = JANE
</p>
<p>
At the end of the third pass, JANE is changed:</p>
<p class="code">NAME = RICHARD SMITH
CHILD = HENRY SMITH
CHILD = SALLY SMITH
CHILD = JANE SMITH
</p>
</p>
<p>This method has the advantage of eliminating the internal sort required by FOR EACH VALUE IN ORDER and provides an easy way to simulate a FOR EACH VALUE loop for a field that does not have the FRV or ORDERED attribute. However, a change in the list of values can require a recreation of the TYPE record to keep the values in order. Recreation is not required if the UPDATE IN PLACE field attribute has been specified for the CODE field and if the new and old values occupy the same place in order. Recreation also is not required if the [[#INSERT statement|INSERT statement]] is used correctly.      </p>
====Deleting occurrences====
=====If the UPDATE AT END option is specified=====
<p>A FOR EACH OCCURRENCE OF loop can be used to delete every other occurrence of a multiply occurring field as explained below. The DELETE EACH statement can be used to delete all occurrences.  </p>
<p>
<b>Processing</b>
If the <var>UPDATE AT END</var> option has been specified for the <code>CHILD</code> field, the <var>FOR EACH OCCURRENCE</var> loop proceeds as described here.</p>
<p>The FOR EACH OCCURRENCE OF loop, when used to delete occurrences, deletes every other occurrence because the fields and records in Table B are shifted to eliminate the space the deleted occurrence took (condensing Table B storage) and the pointer is also shifted, so that the pointer ends up pointing to the third occurrence.</p>
<p>
<b>Example</b>
On the first pass through the <var>FOR EACH OCCURRENCE</var> loop the first value, <code>HENRY</code>, is selected and changed to <code>HENRY SMITH</code>. The act of changing, however, causes the value <code>HENRY</code> to be deleted and the value <code>HENRY SMITH</code> to be added as the last child. Thus after the first pass, the record looks like this:</p>
<p>Suppose the following statement is specified for the RICHARD SMITH record described previously:</p>
<p class="code">NAME = RICHARD SMITH
<p class="code">DEL.CHILD: FOR EACH RECORD IN FIND.RECS
CHILD = SALLY
              FOR EACH OCCURRENCE OF CHILD
CHILD = JANE
                DELETE CHILD
CHILD = HENRY SMITH
              END FOR
</p>
          END FOR
<p>
On the second pass through the loop, <code>JANE</code>, which is now the second occurrence of <code>CHILD</code>, is deleted and <code>JANE SMITH</code> is added to the end of the record:</p>
<p class="code">NAME = RICHARD SMITH
CHILD = SALLY
CHILD = HENRY SMITH
CHILD = JANE SMITH
</p>
<p>
On the third pass, the third occurrence is <code>JANE SMITH</code>, and the record ends with:</p>
<p class="code">NAME = RICHARD SMITH
CHILD = SALLY
CHILD = HENRY SMITH
CHILD = JANE SMITH
</p>
 
====Deleting occurrences of fields====
<p class="note"><b>Note:</b> Deleting occurrences of a field using a <var>FOR EACH OCCURRENCE</var> loop is problematic (as will be shown), and you are advised to use either of these:</p>
<ul>
<li>An index loop that deletes the occurrences in reverse order</li>
<li>A <var>[[#DELETE statement|DELETE EACH]]</var> statement within a record loop, to delete all occurrences </li>
</ul>
 
=====Example: Looping backward through the occurrences=====
There is no specific syntax for reversing the order of an <var>FEO</var> loop, so you must count the occurrences and step backward through them in an index loop, like this:
 
<p class="code">for each record in %myRecordSet
childCount:  -
  count occurrences of child 
  for %x from count in childCount to 1 by -1
      delete child(%x)
  end for
end for</p>
 
<p>Stepping backward through a set is also the standard practice in <var>StringList</var> and <var>NamedArrayList</var> objects, when deletions may occur. This technique is especially worth keeping in mind in situations where only some occurrences may be deleted.</p>
 
=====Example: Using DELETE EACH=====
Use <var>DELETE EACH</var> inside a record loop but without an <var>FEO</var> loop:
<p class="code">for each record in vehicles
  delete each child
end for </p>
=====Example: Using FEO and unsubscripted DELETE=====
In general, it is a mistake to reference a multiply occurring field in an <var>FEO</var> loop without using an occurrence subscript. An unsubscripted reference always refers to the first occurrence of a field. For example, suppose the following statement is specified for the <code>RICHARD SMITH</code> record described previously:
<p class="code">for each occurrence in fd.rec
del.child: -
  for each occurrence of child
      delete child
  end for
end for
</p>
</p>
<p>The record originally contained these CHILD entries:</p>
<p>
The record originally contained these <code>CHILD</code> entries:</p>
<p class="code">CHILD=HENRY
<p class="code">CHILD=HENRY
CHILD=SALLY
CHILD=SALLY
CHILD=JANE  
CHILD=JANE
</p>
</p>
<p>On the first pass through the loop, the first value, HENRY, is selected and deleted. At the end of the first pass, the record contains these CHILD entries:</p>
<p>
On the first pass through the loop, the first value, <code>HENRY</code>, is selected and deleted. At the end of the first pass, the record contains these <code>CHILD</code> entries:</p>
<p class="code">CHILD=SALLY
<p class="code">CHILD=SALLY
CHILD=JANE  
CHILD=JANE
</p>
</p>
<p>On the second pass through the loop, the DELETE CHILD statement again deletes the first occurrence of the field as described in [[#DELETE statement|DELETE statement]]. Since HENRY has already been deleted, <var class="product">Model&nbsp;204</var> deletes the first entry, SALLY.</p>
<p>
<p>After the second pass, the FOR EACH OCCURRENCE loop terminates. This is because the value in DEL.CHILD should be the next (third) occurrence, but after two passes, only one occurrence remains on the record. Therefore, at the end of the FOR EACH OCCURRENCE loop, the remaining value in the CHILD field is:</p>
On the second pass through the loop, the <var>DELETE CHILD</var> statement again deletes the first occurrence of the field as described in [[#DELETE statement|DELETE statement]]. Since <code>HENRY</code> has already been deleted, <var class="product">Model&nbsp;204</var> deletes the first entry, <code>SALLY</code>.</p>
<p>
After the second pass, the <var>FOR EACH OCCURRENCE</var> loop terminates. This is because the value in <code>DEL.CHILD</code> should be the next (third) occurrence, but after two passes, only one occurrence remains on the record. Therefore, at the end of the <var>FOR EACH OCCURRENCE</var> loop, the remaining value in the <code>CHILD</code> field is:</p>
<p class="code">CHILD=JANE
<p class="code">CHILD=JANE
</p>
</p>
====Deleting with an FEO FIELDGROUP versus FAO FIELDGROUP loop====
 
An FEO FIELDGROUP statement on field groups has the same semantics as an FEO statement on a single field. When you iterate in an FEO FIELDGROUP or FEO loop, the next occurrence processed is the occurrence number one greater than the one processed before, whether or not it is one you have already processed (because of insertions) or whether or not you skip one or more (because of deletions). For example:
=====Example: FEO and subscripted DELETE=====
Suppose an occurrence reference is added to the field in the <var>FEO</var> loop:
<p class="code">for each record in fd.rec
del.child: -
  for each occurrence of child
      delete child(occurrence in del.child)
  end for
end for </p>
<p>
The loop above results in <em>every other</em> occurrence being deleted, because the occurrence reference continues to increment upward while the list of occurrences is also shifted. So the second time through the loop, the occurrence pointer is pointing to the item that was formerly the third item, then the fifth item, then the seventh item. Again, this is generally not what the programmer intends.</p>
 
===Subscripts===
<p>
Subscripts can be included in <var class="product">Model&nbsp;204</var> field references to facilitate the selection of particular occurrences of multiply occurring fields. Any field name can be followed by a parenthesized expression. The value of this expression is used as an ordinal number which specifies the desired occurrence of the named field. For example:  </p>
<p class="code">INCIDENT(3)
COURSE.NUMBER(2)
TRANSACTION(A+B)
</p>
=====Example=====
<p>
This request illustrates a class schedule where subscripts are used to change the room number for a course:</p>
<p class="code">BEGIN
COURSE:  FIND ALL RECORDS FOR WHICH
            REC = ROOM ASSIGNMENT
        END FIND
CHANGE:  FOR EACH RECORD IN COURSE
            IF ROOM(1) EQ '214A' THEN
              CHANGE ROOM(1) TO '566A'
            END IF
            IF ROOM(2) EQ '214A' THEN
              CHANGE ROOM(2) TO '566A'
            END IF
        END FOR
END
</p>
====Subscripted field extraction====
<p>
Subscripted field references attempt to maintain their position inside a record much as an <var>FEO</var> loop attempts to maintain its position inside a record. This means that subscripted field references tend to be as efficient as <var>FEO</var> loops. </p>
<p>
The following is an example of using subscripted field extraction for a field that is a member of a [[Field group design#Traditional approaches to_multiply occurring fields|"logical" field group]]. An <var>FEO</var> loop populates two arrays with the corresponding values of group members <code>INCIDENT</code> and <code>INCIDENT_DATE</code>:</p>
<p class="code">%inc is string array (12) no fs
%idate is string array (12) no fs
FeoIdata:
  feo INCIDENT
    %inc(occurence in FeoIdata) = value in FeoIdata
    %idate(occurence in FeoIdata) = INCIDENT_DATE(occurence in FeoIdata)
  end for
</p>
<p>
If you use multiple <var>FEO</var> loops for this kind of field group processing, you might require additional VTBL resources for procedures that have an extremely large number of sorts or subscripted field references.</p>
====Evaluation of subscript expressions====
<p>
The evaluation of subscript expressions is subject to the rules for determining an integer result for an arithmetic expression as described in [[Using variables and values in computation#Arithmetic operations|Arithmetic operations]].      </p>
====Statements and phrases with which you cannot use subscripts====
<p>
Subscripted field references can appear anywhere that unsubscripted references can, except in the following statements and phrases:</p>
<table>
<tr class="head">
<th>Statements you cannot use with subscripts </th>
<th>Because...</th>
</tr>
 
<tr>
<td><var>ADD</var> </td>
<td>Adds a new occurrence of a field. </td>
</tr>
 
<tr>
<td><var>BY EACH phrase</var> in the <var>SORT RECORDS</var> </td>
<td>Loops through all occurrences of a field. </td>
</tr>
 
<tr>
<td><var>DELETE EACH statement</var> </td>
<td>Loops through all occurrences of a field.  </td>
</tr>
 
<tr>
<td><var>EACH phrase</var> in a <var>PRINT</var> specification</td>
<td>Loops through all occurrences of a field. </td>
</tr>
 
<tr>
<td><var>FILE</var> </td>
<td>Deals with fields having the INVISIBLE attribute, which cannot be the object of subscripted references.  </td>
</tr>
 
<tr>
<td><var>FIND</var></td>
<td>Locates records without regard to which occurrence of a field contains the desired value.    </td>
</tr>
 
<tr>
<td><var>FOR EACH OCCURRENCE</var></td>
<td>Loops through all occurrences of a field. Field references within <var>FOR EACH OCCURRENCE</var> loops can be subscripted, depending upon the individual statements in which the references appear. </td>
</tr>
 
<tr>
<td><var>FOR EACH VALUE</var> </td>
<td>Loops through all values of all occurrences of the specified field. Field references within <var>FOR EACH VALUE</var> loops are not allowed at all, unless the field reference is embedded in a nested <var>FOR EACH RECORD</var> loop. See [[Value loops#Setting up a value loop on one field and printing a value of another|Setting up a value loop on one field and printing a value of another]] for more information.</td>
</tr>
 
<tr>
<td><var>IN ORDER BY EACH phrase</var> in the <br/><var>FOR EACH RECORD </var></td>
<td>Loops through all occurrences of a field.    </td>
</tr>
 
<tr>
<td><var>PRINT n </var></td>
<td>Loops through all occurrences of a field.    </td>
</tr>
 
<tr>
<td><var>STORE RECORD</var></td>
<td>Adds fields in the order of appearance in the statement.    </td>
</tr>
</table>
====Unsubscripted field references====
<p>
An unsubscripted field reference in a context in which subscripted references are allowed is always equivalent to a subscripted reference with a value of 1.</p>
====Do not use subscripts with INVISIBLE fields====
<p>
You cannot make subscripted references to fields that have the <var>INVISIBLE</var> attribute (see the discussion on field attributes in [[Field attributes]]). These fields are not truly multiply occurring, although they can have several different values in a single record. A subscript specified for a field with the <var>INVISIBLE</var> attribute is ignored.  </p>
===Subscript validity rules===
<p>
The rules presented for each statement in the subsections that follow indicate whether or not a subscript value is valid and what action to take if the value is not valid. These rules take into account: </p>
<ul>
<ul>
<li>
<li>The value of the subscript</li>
To delete every other occurrence of the field group, use an FEO statement:
<li>The context in which the subscript appears</li>
<p class="code">
<li>The description of the subscripted field </li>
FEO FIELDGROUP VEHICLE
</ul>
DELETE FIELDGROUP
 
END FOR</p>
The rules assume that <b>N</b>, the maximum number of occurrences that can be stored in a record for a given field, is umlimited. This section does <b>not</b> discuss the [[Data maintenance#Storing values in preallocated fields|rules for preallocated fields]], where <b>N</b> is limited to the value of <var class="term">n</var> in the <var>OCCURS</var> clause of the field's description.
</li>
<p>
<li>
In the discussions of these rules, two other quantities are used:</p>
To delete all occurrences of the field group, use an FAO FIELDGROUP statement:
<ul>
<p class="code">
<li><b>P</b> is the number of nonempty occurrences of the referenced field found in the specified record when the reference is evaluated. </li>
FAO FIELDGROUP VEHICLE
 
DELETE FIELDGROUP
<li><b>S</b> is the subscript specified in the statement. </li>
END FOR</p>
</li>
</ul>
</ul>
====FOR FIELDGROUP statement====
The FOR FIELDGROUP statement operates on a specific occurrence of a field group. The occurrence is indicated by either an occurrence number or a field group ID number.
====INSERT statement rules====
<p><b>Syntax</b></p>  
<p>
The format of the statement is:
If the order of occurrence is important, you can use the <var>INSERT</var> statement to
<p class="syntax">
add new occurrences.</p>
FOR FIELDGROUP (<span class="term">fgname</span> | %%<span class="term">varFgname</span>) { (<span class="term">occurrence number</span>) | = <span class="term">fieldgroupID</span> }
<p>
The table below lists the validity rules for subscripted references with <var>INSERT</var>: </p>
 
<table>
<tr class="head">
<th>If P and S values are: </th>
<th>Then Model 204 takes this action:</th></tr>
<tr>
<td>P > S</td>
<td>Inserts the new occurrence in front of the former Sth occurrence; the new occurrence becomes the current Sth occurrence of the field.</td></tr>
<tr>
<td>P < S</td>
<td>Inserts the new occurrence of the field after the Pth occurrence; the new occurrence becomes the (P+1) occurrence.</td></tr>
<tr>
<td>P = 0</td>
<td>Adds the new value at the end of the record, as in the <var>ADD</var> statement.</td></tr>
<tr>
<td>S = 0, or no subscript</td>
<td>Treats the new value as if S = 1 and inserts the new value as the first occurrence, in front of any former occurrence of the field.</td></tr>
<tr>
<td>S < 0</td>
<td>Does not add a new occurrence.</td></tr>
</table>
 
<p>
For fields with the <var>INVISIBLE</var> attribute, only the index is affected. </p>
====Print statement rules====
<p>
In retrieval statements such as <var>Print</var>, subscript values less than zero or greater than <b>P</b> are invalid. Invalid references of this kind cause the null value to be returned. A subscript of zero returns the value of the first occurrence.  </p>
 
====DELETE statement rules====
<p>
In the <var>DELETE</var> statement, subscript values less than one or greater than <b>P</b> are invalid. If an invalid reference of this kind is made, no action is taken. </p>
<p>
If several occurrences of a field are being deleted, you should be careful not to use <var>DELETE</var> in the following way: </p>
<p class="code">FOR EACH RECORD IN FIND.RECS
  DELETE CLIENT(1)
  DELETE CLIENT(2)
  DELETE CLIENT(3)
END FOR
</p>
<p>
As the statements are executed, <var class="product">Model&nbsp;204</var> deletes the first occurrence, <code>CLIENT(1)</code>, then locates the current second occurrence, which is the original <code>CLIENT(3)</code>, and deletes it. Then, because a third occurrence cannot be found, the operation stops, and the original <code>CLIENT(2)</code> is never deleted.</p>
<p>
In such a situation, deleting the occurrences in the reverse order achieves the desired result:</p>
<p class="code">FOR EACH RECORD IN FIND.RECS
  DELETE CLIENT(3)
  DELETE CLIENT(2)
  DELETE CLIENT(1)
END FOR
</p>
<p>
The desired result also can be achieved by completely omitting the subscripts, as follows:</p>
<p class="code">FOR EACH RECORD IN FIND.RECS
  DELETE CLIENT
  DELETE CLIENT
  DELETE CLIENT
END FOR
</p>
====CHANGE statement rules====
<p>
In the <var>CHANGE</var> statement, subscript values less than one or greater than <b>P</b> are treated as attempts to add a new occurrence <code>(P+1)</code>. If <code>(P+1)</code> does not exceed <b>N</b> (the maximum number of occurrences that can be stored), the new occurrence is added to the record. If <code>(P+1)</code> does exceed <b>N</b>, the request is cancelled.  </p>
====SORT RECORDS statement rules====
<p>
The use of subscripts in references to the recordset yielded by a
<var>SORT RECORDS</var> statement sometimes can produce unexpected results.
If the <var>BY EACH</var> option appears in a <var>SORT</var> statement, records in which the <var>BY EACH</var> field is multiply occurring are copied several times (once for each field occurrence) to the system scratch file CCATEMP.
In each copy, the occurrences of the <var>BY EACH</var> field are rotated, so that the occurrence used as the sort key appears first. Therefore, a subscripted reference to this field can yield different values for different copies of the record. </p>
 
==Working with field groups==
<p>
SOUL provides several statements for handling "physical" [[Field group (File architecture)|field groups]]. Field groups are supported as of Model 204 version 7.5. For a brief summary of the basic field group operations, see [[Data maintenance#Updating field groups|Updating field groups]]. For information about defining a field group, see the <var>[[DEFINE FIELDGROUP command|DEFINE FIELDGROUP]]</var> command.</p>
 
===FOR ALL OCCURRENCES OF FIELDGROUP (FAO FIELDGROUP) statement===
The <var>FAO FIELDGROUP</var> statement collects the field group IDs for all occurrences, then processes against that list.
<p class="note"><b>Note:</b> There is no <var>FAO</var> statement for use against multiply occurring fields. </p>
====Syntax====
<p>
The format of the statement is:</p>
<p class="syntax">FOR ALL OCCURRENCES OF FIELDGROUP [<span class="term">fgname</span> | %%<span class="term">varGrp</span>]
</p>
</p>
<b>Where</b>
Where:
<ul>
<ul>
<li><var class="term">fgname</var> specifies a field group name</li>
<li><var class="term">fgname</var> specifies a field group name.</li>
<li>%%<var class="term">varGrp</var> specifies a field group name variable</li>
 
<li>(<var class="term">occurrence number</var>) specifies an occurrence within that field group. The occurrence number must be enclosed in parentheses.</li>
<li>%%<var class="term">varGrp</var> specifies a field group name variable.</li>
<li>=<var class="term">fieldgroupID</var> specifies a field group ID. fieldgroupID must be preceded by an equal sign.</li>
</ul>
</ul>
<p><b>Example</b></p>  
The difference between an <var>FAO</var> statement and an <var>FEO</var> statement is that the <var>FAO</var> statement is less affected by insertions or deletions of <var>FIELDGROUP</var> <var class="term">fgname</var> within the loop, due to the semantics of field group IDs. This means that deleting the current occurrence within the loop will not affect occurrences referenced in subsequent iterations.
<p class="code">
 
FR WHERE ...
FAO achieves this by scanning the record and building a table of fieldgroup occurrences before processing the <var>FAO</var> loop. Because of this, <var>FAO</var> uses more CPU than an <var>FEO</var> loop, possibly significantly more for a large record with many occurrences of the fieldgroup, especially if the loop is often terminated (with a <var>Loop End</var>) before processing all occurrences. As such, <var>FAO</var> should only be used where the interaction of the loop with fieldgroup insertions and deletions is a concern.
   FOR FIELDGROUP DRIVER (3)
 
       PRINT DRIVER_ID
====Example====
A field group is added to a record in the example below:
<p class="code">BEGIN
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100095
  PRINT '...BEFORE...'
   FAO FIELDGROUP VEHICLE
       PRINT MAKE AND MODEL
   END FOR
   END FOR
END FOR
  PRINT 'ADD A HONDA PILOT ...'
  ADD FIELDGROUP VEHICLE
      MAKE = HONDA
      MODEL = PILOT
      * ... OTHER VEHICLE FIELDS
  END ADD
  PRINT '...AFTER....'
  FAO FIELDGROUP VEHICLE
      PRINT MAKE AND MODEL
  END FOR
END </p>
<p>
The result is:</p>
<p class="output">...BEFORE...
VOLKSWAGEN NEW BEETLE
MITSUBISHI ECLIPSE
CHEVROLET SUBURBAN
ADD A HONDA PILOT ...
...AFTER....
VOLKSWAGEN NEW BEETLE
MITSUBISHI ECLIPSE
CHEVROLET SUBURBAN
HONDA PILOT
</p>


FR WHERE ...
===FOR EACH OCCURRENCE OF FIELDGROUP statement===
  %DRIVER = 'DRIVER'
<p>
  FOR FIELDGROUP %%DRIVER = %FGID
The analog of the <var>FOR EACH OCCURRENCE OF</var> statement for multiply occurring fields is the <var>FOR EACH OCCURRENCE OF FIELDGROUP</var> statement.  </p>
      PRINT DRIVER_ID AND DRIVER_NAME
  END FOR
====Syntax====
END FOR
<p>
The format of the <var>FOR EACH OCCURRENCE OF FIELDGROUP</var> statement is:</p>
<p class="syntax"><span class="term">label</span>: [FEO | FOR EACH OCCURRENCE OF] FIELDGROUP {<span class="term">fgname</span> | %%<span class="term">varGrp</span>}
</p>
</p>
Where:
<ul>
<li><var class="term">fgname</var> specifies a field group name. </li>
<li>%%<var class="term">varGrp</var> specifies a field group name variable. </li>
</ul>
Like <var>FEO</var> for multiply occurring fields, you can reference specific field group members inside an <var>FEO FIELDGROUP</var> loop using the <var>OCCURRENCE IN <i>label</i></var> and <var>VALUE IN <i>label</i></var> phrases.


====VALUE IN with FOR EACH OCCURRENCE loops====
For additional <var>FEO FIELDGROUP</var> discussion and examples, see [[#Using a FOR EACH OCCURRENCE loop|Using a FOR EACH OCCURRENCE loop]], below.
<p>VALUE IN references to FOR EACH OCCURRENCE (FEO) statements from outside the FEO loop does not compile. Such references receive the following error message:</p>
 
<p class="code">M204.0311 UNACCEPTABLE STATEMENT REFERENCE 
===FOR FIELDGROUP statement===
The <var>FOR FIELDGROUP</var> statement operates on a specific occurrence of a field group. The occurrence is indicated by either an occurrence number or a field group ID number.
====Syntax====
The format of the statement is:
<p class="syntax"><span class="literal">FOR FIELDGROUP (</span><span class="term">fgname</span> <span class="squareb">|</span> %%<span class="term">varGroup</span><span class="literal">)</span> <span class="squareb">{</span> <span class="literal">(</span><span class="term">occurrence-number</span><span class="literal">)</span> <span class="squareb">|</span> = <span class="term">fieldgroupID</span> <span class="squareb">}</span>
</p>
</p>
<p>At runtime, the space occupied in STBL by the FEO value is reclaimed after each iteration of the FEO loop (including the last). This results in a reduction in the runtime STBL space requirements of some programs that use FEO.</p>
Where:
<p>To avoid getting that error, move the value into a %variable inside the FEO loop, and then reference the %variable outside the FEO loop.</p>
<ul>
====FOR EACH OCCURRENCE OF against INVISIBLE fields====
<li><var class="term">fgname</var> specifies a field group name. </li>
<p>Using FOR EACH OCCURRENCE (FEO) syntax against INVISIBLE fields is a waste of processing time. An FEO causes a scan of the current Table B record, but INVISIBLE fields are not stored in Table B, so an FEO against an INVISIBLE field can never find any occurrences. Prior to V4R2.0 this programming flaw was masked because using FEO syntax against an INVISIBLE field compiled and evaluated successfully, although it never found occurrences. </p>
 
<p>Beginning in V4R2.0, FEO syntax against an INVISIBLE field results in compilation error:</p>
<li>%%<var class="term">varGroup</var> specifies a field group name variable. </li>
<p class="code">M204.0320 FIELD IS INVISIBLE. FIELD =
 
<li><var class="term">occurrence-number</var> specifies an occurrence within that field group. The occurrence number must be enclosed in parentheses.
<p>
The <var>[[$FIELDGROUPOCCURRENCE]]</var> function returns the field group occurrence value. </p></li>
 
<li><var class="term">fieldgroupID</var> specifies a field group ID &ndash; each field group in a record has an ID that's unique among all occurrences of all field groups within the record. A field group ID is assigned when a field group is added to the record, is equal to one more than the highest field group ID that has been assigned in the record, and is not reused if its field group is deleted.
<p>
<var class="term">fieldgroupID</var> must be preceded by an equal sign. </p>
<p>
The <var>[[$FIELDGROUPID]]</var> function returns the field group ID. </p></li>
<li>If neither <var class="term">occurrence-number</var> nor <var class="term">fieldgroupID</var> is specified, the first occurrence of the fieldgroup is accessed. That is, <code>for fieldgroup foo</code> is equivalent to <code>for fieldgroup foo(1)</code>.
</ul>
 
====Examples====
This <var>FOR FIELDGROUP</var> statement sets the third occurrence of the <code>DRIVER</code> field group as the context for the <var>Print</var> statement:
<p class="code">FR Where ...
  For Fieldgroup DRIVER(3)
      Print DRIVER_ID
  End For
End For </p>
<p>
This <var>FOR FIELDGROUP</var> statement uses a [[Using variables and values in computation#Field name variables|field name variable]] to set the <code>DRIVER</code> field group that has ID <var class="term">%fgid</var> as the context for the <var>Print</var> statement: </p>
<p class="code">FR Where ...
  %driver = 'DRIVER'
  For Fieldgroup %%driver = %fgid
      Print DRIVER_ID And DRIVER_NAME
  End For
End For
</p>
</p>
===PAFGI statement===
As described in [[Basic SOUL statements and commands#Print  All Fieldgroup Information (PAFGI) statement|Print  All Fieldgroup Information (PAFGI) statement]], you can use the <var>PAFGI</var> statement to display the contents of a fieldgroup.


===Setting field group context===
===Setting field group context===
To access data in a field group, first establish the record context using the
<var>FOR EACH RECORD</var> (<var>FR</var>) or <var>FOR RECORD NUMBER</var> (<var>FRN</var>) statement. Then to access the occurrences of the field group, you typically use an <var>FAO FIELDGROUP</var> or <var>FEO FIELDGROUP</var> loop.
====Using a FOR ALL OCCURRENCES OF loop====
====Using a FOR ALL OCCURRENCES OF loop====
You can set field group context with a FOR ALL OCCURRENCES OF (FAO) loop. For example:
You can set field group context with an <var>FAO</var> loop. For example:
<p class="code">
<p class="code">BEGIN
BEGIN
IN POLICIES FOR EACH RECORD
IN POLICIES FOR EACH RECORD
FAO:
FAO: FAO FIELDGROUP VEHICLE
  FAO FIELDGROUP VEHICLE
        IF COLOR IS LIKE '*RED*' THEN
      IF COLOR IS LIKE '*RED*' THEN
          PRINT 'POLICY:' AT 5 AND POLICY_NUMBER AND -
        PRINT 'POLICY:' AT 5 AND POLICY_NUMBER AND -
               'COLOR=' WITH COLOR
               'COLOR=' WITH COLOR
        %RED = %RED + 1
          %RED = %RED + 1
      END IF
        END IF
  END FOR
      END FOR
END FOR
END FOR
PRINT 'NUMBER OF RED CARS:' AND %RED
PRINT 'NUMBER OF RED CARS:' AND %RED
END
END
 
</p>
      POLICY: 100001 COLOR=VICTORY RED
The result is:
<p class="output">      POLICY: 100001 COLOR=VICTORY RED
       POLICY: 100007 COLOR=RED
       POLICY: 100007 COLOR=RED
       POLICY: 100011 COLOR=VICTORY RED
       POLICY: 100011 COLOR=VICTORY RED
...
...
NUMBER OF RED CARS: 214
NUMBER OF RED CARS: 214
 
...and so on...
...and so on...
</p>
</p>
====Using a FOR EACH OCCURRENCE loop====
====Using a FOR EACH OCCURRENCE loop====
You can also set field group context with a FOR EACH OCCURRENCE (FEO) loop on the field group. The FEO loop establishes a field group context. References to fields in the field group inside the loop refer to occurrences in that field group. For example:
An <var>FEO</var> loop on a field group establishes a field group context. Inside the loop, references to field group fields are to their occurrences in their field group. For example:
<p class="code">
<p class="code">BEGIN
BEGIN
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013
FEO:
FEO: FEO FIELDGROUP VEHICLE
  FEO FIELDGROUP VEHICLE
       %DRIVER_ID = OTHER_DRIVER
       %DRIVER_ID = OTHER_DRIVER
       PRINT OCCURRENCE IN FEO AND MAKE AND MODEL AND -
       PRINT OCCURRENCE IN FEO AND MAKE AND MODEL AND -
             'OTHER DRIVER(S):' AT 30 AND -
             'OTHER DRIVER(S):' AT 30 AND EACH OTHER_DRIVER          
            EACH OTHER_DRIVER
       PRINT 'FIRST:' AT 30 AND %DRIVER_ID AND -
       PRINT 'FIRST:' AT 30 AND %DRIVER_ID AND -
             'THIRD:' AND OTHER_DRIVER(3)
             'THIRD:' AND OTHER_DRIVER(3)
  END FOR
      END FOR
END FOR
END FOR
END
END </p>


1 AUDI A4 QUATTRO OTHER DRIVER(S): 100035 100037 100036
The result is:
<p class="output">1 AUDI A4 QUATTRO OTHER DRIVER(S): 100035 100037 100036
                             FIRST: 100035 THIRD: 100036
                             FIRST: 100035 THIRD: 100036
2 CADILLAC SEVILLE OTHER DRIVER(S):
2 CADILLAC SEVILLE OTHER DRIVER(S):
                             FIRST: THIRD:
                             FIRST: THIRD:
</p>
</p>
<p>
On each iteration of a field group FEO loop, the current field group is set as the field group context. If that field group is deleted and a field in that field group is referenced, you get a request cancelling error.
</p>
<p>
An important difference between an <var>FAO FIELDGROUP</var> statement and an <var>FEO
FIELDGROUP</var> statement is that the <var>FAO FIELDGROUP</var> statement collects the
field group IDs for all occurrences, then processes against that list. Because of
this, it is less affected by insertions or deletions of occurrences of field groups
within the loop. Deleting the current occurrence within the
loop, for example, will not affect occurrences referenced in subsequent iterations.
For further discussion of this, see [[#Deleting all occurrences and the FEO anomaly|Deleting all occurrences and the FEO anomaly]]. </p>
====Using a FOR FIELDGROUP block====
You can use a field group occurrence established by a [[#FOR FIELDGROUP statement|FOR FIELDGROUP block]] as the context for references to its members.
For example, the following <var>PAFGI</var> (<var>PRINT ALL FIELD GROUP INFORMATION</var>) statement outputs the contents of only the third occurrence of field group <code>GRP</code>:
<p class="code">IN FILE WHATEVER FRN %RECNO
  FOR FIELDGROUP GRP(3)
      PRINT '*** Third occurrence of field group GRP'
      PAFGI
  END FOR
END FOR  </p>


As with non-field group field references for missing occurrences, the field values are returned as null. The exception is EXACTLY-ONE fields in field groups, where the default value is returned for missing occurrences.
====Accessing an external field while in field group context====
You can reference a field from the containing context even inside a field group context. For example:
<p class="code">BEGIN
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013
  FEO FIELDGROUP DRIVER
      PRINT POLICY_NUMBER AND DRIVER_NAME
  END FOR
END </p>
<p>
The result is: </p>
<p class="output">100013 CUMMINGS, BETTY S
100013 CUMMINGS, EDDIE R
100013 CUMMINGS, LEE V
100013 CUMMINGS, MARY U
100013 CUMMINGS, ROBERT T
</p>


====Handling nested field group context====
====Accessing field group members while not in field group context====
If field groups are nested, one can always retrieve an EXACTLY-ONE field in a field group inside the current context without establishing that nested field groups context.
You can access field group occurrences by using the occurrence number
directly in the FR loop, if the field is [[Field_design#AT-MOST-ONE, REPEATABLE, and EXACTLY-ONE attributes|EXACTLY-ONE or AT-MOST-ONE]]. So, instead of using this <var>FEO FIELDGROUP</var> loop to print the <code>MAKE</code> and <code>MODEL</code> for the three vehicles in the <code>VEHICLE</code> field group:
<p class="code">BEGIN
FR WHERE POLICY_NUMBER = 100095
  FEO FIELDGROUP VEHICLE
      PRINT MAKE AND MODEL
  END FOR
END FOR
END
</p>
<p>
Output: </p>
<p class="output">VOLKSWAGEN NEW BEETLE
MITSUBISHI ECLIPSE
CHEVROLET SUBURBAN  </p>
<p>
You can also access the <code>MAKE</code> and <code>MODEL</code> (<var>EXACTLY-ONE</var>) fields directly within the <var>FR</var> loop by using occurrence numbers:  </p>
<p class="code">BEGIN
  FR WHERE POLICY_NUMBER = 100095
      PRINT MAKE AND MODEL
      PRINT MAKE(2) AND MODEL(2)
      PRINT MAKE(3) AND MODEL(3)
  END FOR
END  </p>
<p>
Output: </p>
<p class="output">VOLKSWAGEN NEW BEETLE
MITSUBISHI SUBURBAN
CHEVROLET SUBURBAN  </p>
<p class="note"><b>Note:</b>
If <code>MAKE</code> and <code>MODEL</code> are <var>REPEATABLE</var> fields or defined to all field groups (<code>FIELDGROUP *</code>), attempting to access them with just an <var>FR</var> loop will fail. </p>


In addition to retrieving field values inside an FEO FIELDGROUP loop, fields in that field group can be updated, that is added, inserted, changed and deleted:
====Accessing field group members while in a containing field group====
<p class="code">
If field groups are nested, you can retrieve an <var>EXACTLY-ONE</var> field in a field group inside the current context without establishing that nested field group's context.
BEGIN
<var>EXACTLY-ONE</var> fields in specific field groups (as opposed to fields defined with <code>FIELDGROUP *</code> that belong to all field groups) can be retrieved outside the field group context.
In the following example, <code>CLAIM-NUMBER</code> is an <var>EXACTLY-ONE</var> field in field group <code>CLAIM</code> which is nested within field group <code>VEHICLE</code>:
<p class="code">BEGIN
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100095
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100095
   PRINT POLICY_NUMBER
   PRINT POLICY_NUMBER
Line 541: Line 1,233:
   END FOR
   END FOR
END FOR
END FOR
END
END </p>
100095
<p>
The result is: </p>
<p class="output">100095
     VOLKSWAGEN NEW BEETLE
     VOLKSWAGEN NEW BEETLE
1)
1)
Line 555: Line 1,249:
2)
2)
3)
3)
</p>


===Handling references to missing occurrences===
References to missing occurrences of fieldgroup fields are returned as nulls, except as discussed after the first example below. Invalid occurrence numbers such as negative numbers are ignored, and a 0-numbered occurrence is treated the same as 1.


BEGIN
For example, the third <var>PRINT</var> statement below produces a null (with no error) for a pair of missing occurrences, even if <code>MAKE</code> is defined as <var>EXACTLY-ONE</var>. This is consistent with Model&nbsp;204 behavior for <var>EXACTLY-ONE</var> and <var>OCCURS</var> fields that are not fieldgroup members (see [[#PRINT statement rules|PRINT statement rules]], for example). If you request occurrence 3 of an <var>EXACTLY-ONE</var> or <var>OCCURS 1</var> field in the record, Model 204 returns a null. In this example, <code>MAKE</code> and <code>MODEL</code> are <var>EXACTLY-ONE</var> fields:
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013
  FEO FIELDGROUP VEHICLE
      ADD MAKE = 'XXX'
      INSERT MAKE(1) = %NOTHING
      DELETE MAKE
      CHANGE COLOR(4) TO 'YYYY'
  END FOR
  BACKOUT
END
<nowiki>
*** 1 M204.2853: ADD NOT ALLOWED FOR EXACTLY-ONE FIELD
== 'XXX'
*** 2 M204.0228: PART OF STATEMENT IGNORED
*** 3 M204.2853: INSERT NOT ALLOWED FOR EXACTLY-ONE
FIELD
== %NOTHING
*** 4 M204.0228: PART OF STATEMENT IGNORED
*** 5 M204.2853: DELETE NOT ALLOWED FOR EXACTLY-ONE
FIELD
*** M204.1042: COMPILATION ERRORS
</nowiki>
</p>
====Handling invalid occurrence numbers====
The following PRINT MAKE(3) statement prints a null and without error, even if MAKE was defined as EXACTLY-ONE. This is consistent with Model 204 behavior for non-field group EXACTLY-ONE and OCCURS fields. That is, you can request occurrence 4 of an EXACTLY-ONE or OCCURS 1 field in the record and Model 204 returns a null.


Model 204 ignores invalid occurrence numbers such as negative numbers or 0. 0 is treated the same as 1.
<p class="code">BEGIN
 
EXACTLY-ONE fields in specific field groups (as opposed to FIELDGROUP *) can be retrieved outside the field group context. In the following example, MAKE and MODEL are EXACTLY-ONE fields in field group DRIVER in field POLICIES.
<p class="code">
BEGIN
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013
   PRINT '1' AND MAKE AND MODEL
   PRINT '1' AND MAKE AND MODEL
Line 593: Line 1,262:
   PRINT '3' AND MAKE(3) AND MODEL(3)
   PRINT '3' AND MAKE(3) AND MODEL(3)
END FOR
END FOR
END
END </p>
1 AUDI A4 QUATTRO
The result is:
<p class="output">1 AUDI A4 QUATTRO
2 CADILLAC SEVILLE
2 CADILLAC SEVILLE
3
3  
</p>
 
The two exceptions to the null-return rule are certain <var>EXACTLY-ONE</var> or <var>AT-MOST-ONE</var> fieldgroup members, as follows:
<ol>
<li>Occurrence number 1 of an <var>EXACTLY-ONE</var> or <var>AT-MOST-ONE</var> fieldgroup member, in the context of the containing fieldgroup, and defined to also have the <var>[[Field design#DEFAULT-VALUE (DV) attribute|DEFAULT-VALUE]]</var> attribute. A reference to a missing occurrence of such a field returns the <var>DEFAULT-VALUE</var> value.
<p>
References to non-number-1 occurrences of such fields and context return null.</p></li>


BEGIN
<li>Any occurrence <i>n</i> of an  <var>EXACTLY-ONE</var> or <var>AT-MOST-ONE</var> fieldgroup member, <i>not</i> in the context of the containing fieldgroup, <i>if</i> occurrence <i>n</i>
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100095
of the fieldgroup is present. If such fields are defined to also have the <var>DEFAULT-VALUE</var> attribute, a reference to a missing occurrence returns the <var>DEFAULT-VALUE</var> value.</li>
  PRINT POLICY_NUMBER
</ol>
  FEO FIELDGROUP VEHICLE
 
      PRINT MAKE AT 5 AND MODEL
As an example of item 1, suppose the <code>MAKE</code> and <code>MODEL</code> fields in the example above are defined with <var>DEFAULT-VALUE</var> <code>MAKE0</code> and <code>MODEL0</code>, respectively. Then the following loop prints occurrence 1 of each field within field group context:
      PRINT '1)' AND CLAIM_NUMBER
<p class="code">IN POLICIES FR WHERE POLICY_NUMBER = 100013
      PRINT '2)' AND CLAIM_NUMBER(2)
FEO FIELDGROUP VEHICLE  
      PRINT '3)' AND CLAIM_NUMBER(3)
  PRINT MAKE AND MODEL
  END FOR
END FOR
END FOR
END FOR </p>
END
<p>
100095
The result is: </p>
  VOLKSWAGEN NEW BEETLE
<p class="output">AUDI A4 QUATTRO
1)
CADILLAC SEVILLE
2)
MAKE0 MODEL0 </p>
3)
  MITSUBISHI ECLIPSE
1) 100059
2) 100064
3)
  CHEVROLET SUBURBAN
1)
2)
3)


BEGIN
As an example of item 2, take the loop seen earlier:
<p class="code">IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013
  PRINT '1' AND MAKE AND MODEL
  PRINT '2' AND MAKE(2) AND MODEL(2)
  PRINT '3' AND MAKE(3) AND MODEL(3)
END FOR</p>
<p>
The <code>PRINT</code> for <code>MAKE(3)</code> and <code>MODEL(3)</code> in the loop (occurrence <i>n</i>, <i>not</i> in containing-fieldgroup context) produces the default values: </p>
<p class="output">3 MAKE0 MODEL0 </p>


===Updating fields in a field group===
<p>
In addition to retrieving field values inside an <var>FEO FIELDGROUP</var> loop, fields in that field group can be updated, that is, added, inserted, changed, and deleted: </p>
<p class="code">BEGIN
%GLASSES IS STRING LEN 50
%GLASSES IS STRING LEN 50
%GLASSES = 'CORRECTIVE LENSES REQUIRED'
%GLASSES = 'CORRECTIVE LENSES REQUIRED'
 
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013
   FEO FIELDGROUP DRIVER
   FEO FIELDGROUP DRIVER
Line 640: Line 1,320:
   END FOR
   END FOR
   BACKOUT
   BACKOUT
END
END </p>
 
<p>
BEFORE ..
The <var>[[Basic SOUL statements and commands#Print  All Fieldgroup Information (PAFGI) statement|PAFGI]]</var> statement uses a backslash (<tt>\</tt>) to identify the start of a fieldgroup and a forward slash (<tt>/</tt>) to identify the end of the fieldgroup. The result is: </p>
<p class="output">BEFORE ..
\DRIVER = 1
\DRIVER = 1
  DRIVER_ID = 100034
  DRIVER_ID = 100034
Line 663: Line 1,344:
\DRIVER = 2
\DRIVER = 2
</p>
</p>
 
<blockquote class="note">
===Constraint violations for field groups===
<p>
For ADD, INSERT, and DELETE statements, you can get an EXACTLY-ONE constraint violation, as well as other field constraint violations. In the previous example, had you attempted to add, insert, or delete the field DRIVER_ID, you would have received the following error message:
<b>Note:</b> <var>EXACTLY-ONE</var> fields cannot be <var>ADD</var>ed, <var>INSERT</var>ed or <var>DELETE</var>d. You can only <var>CHANGE</var> them, as shown in this example:
<p class="code">
M204.2853: (ADD/DELETE/INSERT) NOT ALLOWED FOR EXACTLYONE FIELD
</p>
</p>
In general, the behavior of updating statements mimics the Model 204 handling of exception cases. For example, a DELETE of an occurrence that is not there is ignored. A CHANGE statement for an absent occurrence becomes an ADD statement.
<p class="code">BEGIN
 
===Referencing fields in nested field groups===
You can reference a field from the containing context even inside a field group context. For example,
<p class="code">
BEGIN
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013
   FEO FIELDGROUP DRIVER
   FEO FIELDGROUP VEHICLE
       PRINT POLICY_NUMBER AND DRIVER_NAME
       ADD MAKE = 'XXX'
      INSERT MAKE(1) = %NOTHING
      DELETE MAKE
      CHANGE COLOR(4) TO 'YYYY'
   END FOR
   END FOR
END
  BACKOUT
END </p>
<p>
The result is: </p>
<p class="output"><nowiki>*** 1 M204.2853: ADD NOT ALLOWED FOR EXACTLY-ONE FIELD
== 'XXX'
*** 2 M204.0228: PART OF STATEMENT IGNORED
*** 3 M204.2853: INSERT NOT ALLOWED FOR EXACTLY-ONE FIELD
== %NOTHING
*** 4 M204.0228: PART OF STATEMENT IGNORED
*** 5 M204.2853: DELETE NOT ALLOWED FOR EXACTLY-ONE FIELD
*** M204.1042: COMPILATION ERRORS</nowiki>
</p>
</blockquote>
 
In addition to this <var>EXACTLY-ONE</var> constraint violation, other fieldgroup field constraint violations are possible. In general, the behavior of updating statements for fieldgroups mimics the Model 204 handling of exception cases. For example, a <var>DELETE</var> of an occurrence that is not there is ignored. A <var>CHANGE</var> statement for an absent occurrence becomes an <var>ADD</var> statement.
 
===Deleting field groups===
As with multiply occurring fields, the <var>DELETE</var> and <var>DELETE EACH</var> statements are available for deleting a field group. Instead of specifying the name of the field to be deleted, you specify the keyword <var>FIELDGROUP</var> followed (optionally) by the name of the field group, as shown on the [[Data maintenance#Deleting a field group|Data maintenance page]].
 
====Deleting all occurrences====
Since <var>FEO</var> on field groups has the same semantics as those (described in [[#Deleting occurrences of fields|Deleting occurrences of fields]] above) on a single field, <i><b>avoid FEO for deleting all occurrences of a field group</b></i>. Further details are provided in [[#Deleting all occurrences and the FEO anomaly|the next subsection]].
<p>
The three approaches to deleting all occurrences of a field group are: </p>
<ul>
<li>Use the <var>DELETE EACH</var> syntax (within a record loop, but without an FEO loop).<p>
For example:</p>
<p class="code">IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013
  DELETE EACH FIELDGROUP DRIVER
END FOR</p></li>


100013 CUMMINGS, BETTY S
<li>Loop backward through the set of occurrences in an index loop.
100013 CUMMINGS, EDDIE R
<p>For example:</p>
100013 CUMMINGS, LEE V
<p class="code">FOR EACH RECORD WHERE POLICY_NUMBER = 100013
100013 CUMMINGS, MARY U
   CTX: COUNT OCCURRENCES OF FIELDGROUP DRIVER
100013 CUMMINGS, ROBERT T
   FOR %X FROM COUNT IN CTX TO 1 by -1
</p>
      DELETE FIELDGROUP DRIVER(%X)
In other words, you can delete every other occurrence of a field groups:
   END FOR
<p class="code">
BEGIN
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013
   PRINT POLICY_NUMBER
  PRINT 'BEFORE...'
FEO FIELDGROUP DRIVER
   PRINT DRIVER_NAME AT 5
END FOR
FEO FIELDGROUP DRIVER
   DELETE FIELDGROUP
END FOR
PRINT 'AFTER....'
  FEO FIELDGROUP DRIVER
PRINT DRIVER_NAME AT 5
END FOR
END FOR
BACKOUT
</p></li>
END


100013
<li>Use <var>[[#FOR ALL OCCURRENCES OF FIELDGROUP (FAO FIELDGROUP) statement|FOR ALL OCCURRENCES]]</var>.
BEFORE...
<p>
    CUMMINGS, BETTY S
For example:</p>
    CUMMINGS, EDDIE R
<p class="code">BEGIN
    CUMMINGS, LEE V
    CUMMINGS, MARY U
    CUMMINGS, ROBERT T
AFTER....
    CUMMINGS, EDDIE R
    CUMMINGS, MARY U
<nowiki>*** M204.1099: TRANSACTION 1 HAS BEEN BACKED OUT</nowiki>
</p>
The following code example shows the difference when you use an FAO statement instead of an FEO statement.
<p class="code">
BEGIN
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013
   PRINT POLICY_NUMBER
   PRINT POLICY_NUMBER
Line 734: Line 1,414:
       PRINT DRIVER_NAME AT 5
       PRINT DRIVER_NAME AT 5
   END FOR
   END FOR
  BACKOUT
END </p>
END
<p>
 
The result is:</p>
100013
<p class="output">100013
BEFORE...
BEFORE...
     CUMMINGS, BETTY S
     CUMMINGS, BETTY S
Line 745: Line 1,425:
     CUMMINGS, ROBERT T
     CUMMINGS, ROBERT T
AFTER....
AFTER....
<nowiki>*** M204.1099: TRANSACTION 2 HAS BEEN BACKED OUT</nowiki>
</p></li>
</ul>


BEGIN
====Deleting all occurrences and the FEO anomaly====
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100095
<var>FEO</var> on field groups has the same semantics as those (described in [[#Deleting occurrences of fields|Deleting occurrences of fields]] above) on a single field. When you iterate in an <var>FEO FIELDGROUP</var>, the next occurrence processed is the occurrence number one greater than the one processed before, whether or not it is one you have already processed (because of insertions) or whether or not you skip one or more (because of deletions).
  PRINT '...BEFORE...'
  FAO FIELDGROUP VEHICLE
This creates a problem with deleting field groups in order in an <var>FEO</var> loop: the internal pointer is updated after each deletion, resulting in a default behavior of deleting every other occurrence of the repeating field group. This behavior can also cause an error when the final deletion is attempted and the pointer is now set to a value larger than the final occurrence. Consider this example:
      PRINT MAKE AND MODEL
<p class="code">BEGIN
  END FOR
  PRINT 'ADD A HONDA PILOT ...'
  ADD FIELDGROUP VEHICLE
      MAKE = HONDA
      MODEL = PILOT
      * ... OTHER VEHICLE FIELDS
  END ADD
  PRINT '...AFTER....'
  FAO FIELDGROUP VEHICLE
      PRINT MAKE AND MODEL
  END FOR
  BACKOUT
END
 
...BEFORE...
VOLKSWAGEN NEW BEETLE
MITSUBISHI ECLIPSE
CHEVROLET SUBURBAN
ADD A HONDA PILOT ...
...AFTER....
VOLKSWAGEN NEW BEETLE
MITSUBISHI ECLIPSE
CHEVROLET SUBURBAN
HONDA PILOT
<nowiki>*** M204.1099: TRANSACTION 6 HAS BEEN BACKED OUT</nowiki>
</p>
===FEO loops and field group context===
On each iteration of a field group FEO loop, the current field group is set as the field group context. If that field group is deleted and a field in that field group is referenced you get a request cancelling error.
 
FEO on field groups has the same semantics as those on a single field. When you iterate in an FEO FIELDGROUP, the next occurrence processed is the occurrence number one greater than the one processed before, whether or not it is one you have already processed (because of insertions) or whether or not you skip one or more (because of deletions).
 
In other words, you can delete every other occurrence of a field group:
<p class="code">
BEGIN
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013
  FEO FIELDGROUP DRIVER
      PRINT POLICY_NUMBER AND DRIVER_NAME
  END FOR
END
 
100013 CUMMINGS, BETTY S
100013 CUMMINGS, EDDIE R
100013 CUMMINGS, LEE V
100013 CUMMINGS, MARY U
100013 CUMMINGS, ROBERT T
 
BEGIN
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013
   PRINT POLICY_NUMBER
   PRINT POLICY_NUMBER
Line 812: Line 1,446:
       PRINT DRIVER_NAME AT 5
       PRINT DRIVER_NAME AT 5
   END FOR
   END FOR
  BACKOUT
END
END
 
</p>
100013
<p>
The result is: </p>
<p class="output">100013
BEFORE...
BEFORE...
     CUMMINGS, BETTY S
     CUMMINGS, BETTY S
Line 825: Line 1,460:
     CUMMINGS, EDDIE R
     CUMMINGS, EDDIE R
     CUMMINGS, MARY U
     CUMMINGS, MARY U
<nowiki>*** M204.1099: TRANSACTION 1 HAS BEEN BACKED OUT</nowiki>
SHOW DIFFERENCE WHEN YOU USE FAO INSTEAD OF FEO:
BEGIN
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013
  PRINT POLICY_NUMBER
  PRINT 'BEFORE...'
  FAO FIELDGROUP DRIVER
      PRINT DRIVER_NAME AT 5
  END FOR
  FAO FIELDGROUP DRIVER
      DELETE FIELDGROUP
  END FOR
  PRINT 'AFTER....'
  FAO FIELDGROUP DRIVER
      PRINT DRIVER_NAME AT 5
  END FOR
  BACKOUT
END
100013
BEFORE...
    CUMMINGS, BETTY S
    CUMMINGS, EDDIE R
    CUMMINGS, LEE V
    CUMMINGS, MARY U
    CUMMINGS, ROBERT T
AFTER....
<nowiki>*** M204.1099: TRANSACTION 2 HAS BEEN BACKED OUT</nowiki>
BEGIN
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100095
  PRINT '...BEFORE...'
  FAO FIELDGROUP VEHICLE
      PRINT MAKE AND MODEL
  END FOR
  PRINT 'ADD A HONDA PILOT ...'
  ADD FIELDGROUP VEHICLE
      MAKE = HONDA
      MODEL = PILOT
      * ... OTHER VEHICLE FIELDS
  END ADD
  PRINT '...AFTER....'
  FAO FIELDGROUP VEHICLE
      PRINT MAKE AND MODEL
  END FOR
  BACKOUT
END
...BEFORE...
VOLKSWAGEN NEW BEETLE
MITSUBISHI ECLIPSE
CHEVROLET SUBURBAN
ADD A HONDA PILOT ...
...AFTER....
VOLKSWAGEN NEW BEETLE
MITSUBISHI ECLIPSE
CHEVROLET SUBURBAN
HONDA PILOT
<nowiki>*** M204.1099: TRANSACTION 6 HAS BEEN BACKED OUT</nowiki>
</p>
</p>
<p>
The intent of the above code is clearly to delete <em>every</em> occurrence of the field group, not every <em>other</em> occurrence, but this anomalous result is maintained as part of SOUL for backward compatibility reasons. </p>
</div> <!-- end of toc limit div -->


===UPDATE field attribute===
====Impact of changing a value====
<p>When the user changes the value of a field, how <var class="product">Model&nbsp;204</var> changes the occurrence depends upon whether the field was defined with the UPDATE IN PLACE or UPDATE AT END attribute, as follows:</p>
<table>
<tr class="head">
<th>Attribute </th>
<th>How the update works...</th>
</tr>
<tr>
<td><var>UPDATE IN PLACE</var> (the default) </td>
<td>The value of the field occurrence is changed but its position in the record is preserved. To change the order of values, the user must delete the old value and add the new one in separate statements.  </td>
</tr>
<tr>
<td><var>UPDATE AT END</var> </td>
<td>The existing occurrence is deleted and the new one is automatically added at the end. UPDATE AT END is normally specified for applications that depend on value rotation to accomplish aging.  </td>
</tr>
</table>
<b>Example</b>
<p>This example includes both approaches to updating. Suppose a record has the following fields:</p>
<p class="code">NAME = RICHARD SMITH
CHILD = HENRY
CHILD = SALLY
CHILD = JANE
</p>
<p>You could use the technique illustrated below to add a last name to each child. (This technique involves the use of %variables, which are discussed in [[Using Variables and Values in Computation#Using Variables and Values in Computation|Using Variables and Values in Computation]].)</p>
<p class="code">BEGIN
FIND.RECS:  FIND ALL RECORDS FOR WHICH
              NAME = RICHARD SMITH
            END FIND
            FOR EACH RECORD IN FIND.RECS
EACH.CHILD    FOR EACH OCCURRENCE OF CHILD
                  %A = VALUE IN EACH.CHILD WITH ' SMITH'
                  CHANGE CHILD = VALUE IN EACH.CHILD TO %A
              END FOR
            END FOR
END
</p>
====If the UPDATE IN PLACE option is specified====
<p>If the UPDATE IN PLACE option has been specified for the CHILD field, then the FOR EACH OCCURRENCE loop change each occurrence of CHILD in turn. </p>
<p>On the first pass through the loop, the first value, HENRY, is selected and changed to HENRY SMITH. After the first pass, the record looks like this:</p>
<p class="code">NAME = RICHARD SMITH
CHILD = HENRY SMITH
CHILD = SALLY
CHILD = JANE
</p>
<p>At the end of the second pass, SALLY is changed:</p>
<p class="code">NAME = RICHARD SMITH
CHILD = HENRY SMITH
CHILD = SALLY SMITH
CHILD = JANE
</p>
<p>At the end of the third pass, JANE is changed:</p>
<p class="code">NAME = RICHARD SMITH
CHILD = HENRY SMITH
CHILD = SALLY SMITH
CHILD = JANE SMITH
</p>
====If the UPDATE AT END option is specified====
<p>If the UPDATE AT END option has been specified for the CHILD field, the FOR EACH OCCURRENCE loop proceeds as described here.</p>
<p>On the first pass through the FOR EACH OCCURRENCE loop the first value, HENRY, is selected and changed to HENRY SMITH. The act of changing, however, causes the value HENRY to be deleted and the value HENRY SMITH to be added as the last child. Thus after the first pass, the record looks like this:</p>
<p class="code">NAME = RICHARD SMITH
CHILD = SALLY
CHILD = JANE
CHILD = HENRY SMITH
</p>
<p>On the second pass through the loop, JANE, which is now the second occurrence of CHILD, is deleted and JANE SMITH added to the end of the record:</p>
<p class="code">NAME = RICHARD SMITH
CHILD = SALLY
CHILD = HENRY SMITH
CHILD = JANE SMITH
</p>
<p>On the third pass, the third occurrence is JANE SMITH, and the record ends with:</p>
<p class="code">NAME = RICHARD SMITH
CHILD = SALLY
CHILD = HENRY SMITH
CHILD = JANE SMITH
</p>
===Subscripts===
<p>Subscripts can be included in <var class="product">Model&nbsp;204</var> field references to facilitate the selection of particular occurrences of multiply occurring fields. Any field name can be followed by a parenthesized expression. The value of this expression is used as an ordinal number which specifies the desired occurrence of the named field. For example:                </p>
<p class="code">INCIDENT(3)
COURSE.NUMBER(2)
TRANSACTION(A+B)
</p>
<b>Example</b>
<p>This request illustrates a class schedule where subscripts are used to change the room number for a course:</p>
<p class="code">BEGIN
COURSE:  FIND ALL RECORDS FOR WHICH
            REC = ROOM ASSIGNMENT
        END FIND
CHANGE:  FOR EACH RECORD IN COURSE
            IF ROOM(1) EQ '214A' THEN
              CHANGE ROOM(1) TO '566A'
            END IF
            IF ROOM(2) EQ '214A' THEN
              CHANGE ROOM(2) TO '566A'
            END IF
        END FOR
END
</p>
====Subscripted field extraction====
<p>Subscripted field references attempt to maintain their position inside a record much as an FEO loop attempts to maintain its position inside a record. This means that subscripted field references tend to be as efficient as FEO loops. </p>
<p>The following is an example of using subscripted field extraction for a repeating field group:</p>
<p class="code">%INC IS STRING ARRAY (12) NO FS
%IDATE IS STRING ARRAY (12) NO FS
FEOIDATA: FEO INCIDENT
  %INC(OCCURRENCE IN FEOIDATA) = VALUE IN FEOIDATA
%IDATE(OCCURRENCE IN FEOIDATA) = INCIDENT
DATE(OCCURRENCE IN FEOIDATA)
  END FOR
</p>
<p>If you use multiple FEO loops for field group processing, it is possible that using this technique will require additional VTBL resources for procedures with an extremely large number of sorts or subscripted field references.</p>
====Evaluation of subscript expressions====
<p>The evaluation of subscript expressions is subject to the rules for determining an integer result for an arithmetic expression as described in [[Using Variables and Values in Computation#Arithmetic operations|Arithmetic operations]].      </p>
====Statements and phrases with which you cannot use subscripts====
<p>Subscripted field references can appear anywhere that unsubscripted references can, except in the following statements and phrases:</p>
<table>
<tr class="head">
<th>Statements you cannot use with subscripts </th>
<th>
Because...</th>
</tr>
<tr>
<td><var>ADD</var> </td>
<td>Adds a new occurrence of a field.    </td>
</tr>
<tr>
<td><var>BY EACH phrase</var> in the <var>SORT RECORDS</var> </td>
<td>Loops through all occurrences of a field.    </td>
</tr>
<tr>
<td><var>DELETE EACH statement</var> </td>
<td>Loops through all occurrences of a field.    </td>
</tr>
<tr>
<td><var>EACH phrase</var> in a <var>PRINT</var> specification</td>
<td>Loops through all occurrences of a field.    </td>
</tr>
<tr>
<td><var>FILE</var> </td>
<td>Deals with fields having the INVISIBLE attribute, which cannot be the object of subscripted references.    </td>
</tr>
<tr>
<td><var>FIND</var></td>
<td>Locates records without regard to which occurrence of a field contains the desired value.    </td>
</tr>
<tr>
<td><var>FOR EACH OCCURRENCE</var></td>
<td>Loops through all occurrences of a field. Field references within FOR EACH OCCURRENCE loops can be subscripted, depending upon the individual statements in which the references appear.    </td>
</tr>
<tr>
<td><var>FOR EACH VALUE</var> </td>
<td>Loops through all values of all occurrences of the specified field. Field references within FOR EACH VALUE loops are not allowed at all, unless the field reference is embedded in a nested FOR EACH RECORD loop. See [[Value Loops#Setting up a value loop on one field and printing a value of another|Setting up a value loop on one field and printing a value of another]] for more information.</td>
</tr>
<tr>
<td><var>IN ORDER BY EACH phrase</var> in the <var>FOR EACH RECORD </var></td>
<td>Loops through all occurrences of a field.    </td>
</tr>
<tr>
<td><var>PRINT n </var></td>
<td>Loops through all occurrences of a field.    </td>
</tr>
<tr>
<td><var>STORE RECORD</var></td>
<td>Adds fields in the order of appearance in the statement.    </td>
</tr>
</table>
====Unsubscripted field references====
<p>An unsubscripted field reference in a context in which subscripted references are allowed is always equivalent to a subscripted reference with a value of one.</p>
====Do not use subscripts with INVISIBLE fields====
<p>You cannot make subscripted references to fields that have the INVISIBLE attribute (see the discussion on field attributes in [[Field Attributes#Field Attributes|Field Attributes]]). These fields are not truly multiply occurring, although they can have several different values in a single record. A subscript specified for a field with the INVISIBLE attribute is ignored.            </p>
===Subscript validity rules===
<p>The rules presented below indicate whether or not a subscript value is valid and what action to take if the value is not valid. These rules take into account:    </p>
<ul>
<li>The value of the subscript</li>
<li>The context in which the subscript appears</li>
<li>The description of the subscripted field </li>
</ul>
====Explanation of the rules====
<p>In these rules, two quantities are used:</p>
<ol>
<li>N is the maximum number of occurrences that can be stored in a record for a given field. For a preallocated field, N equals the value of n in the OCCURS clause of the field's description. For other fields, N has no limit.      </li>
<li>P is the number of nonempty occurrences of the referenced field found in the specified record when the reference is evaluated. </li>
</ol>
<p>For a summary of rules for preallocated fields, refer to the discussion on preallocated fields in [[Data Maintenance#Storing values in preallocated fields|Storing values in preallocated fields]].    </p>
====INSERT statement====
<p>The INSERT statement, like the ADD statement, is used for adding new occurrences of a field. INSERT is used to add occurrences when the order of the values is important and the values are added out of order.  </p>
<p>The INSERT statement is supported in remote file and scattered group contexts. The INSERT statement is not supported for Large Object fields.</p>
<b>Syntax</b>
<p>The format of the INSERT statement is:</p>
<p class="code">INSERT fieldname <var>[ </var>(subscript)<var>]</var> = value
</p>
<b>Example</b>
<p>Assume a record with these fields:</p>
<p class="code">DEPT = PERSONNEL
DEPT = FINANCE
DEPT = MARKETING
</p>
<p>The following statement:</p>
<p class="code">INSERT DEPT(3) = ACCOUNTING
</p>
<p>results in the record:</p>
<p class="code">DEPT = PERSONNEL
DEPT = FINANCE
DEPT = ACCOUNTING
DEPT = MARKETING
</p>
<b>Subscript validity rules</b>
<p>[[#INSERT statement|INSERT statement]] lists validity rules for subscripts. In this table:</p>
<ul>
<li>P is the number of occurrences of the field in the record when the INSERT statement is issued. </li>
</li>
<li>S is the subscript specified in the INSERT statement. </li>
<table>
<caption>Subscript validation rules</caption>
<tr class="head">
<th>If P and S
values are: </th>
<th>
Then <var class="product">Model&nbsp;204</var> takes this action:</th>
</tr>
<tr>
<td>P > S</td>
<td>Inserts the new occurrence in front of the former Sth occurrence; the new occurrence becomes the current Sth occurrence of the field.</td>
</tr>
<tr>
<td>P < S</td>
<td>Inserts the new occurrence of the field after the Pth occurrence; the new occurrence becomes the (P+1) occurrence.</td>
</tr>
<tr>
<td>P = 0</td>
<td>Adds the new value at the end of the record, as in the ADD statement.</td>
</tr>
<tr>
<td>S = 0, or
no subscript</td>
<td>Treats the new value as if S = 1 and inserts the new value as the first occurrence, in front of any former occurrence of the field.</td>
</tr>
<tr>
<td>S < 0</td>
<td>Does not add a new occurrence.</td>
</tr>
</table>
</li>
</ul>
<p>For fields with the INVISIBLE attribute, only the index is affected.  </p>
====PRINT statement====
<p>In retrieval statements such as PRINT, subscript values less than zero or greater than P are invalid. Invalid references of this kind cause the null value to be returned. A subscript of zero returns the value of the first occurrence.      </p>
====DELETE statement====
<p>In the DELETE statement, subscript values less than one or greater than P are invalid. If an invalid reference of this kind is made, no action is taken. </p>
<p>If several occurrences of a field are being deleted, you should be careful not to use DELETE in the following way:    </p>
<p class="code">FOR EACH RECORD IN FIND.RECS
  DELETE CLIENT(1)
  DELETE CLIENT(2)
  DELETE CLIENT(3)
END FOR
</p>
<p>As the statements are executed, <var class="product">Model&nbsp;204</var> deletes the first occurrence, CLIENT(1), then locates the current second occurrence, which is the original CLIENT(3), and deletes it. Then, because a third occurrence cannot be found, the operation stops, and the original CLIENT(2) is never deleted.</p>
<p>In such a situation, deleting the occurrences in the reverse order achieves the desired result:</p>
<p class="code">FOR EACH RECORD IN FIND.RECS
  DELETE CLIENT(3)
  DELETE CLIENT(2)
  DELETE CLIENT(1)
END FOR
</p>
<p>The desired result also can be achieved by completely omitting the subscripts, as follows:</p>
<p class="code">FOR EACH RECORD IN FIND.RECS
  DELETE CLIENT
  DELETE CLIENT
  DELETE CLIENT
END FOR   
</p>
====CHANGE statement====
<p>In the CHANGE statement, subscript values less than one or greater than P are treated as attempts to add a new occurrence (P+1). If (P+1) does not exceed N (the maximum number of occurrences that can be stored), the new occurrence is added to the record. If (P+1) does exceed N, the request is cancelled.      </p>
====SORT RECORDS statement====
<p>The use of subscripts in references to the record set yielded by a SORT RECORDS statement sometimes can produce unexpected results. If the BY EACH option appears in a SORT statement, records in which the BY EACH field is multiply occurring are copied several times (once for each field occurrence) to the system scratch file CCATEMP. In each copy, the occurrences of the BY EACH field are rotated, so that the occurrence used as the sort key appears first. Therefore, a subscripted reference to this field can yield different values for different copies of the record.          </p>
[[Category:SOUL]]
[[Category:SOUL]]

Latest revision as of 12:17, 11 December 2023

Overview

A single field name that has different values can be stored repeatedly in a record. For example, the field CHILD in the record shown below is an example of a multiply occurring field:

FATHER = JOHN DOE CHILD = ELIZABETH CHILD = ROBERT

Any field name except for the following types of fields can be present more than once in a record:

  • A NUMERIC RANGE field
  • A sort key
  • A hash key

JOHN DOE might also have a medical record that recorded multiple surgeries, each of which is associated with a surgeon and a date. His record contains repeated instances of these three related fields:

SURGERY_TYPE SURGEON SURGERY_DATE

As of version 7.5 of Model 204, you can define these fields as part of a "physical" field group, which means they are stored, retrieved, and updated as a single instance of, say, the SURGERY field group. You could define these fields as individual multiply occurring fields and group them logically in your SOUL code, but the physical field group structure has significant processing efficiencies.

Field groups may only be defined for records in files that have the FILEORG parameter set to include X'100'. How to define a field group is described in Defining a field group.

The remaining sections on this page describe how to use the basic and the special SOUL statements that operate on multiply occurring fields and field groups.

Fields in the FIND and SORT statements

Retrieval by an exact occurrence value

Assume this sample record:

FATHER = JOHN DOE CHILD = ELIZABETH CHILD = ROBERT

You can use either of these two statements to retrieve the record:

FIND.RECS: FIND ALL RECORDS FOR WHICH CHILD = ELIZABETH END FIND FIND.RECS: FIND ALL RECORDS FOR WHICH CHILD = ROBERT END FIND

The record is not retrieved by either of these statements:

FIND.RECS: FIND ALL RECORDS FOR WHICH CHILD = NOT ELIZABETH END FIND FIND.RECS: FIND ALL RECORDS FOR WHICH CHILD = NOT ROBERT END FIND

Multi-condition range retrievals

You should pay special attention to the effect of multiply occurring fields on multi-condition range retrievals. Undesirable results can occur if a range search is specified for a multiply occurring field. For example, this FIND retrieves the sample record even though neither child's name in the sample is between KEN and PAUL:

FIND.RECS: FIND ALL RECORDS FOR WHICH CHILD IS AFTER KEN AND BEFORE PAUL END FIND

Model 204 evaluates each condition of the FIND separately, then combines the results of each evaluation to build the set of records satisfying all conditions. The BETWEEN operator behaves exactly like any other multi-condition range retrieval when used on a multiply occurring field.

If a range search must be performed on a multiply occurring field, use the IN RANGE clause of the FIND statement. Refer to NUMERIC RANGE and ORDERED NUMERIC attributes for more information about the IN RANGE clause. Better code for the previous example is:

FIND.RECS: FIND ALL RECORDS FOR WHICH CHILD IS ALPHA IN RANGE AFTER KEN AND BEFORE PAUL END FIND

Use of subscripts

You cannot use subscripted references with the FIND statement. Refer to Subscripts for more information about using subscripts with multiply occurring fields.

SORT RECORDS statement

When a multiply occurring field is chosen as a sort key, each record in the set being sorted is processed once using the first occurrence of the field as the key.

EACH modifier with one sort field

If the EACH modifier is present in the SORT statement, and if there are n occurrences of the field in the record, similar records are created in the sorted copy of the original set.

Example

The following statement:

SORT RECORDS IN FIND.RECS BY EACH KEY

generates three temporary records for the single permanent record in which the field named KEY occurs three times:

KEY = COMPUTER KEY = CORPORATION KEY = AMERICA

The records generated are:

1 KEY = COMPUTER 2 KEY = CORPORATION KEY = CORPORATION KEY = AMERICA KEY = AMERICA KEY = COMPUTER 3 KEY = AMERICA KEY = COMPUTER KEY = CORPORATION

These correspond to the n cyclic permutations of the set of field occurrences and, in this example, are sorted into the order 3, 1, 2 (if no other option is selected). Statements that refer to the sorted set, such as PRINT, PRINT EACH, NOTE, and PRINT ALL INFORMATION (PAI), reflect the permutation. No record is generated if n is 0.

EACH modifier with several key fields

When the EACH option is selected for several keys, n occurrences of one key and m of another produce differently permuted records. If either n or m equal 0, no records are generated.

Example

For this request:

BEGIN FIND.RECS: IN CLIENTS FIND ALL RECORDS FOR WHICH INCIDENT = T1 END FIND SORT.RECS: SORT RECORDS IN FIND.RECS BY FULLNAME - AND EACH INCIDENT DATE FOR EACH RECORD IN SORT.RECS PRINT FULLNAME WITH INCIDENT DATE - AT COLUMN 25 END FOR END

The printed output is:

ABBOTT, FRANKLIN G 860323 ABBOTT, GAIL H 861022 ABRAMS, RUTH Z 861115 ABRAMS, RUSSELL Y 870218 ABBOTT, FRANKLIN G 870424 ABBOTT, GAIL H 871123 . . . . . .

If no occurrences are present

A record that does not contain at least one occurrence of the INCIDENT DATE field produces no printed output. Similarly, the following statement produces nothing for those records that do not have at least one occurrence of both A and B:

SORT RECORDS IN FIND.RECS BY EACH A AND EACH B

Fields inside record loops

This section describes by SOUL statement aspects of working with fields in a record loop (such as For Each Record or For Record context. Certain statements operate differently on multiply occurring fields than on singly occurring fields.

SOUL provides several statements for handling multiply occurring fields and field groups. Field groups are supported as of Model 204 version 7.5. While all these statements can also be used with singly occurring fields, they are most useful for multiply occurring fields. They are:

  • COUNT OCCURRENCES (used with singly occurring fields, too)
  • FOR EACH OCCURRENCE (used with singly occurring fields, too)
  • DELETE EACH OCCURRENCE

Note: See also the INSERT statement, which you can use to add new occurrences of a field.

Field references in expressions

A field's value can be references in a SOUL expression simply by specifying the field name with an optionl subscript in parentheses. If no subscript is specified, occurrence 1 is assumed. For example, the following assigns the value of field Foo to %foo and adds the first two occurrences of field bar to stringlist %sl:

%foo is string len 31 %sl is object stringlist ... for each record in %recordset %foo = foo %sl = new %sl:add(bar(1)) %sl:add(bar(2)) ... end for

NOTE statement

Only first occurrence is noted

The NOTE statement notes only the first occurrence of a multiply occurring field. For example, only the first occurrence of the field (CHILD = ELIZABETH) are noted by:

KEEP.CHILD: NOTE CHILD

Use of subscripts

In order to note a particular occurrence of a multiply occurring field, the field name must be subscripted. Refer to Subscripts for detailed information on subscripted field names and usage.

Print and Print n statements for fields

Print statement

The Print statement prints only the first occurrence of a field in a record.

If there is more than one value of a field in a record, you can use the modifier Each in a Print statement to print out all the values on a single line, with a single space between values.

Example 1

print each incident

yields:

T1 T2 T1 T3 T2 T1

Example 2

If the field is given a column position, as in:

print fullname with each incident at column 18

values are printed one to a line and positioned at the column specified.

ABBOTT, GAIL H T1 - T3 - T1 - T3 - T2 - T1

Example 3

A field cited in a Print statement after a multiply occurring field is printed on the same line as the last value of the multiply occurring field. If you change the Print statement in the sample request as here:

print fullname with each incident at column 18 - with policy no at column 23 - with state at column 32

Output like this results:

ABBOTT, GAIL H T1 - T3 - T1 - T3 - T2 - T1 100340 CALIFORNIA

Use of Print with subscripts

See the Print statement subscript rules for a description of using Print with field subscripts.

Print n statement

"Long logical fields," such as abstracts, evaluations, or statements of purpose, can be stored as a multiply occurring field. Each occurrence can contain as many as 255 characters.

You can use this PRINT statement to print such a field as a paragraph:

PRINT n fieldname

where n must be a number less than 32,768.

If you do not require the "pieces" of a "long logical field" to be available as individual field occurrences, you can, instead, store such values in a single BLOB/CLOB field occurrence.

Output format

The PRINT n statement prints up to n lines of text, composed of all of the occurrences of the field concatenated in order. Nothing is inserted. At most, n lines are printed; any extra lines are ignored.

Ordinarily, a PRINT statement that produces more than one line inserts a hyphen in the continuation column of the output device. Instead of using the continuation column, this form of PRINT attempts to end each line with a complete word delimited by spaces. If there is insufficient space to fit the last word on a line, the word is hyphenated arbitrarily and continued on the next line.

The AT COLUMN and TO COLUMN clauses can be used to adjust the output to a narrower column. When used together with PRINT n, text is broken at word boundaries to fit within the column. The AT clause does not affect the first line, and the TO clause does not affect the last line (unless the line limit n is exceeded). This allows indenting.

The AT or TO column options cannot accept negative numbers or numbers greater than 32767 for the column.

Procedures containing PRINT statements with negative numbers or numbers greater than 32767 fail at compile time with the following counting error message:

M204.0263: AT/TO MUST BE BETWEEN 1 AND 32767

Example

This example illustrates the use of the PRINT n statement:

BEGIN PRINT '1234567890123456' STORE RECORD TEXT = 'NOW IS THE T' TEXT = 'IME FOR' TEXT = ' ALL GOOD MEN TO' ITEM = 1 END STORE FD.REC: FIND ALL RECORDS FOR WHICH ITEM = 1 END FIND FOR 1 RECORD IN FD.REC PRINT.TEXT: PRINT '' AT COLUMN 5 WITH 3 TEXT - AT COLUMN 3 TO 16 END FOR END

The output produced is:

1234567890123456 NOW IS THE TIME FOR ALL GOOD MEN TO

The PRINT.TEXT statement concatenates a null value (two single quotes with no space between them) with the PRINT n form to indent the first line of text by using the location of the null value (column 5).

Note that the printing stops short of column 16 to avoid truncating TIME and GOOD.

Use with subscripts

You cannot use subscripted references with the PRINT n statement. Refer to Subscripts for more information about using subscripts with multiply occurring fields.

Is tests

There are three tests that can be applied to field names via Is operators:

Is DefinedReturns 1 if the field is defined in the current file or 0, if not. This operator really only useful in a record loop in group context where a field might be defined in some, but not all files. Subscripts are not allowed for Is Defined.
Is PresentReturns 1 if the specified or implicit (1) occurrence of the field is present in the current record or 0, if not. Subscripts are allowed for Is Present.
Is VisibleReturns 1 if the field is visible in the current file or 0, if not. This operator really only useful in a record loop in group context where a field might be visible in some, but not all files. Subscripts are not allowed for Is Visible.

The Not keyword can be specified after Is to invert the value returned by any of these operators. The following illustrates use of the Is Defined and Is Present tests in a loop in group context:

%rs is object recordset in group foobar ... for each record in %rs if foo is defined then add foo = "This is foo" end if if bar is not present then add bar = "This is bar" end if ... end for

ADD, INSERT, CHANGE, and DELETE statements

The ADD, INSERT, and CHANGE statements, and both forms of the DELETE statement, are supported in remote file and scattered group contexts.

ADD statement

The ADD statement places new occurrences of a field after existing occurrences. For example, if new children are added to the sample record, the additions are placed last. Thus:

ADD CHILD = SARAH ADD CHILD = PATRICK

results in this order in the record:

FATHER = JOHN DOE CHILD = ELIZABETH CHILD = ROBERT CHILD = SARAH CHILD = PATRICK

INSERT statement

The INSERT statement, like the ADD statement, is used for adding new occurrences of a field. INSERT is used to add occurrences when the order of the values is important and the values are added out of order.

INSERT is supported in remote file and scattered group contexts, but it is not supported for Large Object fields.

Syntax

The format of the INSERT statement is:

INSERT fieldname [(subscript)] = value

Example

Assume a record with these fields:

DEPT = PERSONNEL DEPT = FINANCE DEPT = MARKETING

The following statement:

INSERT DEPT(3) = ACCOUNTING

results in this record:

DEPT = PERSONNEL DEPT = FINANCE DEPT = ACCOUNTING DEPT = MARKETING

See also

See (below) the subscript validity rules for INSERT.

Change statement

The basic Change statement alters only the first occurrence of a field in a record.

You can use the following form of the Change statement if there is more than one occurrence:

Change fieldname = value1 TO value2

This form, like the basic Change statement, can be used only inside a For Each Record loop. It deletes the first occurrence of the pair fieldname = value1 from a record, and adds the pair fieldname = value2 to the record. The new value is added either in the position occupied by the original value or at the end of the record, depending upon the update attribute specified for the field by the file manager. See the discussion in UPDATE field attribute.

If the specified field, fieldname = value1, does not appear in the record, fieldname = value2 is simply added to the record. If the specified fieldname = value1 pair appears more than once in the record, only the first occurrence of it is deleted. The pair, fieldname = value2, is added just once. Occurrences of the field name that have other values are not altered by the statement.

DELETE statement

The DELETE statement deletes only the first occurrence of the field in the record by default.

Two forms of DELETE statement

For multiply occurring fields, two forms below of the DELETE statement are provided.

Note: These statements are only for use inside a FOR EACH RECORD loop.

To delete... Syntax Usage
A particular occurrence DELETE fieldname = value To delete the first occurrence of the pair, fieldname = value, from a record.

Occurrences of the field that have other values are not removed. If the field with the specified value occurs more than once in the record, only the first occurrence is deleted. If that field cannot be found, no deletion occurs.

Every occurrence of the field in the record DELETE EACH fieldname To delete all occurrences of the specified field name. The field to be deleted cannot have the INVISIBLE attribute.
See also

FOR EACH RECORD statement

The FOR EACH RECORD IN ORDER BY statement retrieves and loops on only the first occurrence of a field in a record.

EACH modifier

If there is more than one value of a field in a record, the special modifier, EACH, can be used to retrieve and loop on all values of the field.

The EACH modifier only can be used on a FOR EACH RECORD statement that specifies index order processing (the IN ORDER BY fieldname clause must be used, and the field must have the ORDERED attribute). The VALUE IN phrase must be used to retrieve the current value of the ORDERED field driving the loop.

Example

This example of the FOR EACH RECORD IN ORDER BY statement returns each record in order by each value of the ORDERED field:

BEGIN FR1: FOR EACH RECORD IN ORDER BY EACH INCIDENT DATE PRINT VALUE IN FR1 WITH FULLNAME AT COLUMN 25 END FOR END

Here is sample output:

760323 ABBOTT, FRANKLIN G 761022 ABBOTT, GAIL H 761115 ABRAMS, RUTH Z 770218 ABRAMS, RUSSELL Y . . .

COUNT OCCURRENCES OF statement

The COUNT OCCURRENCES OF (CTO) statement counts the number of occurrences of the named field in the current record.

Syntax

The format of the COUNT OCCURRENCES statement is:

label: COUNT OCCURRENCES OF fieldname

or

label: CTO fieldname

The field name cannot be subscripted. The COUNT OCCURRENCES OF statement can appear only within a FOR EACH RECORD loop.

The COUNT OCCURRENCES statement is supported in remote file and scattered group contexts.

COUNT IN clause

The COUNT IN clause refers to the count obtained by the COUNT OCCURRENCES OF statement. For example:

BEGIN FIND.RECS: IN VEHICLES FIND ALL RECORDS END FIND FOR 5 RECORDS IN FIND.RECS NO.OF.VINS: COUNT OCCURRENCES OF VIN PRINT OWNER POLICY WITH ' INSURES ' WITH - COUNT IN NO.OF.VINS WITH ' VEHICLE(S)' END FOR END

The request above generates the following output:

100025 INSURES 1 VEHICLE(S) 100030 INSURES 1 VEHICLE(S) 100032 INSURES 1 VEHICLE(S) 100051 INSURES 1 VEHICLE(S) 100058 INSURES 1 VEHICLE(S)

FOR EACH OCCURRENCE OF loops

On each loop of FOR EACH OCCURRENCE OF (FEO), the VALUE IN and OCCURRENCE IN labels refer to value and position, respectively, of the next field occurrence, starting with occurrence 1 and increasing by 1 for each pass through the loop. When the next field occurrence number is greater than the number of field occurrences in the record, the loop terminates.

Syntax

The format of the FOR EACH OCCURRENCE loop is:

label: FOR EACH OCCURRENCE OF fieldname

Alternatively,

label: FEO fieldname

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

To work with specific field values inside an FEO loop, two styles of reference are supported: OCCURRENCE IN label, which is used as a field subscript, and VALUE IN label, which is a direct reference to a field occurrence.

Example

Consider this record:

FIRST NAME = RICHARD LAST NAME = SMITH CHILD = HENRY CHILD = SALLY CHILD = JANE ADDRESS = AVON DRIVE

This request creates a separate record for each child.

BEGIN FD.REC: FIND ALL RECORDS FOR WHICH FIRST NAME = RICHARD LAST NAME = SMITH END FIND FOR EACH RECORD IN FD.REC NOTE.ADD: NOTE ADDRESS CHILD.LOOP: FOR EACH OCCURRENCE OF CHILD PRINT VALUE IN CHILD.LOOP STORE RECORD FIRST NAME = VALUE IN CHILD.LOOP LAST NAME = SMITH ADDRESS = VALUE IN NOTE.ADD END STORE END FOR END FOR END

Simulating a FOR EACH VALUE loop

The FOR EACH OCCURRENCE OF statement can be used to simulate a FOR EACH VALUE loop if the field contains a static collection of known values. Consider the values of states. You set up a single record that has a multiply occurring field in sorted sequence as follows:

TYPE = STATE CODE = ALABAMA CODE = ARKANSAS . . CODE = WYOMING

These statements begin a request to generate a report in order by state:

BEGIN FIND.TYPE: FIND ALL RECORDS FOR WHICH TYPE = STATE END FIND FOR EACH RECORD IN FIND.TYPE EACH.CODE: FOR EACH OCCURRENCE OF CODE FIND.STATE: FIND ALL RECORDS FOR WHICH STATE = VALUE IN EACH.CODE END FIND FOR EACH RECORD IN FIND.STATE . . .

This method has the advantage of eliminating the internal sort required by FOR EACH VALUE IN ORDER and provides an easy way to simulate a FOR EACH VALUE loop for a field that does not have the FRV or ORDERED attribute. However, a change in the list of values can require a recreation of the TYPE record to keep the values in order. Recreation is not required if the UPDATE IN PLACE field attribute has been specified for the CODE field and if the new and old values occupy the same place in order. Recreation also is not required if the INSERT statement is used correctly.

VALUE IN with FOR EACH OCCURRENCE loops

VALUE IN references to FOR EACH OCCURRENCE (FEO) statements from outside the FEO loop does not compile. Such references receive the following error message:

M204.0311 UNACCEPTABLE STATEMENT REFERENCE

At runtime, the space occupied in STBL by the FEO value is reclaimed after each iteration of the FEO loop (including the last). This results in a reduction in the runtime STBL space requirements of some programs that use FEO.

To avoid getting that error, move the value into a %variable inside the FEO loop, and then reference the %variable outside the FEO loop.

FOR EACH OCCURRENCE against INVISIBLE fields

Using FOR EACH OCCURRENCE (FEO) syntax against INVISIBLE fields is a waste of processing time. An FEO causes a scan of the current Table B record, but INVISIBLE fields are not stored in Table B, so an FEO against an INVISIBLE field can never find any occurrences.

FEO syntax against an INVISIBLE field results in a compilation error:

M204.0320 Field is Invisable. Field = fieldname

UPDATE field attribute

Impact of changing a value

When the user changes the value of a field, how Model 204 changes the occurrence depends upon whether the field was defined with the UPDATE IN PLACE or UPDATE AT END attribute, as follows:

Attribute How the update works...
UPDATE IN PLACE (the default) The value of the field occurrence is changed but its position in the record is preserved. To change the order of values, the user must delete the old value and add the new one in separate statements.
UPDATE AT END The existing occurrence is deleted and the new one is automatically added at the end. UPDATE AT END is normally specified for applications that depend on value rotation to accomplish aging.
Example

This example includes both approaches to updating. Suppose a record has the following fields:

NAME = RICHARD SMITH CHILD = HENRY CHILD = SALLY CHILD = JANE

You could use the technique illustrated below to add a last name to each child. (This technique involves the use of %variables, which are discussed in Using variables and values in computation.)

BEGIN FIND.RECS: FIND ALL RECORDS FOR WHICH NAME = RICHARD SMITH END FIND FOR EACH RECORD IN FIND.RECS EACH.CHILD FOR EACH OCCURRENCE OF CHILD %A = VALUE IN EACH.CHILD WITH ' SMITH' CHANGE CHILD = VALUE IN EACH.CHILD TO %A END FOR END FOR END

If the UPDATE IN PLACE option is specified

If the UPDATE IN PLACE option has been specified for the CHILD field, then the FOR EACH OCCURRENCE loop change each occurrence of CHILD in turn.

On the first pass through the loop, the first value, HENRY, is selected and changed to HENRY SMITH. After the first pass, the record looks like this:

NAME = RICHARD SMITH CHILD = HENRY SMITH CHILD = SALLY CHILD = JANE

At the end of the second pass, SALLY is changed:

NAME = RICHARD SMITH CHILD = HENRY SMITH CHILD = SALLY SMITH CHILD = JANE

At the end of the third pass, JANE is changed:

NAME = RICHARD SMITH CHILD = HENRY SMITH CHILD = SALLY SMITH CHILD = JANE SMITH

If the UPDATE AT END option is specified

If the UPDATE AT END option has been specified for the CHILD field, the FOR EACH OCCURRENCE loop proceeds as described here.

On the first pass through the FOR EACH OCCURRENCE loop the first value, HENRY, is selected and changed to HENRY SMITH. The act of changing, however, causes the value HENRY to be deleted and the value HENRY SMITH to be added as the last child. Thus after the first pass, the record looks like this:

NAME = RICHARD SMITH CHILD = SALLY CHILD = JANE CHILD = HENRY SMITH

On the second pass through the loop, JANE, which is now the second occurrence of CHILD, is deleted and JANE SMITH is added to the end of the record:

NAME = RICHARD SMITH CHILD = SALLY CHILD = HENRY SMITH CHILD = JANE SMITH

On the third pass, the third occurrence is JANE SMITH, and the record ends with:

NAME = RICHARD SMITH CHILD = SALLY CHILD = HENRY SMITH CHILD = JANE SMITH

Deleting occurrences of fields

Note: Deleting occurrences of a field using a FOR EACH OCCURRENCE loop is problematic (as will be shown), and you are advised to use either of these:

  • An index loop that deletes the occurrences in reverse order
  • A DELETE EACH statement within a record loop, to delete all occurrences
Example: Looping backward through the occurrences

There is no specific syntax for reversing the order of an FEO loop, so you must count the occurrences and step backward through them in an index loop, like this:

for each record in %myRecordSet childCount: - count occurrences of child for %x from count in childCount to 1 by -1 delete child(%x) end for end for

Stepping backward through a set is also the standard practice in StringList and NamedArrayList objects, when deletions may occur. This technique is especially worth keeping in mind in situations where only some occurrences may be deleted.

Example: Using DELETE EACH

Use DELETE EACH inside a record loop but without an FEO loop:

for each record in vehicles delete each child end for

Example: Using FEO and unsubscripted DELETE

In general, it is a mistake to reference a multiply occurring field in an FEO loop without using an occurrence subscript. An unsubscripted reference always refers to the first occurrence of a field. For example, suppose the following statement is specified for the RICHARD SMITH record described previously:

for each occurrence in fd.rec del.child: - for each occurrence of child delete child end for end for

The record originally contained these CHILD entries:

CHILD=HENRY CHILD=SALLY CHILD=JANE

On the first pass through the loop, the first value, HENRY, is selected and deleted. At the end of the first pass, the record contains these CHILD entries:

CHILD=SALLY CHILD=JANE

On the second pass through the loop, the DELETE CHILD statement again deletes the first occurrence of the field as described in DELETE statement. Since HENRY has already been deleted, Model 204 deletes the first entry, SALLY.

After the second pass, the FOR EACH OCCURRENCE loop terminates. This is because the value in DEL.CHILD should be the next (third) occurrence, but after two passes, only one occurrence remains on the record. Therefore, at the end of the FOR EACH OCCURRENCE loop, the remaining value in the CHILD field is:

CHILD=JANE

Example: FEO and subscripted DELETE

Suppose an occurrence reference is added to the field in the FEO loop:

for each record in fd.rec del.child: - for each occurrence of child delete child(occurrence in del.child) end for end for

The loop above results in every other occurrence being deleted, because the occurrence reference continues to increment upward while the list of occurrences is also shifted. So the second time through the loop, the occurrence pointer is pointing to the item that was formerly the third item, then the fifth item, then the seventh item. Again, this is generally not what the programmer intends.

Subscripts

Subscripts can be included in Model 204 field references to facilitate the selection of particular occurrences of multiply occurring fields. Any field name can be followed by a parenthesized expression. The value of this expression is used as an ordinal number which specifies the desired occurrence of the named field. For example:

INCIDENT(3) COURSE.NUMBER(2) TRANSACTION(A+B)

Example

This request illustrates a class schedule where subscripts are used to change the room number for a course:

BEGIN COURSE: FIND ALL RECORDS FOR WHICH REC = ROOM ASSIGNMENT END FIND CHANGE: FOR EACH RECORD IN COURSE IF ROOM(1) EQ '214A' THEN CHANGE ROOM(1) TO '566A' END IF IF ROOM(2) EQ '214A' THEN CHANGE ROOM(2) TO '566A' END IF END FOR END

Subscripted field extraction

Subscripted field references attempt to maintain their position inside a record much as an FEO loop attempts to maintain its position inside a record. This means that subscripted field references tend to be as efficient as FEO loops.

The following is an example of using subscripted field extraction for a field that is a member of a "logical" field group. An FEO loop populates two arrays with the corresponding values of group members INCIDENT and INCIDENT_DATE:

%inc is string array (12) no fs %idate is string array (12) no fs FeoIdata: feo INCIDENT %inc(occurence in FeoIdata) = value in FeoIdata %idate(occurence in FeoIdata) = INCIDENT_DATE(occurence in FeoIdata) end for

If you use multiple FEO loops for this kind of field group processing, you might require additional VTBL resources for procedures that have an extremely large number of sorts or subscripted field references.

Evaluation of subscript expressions

The evaluation of subscript expressions is subject to the rules for determining an integer result for an arithmetic expression as described in Arithmetic operations.

Statements and phrases with which you cannot use subscripts

Subscripted field references can appear anywhere that unsubscripted references can, except in the following statements and phrases:

Statements you cannot use with subscripts Because...
ADD Adds a new occurrence of a field.
BY EACH phrase in the SORT RECORDS Loops through all occurrences of a field.
DELETE EACH statement Loops through all occurrences of a field.
EACH phrase in a PRINT specification Loops through all occurrences of a field.
FILE Deals with fields having the INVISIBLE attribute, which cannot be the object of subscripted references.
FIND Locates records without regard to which occurrence of a field contains the desired value.
FOR EACH OCCURRENCE Loops through all occurrences of a field. Field references within FOR EACH OCCURRENCE loops can be subscripted, depending upon the individual statements in which the references appear.
FOR EACH VALUE Loops through all values of all occurrences of the specified field. Field references within FOR EACH VALUE loops are not allowed at all, unless the field reference is embedded in a nested FOR EACH RECORD loop. See Setting up a value loop on one field and printing a value of another for more information.
IN ORDER BY EACH phrase in the
FOR EACH RECORD
Loops through all occurrences of a field.
PRINT n Loops through all occurrences of a field.
STORE RECORD Adds fields in the order of appearance in the statement.

Unsubscripted field references

An unsubscripted field reference in a context in which subscripted references are allowed is always equivalent to a subscripted reference with a value of 1.

Do not use subscripts with INVISIBLE fields

You cannot make subscripted references to fields that have the INVISIBLE attribute (see the discussion on field attributes in Field attributes). These fields are not truly multiply occurring, although they can have several different values in a single record. A subscript specified for a field with the INVISIBLE attribute is ignored.

Subscript validity rules

The rules presented for each statement in the subsections that follow indicate whether or not a subscript value is valid and what action to take if the value is not valid. These rules take into account:

  • The value of the subscript
  • The context in which the subscript appears
  • The description of the subscripted field

The rules assume that N, the maximum number of occurrences that can be stored in a record for a given field, is umlimited. This section does not discuss the rules for preallocated fields, where N is limited to the value of n in the OCCURS clause of the field's description.

In the discussions of these rules, two other quantities are used:

  • P is the number of nonempty occurrences of the referenced field found in the specified record when the reference is evaluated.
  • S is the subscript specified in the statement.

INSERT statement rules

If the order of occurrence is important, you can use the INSERT statement to add new occurrences.

The table below lists the validity rules for subscripted references with INSERT:

If P and S values are: Then Model 204 takes this action:
P > S Inserts the new occurrence in front of the former Sth occurrence; the new occurrence becomes the current Sth occurrence of the field.
P < S Inserts the new occurrence of the field after the Pth occurrence; the new occurrence becomes the (P+1) occurrence.
P = 0 Adds the new value at the end of the record, as in the ADD statement.
S = 0, or no subscript Treats the new value as if S = 1 and inserts the new value as the first occurrence, in front of any former occurrence of the field.
S < 0 Does not add a new occurrence.

For fields with the INVISIBLE attribute, only the index is affected.

Print statement rules

In retrieval statements such as Print, subscript values less than zero or greater than P are invalid. Invalid references of this kind cause the null value to be returned. A subscript of zero returns the value of the first occurrence.

DELETE statement rules

In the DELETE statement, subscript values less than one or greater than P are invalid. If an invalid reference of this kind is made, no action is taken.

If several occurrences of a field are being deleted, you should be careful not to use DELETE in the following way:

FOR EACH RECORD IN FIND.RECS DELETE CLIENT(1) DELETE CLIENT(2) DELETE CLIENT(3) END FOR

As the statements are executed, Model 204 deletes the first occurrence, CLIENT(1), then locates the current second occurrence, which is the original CLIENT(3), and deletes it. Then, because a third occurrence cannot be found, the operation stops, and the original CLIENT(2) is never deleted.

In such a situation, deleting the occurrences in the reverse order achieves the desired result:

FOR EACH RECORD IN FIND.RECS DELETE CLIENT(3) DELETE CLIENT(2) DELETE CLIENT(1) END FOR

The desired result also can be achieved by completely omitting the subscripts, as follows:

FOR EACH RECORD IN FIND.RECS DELETE CLIENT DELETE CLIENT DELETE CLIENT END FOR

CHANGE statement rules

In the CHANGE statement, subscript values less than one or greater than P are treated as attempts to add a new occurrence (P+1). If (P+1) does not exceed N (the maximum number of occurrences that can be stored), the new occurrence is added to the record. If (P+1) does exceed N, the request is cancelled.

SORT RECORDS statement rules

The use of subscripts in references to the recordset yielded by a SORT RECORDS statement sometimes can produce unexpected results. If the BY EACH option appears in a SORT statement, records in which the BY EACH field is multiply occurring are copied several times (once for each field occurrence) to the system scratch file CCATEMP. In each copy, the occurrences of the BY EACH field are rotated, so that the occurrence used as the sort key appears first. Therefore, a subscripted reference to this field can yield different values for different copies of the record.

Working with field groups

SOUL provides several statements for handling "physical" field groups. Field groups are supported as of Model 204 version 7.5. For a brief summary of the basic field group operations, see Updating field groups. For information about defining a field group, see the DEFINE FIELDGROUP command.

FOR ALL OCCURRENCES OF FIELDGROUP (FAO FIELDGROUP) statement

The FAO FIELDGROUP statement collects the field group IDs for all occurrences, then processes against that list.

Note: There is no FAO statement for use against multiply occurring fields.

Syntax

The format of the statement is:

FOR ALL OCCURRENCES OF FIELDGROUP [fgname | %%varGrp]

Where:

  • fgname specifies a field group name.
  • %%varGrp specifies a field group name variable.

The difference between an FAO statement and an FEO statement is that the FAO statement is less affected by insertions or deletions of FIELDGROUP fgname within the loop, due to the semantics of field group IDs. This means that deleting the current occurrence within the loop will not affect occurrences referenced in subsequent iterations.

FAO achieves this by scanning the record and building a table of fieldgroup occurrences before processing the FAO loop. Because of this, FAO uses more CPU than an FEO loop, possibly significantly more for a large record with many occurrences of the fieldgroup, especially if the loop is often terminated (with a Loop End) before processing all occurrences. As such, FAO should only be used where the interaction of the loop with fieldgroup insertions and deletions is a concern.

Example

A field group is added to a record in the example below:

BEGIN IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100095 PRINT '...BEFORE...' FAO FIELDGROUP VEHICLE PRINT MAKE AND MODEL END FOR PRINT 'ADD A HONDA PILOT ...' ADD FIELDGROUP VEHICLE MAKE = HONDA MODEL = PILOT * ... OTHER VEHICLE FIELDS END ADD PRINT '...AFTER....' FAO FIELDGROUP VEHICLE PRINT MAKE AND MODEL END FOR END

The result is:

...BEFORE... VOLKSWAGEN NEW BEETLE MITSUBISHI ECLIPSE CHEVROLET SUBURBAN ADD A HONDA PILOT ... ...AFTER.... VOLKSWAGEN NEW BEETLE MITSUBISHI ECLIPSE CHEVROLET SUBURBAN HONDA PILOT

FOR EACH OCCURRENCE OF FIELDGROUP statement

The analog of the FOR EACH OCCURRENCE OF statement for multiply occurring fields is the FOR EACH OCCURRENCE OF FIELDGROUP statement.

Syntax

The format of the FOR EACH OCCURRENCE OF FIELDGROUP statement is:

label: [FEO | FOR EACH OCCURRENCE OF] FIELDGROUP {fgname | %%varGrp}

Where:

  • fgname specifies a field group name.
  • %%varGrp specifies a field group name variable.

Like FEO for multiply occurring fields, you can reference specific field group members inside an FEO FIELDGROUP loop using the OCCURRENCE IN label and VALUE IN label phrases.

For additional FEO FIELDGROUP discussion and examples, see Using a FOR EACH OCCURRENCE loop, below.

FOR FIELDGROUP statement

The FOR FIELDGROUP statement operates on a specific occurrence of a field group. The occurrence is indicated by either an occurrence number or a field group ID number.

Syntax

The format of the statement is:

FOR FIELDGROUP (fgname | %%varGroup) { (occurrence-number) | = fieldgroupID }

Where:

  • fgname specifies a field group name.
  • %%varGroup specifies a field group name variable.
  • occurrence-number specifies an occurrence within that field group. The occurrence number must be enclosed in parentheses.

    The $FIELDGROUPOCCURRENCE function returns the field group occurrence value.

  • fieldgroupID specifies a field group ID – each field group in a record has an ID that's unique among all occurrences of all field groups within the record. A field group ID is assigned when a field group is added to the record, is equal to one more than the highest field group ID that has been assigned in the record, and is not reused if its field group is deleted.

    fieldgroupID must be preceded by an equal sign.

    The $FIELDGROUPID function returns the field group ID.

  • If neither occurrence-number nor fieldgroupID is specified, the first occurrence of the fieldgroup is accessed. That is, for fieldgroup foo is equivalent to for fieldgroup foo(1).

Examples

This FOR FIELDGROUP statement sets the third occurrence of the DRIVER field group as the context for the Print statement:

FR Where ... For Fieldgroup DRIVER(3) Print DRIVER_ID End For End For

This FOR FIELDGROUP statement uses a field name variable to set the DRIVER field group that has ID %fgid as the context for the Print statement:

FR Where ... %driver = 'DRIVER' For Fieldgroup %%driver = %fgid Print DRIVER_ID And DRIVER_NAME End For End For

PAFGI statement

As described in Print All Fieldgroup Information (PAFGI) statement, you can use the PAFGI statement to display the contents of a fieldgroup.

Setting field group context

To access data in a field group, first establish the record context using the FOR EACH RECORD (FR) or FOR RECORD NUMBER (FRN) statement. Then to access the occurrences of the field group, you typically use an FAO FIELDGROUP or FEO FIELDGROUP loop.

Using a FOR ALL OCCURRENCES OF loop

You can set field group context with an FAO loop. For example:

BEGIN IN POLICIES FOR EACH RECORD FAO: FAO FIELDGROUP VEHICLE IF COLOR IS LIKE '*RED*' THEN PRINT 'POLICY:' AT 5 AND POLICY_NUMBER AND - 'COLOR=' WITH COLOR %RED = %RED + 1 END IF END FOR END FOR PRINT 'NUMBER OF RED CARS:' AND %RED END

The result is:

POLICY: 100001 COLOR=VICTORY RED POLICY: 100007 COLOR=RED POLICY: 100011 COLOR=VICTORY RED ... NUMBER OF RED CARS: 214 ...and so on...

Using a FOR EACH OCCURRENCE loop

An FEO loop on a field group establishes a field group context. Inside the loop, references to field group fields are to their occurrences in their field group. For example:

BEGIN IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013 FEO: FEO FIELDGROUP VEHICLE %DRIVER_ID = OTHER_DRIVER PRINT OCCURRENCE IN FEO AND MAKE AND MODEL AND - 'OTHER DRIVER(S):' AT 30 AND EACH OTHER_DRIVER PRINT 'FIRST:' AT 30 AND %DRIVER_ID AND - 'THIRD:' AND OTHER_DRIVER(3) END FOR END FOR END

The result is:

1 AUDI A4 QUATTRO OTHER DRIVER(S): 100035 100037 100036 FIRST: 100035 THIRD: 100036 2 CADILLAC SEVILLE OTHER DRIVER(S): FIRST: THIRD:

On each iteration of a field group FEO loop, the current field group is set as the field group context. If that field group is deleted and a field in that field group is referenced, you get a request cancelling error.

An important difference between an FAO FIELDGROUP statement and an FEO FIELDGROUP statement is that the FAO FIELDGROUP statement collects the field group IDs for all occurrences, then processes against that list. Because of this, it is less affected by insertions or deletions of occurrences of field groups within the loop. Deleting the current occurrence within the loop, for example, will not affect occurrences referenced in subsequent iterations. For further discussion of this, see Deleting all occurrences and the FEO anomaly.

Using a FOR FIELDGROUP block

You can use a field group occurrence established by a FOR FIELDGROUP block as the context for references to its members.

For example, the following PAFGI (PRINT ALL FIELD GROUP INFORMATION) statement outputs the contents of only the third occurrence of field group GRP:

IN FILE WHATEVER FRN %RECNO FOR FIELDGROUP GRP(3) PRINT '*** Third occurrence of field group GRP' PAFGI END FOR END FOR

Accessing an external field while in field group context

You can reference a field from the containing context even inside a field group context. For example:

BEGIN IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013 FEO FIELDGROUP DRIVER PRINT POLICY_NUMBER AND DRIVER_NAME END FOR END

The result is:

100013 CUMMINGS, BETTY S 100013 CUMMINGS, EDDIE R 100013 CUMMINGS, LEE V 100013 CUMMINGS, MARY U 100013 CUMMINGS, ROBERT T

Accessing field group members while not in field group context

You can access field group occurrences by using the occurrence number directly in the FR loop, if the field is EXACTLY-ONE or AT-MOST-ONE. So, instead of using this FEO FIELDGROUP loop to print the MAKE and MODEL for the three vehicles in the VEHICLE field group:

BEGIN FR WHERE POLICY_NUMBER = 100095 FEO FIELDGROUP VEHICLE PRINT MAKE AND MODEL END FOR END FOR END

Output:

VOLKSWAGEN NEW BEETLE MITSUBISHI ECLIPSE CHEVROLET SUBURBAN

You can also access the MAKE and MODEL (EXACTLY-ONE) fields directly within the FR loop by using occurrence numbers:

BEGIN FR WHERE POLICY_NUMBER = 100095 PRINT MAKE AND MODEL PRINT MAKE(2) AND MODEL(2) PRINT MAKE(3) AND MODEL(3) END FOR END

Output:

VOLKSWAGEN NEW BEETLE MITSUBISHI SUBURBAN CHEVROLET SUBURBAN

Note: If MAKE and MODEL are REPEATABLE fields or defined to all field groups (FIELDGROUP *), attempting to access them with just an FR loop will fail.

Accessing field group members while in a containing field group

If field groups are nested, you can retrieve an EXACTLY-ONE field in a field group inside the current context without establishing that nested field group's context. EXACTLY-ONE fields in specific field groups (as opposed to fields defined with FIELDGROUP * that belong to all field groups) can be retrieved outside the field group context. In the following example, CLAIM-NUMBER is an EXACTLY-ONE field in field group CLAIM which is nested within field group VEHICLE:

BEGIN IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100095 PRINT POLICY_NUMBER FEO FIELDGROUP VEHICLE PRINT MAKE AT 5 AND MODEL PRINT '1)' AND CLAIM_NUMBER PRINT '2)' AND CLAIM_NUMBER(2) PRINT '3)' AND CLAIM_NUMBER(3) END FOR END FOR END

The result is:

100095 VOLKSWAGEN NEW BEETLE 1) 2) 3) MITSUBISHI ECLIPSE 1) 100059 2) 100064 3) CHEVROLET SUBURBAN 1) 2) 3)

Handling references to missing occurrences

References to missing occurrences of fieldgroup fields are returned as nulls, except as discussed after the first example below. Invalid occurrence numbers such as negative numbers are ignored, and a 0-numbered occurrence is treated the same as 1.

For example, the third PRINT statement below produces a null (with no error) for a pair of missing occurrences, even if MAKE is defined as EXACTLY-ONE. This is consistent with Model 204 behavior for EXACTLY-ONE and OCCURS fields that are not fieldgroup members (see PRINT statement rules, for example). If you request occurrence 3 of an EXACTLY-ONE or OCCURS 1 field in the record, Model 204 returns a null. In this example, MAKE and MODEL are EXACTLY-ONE fields:

BEGIN IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013 PRINT '1' AND MAKE AND MODEL PRINT '2' AND MAKE(2) AND MODEL(2) PRINT '3' AND MAKE(3) AND MODEL(3) END FOR END

The result is:

1 AUDI A4 QUATTRO 2 CADILLAC SEVILLE 3

The two exceptions to the null-return rule are certain EXACTLY-ONE or AT-MOST-ONE fieldgroup members, as follows:

  1. Occurrence number 1 of an EXACTLY-ONE or AT-MOST-ONE fieldgroup member, in the context of the containing fieldgroup, and defined to also have the DEFAULT-VALUE attribute. A reference to a missing occurrence of such a field returns the DEFAULT-VALUE value.

    References to non-number-1 occurrences of such fields and context return null.

  2. Any occurrence n of an EXACTLY-ONE or AT-MOST-ONE fieldgroup member, not in the context of the containing fieldgroup, if occurrence n of the fieldgroup is present. If such fields are defined to also have the DEFAULT-VALUE attribute, a reference to a missing occurrence returns the DEFAULT-VALUE value.

As an example of item 1, suppose the MAKE and MODEL fields in the example above are defined with DEFAULT-VALUE MAKE0 and MODEL0, respectively. Then the following loop prints occurrence 1 of each field within field group context:

IN POLICIES FR WHERE POLICY_NUMBER = 100013 FEO FIELDGROUP VEHICLE PRINT MAKE AND MODEL END FOR END FOR

The result is:

AUDI A4 QUATTRO CADILLAC SEVILLE MAKE0 MODEL0

As an example of item 2, take the loop seen earlier:

IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013 PRINT '1' AND MAKE AND MODEL PRINT '2' AND MAKE(2) AND MODEL(2) PRINT '3' AND MAKE(3) AND MODEL(3) END FOR

The PRINT for MAKE(3) and MODEL(3) in the loop (occurrence n, not in containing-fieldgroup context) produces the default values:

3 MAKE0 MODEL0

Updating fields in a field group

In addition to retrieving field values inside an FEO FIELDGROUP loop, fields in that field group can be updated, that is, added, inserted, changed, and deleted:

BEGIN %GLASSES IS STRING LEN 50 %GLASSES = 'CORRECTIVE LENSES REQUIRED' IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013 FEO FIELDGROUP DRIVER PRINT 'BEFORE ..' PAFGI ADD DRIVER_RESTRICTIONS = 'MEDICAL RESTRICTIONS PRESENT' INSERT DRIVER_RESTRICTIONS(3) = %GLASSES DELETE DRIVER_RESTRICTIONS(4) CHANGE DRIVER_MARITAL_STATUS TO 'WIDOWED' PRINT 'AFTER ...' PAFGI END FOR BACKOUT END

The PAFGI statement uses a backslash (\) to identify the start of a fieldgroup and a forward slash (/) to identify the end of the fieldgroup. The result is:

BEFORE .. \DRIVER = 1 DRIVER_ID = 100034 DRIVER_NAME = CUMMINGS, BETTY S DRIVER_MARITAL_STATUS = SINGLE DRIVER_GENDER = F DRIVER_DATE_OF_BIRTH = 19791225 /DRIVER = 1 AFTER ... \DRIVER = 1 DRIVER_ID = 100034 DRIVER_NAME = CUMMINGS, BETTY S DRIVER_MARITAL_STATUS = WIDOWED DRIVER_GENDER = F DRIVER_DATE_OF_BIRTH = 19791225 DRIVER_RESTRICTIONS = MEDICAL RESTRICTIONS PRESENT DRIVER_RESTRICTIONS = CORRECTIVE LENSES REQUIRED /DRIVER = 1 BEFORE .. \DRIVER = 2

Note: EXACTLY-ONE fields cannot be ADDed, INSERTed or DELETEd. You can only CHANGE them, as shown in this example:

BEGIN IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013 FEO FIELDGROUP VEHICLE ADD MAKE = 'XXX' INSERT MAKE(1) = %NOTHING DELETE MAKE CHANGE COLOR(4) TO 'YYYY' END FOR BACKOUT END

The result is:

*** 1 M204.2853: ADD NOT ALLOWED FOR EXACTLY-ONE FIELD == 'XXX' *** 2 M204.0228: PART OF STATEMENT IGNORED *** 3 M204.2853: INSERT NOT ALLOWED FOR EXACTLY-ONE FIELD == %NOTHING *** 4 M204.0228: PART OF STATEMENT IGNORED *** 5 M204.2853: DELETE NOT ALLOWED FOR EXACTLY-ONE FIELD *** M204.1042: COMPILATION ERRORS

In addition to this EXACTLY-ONE constraint violation, other fieldgroup field constraint violations are possible. In general, the behavior of updating statements for fieldgroups mimics the Model 204 handling of exception cases. For example, a DELETE of an occurrence that is not there is ignored. A CHANGE statement for an absent occurrence becomes an ADD statement.

Deleting field groups

As with multiply occurring fields, the DELETE and DELETE EACH statements are available for deleting a field group. Instead of specifying the name of the field to be deleted, you specify the keyword FIELDGROUP followed (optionally) by the name of the field group, as shown on the Data maintenance page.

Deleting all occurrences

Since FEO on field groups has the same semantics as those (described in Deleting occurrences of fields above) on a single field, avoid FEO for deleting all occurrences of a field group. Further details are provided in the next subsection.

The three approaches to deleting all occurrences of a field group are:

  • Use the DELETE EACH syntax (within a record loop, but without an FEO loop).

    For example:

    IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013 DELETE EACH FIELDGROUP DRIVER END FOR

  • Loop backward through the set of occurrences in an index loop.

    For example:

    FOR EACH RECORD WHERE POLICY_NUMBER = 100013 CTX: COUNT OCCURRENCES OF FIELDGROUP DRIVER FOR %X FROM COUNT IN CTX TO 1 by -1 DELETE FIELDGROUP DRIVER(%X) END FOR END FOR

  • Use FOR ALL OCCURRENCES.

    For example:

    BEGIN IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013 PRINT POLICY_NUMBER PRINT 'BEFORE...' FAO FIELDGROUP DRIVER PRINT DRIVER_NAME AT 5 END FOR FAO FIELDGROUP DRIVER DELETE FIELDGROUP END FOR PRINT 'AFTER....' FAO FIELDGROUP DRIVER PRINT DRIVER_NAME AT 5 END FOR END

    The result is:

    100013 BEFORE... CUMMINGS, BETTY S CUMMINGS, EDDIE R CUMMINGS, LEE V CUMMINGS, MARY U CUMMINGS, ROBERT T AFTER....

Deleting all occurrences and the FEO anomaly

FEO on field groups has the same semantics as those (described in Deleting occurrences of fields above) on a single field. When you iterate in an FEO FIELDGROUP, the next occurrence processed is the occurrence number one greater than the one processed before, whether or not it is one you have already processed (because of insertions) or whether or not you skip one or more (because of deletions).

This creates a problem with deleting field groups in order in an FEO loop: the internal pointer is updated after each deletion, resulting in a default behavior of deleting every other occurrence of the repeating field group. This behavior can also cause an error when the final deletion is attempted and the pointer is now set to a value larger than the final occurrence. Consider this example:

BEGIN IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100013 PRINT POLICY_NUMBER PRINT 'BEFORE...' FEO FIELDGROUP DRIVER PRINT DRIVER_NAME AT 5 END FOR FEO FIELDGROUP DRIVER DELETE FIELDGROUP END FOR PRINT 'AFTER....' FEO FIELDGROUP DRIVER PRINT DRIVER_NAME AT 5 END FOR END

The result is:

100013 BEFORE... CUMMINGS, BETTY S CUMMINGS, EDDIE R CUMMINGS, LEE V CUMMINGS, MARY U CUMMINGS, ROBERT T AFTER.... CUMMINGS, EDDIE R CUMMINGS, MARY U

The intent of the above code is clearly to delete every occurrence of the field group, not every other occurrence, but this anomalous result is maintained as part of SOUL for backward compatibility reasons.