Basic SOUL statements and commands: Difference between revisions

From m204wiki
Jump to navigation Jump to search
mNo edit summary
 
(58 intermediate revisions by 4 users not shown)
Line 1: Line 1:
<p>
<p>
This page discusses in more detail the basic statements that can be used within a request. This chapter also presents the system control commands that you commonly use during a terminal session. There are many more User Language statements and [[:Category:Commands|commands]] than are presented here.</p>
This page discusses in more detail the basic statements that can be used within a request. This page also presents the system control commands that you commonly use during a terminal session. There are many more SOUL statements and [[:Category:Commands|commands]] than are presented here. </p>
__TOC__
__TOC__
    
    
==System control commands==
==System control commands==
<p>
<p>
Before learning about User Language statements, it is important to understand the role of <var class="product">Model&nbsp;204</var> commands that are required for basic request processing.</p>
Before learning about SOUL statements, it is important to understand the role of <var class="product">Model&nbsp;204</var> commands that are required for basic request processing. </p>
   
   
===Use of commands with requests===
===Use of commands with requests===
<p>
<p>
System control commands are used outside requests to perform system level operations such as opening files and saving requests. Although system control commands are not part of User Language, many commands you might find useful are illustrated in examples throughout this manual. </p>
System control commands are used outside requests to perform system level operations such as opening files and saving requests. Although system control commands are not part of SOUL, many commands you might find useful are found in examples of SOUL constructs. </p>
<p>
<p>
Complete descriptions of these and other commands are included in [[:Category:Commands|commands]].   </p>
Complete descriptions of these and other commands are included in [[:Category:Commands|commands]]. </p>
   
   
===Key commands===
===Key commands===
<p>
<p>
The following system control commands are essential to allow User Language requests to function:  </p>
The following system control commands are essential to allow SOUL requests to function:  </p>
<table>
<table>
<tr class="head">
<tr class="head">
Line 21: Line 21:
<th>Action</th>
<th>Action</th>
</tr>
</tr>
<tr>
<tr>
<td><var>[[BEGIN command|BEGIN]]</var></td>
<td><var>[[BEGIN command|BEGIN]]</var></td>
<td>Initiates a User Language request.</td>
<td>Initiates a User Language request.</td>
</tr>
</tr>
<tr>
<tr>
<td><var>[[OPEN FILE command|OPEN]]</var> </td>
<td><var>[[OPEN FILE command|OPEN]]</var> </td>
<td>Opens a <var class="product">Model&nbsp;204</var> file or group. See [[Files, groups, and reference context#OPEN or OPENC statement|OPEN or OPENC statement]] for a detailed discussion of the OPEN statement. Refer to [[#IN clauses|IN clauses]] for a discussion of using the IN clause when several files are open.</td>
<td>Opens a <var class="product">Model&nbsp;204</var> file or group. See [[Files, groups, and reference context#OPEN statement and OPENC statement|Open statement and OpenC statement]] for a detailed discussion of the OPEN statement. Refer to [[#IN clauses|IN clauses]] for a discussion of using the IN clause when several files are open.</td>
</tr>
</tr>
<tr>
<tr>
<td><var>[[CLOSE command|CLOSE]]</var> </td>
<td><var>[[CLOSE command|CLOSE]]</var> </td>
<td>Closes a <var class="product">Model&nbsp;204</var> file or group that has been opened.   </td>
<td>Closes a <var class="product">Model&nbsp;204</var> file or group that has been opened. </td>
</tr>
</tr>
</table>
</table>
<p>
<p>
The MORE command and END MORE statement are discussed in the section [[#Request continuation|Request continuation]] and in [[Large request considerations]].</p>
The <var>MORE</var> command and <var>End More</var> statement are discussed in the [[#Request continuation|Request continuation]] and [[Large request considerations]] sections. </p>
<div id="findRecords"></div>


==FIND statement==
==<b id="findRecords"></b>Find statement==
<p>
<p>
You can retrieve sets of records, records with specified characteristics, and individual records. </p>
You can retrieve sets of records, records with specified characteristics, and individual records. </p>
<p>
<p>
The basic retrieval statement in User Language is the FIND statement. Several forms of the FIND statement are provided for basic and complex retrievals. Basic retrieval statements are discussed in this chapter. See [[Record retrievals|"Record retrievals"]] for a discussion of more complex retrieval statements.   </p>
The basic retrieval statement in SOUL is the <var>Find</var> statement. Several forms of the <var>Find</var> statement are provided for basic and complex retrievals. Basic retrieval statements are discussed in this topic. See [[Record retrievals]] for a discussion of more complex retrieval statements. </p>
<p>
The complete syntax is presented on the <var>[[Find Records statement|Find Records]]</var> statement page, including its various forms such as <var>Find Without Locks</var> (which is abbreviated <var>FDWOL</var>). </p>
   
   
===FIND ALL RECORDS statement===
===Find All Records statement===
<b>Syntax</b>
<b>Syntax</b>
<p>
<p>
The syntax for retrieving records in a <var class="product">Model&nbsp;204</var> file is:</p>
The syntax for retrieving records in a <var class="product">Model&nbsp;204</var> file is: </p>
<p class="syntax"><span class="term">label</span>: FIND ALL RECORDS
<p class="syntax"><span class="term">label</span>: Find All Records
   
   
[IN {FILE <span class="term">filename</span> | GROUP <span class="term">groupname</span>}] -
[In {File <span class="term">filename</span> | Group <span class="term">groupname</span>}] -
   
   
FIND [AND RESERVE] [ALL] RECORDS -
Find [And Reserve] [All] Records -
   
   
[IN <span class="term">label</span> | ON [LIST] <span class="term">listname</span>] -
[In <span class="term">label</span> | On [List] <span class="term">listname</span>] -
   
   
FOR WHICH | WITH] <span class="term">retrieval-conditions</span>
For Which | With] <span class="term">retrieval-conditions</span>  
   
  . . .  
. . . . .
End Find
END FIND
</p>
</p>
<b>Where</b>
<b>Where</b>
<ul>
<ul>
<li><i>filename</i> specifies the file that has the records you want to retrieve.</li>
<li><var class="term">filename</var> specifies the file that has the records you want to retrieve.</li>
</li>
 
<li><i>groupname</i> specifies the group of files that has the records you want to retrieve.</li>
<li><var class="term">groupname</var> specifies the group of files that has the records you want to retrieve.</li>
</li>
 
<li><i>label</i> specifies the label of a preceding FIND statement. </li>
<li><var class="term">label</var> specifies the label of a preceding <var>Find</var> statement. </li>
</li>
 
<li><i>listname</i> specifies a list from a preceding FIND statement.</li>
<li><var class="term">listname</var> specifies a list from a preceding <var>Find</var> statement.</li>
</li>
 
<li>The <i>retrieval-conditions</i> can be:</li>
<li>The <i>retrieval-conditions</i> can be:
<p class="syntax">[<span class="term">fieldname</span> {EQ | NE} VALUE IN <span class="term">value_set</span>]
<p class="syntax">[<span class="term">fieldname</span> {Eq | Ne} Value In <span class="term">value_set</span>] </p>
</p></li>
</ul>
<p>
<p>
<i>fieldname</i> identifies the field from which to retrieve values.</p>
<var class="term">fieldname</var> identifies the field from which to retrieve values. </p>
<p>
<p>
The <i>value_set</i> label represents an existing value set. Values of the value set in the EQ VALUE IN <i>value_set</i> clause are treated as boolean OR, for example:</p>
The <var class="term">value_set</var> label represents an existing value set. Values of the value set in the <var>Eq Value In <i>value_set</i></var> clause are treated as boolean OR, for example:
<p class="syntax"><span class="term">field</span> = <span class="term">value1</span> OR <span class="term">field</span> = <span class="term">value2</span> OR....
</p>
</p>
<p class="syntax"><span class="term">field</span> = <span class="term">value1</span> OR <span class="term">field</span> = <span class="term">value2</span> OR.... </p>
<p>
<p>
The NE VALUE IN <i>value_set</i> clause is likewise treated as boolean AND, for example:</p>
The <var>Ne Value In <i>value_set</i></var> clause is likewise treated as boolean AND, for example:
</p>
<p class="syntax"><span class="term">field</span> <> <span class="term">value1</span> AND <span class="term">field</span> <> <span class="term">value2</span> AND....
<p class="syntax"><span class="term">field</span> <> <span class="term">value1</span> AND <span class="term">field</span> <> <span class="term">value2</span> AND....
</p>
</p>
<p>
For the full variety of possible retrieval conditions, see [[Statement syntax#Retrieval condition syntax|Retrieval condition syntax]]. </p></li>
</ul>
<b>Example</b>
<b>Example</b>
<p>
<p>
A basic FIND statement is coded as follows: </p>
A basic <var>Find</var> statement is coded as follows: </p>
<p class="code">FIND.RECS: FIND ALL RECORDS
<p class="code">FIND.RECS: FIND ALL RECORDS
           END FIND
           END FIND
</p>
</p>
 
===Ending a FIND statement===
===Ending a Find statement===
<p>
<p>
Except where noted in this manual, you must end a FIND statement with either an END FIND statement or a label. A FIND statement cannot be ended by an END BLOCK statement.       </p>
Except where explicitly noted, you must end a <var>Find</var> statement with either an <var>End Find</var> statement or a label. A <var>Find</var> statement cannot be ended by an <var>End Block</var> statement. </p>
<b>Syntax</b>
<b>Syntax</b>
<p>
<p>
The format of the END FIND statement is:</p>
The format of the <var>End Find</var> statement is:  
<p class="syntax">END FIND [<span class="term">label</span>]
</p>
<p class="syntax">End Find [<span class="term">label</span>]
</p>
</p>
<p>
<p>
In addition, the retrieval conditions of a FIND statement must not be labeled, even if they begin new lines.</p>
In addition, the retrieval conditions of a <var>Find</var> statement must not be labeled, even if they begin new lines.</p>
   
   
===Large Object data support===
===Large Object data support===
<p>
<p>
The FIND statement supports Large Object data in the following format:</p>
The <var>Find</var> statement supports Large Object data in the following format:</p>
<p class="syntax">FIND <span class="term">lob-fieldname</span> IS [NOT] PRESENT
<p class="syntax">Find <span class="term">lob-fieldname</span> Is [Not] Present
</p>
</p>
 
==Understanding retrieval conditions==
==Understanding retrieval conditions==
   
   
===Processing a VALUE IN clause===
===<b id="valueIn"></b>Processing a VALUE IN clause===
<p>
<p>
You can incorporate a VALUE IN clause in the following User Language statements: </p>
You can incorporate a <var>Value In</var> clause in the following SOUL statements: </p>
<ul>
<ul>
<li>FIND ALL RECORDS: see <i>retrieval-conditions</i> in [[#FIND ALL RECORDS statement|FIND ALL RECORDS statement]]</li>
<li><var>Find All Records</var>: see <i>retrieval-conditions</i> in the <var>[[#FIND ALL RECORDS statement|Find All Records]]</var> statement</li>
</li>
 
<li>FIND ALL VALUES: see [[Value loops#FIND ALL VALUES statement|FIND ALL VALUES statement]] </li>
<li><var>Find All Values</var>: see <var>[[Value loops#FIND ALL VALUES statement|Find All Values]]</var> statement</li>
</li>
 
<li>FOR EACH RECORD WHERE: see [[Record loops#Restricting FOR EACH RECORD processing|Restricting FOR EACH RECORD processing]]</li>
<li><var>For Each Record Where</var>: see [[Record loops#WhereWith|Restricting For Each Record processing]] </li>
</li>
</ul>
</ul>
<p>
<p>
The VALUE IN clause is valid for fields that include the ORDERED attribute. The interprocessing between fields works between fields of the same ORDERED type: ORDERED CHARACTER or ORDERED NUMERIC. If processing between fields of differing types, you will likely not find records. </p>
The <var>Value In</var> clause is valid for fields that include the <var>ORDERED</var> attribute. The inter-processing between fields works between fields of the same <var>ORDERED</var> type: <var>ORDERED CHARACTER</var> or <var>ORDERED NUMERIC</var>. If processing between fields of differing types, you will likely not find records. </p>
   
   
====Rewriting applications to take advantage of VALUE IN clause processing====
====Rewriting applications to take advantage of Value In clause processing====
<p>
<p>
If your User Language applications now use nested loops to perform join processing, you could rewrite your applications to take advantage of the VALUE IN clause.</p>
If your SOUL applications now use nested loops to perform join processing, you could rewrite your applications to take advantage of the <var>Value In</var> clause. </p>
<p>
<p>
You are likely to see performance improvements, which result in a reduction of overall wall clock time due to:</p>
You are likely to see performance improvements, which result in a reduction of overall wall clock time due to:</p>
Line 133: Line 139:
<li>A decrease in disk I/O</li>
<li>A decrease in disk I/O</li>
</li>
</li>
<li>Decreases in the number of FINDs and BXFINDs, because the processing has moved. You will see an increase in BXNEXTs </li>
<li>Decreases in the number of <var>Find</var>s and [[Using system statistics#Description of statistics|BXFIND]]s, because the processing has moved. You will see an increase in BXNEXTs </li>
</li>
</li>
</ul>
</ul>
<p>
<p>
The following code example illustrates the use of a VALUE IN clause:</p>
The following code example shows a use of the <var>Value In</var> clause: </p>
<p class="code">OPEN CLAIMS90
<p class="code">OPEN CLAIMS90
FILEMGR
FILEMGR
Line 154: Line 160:
END
END
</p>
</p>
 
===FIND ALL RECORDS FOR WHICH/WITH statement===
===FIND ALL RECORDS FOR WHICH/WITH statement===
<p>
<p>
The basic statement for retrieving data based on specific retrieval conditions is: </p>
The basic statement for retrieving data based on specific retrieval conditions is: </p>
<p class="syntax"><span class="term">label</span>: FIND ALL RECORDS
<p class="syntax"><span class="term">label</span>: Find All Records {For Which | With}
{FOR WHICH | WITH}
   
   
  {<span class="term">fieldname</span>=<span class="term">value</span> | <span class="term">fieldname</span>=<span class="term">value1</span> OR <span class="term">value2</span>
  {<span class="term">fieldname</span>=<span class="term">value</span> | <span class="term">fieldname</span>=<span class="term">value1</span> Or <span class="term">value2</span>
   
   
  | <span class="term">fieldname</span> NOT=<span class="term">value</span>
  | <span class="term">fieldname</span> Not=<span class="term">value</span>
   
   
  | <span class="term">fieldname</span>=<span class="term">value1</span> AND NOT=<span class="term">value2</span>
  | <span class="term">fieldname</span>=<span class="term">value1</span> And Not=<span class="term">value2</span>
   
   
  | <span class="term">fieldname</span> NOT=<span class="term">value1</span> NOR <span class="term">value2</span>
  | <span class="term">fieldname</span> Not=<span class="term">value1</span> Nor <span class="term">value2</span>
   
   
  | <span class="term">fieldname</span> IS LIKE <span class="term">pattern</span>}
  | <span class="term">fieldname</span> Is Like <span class="term">pattern</span>}
   
   
  | <span class="term">fieldname</span> {EQ | NE} VALUE IN <span class="term">value-set</span>
  | <span class="term">fieldname</span> {Eq | Ne} Value In <span class="term">value-set</span>
</p>
</p>
<b>Example</b>
<b>Example</b>
<p>
<p>
This FIND ALL RECORDS FOR WHICH statement directs <var class="product">Model&nbsp;204</var> to look for records of blue Fords:</p>
This <var>Find All Records For Which</var> statement directs <var class="product">Model&nbsp;204</var> to look for records of blue Fords:</p>
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
               MAKE = FORD
               MAKE = FORD
Line 183: Line 187:
</p>
</p>
<p>
<p>
This statement contains an AND implied by the fact that COLOR = BLUE appears on a separate line. Only the records containing both fields, MAKE = FORD and COLOR = BLUE, are retrieved.                 </p>
This statement contains an implied <var>And</var> by the fact that <code>COLOR=BLUE</code> appears on a separate line. Only the records containing both fields, <code>MAKE=FORD</code> and <code>COLOR=BLUE</code>, are retrieved. </p>
 
==Using expressions for value retrieval==
==Using expressions for value retrieval==
<p>
<p>
You can use expressions for value retrieval in the following FIND statements:</p>
You can use expressions for value retrieval in the following <var>Find</var> statements: </p>
<ul>
<ul>
<li>FIND - POINT$ condition</li>
<li><var>Find...Point$</var> condition </li>
<li>FIND - SFL$ and SFGE$ conditions</li>
<li><var>Find...Sfl$</var> and <var>Find...Sfge$</var> conditions </li>
<li>FIND ALL VALUES</li>
<li><var>Find All Values</var> </li>
</ul>
</ul>
<p>
<p>
For syntax examples, see [[Statement syntax#Retrieval condition syntax|"Retrieval condition syntax"]]. For more information on expression types that you can use to supply retrieval values, see [[Using variables and values in computation#Using expressions for value retrieval|Using expressions for value retrieval]].</p>
For syntax examples, see [[Statement syntax#Retrieval condition syntax|Retrieval condition syntax]]. For more information on expression types that you can use to supply retrieval values, see [[Using variables and values in computation#Using expressions for value retrieval|Using expressions for value retrieval]]. </p>
 
==Using Boolean operators with retrieval statements==
==Using Boolean operators with retrieval statements==
<p>
<p>
Line 287: Line 291:
==Loop statements==
==Loop statements==
<p>
<p>
Once a set of records or values has been located, the found set can be referred to by using the FOR statement. </p>
Once a set of records or values has been located, the found set can be referred to by using the <var>For</var> statement. </p>
<p>
<p>
The FOR statement introduces a loop. The complete set of statements within the loop is performed once for each record or value located. If no record or values are found, the statements in the loop are skipped.</p>
The <var>For</var> statement introduces a loop. The complete set of statements within the loop is performed once for each record or value located. If no record or values are found, the statements in the loop are skipped.</p>
<p>
<p>
When a FIND statement refers back to a previous found set and the found set is empty, then the loop for the FIND statement is skipped. Performance for the FIND processing is enhanced because no retrieval is performed. </p>
When a <var>Find</var> statement refers back to a previous found set and the found set is empty, then the loop for the <var>Find</var> statement is skipped. Performance for the <var>Find</var> processing is enhanced because no retrieval is performed. </p>
   
   
===FOR EACH RECORD statement===
===For Each Record statement===
<p>
<p>
Use the FOR EACH RECORD statement to refer to each record within a set of records located by a FIND statement.</p>
Use the <var>For Each Record</var> statement to refer to each record within a set of records located by a <var>Find</var> statement. </p>
<b>Syntax</b>
<p>
<b>Syntax</b> </p>
<p>
<p>
The form of the FOR EACH RECORD statement is:      </p>
The simple form of the <var>For Each Record</var> statement follows. For a more complete syntax and description, see the "For Each Record statement" section in [[Record loops#For Each Record statement|Record loops]].
<p class="syntax">FOR EACH RECORD IN <span class="term">label</span>
</p>
<p class="syntax">For Each Record In <span class="term">label</span>
</p>
</p>
<p>
<p>
where <i>label</i> is the label of a preceding FIND statement. </p>
where <var class="term">label</var> is the label of a preceding <var>Find</var> statement. </p>
<b>Example</b>
<p>
<b>Example</b>
</p>
<p class="code">FIND.RECS:  FIND ALL RECORDS FOR WHICH
<p class="code">FIND.RECS:  FIND ALL RECORDS FOR WHICH
                 MAKE = FORD
                 MAKE = FORD
Line 313: Line 321:
             END FOR
             END FOR
</p>
</p>
<p class="note"><b>Notes:</b>
<blockquote class="note">
<p><b>Notes:</b> </p>
<ul>
<ul>
<li>The FOR EACH RECORD statement also can be used to combine retrieval and looping when a particular set of records is processed only once in a request.</li>
<li>The <var>For Each Record</var> statement also can be used to combine retrieval and looping when a particular set of records is processed only once in a request. </li>
   
   
<li><var class="product">Model&nbsp;204</var> also supports a prefetch (look-ahead read) feature, which can provide performance improvements for applications containing FOR EACH RECORD statements. See [[Record loops]] for more information on the FOR EACH RECORD statement and the prefetch feature.
<li><var class="product">Model&nbsp;204</var> also supports a [[Record loops#The prefetch (look-ahead read) feature|prefetch (look-ahead read) feature]], which can provide performance improvements for applications containing <var>For Each Record</var> statements. </li>
</li>
</ul>
</ul>
</p>
</blockquote>
 
===Ending a FOR statement===
===Ending a For statement===
<p>
The <var>For</var> statement must be ended by using an <var>End For</var> or an <var>End Block</var> statement. </p>
<p>
<p>
The FOR statement must be ended by using an END FOR or an END BLOCK statement. </p>
<b>Syntax</b> </p>
<b>Syntax</b>
<p>
<p>
The format of the END FOR statement is:   </p>
The format of the <var>End For</var> statement is: </p>
<p class="syntax">END FOR [<span class="term">label</span>]
<p class="syntax">End For [<span class="term">label</span>]
</p>
</p>
<p>
<p>
where <i>label</i> is the label of the line containing the FOR statement.</p>
where <var class="term">label</var> is the label of the line containing the <var>For</var> statement. </p>
   
   
===Nested loops===
===Nested loops===
<p>
<p>
Any loop in User Language can be nested: embedded in another loop or series of statements. The END FOR statement returns the nesting level to the same level as the FOR statement.</p>
Any loop in SOUL can be nested: embedded in another loop or series of statements. The <var>End For</var> statement returns the nesting level to the same level as the <var>For</var> statement. </p>
 
==IF statement==
==IF statement==
<p>
<p>
Line 464: Line 473:
</p>
</p>
   
   
==Output statements==
==Output statements for fields and field groups==
<p>
<p>
Print statements are often used in loops to display information from each record located by a FIND statement. </p>
Print statements are often used in loops to display information from each record located by a FIND statement. </p>
Line 470: Line 479:
Two of the statements described in this section, PRINT ALL INFORMATION and PRINT <i>fieldname</i>, are basic output statements. PRINT ALL INFORMATION and PRINT <i>fieldname</i> can be used only in a record loop.</p>
Two of the statements described in this section, PRINT ALL INFORMATION and PRINT <i>fieldname</i>, are basic output statements. PRINT ALL INFORMATION and PRINT <i>fieldname</i> can be used only in a record loop.</p>
<p>
<p>
The PAI INTO statement, an extension of the PAI statement, allows you to store the field names and values associated with a record in an array. See [[Record loops#PAI INTO statement|PAI INTO statement]] for more information.</p>
The PAI INTO statement, an extension of the PAI statement, allows you to store the field names and values associated with a record in an array. See [[Record loops]] for more information.</p>
<p>
<p>
More complex statements are used for variable format output. These are discussed in [[Report generation]].</p>
More complex statements are used for variable format output. These are discussed in [[Report generation]].</p>
Line 476: Line 485:
The following User Language statements provide display output for Model 204.  
The following User Language statements provide display output for Model 204.  


(Field group processing is available as of Model 204 version 7.5.)
<p class="note"><b>Note:</b> Field group processing is generally available as of Model 204 version 7.5.</p>
<p class="code">
<p class="code">AUDIT ALL FIELDGROUP INFORMATION (AAFGI)
AUDIT ALL FIELDGROUP INFORMATION (AAFGI)
AUDIT ALL INFORMATION (AAI)
AUDIT ALL INFORMATION (AAI)
PRINT ALL FIELDGROUP INFORMATION (PAFGI)
PRINT ALL FIELDGROUP INFORMATION (PAFGI)
Line 484: Line 492:
</p>
</p>


===Output statements for fields and field groups===
AUDIT statement processing is just like PRINT statement processing, except the output goes to the audit trail. The PRINT ALL FIELDGROUP INFORMATION and the AUDIT ALL FIELDGROUP INFORMATION statements do the equivalent of a PAI statement that is limited to the current context field group.
AUDIT statement processing is just like PRINT statement processing, except the output goes to the audit trail. The PRINT ALL FIELDGROUP INFORMATION and the AUDIT ALL FIELDGROUP INFORMATION statements do the equivalent of a PAI statement that is limited to the current context field group.


If you just want to display the contents of the third occurrence of field group FOO, you could issue the following statement:
If you just want to display the contents of the third occurrence of field group FOO, you could issue the following statement:
<p class="code">
<p class="code">IN FILE WHATEVER FRN %RECNO
IN FILE WHATEVER FRN %RECNO
   FOR FIELDGROUP FOO(3)
   FOR FIELDGROUP FOO(3)
       PRINT ’*** Third occurrence of field group FOO’
       PRINT ’*** Third occurrence of field group FOO’
Line 496: Line 502:
END FOR
END FOR
</p>
</p>
 
===Statements using the CTOFIELDS argument===
===Statements using the CTOFIELDS argument===
Usually you would not want to display a counter (CTO) field when you use the
Usually you would not want to display a counter (CTO) field when you use the
following statements:
following statements:
<p class="code">
<p class="code">PRINT ALL INFORMATION (PAI)
PRINT ALL INFORMATION (PAI)
AUDIT ALL INFORMATION (AAI)
AUDIT ALL INFORMATION (AAI)
PRINT ALL FIELDGROUP INFORMATION (PAFGI)
PRINT ALL FIELDGROUP INFORMATION (PAFGI)
Line 508: Line 513:


However, when you need that information you can use the argument to indicate that you do want a counter (CTO) field to be printed by these statements.
However, when you need that information you can use the argument to indicate that you do want a counter (CTO) field to be printed by these statements.
<p class="code">
<p class="code">PAI CTOFIELDS
PAI CTOFIELDS
</p>
</p>
===AUDIT ALL FIELDGROUP INFORMATION statement===
<b>Purpose</b>
<p>AUDIT ALL FIELDGROUP INFORMATION (AAFGI) writes the output of the current field group to the audit trail.</p>


<p>The <var>[[#PRINT_ALL_FIELDGROUP_INFORMATION_statement|PAFGI]]</var> statement displays the same information but is routed to the terminal.</p>
===<b id="aafgiStmt"></b>Audit All Fieldgroup Information (AAFGI) statement===
<p>
<b>Purpose</b> </p>
<p>
<var>Audit All Fieldgroup Information</var> (<var>AAFGI</var>) writes the output of the current field group to the audit trail.</p>
<p>The same information can be displayed on the terminal using the
<var>[[PAFGI statement|PAFGI]]</var> statement.</p>
<b>Syntax</b>
<b>Syntax</b>
<p>The form of the AAFGI statement is:</p>
<p>The form of the AAFGI statement is:</p>
<p class="code">
{{Template:AAFGI statement syntax}}
AUDIT ALL FIELDGROUP INFORMATION [CTOFIELDS]
<b>Where</b>
</p>
<p>If <var>CtOFields</var> is specified, the <var>[[Field design#cto|COUNT-OCCURRENCES-OF]]</var>
<b>Where</b>  
(<var>CTO</var>) field(s) are audited. By default <var>CTO</var> fields are not audited.</p>
<p>CTOFIELDS specifies to print the counter (CTO) field(s). By default CTO fields are not printed.</p>


===AUDIT ALL INFORMATION statement===
===Audit All Information statement===
<b>Purpose</b>  
<b>Purpose</b>  
<p>The AUDIT ALL INFORMATION (or AAI) statement writes the output of entire records to the audit trail.</p>
<p>
The <var>Audit All Information</var> (or <var>AAI</var>) statement writes the output of entire records to the [[Report generation#Audit All Information statement|audit trail]]. </p>
<p>
<b>Syntax</b> </p>
<p>
The form of the <var>AAI</var> statement is: </p>
{{Template:Audit All Information statement syntax}}
<p>
<b>Where</b> </p>
<p>
<var>CtoFields</var> specifies to print the counter (CTO) field(s). By default CTO fields are not printed. </p>
 
===<b id="pafgiStmt"></b>Print All Fieldgroup Information (PAFGI) statement===
<p>
<b>Purpose</b> </p>
<p>
The <var>Print All Fieldgroup Information</var> (<var>PAFGI</var>) statement writes the output of the current fieldgroup to the terminal.</p>
<p>The <var>[[AAFGI statement|AAFGI]]</var> statement displays the same information but is routed to the audit trail.</p>
<b>Syntax</b>
<b>Syntax</b>
<p>The form of the AAI statement is:</p>
<p>
<p class="code">
The form of the <var>PAFGI</var> statement is: </p>
AUDIT ALL INFORMATION [CTOFIELDS]
{{Template:PAFGI statement syntax}}
</p>
Where:
<b>Where</b>
<p>CTOFIELDS specifies to print the counter (CTO) field(s). By default CTO fields
are not printed.</p>
 
===PRINT ALL FIELDGROUP INFORMATION statement===
<b>Purpose</b>
<p>The PRINT ALL FIELDGROUP INFORMATION (PAFGI) statement writes the output of the current field group to the terminal.</p>
<p>The <var>[[#AUDIT_ALL_FIELDGROUP_INFORMATION_statement|AAFGI]]</var> statement displays the same information but is routed to the audit trail.</p>  
<b>Syntax</b>
<p>The form of the PAFGI statement is:</p>
<p class="code">
PRINT ALL FIELDGROUP INFORMATION [lob-option] [CTOFIELDS]
</p>
where:  
<ul>
<ul>
<li>lob-option can be one of the following and specifies how Large Object fields
<li><var class="term">lob-option</var> can be one of the following and specifies how Large Object fields are handled.
are handled.
<ul>
<ul>
<li>LOB_FLOD, the default, which means output in a format that FLOD can
<li><var>Lob_Flod</var>, the default, which means output in a format that FLOD can read and reload Large Object fields. The output includes the descriptor in binary format, plus the full field value. </li>
read and reload Large Object fields. The output includes the descriptor
 
in binary format, plus the full field value.</li>
<li><var>Lob_Data</var> specifies whether to display the field value. The <var>PAFGI</var> statement displays the Large Object descriptor in printable format. </li>
<li>LOB_DATA specifies whether to display the field value. The PAI statement displays the Large Object descriptor in printable format.</li>
 
<li>LOB_SHORT_DATA specifies to display the first 255 bytes of the field
<li><var>Lob_Short_Data</var> specifies to display the first 255 bytes of the field value. The <var>PAFGI</var> statement displays the Large Object descriptor in printable
value. The PAI statement displays the Large Object descriptor in printable
format. </li>
format.</li>
 
<li>LOB_NO_DATA specifies not to display the value. However, the PAI
<li><var>Lob_No_Data</var> specifies not to display the value. However, the <var>PAFGI</var> statement displays the Large Object descriptor in printable format. </li>
statement displays the Large Object descriptor in printable format.</li>
<li>LOB_NONE, which says not to display any Large Object fields.</li>
</ul>
<li>CTOFIELDS specifies to print the counter (CTO) field(s). By default CTO
fields are not printed.</li>
</ul>


<p><b>Example</b></p>
<li><var>Lob_None</var>, which says not to display any Large Object fields. </li>
This example shows printed terminal output from PAFGI and audit trail output from AAFGI.
</ul></li>


<p class="code">
<li><var>CtoFields</var> specifies to print the counter (<var>CTO</var>) field(s). By default <var>CTO</var>
FR WHERE POLICY_NUMBER = 100095
fields are not printed. </li>
   FEO FIELDGROUP DRIVER
</ul>
       PRINT '---' AND DRIVER_NAME AND '---'
       AUDIT '---' AND DRIVER_NAME AND '---'
<p>
       PAFGI
<b>Example</b> </p>
       AAFGI
This example shows printed terminal output from <var>PAFGI</var> and audit trail output from <var>AAFGI</var>:
   END FOR
END FOR
<p class="code">fr where policy_number = 100095
   feo fieldgroup driver
       print '---' and driver_name and '---'
       audit '---' and driver_name and '---'
       pafgi
       aafgi
   end for
end for
</p>
</p>
Printed output:
Printed output:
<p class="code">
<p class="output">--- ALTON, BARBARA W ---
--- ALTON, BARBARA W ---
\DRIVER = 1
\DRIVER = 1
  DRIVER_ID = 100223
  DRIVER_ID = 100223
Line 591: Line 600:
</p>
</p>
Audit trail output:
Audit trail output:
<p class="code">
<p class="output">10189103542 30  2    9 US --- ALTON, BARBARA W ---
10189103542 30  2    9 US --- ALTON, BARBARA W ---
10189103542 31  2    9 US \DRIVER = 1
10189103542 31  2    9 US \DRIVER = 1
10189103542 32  2    9 US DRIVER_ID = 100223
10189103542 32  2    9 US DRIVER_ID = 100223
Line 603: Line 611:
</p>
</p>


===PRINT ALL INFORMATION (or PAI) statement===
===<b id="PAI"></b>PRINT ALL INFORMATION (or PAI) statement===
<b>Purpose</b>
<p>
<b>Purpose</b> </p>
<p>
<p>
The PRINT ALL INFORMATION (or PAI) statement causes output of whole records. </p>
The <var>Print All Information</var> (or <var>PAI</var>) statement causes output of whole records. </p>
<b>Syntax</b>
<b>Syntax</b>
<p>
<p>
The form of the PAI statement is:</p>
The format of the <var>PAI</var> statement is: </p>
<p class="syntax">PRINT ALL INFORMATION [<span class="term">lob-option</span>] [CTOFIELDS]
{{Template:PAI statement syntax}}
</p>
<p>
<p>
where
Where: </p>
<ul>
<ul>
<li><var class="term">lob-option</var> can be one of the following and specifies how Large Object fields are handled.
<li>At most one Lob option can be specified for large object display:
<ul>
<ul>
<li>LOB_FLOD, the default, which means output in a format that FLOD can read and reload Large Object fields. The output includes the descriptor in binary format, plus the full field value.</li>
<li><var>Lob_Flod</var>, the default, specifies output in a format that Flod can read and reload Large Object fields. The output includes the descriptor in binary format, plus the full field value.</li>
</li>
 
<li>LOB_DATA specifies whether to display the field value. The PAI statement displays the Large Object descriptor in printable format.</li>
<li><var>Lob_Data</var> specifies whether to display the field value. The <var>PAI</var> statement displays the Large Object descriptor in printable format.</li>
</li>
 
<li>LOB_SHORT_DATA specifies to display the first 255 bytes of the field value. The PAI statement displays the Large Object descriptor in printable format.</li>
<li><var>Lob_Short_Data</var> specifies to display the first 255 bytes of the field value. The <var>PAI</var> statement displays the Large Object descriptor in printable format.</li>
</li>
 
<li>LOB_NO_DATA specifies not to display the value. However, the PAI statement displays the Large Object descriptor in printable format.</li>
<li><var>Lob_No_Data</var> specifies not to display the value. However, the <var>PAI</var>
</li>
<li><var>Lob_None</var> specifies not to display any Large Object fields. </li>
<li>LOB_NONE, which specifies not to display any Large Object fields.</li>
</ul></li>
</li>
 
</ul>
<li><var>CtoFields</var> specifies that the counter (<var>CTO</var>) field(s) should be printed. By default, <var>CTO</var> fields are not printed.
<li>CTOFIELDS specifies to print the counter (CTO) field(s). By default CTO fields are not printed.
<p class="note"><b>Note:</b>
</li>
The <var>CtoFields</var> keyword and a <var>Lob_</var>xxx keyword can be in either order, but neither of them can follow the <var>Into</var> clause. </p></li>
 
<li>The <var>Into</var> clause is not used for display purposes. It lets you move a record's field names and values into a form of storage that can be easily manipulated. For details, see [[Record loops#paiInto|Print All Information (or PAI) Into statement]].</li>
</ul>
</ul>
<b>Examples</b>


====Examples====
<ul>
<li><div id="FIND results">
Displaying FIND results</div>
<p>
This request:</p>
This request:</p>
<p class="code">BEGIN
<p class="code">BEGIN
Line 645: Line 659:
</p>
</p>
<p>
<p>
results in output with the format:</p>
results in output with the following format:</p>
<p class="code">FULLNAME = ABBOTT, GAIL H
<p class="output">FULLNAME = ABBOTT, GAIL H
POLICY NO = 100340
POLICY NO = 100340
DRIVER ID = 100817
DRIVER ID = 100817
Line 657: Line 671:
INCIDENT DATE = 19900921
INCIDENT DATE = 19900921
DATE OF BIRTH = 19390305
DATE OF BIRTH = 19390305
</p>
</p></li>
This request:
 
<p class="code">
<li>Displaying counter fields
BEGIN
<p>
This request:</p>
<p class="code">BEGIN
FR WHERE POLICY_NUMBER = 100000
FR WHERE POLICY_NUMBER = 100000
   PAI CTOFIELDS
   PAI CTOFIELDS
Line 666: Line 682:
END
END
</p>
</p>
results in output with the following format (note the inclusion of the CTO fields NUMBER_OF_DRIVERS and NUMBER_OF_VEHICLES):
<p>
<p class="code">
results in output with the following format (note the inclusion of the <var>CTO</var> fields <code>NUMBER_OF_DRIVERS</code> and <code>NUMBER_OF_VEHICLES</code>): </p>
ADDRESS = 3621 GLEN CT
<p class="output">ADDRESS = 3621 GLEN CT
POLICY_NUMBER = 100000
POLICY_NUMBER = 100000
NUMBER_OF_DRIVERS = 4
NUMBER_OF_DRIVERS = 4
Line 682: Line 698:
DRIVER_NAME = ALLEN, RAY F
DRIVER_NAME = ALLEN, RAY F
...and so on...  
...and so on...  
</p></li>
<li>Displaying fields of different storage types, including non-preallocated, preallocated, and the <var>EXACTLY-ONE</var> type
<p>
This definition and request:</p>
<p class="code">DEFINE FIELD PORD1 ( ORD CHAR OCC 1 LEN 4 )
DEFINE FIELD EORD1 ( ORD CHAR EXACTLY-ONE )
DEFINE FIELD NORD1 ( ORD CHAR            )
BEGIN
IN F100A STORE RECORD
  PORD1 = FRED
  EORD1 = JANE
  NORD1 = STEV
END STORE
END
BEGIN
  FOR EACH RECORD
    PAI
    PRINT
  END FOR
END</p>
<p>
results in output with the following format. Note the output of the <var>EXACTLY-ONE</var> field first in the list, followed by the preallocated field, and finally the non-preallocated field: </p>
<p class="output">EORD1 = JANE
PORD1 = FRED
NORD1 = STEV
</p>
</p>
===INVISIBLE fields not printed===
<p>
<p>
In the example on the previous page, the fieldname = value pair,
Because <var>EXACTLY-ONE</var> fields are always present but are not always physically stored in the record, it is most efficient to display them first in the <var>PAI</var> statement. If there are no <var>EXACTLY-ONE</var> fields defined to the file, then the order of printing will be any preallocated fields (in the order in which they were defined to the file), followed by any non preallocated fields (in the order in which they are stored within the record). </p></li>
RECTYPE = DRIVER, is not printed in the output because the RECTYPE field has the INVISIBLE attribute. </p>
</ul>
 
===INVISIBLE fields are not printed===
<p>
A field defined with the <var>INVISIBLE</var> attribute cannot be printed, noted, or sorted.</p>
<p>
The previous [[#FIND results|"Displaying FIND results" example]] shows a fieldname = value pair (<code>RECTYPE=DRIVER</code>) that is not printed in the output because the field (<code>RECTYPE</code>) has the <var>INVISIBLE</var> attribute. </p>
<p>
<p>
For more information about the INVISIBLE attribute, refer to [[Field design (File management)#Field descriptions and attributes | Field descriptions and attributes]]. </p>
For more information about the <var>INVISIBLE</var> attribute, refer to [[Field design#Field descriptions and attributes|Field descriptions and attributes]]. </p>
 
===PRINT <i>fieldname</i> statement===
===PRINT <i>fieldname</i> statement===
<b>Syntax</b>
<b>Syntax</b>
<p>
<p>
To display the value of a certain field in a record, use this form of the PRINT statement:   </p>
To display the value of a certain field in a record, use this form of the <var>Print</var> statement:  
<p class="syntax">PRINT <span class="term">fieldname</span> [AND <span class="term">fieldname2</span>]
</p>
<p class="syntax">Print <span class="term">fieldname</span> [And <span class="term">fieldname2</span>]
</p>
</p>
<b>Example</b>
<b>Example</b>
<p>
<p>
Here is a sample PRINT <i>fieldname</i> statement:</p>
Here is a sample <var>Print <i>fieldname</i></var> statement:
</p>
<p class="code">BEGIN
<p class="code">BEGIN
DRIVER: FIND ALL RECORDS FOR WHICH
DRIVER: FIND ALL RECORDS FOR WHICH
Line 707: Line 757:
             PRINT FULLNAME
             PRINT FULLNAME
         END FOR
         END FOR
END
END </p>
</p>
<p>
<p>
In response to the above request, <var class="product">Model&nbsp;204</var> prints:</p>
In response to the above request, <var class="product">Model&nbsp;204</var> prints:</p>
<p class="code">ABBOTT, FRANKLIN G
<p class="output">ABBOTT, FRANKLIN G
ABBOTT, GAIL H
ABBOTT, GAIL H
</p>
</p>
<p>
<p>
Each value of the FULLNAME field is displayed on a new line. A record that has no FULLNAME field produces a blank line of output.     </p>
Each value of the <code>FULLNAME</code> field is displayed on a new line. A record that has no <code>FULLNAME</code> field produces a blank line of output. </p>
   
   
===Printing several fields===
===Printing several fields===
<p>
<p>
The values of more than one field can be printed on the same output line by using the keywords AND and WITH. AND causes a space to be printed between values, as in the following example:</p>
The values of more than one field can be printed on the same output line by using the keywords <var>And</var> and <var>With</var>. <var>And</var> causes a space to be printed between values, as in the following example:
</p>
<p class="code">BEGIN
<p class="code">BEGIN
DRIVER: FIND ALL RECORDS FOR WHICH
DRIVER: FIND ALL RECORDS FOR WHICH
Line 728: Line 778:
             PRINT SEX AND DATE OF BIRTH
             PRINT SEX AND DATE OF BIRTH
         END FOR
         END FOR
END
END </p>
</p>
<p>
<p>
Running the request above results in this output:</p>
Running the request above results in this output:</p>
<p class="code">M 19480204
<p class="output">M 19480204
F 19390305
F 19390305
</p>
</p>
<p>
<p>
If a retrieved record has no SEX field, the value of DATE OF BIRTH is printed on a line beginning with a space, as specified by the keyword AND, for example:</p>
If a retrieved record has no <code>SEX</code> field, the value of <code>DATE OF BIRTH</code> is printed on a line beginning with a space, as specified by the keyword <var>And</var>, for example:</p>
<p class="code">19480204
<p class="output">19480204
</p>
</p>
<p>
<p>
If a record has neither field, it produces a blank line of output.</p>
If a record has neither field, it produces a blank line of output.</p>
<p>
<p>
The keyword WITH works like AND except that WITH does not cause a space to be printed between values.              </p>
The keyword <var>With</var> works like <var>And</var> except that <var>With</var> does not cause a space to be printed between values.              </p>
   
   
===Controlling spacing between output lines===
===Controlling spacing between output lines===
<p>
<p>
The SKIP LINE(S) and NEW PAGE statements enable you to control spacing between lines.</p>
The <var>Skip Line</var> and <var>New Page</var> statements enable you to control spacing between lines. </p>
   
   
====SKIP LINE(S)====
====SKIP LINE(S)====
Line 755: Line 804:
<p class="syntax">SKIP {<span class="term">n</span> | %<span class="term">variable</span>} LINE[S]
<p class="syntax">SKIP {<span class="term">n</span> | %<span class="term">variable</span>} LINE[S]
</p>
</p>
<b>Where</b>
Where:
<ul>
<ul>
<li><i>n</i> is a positive integer less than or equal to 100. </li>
<li><i>n</i> is a positive integer less than or equal to 100. </li>
</li>
 
<li>%<i>variable</i> is a positive integer from 0 through 100 supplied by a %variable or a :% variable. Field names or %% variables are invalid. </li>
<li>%<i>variable</i> is a positive integer from 0 through 100 supplied by a %variable or a :% variable. Field names or %% variables are invalid. </li>
</li>
</ul>
</ul>
<p>
<p>
Line 781: Line 829:


===Loading Large Object data===
===Loading Large Object data===
A <var class="product">Model&nbsp;204</var> file that contains Large Object data can be reorganized using the standard [[File loading techniques#Loading Large Object Data | PAI and FLOD procedure]], which employs the User Language PAI (PRINT ALL INFORMATION) statement to unload the data from a file, and a standard FLOD program to reload the data. This is identical to previous releases of <var class="product">Model&nbsp;204</var>, and no change to your existing unload or load code is required.</p>
A <var class="product">Model&nbsp;204</var> file that contains Large Object data can be reorganized using the standard [[File loading techniques#Loading Large Object Data | PAI and FLOD procedure]], which employs the User Language PAI (PRINT ALL INFORMATION) statement to unload the data from a file, and a standard FLOD program to reload the data. This is identical to previous releases of <var class="product">Model&nbsp;204</var>, and no change to your existing unload or load code is required.


==IN clauses==
==IN clauses==
Line 828: Line 876:
==Request continuation==
==Request continuation==
<p>
<p>
Lengthy requests can be handled by using the END MORE statement and the MORE command. See [[Large request considerations]] for more information on request continuation, including a discussion of the MORE command and the END MORE statement and its USE option.             </p>
Lengthy requests can be handled by using the END MORE statement and the MORE command. See [[Large request considerations]] for more information on request continuation, including a discussion of the MORE command and the END MORE statement and its USE option. </p>
<b>Example</b>
<b>Example</b>
<p>
<p>
Line 841: Line 889:
</p>
</p>
<p>
<p>
After printing its output, this request returns you to command level. Using the MORE command, you can continue the request, adding further statements as well as referring to the results of statements in the original request. If, for example, you wanted to perform further processing of the records found in the original request without repeating the FIND operation, you could enter the following lines, referencing the label GET.DRIVER:</p>
After printing its output, this request returns you to command level. Using the MORE command, you can continue the request, adding further statements as well as referring to the results of statements in the original request. If, for example, you wanted to perform further processing of the records found in the original request without repeating the FIND operation, you could enter the following lines, referencing the label <code>GET.DRIVER</code>:</p>
<p class="code">MORE
<p class="code">MORE
        FOR EACH RECORD IN GET.DRIVER
    FOR EACH RECORD IN GET.DRIVER
            PRINT FULLNAME AND DATE OF BIRTH
        PRINT FULLNAME AND DATE OF BIRTH
        END FOR
    END FOR
END
END
</p>
</p>
 
==See also==
 
[[SOUL statements]]
 
[[Category:SOUL]]
[[Category:SOUL]]

Latest revision as of 00:20, 19 June 2018

This page discusses in more detail the basic statements that can be used within a request. This page also presents the system control commands that you commonly use during a terminal session. There are many more SOUL statements and commands than are presented here.

System control commands

Before learning about SOUL statements, it is important to understand the role of Model 204 commands that are required for basic request processing.

Use of commands with requests

System control commands are used outside requests to perform system level operations such as opening files and saving requests. Although system control commands are not part of SOUL, many commands you might find useful are found in examples of SOUL constructs.

Complete descriptions of these and other commands are included in commands.

Key commands

The following system control commands are essential to allow SOUL requests to function:

Command Action
BEGIN Initiates a User Language request.
OPEN Opens a Model 204 file or group. See Open statement and OpenC statement for a detailed discussion of the OPEN statement. Refer to IN clauses for a discussion of using the IN clause when several files are open.
CLOSE Closes a Model 204 file or group that has been opened.

The MORE command and End More statement are discussed in the Request continuation and Large request considerations sections.

Find statement

You can retrieve sets of records, records with specified characteristics, and individual records.

The basic retrieval statement in SOUL is the Find statement. Several forms of the Find statement are provided for basic and complex retrievals. Basic retrieval statements are discussed in this topic. See Record retrievals for a discussion of more complex retrieval statements.

The complete syntax is presented on the Find Records statement page, including its various forms such as Find Without Locks (which is abbreviated FDWOL).

Find All Records statement

Syntax

The syntax for retrieving records in a Model 204 file is:

label: Find All Records [In {File filename | Group groupname}] - Find [And Reserve] [All] Records - [In label | On [List] listname] - For Which | With] retrieval-conditions . . . End Find

Where

  • filename specifies the file that has the records you want to retrieve.
  • groupname specifies the group of files that has the records you want to retrieve.
  • label specifies the label of a preceding Find statement.
  • listname specifies a list from a preceding Find statement.
  • The retrieval-conditions can be:

    [fieldname {Eq | Ne} Value In value_set]

    fieldname identifies the field from which to retrieve values.

    The value_set label represents an existing value set. Values of the value set in the Eq Value In value_set clause are treated as boolean OR, for example:

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

    The Ne Value In value_set clause is likewise treated as boolean AND, for example:

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

    For the full variety of possible retrieval conditions, see Retrieval condition syntax.

Example

A basic Find statement is coded as follows:

FIND.RECS: FIND ALL RECORDS END FIND

Ending a Find statement

Except where explicitly noted, you must end a Find statement with either an End Find statement or a label. A Find statement cannot be ended by an End Block statement.

Syntax

The format of the End Find statement is:

End Find [label]

In addition, the retrieval conditions of a Find statement must not be labeled, even if they begin new lines.

Large Object data support

The Find statement supports Large Object data in the following format:

Find lob-fieldname Is [Not] Present

Understanding retrieval conditions

Processing a VALUE IN clause

You can incorporate a Value In clause in the following SOUL statements:

The Value In clause is valid for fields that include the ORDERED attribute. The inter-processing between fields works between fields of the same ORDERED type: ORDERED CHARACTER or ORDERED NUMERIC. If processing between fields of differing types, you will likely not find records.

Rewriting applications to take advantage of Value In clause processing

If your SOUL applications now use nested loops to perform join processing, you could rewrite your applications to take advantage of the Value In clause.

You are likely to see performance improvements, which result in a reduction of overall wall clock time due to:

  • A decrease in disk I/O
  • Decreases in the number of Finds and BXFINDs, because the processing has moved. You will see an increase in BXNEXTs

The following code example shows a use of the Value In clause:

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

FIND ALL RECORDS FOR WHICH/WITH statement

The basic statement for retrieving data based on specific retrieval conditions is:

label: Find All Records {For Which | With} {fieldname=value | fieldname=value1 Or value2 | fieldname Not=value | fieldname=value1 And Not=value2 | fieldname Not=value1 Nor value2 | fieldname Is Like pattern} | fieldname {Eq | Ne} Value In value-set

Example

This Find All Records For Which statement directs Model 204 to look for records of blue Fords:

FIND.RECS: FIND ALL RECORDS FOR WHICH MAKE = FORD COLOR = BLUE END FIND

This statement contains an implied And by the fact that COLOR=BLUE appears on a separate line. Only the records containing both fields, MAKE=FORD and COLOR=BLUE, are retrieved.

Using expressions for value retrieval

You can use expressions for value retrieval in the following Find statements:

  • Find...Point$ condition
  • Find...Sfl$ and Find...Sfge$ conditions
  • Find All Values

For syntax examples, see Retrieval condition syntax. For more information on expression types that you can use to supply retrieval values, see Using expressions for value retrieval.

Using Boolean operators with retrieval statements

You can use the Boolean operators AND, OR, NOT, and NOR to create complex retrieval conditions. In the previous example, the AND could be stated implicitly. Thus this statement:

FIND.RECS: FIND ALL RECORDS FOR WHICH MAKE = FORD AND COLOR = BLUE END FIND

retrieves the same data as the example in the section above.

However, the statement:

FIND.RECS: FIND ALL RECORDS FOR WHICH MAKE = FORD OR COLOR = BLUE END FIND

finds records for which either the make is Ford, the color is blue, or both.

The following statement:

FIND.RECS: FIND ALL RECORDS FOR WHICH MAKE = NOT FORD NOR COLOR = BLUE END FIND

finds all records other than blue Fords. NOR is the same as AND NOT.

Understanding NOT operator processing

Creating the complimentary foundset

Although a FIND statement with the NOT operator ultimately returns a foundset that excludes records, Model 204 FIND processing starts by creating a foundset based on the retrieval without the NOT operator. For the following code example, Model 204 locates all the records with MAKE=FORD, then returns to you the remainders or complimentary foundset: a foundset of records where MAKE=NOT FORD.

FIND.RECS: FIND ALL RECORDS FOR WHICH MAKE = NOT FORD END FIND

Note: Records that do not have the MAKE field are not included in either foundset.

Creating a foundset based on the position of the NOT operator

In the following abstract examples, notice the placement of the NOT operator.

Example A

label1: FIND ALL RECORDS FOR WHICH NOT fieldname IS LIKE*

In Example A, Model 204 locates a found set of the records with fieldname=*: that is, any value. Then Model 204 applies the NOT operator to fieldname and returns a found set of records that does not include fieldname. This mimics the behavior of IS NOT PRESENT; see IS PRESENT condition.

Example B

label2: FIND ALL RECORDS FOR WHICH fieldname IS NOT LIKE*

In Example B, Model 204 locates a foundset of the records with fieldname=*: that is, any value. Then Model 204 applies the NOT operator to LIKE* and returns a foundset of no records, because all values for fieldname have been excluded from the foundset.

Use the following example data set with three records of defined fields ABC and XYZ to illustrate the differing found sets from the placement of the NOT operator.

Record number Field ABC Field XYZ
1 14 11
2 25  
3 7  

Repeating Example A

LABEL1: FIND ALL RECORDS FOR WHICH NOT XYZ IS LIKE*

The count returned to you is 2: Records 2 and 3.

Repeating Example B

LABEL1: FIND ALL RECORDS FOR WHICH XYZ NOT IS LIKE*

The count returned to you is 0.

Loop statements

Once a set of records or values has been located, the found set can be referred to by using the For statement.

The For statement introduces a loop. The complete set of statements within the loop is performed once for each record or value located. If no record or values are found, the statements in the loop are skipped.

When a Find statement refers back to a previous found set and the found set is empty, then the loop for the Find statement is skipped. Performance for the Find processing is enhanced because no retrieval is performed.

For Each Record statement

Use the For Each Record statement to refer to each record within a set of records located by a Find statement.

Syntax

The simple form of the For Each Record statement follows. For a more complete syntax and description, see the "For Each Record statement" section in Record loops.

For Each Record In label

where label is the label of a preceding Find statement.

Example

FIND.RECS: FIND ALL RECORDS FOR WHICH MAKE = FORD END FIND PRINT.MODEL: FOR EACH RECORD IN FIND.RECS . . . END FOR

Notes:

  • The For Each Record statement also can be used to combine retrieval and looping when a particular set of records is processed only once in a request.
  • Model 204 also supports a prefetch (look-ahead read) feature, which can provide performance improvements for applications containing For Each Record statements.

Ending a For statement

The For statement must be ended by using an End For or an End Block statement.

Syntax

The format of the End For statement is:

End For [label]

where label is the label of the line containing the For statement.

Nested loops

Any loop in SOUL can be nested: embedded in another loop or series of statements. The End For statement returns the nesting level to the same level as the For statement.

IF statement

The IF statement tests a condition and then executes one or more statements if the condition is true.

The IF statement is discussed in detail in Flow of control in User Language#Flow of control in User Language.

Syntax

The format of the IF statement is:

IF condition THEN statements

where condition follows the syntax rules described in "Expression syntax".

Ending an IF statement

The IF statement must be ended by an END IF statement or an END BLOCK statement.

Syntax

The format of the END IF statement is:

END IF [label]

Example

FOR EACH RECORD IN FIND.RECS IF TOTAL PREMIUM = 300 THEN PRINT POLICY NO END IF END FOR . . .

Counting records

In addition to retrieving specified records and printing fields from them, you might wish to know the number of records that satisfy a certain condition. Use either of these statements:

  • COUNT RECORDS IN/ON (or CT)
  • FIND AND PRINT COUNT (or FPC)

COUNT RECORDS IN/ON statement

The COUNT RECORDS statement takes two forms:

  • COUNT RECORDS IN is used for counting records in a set of records retrieved with the FIND ALL RECORDS statement.
  • COUNT RECORDS ON is used for counting records on a list. See Lists for more about lists.

The COUNT RECORDS IN/ON statement is supported in scattered group context. See Files, groups, and reference context for more information on the reference context.

Syntax

The format for the COUNT RECORDS IN statement is:

label.b: COUNT RECORDS IN label.a

where label.a is the label of a previous FIND statement.

The format for the COUNT RECORDS ON statement is:

label.b: COUNT RECORDS ON list

where list is the name of a list on which you have previously placed found records.

For both forms of the COUNT RECORDS statement, you can print the count using the statement:

PRINT COUNT IN label.b

Example

This COUNT RECORDS IN request:

BEGIN DRIVER: FIND ALL RECORDS FOR WHICH RECTYPE = DRIVER POLICY NO = 100340 END FIND CT: COUNT RECORDS IN DRIVER PRINT COUNT IN CT END

causes Model 204 to note that two records were found by the FIND statement. Model 204 then prints the number 2.

FIND AND PRINT COUNT statement

The FIND AND PRINT COUNT statement is used to combine record retrieval with the COUNT and associated PRINT statements.

Example 1

This statement:

DRIVER: FIND AND PRINT COUNT RECTYPE = DRIVER POLICY NO = 100340 END FIND

is similar to:

DRIVER: FIND ALL RECORDS FOR WHICH RECTYPE = DRIVER POLICY NO = 100340 END FIND CT: COUNT RECORDS IN DRIVER PRINT COUNT IN CT

The label for the FIND AND PRINT COUNT statement can be referred to in the same way in which a label for a FIND ALL RECORDS FOR WHICH statement can be used.

Example 2

The count found by a FIND AND PRINT COUNT statement cannot be positioned to a specified column. The position defaults to column 1 or the next print location, if preceded by an ellipsis (...).

For example, this request:

BEGIN DRIVER: FIND AND PRINT COUNT RECTYPE = DRIVER POLICY NO = 100340 END FIND FOR EACH RECORD IN DRIVER PRINT FULLNAME END FOR END

results in this output:

2 ABBOTT, FRANKLIN G ABBOTT, GAIL H

Output statements for fields and field groups

Print statements are often used in loops to display information from each record located by a FIND statement.

Two of the statements described in this section, PRINT ALL INFORMATION and PRINT fieldname, are basic output statements. PRINT ALL INFORMATION and PRINT fieldname can be used only in a record loop.

The PAI INTO statement, an extension of the PAI statement, allows you to store the field names and values associated with a record in an array. See Record loops for more information.

More complex statements are used for variable format output. These are discussed in Report generation.

The following User Language statements provide display output for Model 204.

Note: Field group processing is generally available as of Model 204 version 7.5.

AUDIT ALL FIELDGROUP INFORMATION (AAFGI) AUDIT ALL INFORMATION (AAI) PRINT ALL FIELDGROUP INFORMATION (PAFGI) PRINT ALL INFORMATION (PAI)

AUDIT statement processing is just like PRINT statement processing, except the output goes to the audit trail. The PRINT ALL FIELDGROUP INFORMATION and the AUDIT ALL FIELDGROUP INFORMATION statements do the equivalent of a PAI statement that is limited to the current context field group.

If you just want to display the contents of the third occurrence of field group FOO, you could issue the following statement:

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

Statements using the CTOFIELDS argument

Usually you would not want to display a counter (CTO) field when you use the following statements:

PRINT ALL INFORMATION (PAI) AUDIT ALL INFORMATION (AAI) PRINT ALL FIELDGROUP INFORMATION (PAFGI) AUDIT ALL FIELDGROUP INFORMATION (AAFGI)

However, when you need that information you can use the argument to indicate that you do want a counter (CTO) field to be printed by these statements.

PAI CTOFIELDS

Audit All Fieldgroup Information (AAFGI) statement

Purpose

Audit All Fieldgroup Information (AAFGI) writes the output of the current field group to the audit trail.

The same information can be displayed on the terminal using the PAFGI statement.

Syntax

The form of the AAFGI statement is:

{AAFGI | Audit All Fieldgroup Information} [lob-option] [CtoFields]

Where

If CtOFields is specified, the COUNT-OCCURRENCES-OF (CTO) field(s) are audited. By default CTO fields are not audited.

Audit All Information statement

Purpose

The Audit All Information (or AAI) statement writes the output of entire records to the audit trail.

Syntax

The form of the AAI statement is:

{Audit All Information | AAI} [CtoFields]

Where

CtoFields specifies to print the counter (CTO) field(s). By default CTO fields are not printed.

Print All Fieldgroup Information (PAFGI) statement

Purpose

The Print All Fieldgroup Information (PAFGI) statement writes the output of the current fieldgroup to the terminal.

The AAFGI statement displays the same information but is routed to the audit trail.

Syntax

The form of the PAFGI statement is:

{PAFGI | Print All Fieldgroup Information} [lob-option] [CtoFields]

Where:

  • lob-option can be one of the following and specifies how Large Object fields are handled.
    • Lob_Flod, the default, which means output in a format that FLOD can read and reload Large Object fields. The output includes the descriptor in binary format, plus the full field value.
    • Lob_Data specifies whether to display the field value. The PAFGI statement displays the Large Object descriptor in printable format.
    • Lob_Short_Data specifies to display the first 255 bytes of the field value. The PAFGI statement displays the Large Object descriptor in printable format.
    • Lob_No_Data specifies not to display the value. However, the PAFGI statement displays the Large Object descriptor in printable format.
    • Lob_None, which says not to display any Large Object fields.
  • CtoFields specifies to print the counter (CTO) field(s). By default CTO fields are not printed.

Example

This example shows printed terminal output from PAFGI and audit trail output from AAFGI:

fr where policy_number = 100095 feo fieldgroup driver print '---' and driver_name and '---' audit '---' and driver_name and '---' pafgi aafgi end for end for

Printed output:

--- ALTON, BARBARA W --- \DRIVER = 1 DRIVER_ID = 100223 DRIVER_NAME = ALTON, BARBARA W DRIVER_MARITAL_STATUS = MARRIED DRIVER_GENDER = F DRIVER_DATE_OF_BIRTH = 19280311 /DRIVER = 1 ......

Audit trail output:

10189103542 30 2 9 US --- ALTON, BARBARA W --- 10189103542 31 2 9 US \DRIVER = 1 10189103542 32 2 9 US DRIVER_ID = 100223 10189103542 33 2 9 US DRIVER_NAME = ALTON, BARBARA W 10189103542 34 2 9 US DRIVER_MARITAL_STATUS = MARRIED 10189103542 35 2 9 US DRIVER_GENDER = F 10189103542 36 2 9 US DRIVER_DATE_OF_BIRTH = 19280311 10189103542 37 2 9 US /DRIVER = 1 ...and so on...

PRINT ALL INFORMATION (or PAI) statement

Purpose

The Print All Information (or PAI) statement causes output of whole records.

Syntax

The format of the PAI statement is:

{PAI | Print All Information}
[Lob_Flod | Lob_Data | Lob_Short_Data | Lob_No_Data | Lob_None]
[CtoFields]
[Into array1, array2 [, array3] [From start] [Count ct] ]

Where:

  • At most one Lob option can be specified for large object display:
    • Lob_Flod, the default, specifies output in a format that Flod can read and reload Large Object fields. The output includes the descriptor in binary format, plus the full field value.
    • Lob_Data specifies whether to display the field value. The PAI statement displays the Large Object descriptor in printable format.
    • Lob_Short_Data specifies to display the first 255 bytes of the field value. The PAI statement displays the Large Object descriptor in printable format.
    • Lob_No_Data specifies not to display the value. However, the PAI
    • Lob_None specifies not to display any Large Object fields.
  • CtoFields specifies that the counter (CTO) field(s) should be printed. By default, CTO fields are not printed.

    Note: The CtoFields keyword and a Lob_xxx keyword can be in either order, but neither of them can follow the Into clause.

  • The Into clause is not used for display purposes. It lets you move a record's field names and values into a form of storage that can be easily manipulated. For details, see Print All Information (or PAI) Into statement.

Examples

  • Displaying FIND results

    This request:

    BEGIN FIND.RECS: FIND ALL RECORDS FOR WHICH RECTYPE = DRIVER FULLNAME = ABBOTT, GAIL H END FIND FOR EACH RECORD IN FIND.RECS PRINT ALL INFORMATION END FOR END

    results in output with the following format:

    FULLNAME = ABBOTT, GAIL H POLICY NO = 100340 DRIVER ID = 100817 MARITAL STATUS = SINGLE SEX = F STATE = CALIFORNIA INCIDENT = T1 INCIDENT DATE = 19890820 INCIDENT = T3 INCIDENT DATE = 19900921 DATE OF BIRTH = 19390305

  • Displaying counter fields

    This request:

    BEGIN FR WHERE POLICY_NUMBER = 100000 PAI CTOFIELDS END FOR END

    results in output with the following format (note the inclusion of the CTO fields NUMBER_OF_DRIVERS and NUMBER_OF_VEHICLES):

    ADDRESS = 3621 GLEN CT POLICY_NUMBER = 100000 NUMBER_OF_DRIVERS = 4 NUMBER_OF_VEHICLES = 3 ...and so on... \DRIVER = 1 DRIVER_ID = 100001 DRIVER_NAME = ALLEN, MARGARET E ...and so on... /DRIVER = 1 \DRIVER = 2 DRIVER_ID = 100002 DRIVER_NAME = ALLEN, RAY F ...and so on...

  • Displaying fields of different storage types, including non-preallocated, preallocated, and the EXACTLY-ONE type

    This definition and request:

    DEFINE FIELD PORD1 ( ORD CHAR OCC 1 LEN 4 ) DEFINE FIELD EORD1 ( ORD CHAR EXACTLY-ONE ) DEFINE FIELD NORD1 ( ORD CHAR ) BEGIN IN F100A STORE RECORD PORD1 = FRED EORD1 = JANE NORD1 = STEV END STORE END BEGIN FOR EACH RECORD PAI PRINT END FOR END

    results in output with the following format. Note the output of the EXACTLY-ONE field first in the list, followed by the preallocated field, and finally the non-preallocated field:

    EORD1 = JANE PORD1 = FRED NORD1 = STEV

    Because EXACTLY-ONE fields are always present but are not always physically stored in the record, it is most efficient to display them first in the PAI statement. If there are no EXACTLY-ONE fields defined to the file, then the order of printing will be any preallocated fields (in the order in which they were defined to the file), followed by any non preallocated fields (in the order in which they are stored within the record).

INVISIBLE fields are not printed

A field defined with the INVISIBLE attribute cannot be printed, noted, or sorted.

The previous "Displaying FIND results" example shows a fieldname = value pair (RECTYPE=DRIVER) that is not printed in the output because the field (RECTYPE) has the INVISIBLE attribute.

For more information about the INVISIBLE attribute, refer to Field descriptions and attributes.

PRINT fieldname statement

Syntax

To display the value of a certain field in a record, use this form of the Print statement:

Print fieldname [And fieldname2]

Example

Here is a sample Print fieldname statement:

BEGIN DRIVER: FIND ALL RECORDS FOR WHICH RECTYPE = DRIVER POLICY NO = 100340 END FIND FOR EACH RECORD IN DRIVER PRINT FULLNAME END FOR END

In response to the above request, Model 204 prints:

ABBOTT, FRANKLIN G ABBOTT, GAIL H

Each value of the FULLNAME field is displayed on a new line. A record that has no FULLNAME field produces a blank line of output.

Printing several fields

The values of more than one field can be printed on the same output line by using the keywords And and With. And causes a space to be printed between values, as in the following example:

BEGIN DRIVER: FIND ALL RECORDS FOR WHICH RECTYPE = DRIVER POLICY NO = 100340 END FIND FOR EACH RECORD IN DRIVER PRINT SEX AND DATE OF BIRTH END FOR END

Running the request above results in this output:

M 19480204 F 19390305

If a retrieved record has no SEX field, the value of DATE OF BIRTH is printed on a line beginning with a space, as specified by the keyword And, for example:

19480204

If a record has neither field, it produces a blank line of output.

The keyword With works like And except that With does not cause a space to be printed between values.

Controlling spacing between output lines

The Skip Line and New Page statements enable you to control spacing between lines.

SKIP LINE(S)

The SKIP LINE(S) statement directs Model 204 to skip a specified number of lines before printing the next line of output. If the number of lines remaining on a page is fewer than the number of lines specified, Model 204 skips the remainder of the lines on the page, starts a new page, and then continues to skip lines until n is reached.

The syntax of the SKIP LINE(S) statement is as follows:

SKIP {n | %variable} LINE[S]

Where:

  • n is a positive integer less than or equal to 100.
  • %variable is a positive integer from 0 through 100 supplied by a %variable or a :% variable. Field names or %% variables are invalid.

SKIP %variable LINE(S) bypasses the processing and skips no lines if the value of the %variable is zero. However, if n is zero, SKIP n LINES is invalid.

The SKIP %variable LINE(S) option does not apply to the full-screen SKIP LINE(S) statement, as described on SKIP statement for menus.

NEW PAGE

The NEW PAGE statement directs Model 204 to start a new page before printing further output.

For example, in response to:

FOR EACH RECORD IN DRIVER NEW PAGE PRINT ALL INFORMATION END FOR

Model 204 starts a new page before printing each record.

Loading Large Object data

A Model 204 file that contains Large Object data can be reorganized using the standard PAI and FLOD procedure, which employs the User Language PAI (PRINT ALL INFORMATION) statement to unload the data from a file, and a standard FLOD program to reload the data. This is identical to previous releases of Model 204, and no change to your existing unload or load code is required.

IN clauses

Many User Language statements can be preceded by an IN clause. An IN indicates explicitly the file or group to which a statement applies when several files or groups are open.

When you do not use the IN clause, Model 204 acts on the default file as established by the most recently executed OPEN or DEFAULT command.

In addition to specifying an individual file or group name, the IN clause has more detailed information about the reference context of the statement it precedes. For example, when you are working with individual remote files, you can specify the location of the file; and when you are working in group context, you can restrict the context of certain statements to one member file in the group by using the IN GROUP MEMBER form of the IN clause.

Refer to Files, groups, and reference context for more information on the reference context and ways to use the IN clause.

Syntax

The simplest form of the IN clause is:

IN {filename | groupname} [AT location]

Example

This request prints the policy number, body, and make of a policy in the VEHICLES file, and the full name, marital status, and sex of records for that policy number in the CLIENTS file:

OPEN CLIENTS OPEN VEHICLES BEGIN VEHICLE.RECS: IN VEHICLES AT BOSTON FIND ALL RECORDS FOR WHICH OWNER POLICY = 100340 VIN = DAT7100707 END FIND FOR EACH RECORD IN VEHICLE.RECS PRINT OWNER POLICY AND BODY AND MAKE END FOR DRIVER: IN CLIENTS FIND ALL RECORDS FOR WHICH RECTYPE = DRIVER POLICY NO = 100340 END FIND FOR EACH RECORD IN DRIVER PRINT FULLNAME AND MARITAL STATUS AND SEX END FOR END

The output from this request would be:

100340 4DR DATSUN ABBOTT, FRANKLIN G SINGLE M ABBOTT, GAIL H SINGLE F

Request continuation

Lengthy requests can be handled by using the END MORE statement and the MORE command. See Large request considerations for more information on request continuation, including a discussion of the MORE command and the END MORE statement and its USE option.

Example

The sample request below tells you how many records exist in a given file for female drivers registered in Florida:

BEGIN GET.DRIVER: FIND AND PRINT COUNT RECTYPE = DRIVER STATE = FLORIDA SEX = F END FIND END MORE

After printing its output, this request returns you to command level. Using the MORE command, you can continue the request, adding further statements as well as referring to the results of statements in the original request. If, for example, you wanted to perform further processing of the records found in the original request without repeating the FIND operation, you could enter the following lines, referencing the label GET.DRIVER:

MORE FOR EACH RECORD IN GET.DRIVER PRINT FULLNAME AND DATE OF BIRTH END FOR END

See also

SOUL statements