Record retrievals: Difference between revisions

From m204wiki
Jump to navigation Jump to search
m (mixed case for statements)
 
(54 intermediate revisions by 6 users not shown)
Line 1: Line 1:
<div class="toclimit-3">
==Overview==
==Overview==
<p>
<p>
[[Basic User Language Statements and Commands#Basic User Language Statements and Commands|Basic User Language Statements and Commands]] introduced basic FIND statements that could be used for record retrieval. This page discusses more advanced ways to locate records.</p>
[[Basic SOUL statements and commands|Basic SOUL statements and commands]] introduced basic FIND statements that could be used for record retrieval. This page discusses more advanced ways to locate records.</p>
   
   
===Reference context===
===Reference context===
<p>
<p>
The FIND statement is supported in remote file and scattered group contexts. See [[Files, Groups, and Reference Context#Files, Groups, and Reference Context|Files, Groups, and Reference Context]] for additional information on the reference context.</p>
The FIND statement is supported in remote file and scattered group contexts. See [[Files, groups, and reference context]] for additional information on the reference context.</p>
   
   
===Retrieval conditions===
===Retrieval conditions===
Line 12: Line 14:
<p>
<p>
This page discusses the following types of record retrievals:</p>
This page discusses the following types of record retrievals:</p>
<table>
<table>
<tr class="head">
<tr class="head">
Line 17: Line 20:
<th>Locates records...</th>
<th>Locates records...</th>
</tr>
</tr>
<tr>
<tr>
<td>Range </td>
<td>Range </td>
<td>With field values within a specified numeric or character value range.</td>
<td>With field values within a specified numeric or character value range.</td>
</tr>
</tr>
<tr>
<tr>
<td>Pattern </td>
<td>Pattern </td>
<td>With field values matching a specified pattern (for example, a <code>c*</code> pattern searches for all patterns beginning with the letter ''c'').</td>
<td>With field values matching a specified pattern (for example, a <code>c*</code> pattern searches for all patterns beginning with the letter "c").</td>
</tr>
</tr>
<tr>
<tr>
<td>IS PRESENT </td>
<td>IS PRESENT </td>
<td>Based on whether or not the specified field exists in a record.</td>
<td>Based on whether or not the specified field exists in a record.</td>
</tr>
</tr>
<tr>
<tr>
<td>FIND$ </td>
<td>FIND$ </td>
<td>From a found or deleted set of records.</td>
<td>From a found or deleted set of records.</td>
</tr>
</tr>
<tr>
<tr>
<td>IN label </td>
<td>IN label </td>
<td>From a found set of records.</td>
<td>From a found set of records.</td>
</tr>
</tr>
<tr>
<tr>
<td>SFL$ and SFGE$</td>
<td>SFL$ and SFGE$</td>
<td>In sorted files. </td>
<td>In sorted files. </td>
</tr>
</tr>
<tr>
<tr>
<td>POINT$</td>
<td>POINT$</td>
<td>Based on internal record number.</td>
<td>Based on internal record number.</td>
</tr>
</tr>
<tr>
<tr>
<td>LIST$</td>
<td>LIST$</td>
<td>From a list. [[Lists#Lists|Lists]] discusses lists and the LIST$ condition in detail.</td>
<td>From a list. [[Lists#Lists|Lists]] discusses lists and the LIST$ condition in detail.</td>
</tr>
</tr>
<tr>
<tr>
<td>IN MEMBER and FILE$</td>
<td>IN MEMBER and FILE$</td>
<td>In file groups.</td>
<td>In file groups.</td>
</tr>
</tr>
<tr>
<tr>
<td>LOCATION$</td>
<td>LOCATION$</td>
Line 58: Line 71:
</tr>
</tr>
</table>
</table>
 
===Handling a foundset===
===Handling a foundset===
<p>
<p>
Line 65: Line 78:
==Numeric range retrievals==
==Numeric range retrievals==
<p>
<p>
When a field contains mostly numerical values, the magnitude of the numbers can be tested by using this statement:                 </p>
When a field contains mostly numerical values, the magnitude of the numbers can be tested by using this statement: </p>
<b>Syntax</b>
<b>Syntax</b>
<p class="syntax">FIND ALL RECORDS FOR WHICH
<p class="syntax">FIND ALL RECORDS FOR WHICH
   <span class="term">fieldname</span> IS <span class="squareb">[</span>NUMERICALLY<span class="squareb">]</span> <span class="squareb">[</span><span class="term">operator</span><span class="squareb">]</span> <span class="term">value</span>
   <span class="term">fieldname</span> IS <span class="squareb">[</span>NUMERICALLY<span class="squareb">]</span> <span class="squareb">[</span><span class="term">operator</span><span class="squareb">]</span> <span class="term">value</span>
</p>
</p>
<b>Where</b>
Where:
<ul>
<ul>
<li>The NUMERICALLY keyword is optional and specifies that a numeric range retrieval should be performed. If this keyword is omitted, the type of retrieval performed is based upon the default type of the operator. For more information about the default type for each operator, refer to [[#Interpretation of values in retrievals|Interpretation of values in retrievals]].  </li>
<li>The NUMERICALLY keyword is optional and specifies that a numeric range retrieval should be performed. If this keyword is omitted, the type of retrieval performed is based upon the default type of the operator. For more information about the default type for each operator, refer to [[#Interpretation of values in retrievals|Interpretation of values in retrievals]].  </li>
<li><var class="term">operator</var> is one of the range retrieval operators in [[#Range retrieval operators|Range retrieval operators]].</li>
<li><var class="term">operator</var> is one of the range retrieval operators in [[#Range retrieval operators|Range retrieval operators]].</li>
</li>
</ul>
</ul>
   
   
Line 85: Line 98:
<th>Requires the field value to be...</th>
<th>Requires the field value to be...</th>
</tr>
</tr>
<tr>
<tr>
<td>EQ<br>=</td>
<td>EQ<br>=</td>
<td>Equal to the value specified.</td>
<td>Equal to the value specified.</td>
</tr>
</tr>
<tr>
<tr>
<td>NE<br>&#172;=</td>
<td>NE<br>&#172;=</td>
<td>Unequal to the value specified.</td>
<td>Unequal to the value specified.</td>
</tr>
</tr>
<tr>
<tr>
<td>LESS THAN<br>LT<br><</td>
<td>LESS THAN<br>LT<br><</td>
<td>Less than the value specified.</td>
<td>Less than the value specified.</td>
</tr>
</tr>
<tr>
<tr>
<td>LE<br><=</td>
<td>LE<br><=</td>
<td>Less than or equal to the value specified.</td>
<td>Less than or equal to the value specified.</td>
</tr>
</tr>
<tr>
<tr>
<td>GREATER THAN<br>GT<br>></td>
<td>GREATER THAN<br>GT<br>></td>
<td>Greater than the value specified.</td>
<td>Greater than the value specified.</td>
</tr>
</tr>
<tr>
<tr>
<td>GE<br>>=</td>
<td>GE<br>>=</td>
<td>Greater than or equal to the value specified. </td>
<td>Greater than or equal to the value specified. </td>
</tr>
</tr>
<tr>
<tr>
<td>BETWEEN <var class="term">value1</var> AND <var class="term">value2</var></td>
<td>BETWEEN <var class="term">value1</var> AND <var class="term">value2</var></td>
<td>
<td>Between <var class="term">value1</var> and <var class="term">value2</var>.  
<p>
If the field value is equal to <var class="term">value1</var> or <var class="term">value2</var>, the record is not retrieved.
Between <var class="term">value1</var> and <var class="term">value2</var>. </p>
<p>
<p>
If the field value is equal to <var class="term">value1</var> or <var class="term">value2</var>, the record is not retrieved. </p>
If value1 is greater than <var class="term">value2</var>, no records are retrieved.</p></td>
<p>
If value1 is greater than <var class="term">value2</var>, no records are retrieved.</p>
</td>
</tr>
</tr>
</table>
</table>
Line 142: Line 158:
If the value of the named field in a given record has the stated relation to the number in the retrieval condition, then the record is retrieved. For example, AGE IS BETWEEN 21 AND 35 does retrieve a record with the pair AGE = 27.</p>
If the value of the named field in a given record has the stated relation to the number in the retrieval condition, then the record is retrieved. For example, AGE IS BETWEEN 21 AND 35 does retrieve a record with the pair AGE = 27.</p>
<p>
<p>
When a numeric retrieval is performed on a multiply occurring field, all records with at least one occurrence of the field that meets the specified condition are retrieved. See [[Operations on Multiply Occurring Fields#Operations on Multiply Occurring Fields|Operations on Multiply Occurring Fields]] for more information about retrieval of multiply occurring fields.         </p>
When a numeric retrieval is performed on a multiply occurring field, all records with at least one occurrence of the field that meets the specified condition are retrieved. See [[Processing multiply occurring fields and field groups]] for more information about retrieval of multiply occurring fields. </p>
   
   
===NUMERIC RANGE and ORDERED NUMERIC attributes===
===NUMERIC RANGE and ORDERED NUMERIC attributes===
Line 149: Line 165:
<ul>
<ul>
<li>[[#Character string retrievals|Character string retrievals]]</li>
<li>[[#Character string retrievals|Character string retrievals]]</li>
</li>
 
<li>[[#Range retrieval optimization|Range retrieval optimization]] for more information on the relationship between field attributes and range retrievals.</li>
<li>[[#Range retrieval optimization|Range retrieval optimization]] for more information on the relationship between field attributes and range retrievals.</li>
</li>
</ul>
</ul>
   
   
Line 159: Line 174:
<ul>
<ul>
<li>Optional leading plus sign (+) or minus sign (-).</li>
<li>Optional leading plus sign (+) or minus sign (-).</li>
</li>
 
<li>Digits 0-9 and an optional decimal point.</li>
<li>Digits 0-9 and an optional decimal point.</li>
</li>
 
<li>No more than 10 digits on either side of the decimal point (20 digits maximum) for NUMERIC RANGE fields.</li>
<li>No more than 10 digits on either side of the decimal point (20 digits maximum) for NUMERIC RANGE fields.</li>
</li>
 
<li>No more than 10 digits on either side of the decimal point for ORDERED NUMERIC fields; only the first 15 significant digits are used. (Exponent notation can be used for ORDERED NUMERIC fields; see the discussion in [[#Exponent notation|Exponent notation]].) </li>
<li>No more than 10 digits on either side of the decimal point for ORDERED NUMERIC fields; only the first 15 significant digits are used. (Exponent notation can be used for ORDERED NUMERIC fields; see the discussion in [[#Exponent notation|Exponent notation]].) </li>
</li>
</ul>
</ul>
   
   
Line 185: Line 199:
<ul>
<ul>
<li>If you try to add a NUMERIC RANGE field to a record in which that field name already appears, an error occurs. The field is not added and a message is displayed during execution of the request.</li>
<li>If you try to add a NUMERIC RANGE field to a record in which that field name already appears, an error occurs. The field is not added and a message is displayed during execution of the request.</li>
</li>
 
<li>If you try to add an illegal value to a NUMERIC RANGE or ORDERED NUMERIC field, the value is nevertheless stored as entered. For example, if AGE is defined as a NUMERIC RANGE retrieval field type and AGE = TWO is added to the record, it is stored. In order to retrieve a field with an illegal value, the following methods must be used. </li>
<li>If you try to add an illegal value to a NUMERIC RANGE or ORDERED NUMERIC field, the value is nevertheless stored as entered. For example, if AGE is defined as a NUMERIC RANGE retrieval field type and AGE = TWO is added to the record, it is stored. In order to retrieve a field with an illegal value, the following methods must be used. </li>
</li>
</ul>
</ul>
   
   
Line 200: Line 213:
</p>
</p>
<p>
<p>
To print an illegal value in a NUMERIC RANGE field, the regular name of the field should be used. PRINT AGE outputs all the values of the AGE field in a record, including A3 and TWO. Illegal values in an ORDERED NUMERIC field can be found by locating a value alphabetically greater than nulls and less than zero.       </p>
To print an illegal value in a NUMERIC RANGE field, the regular name of the field should be used. PRINT AGE outputs all the values of the AGE field in a record, including A3 and TWO. Illegal values in an ORDERED NUMERIC field can be found by locating a value alphabetically greater than nulls and less than zero. </p>
   
   
==Negated numeric range retrievals==
==Negated numeric range retrievals==
Line 239: Line 252:
<th>Then records are...</th>
<th>Then records are...</th>
</tr>
</tr>
<tr>
<tr>
<td>NUMERIC RANGE </td>
<td>NUMERIC RANGE </td>
<td>Retrieved by this statement are those with AGE fields whose values are numerical and greater than or equal to 10.</td>
<td>Retrieved by this statement are those with AGE fields whose values are numerical and greater than or equal to 10.</td>
</tr>
</tr>
<tr>
<tr>
<td>NON-RANGE or ORDERED </td>
<td>NON-RANGE or ORDERED </td>
<td>Retrieved by this condition are those that do not contain AGE fields less than 10. These records include those containing nonnumerical AGE fields and those containing no AGE field.</td>
<td>Retrieved by this condition are those that do not contain AGE fields less than 10. These records include those containing nonnumerical AGE fields and those containing no AGE field.</td>
</tr>
</tr>
<tr>
<tr>
<td>NON-RANGE and INVISIBLE</td>
<td nowrap>NON-RANGE and INVISIBLE</td>
<td>Not retrieved.       </td>
<td>Not retrieved. </td>
</tr>
</tr>
</table>
</table>
Line 258: Line 274:
<ul>
<ul>
<li>The treatment of exponent notation in a retrieval statement is discussed in [[#Interpretation of values in retrievals|Interpretation of values in retrievals]].</li>
<li>The treatment of exponent notation in a retrieval statement is discussed in [[#Interpretation of values in retrievals|Interpretation of values in retrievals]].</li>
<li>The storage of exponent notation values is discussed in [[Data Maintenance#Storing values in FLOAT fields|Storing values in FLOAT fields]].</li>
 
<li>The storage of exponent notation values is discussed in [[Data maintenance#Storing values in FLOAT fields|Storing values in FLOAT fields]].</li>
</ul>
</ul>
<b>Syntax</b>
<b>Syntax</b>
Line 269: Line 286:
  <span class="squareb">|</span> <span class="term">fractional-number</span><span class="squareb">}</span> <span class="literal">E</span> <span class="squareb">[</span>+ <span class="squareb">|</span> -<span class="squareb">]</span>
  <span class="squareb">|</span> <span class="term">fractional-number</span><span class="squareb">}</span> <span class="literal">E</span> <span class="squareb">[</span>+ <span class="squareb">|</span> -<span class="squareb">]</span>
</p>
</p>
<b>Where</b>
Where:
<ul>
<ul>
<li>The value of the whole number and/or the fractional number should be a maximum of 15 significant decimal digits. If the number of significant digits exceeds 15, the remaining precision is lost.</li>
<li>The value of the whole number and/or the fractional number should be a maximum of 15 significant decimal digits. If the number of significant digits exceeds 15, the remaining precision is lost.</li>
Line 288: Line 305:
==Character string retrievals==
==Character string retrievals==
<p>
<p>
When a field contains string values, for example, FULLNAME or ADDRESS, the field can be tested to determine whether a record containing values within a particular range should be retrieved. </p>
When a field contains string values, for example, FULLNAME or ADDRESS, the field can be tested to determine whether a record containing values within a particular range should be retrieved, rather than simply retrieving records based on equality conditions. An example of string equality retrieval is this <var>Find All Records For Which</var> statement to retrieve records of blue Fords:</p>
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
              MAKE = FORD
              COLOR = BLUE
          END FIND
</p>
<b>Syntax</b>
<b>Syntax</b>
<p>
<p>
In addition to the equality condition, NAME = SMITH that is described in [[Basic User Language Statements and Commands#FIND statement|FIND statement]], the following statement is used for the retrieval of string values:</p>
In addition to the equality condition, the following statement is used for the retrieval of string values:</p>
<p class="code">FIND ALL RECORDS FOR WHICH fieldname IS  
<p class="code">FIND ALL RECORDS FOR WHICH fieldname IS  
  [ALPHABETICALLY]  
  [ALPHABETICALLY]  
  {BEFORE | AFTER | operator} value
  {BEFORE | AFTER | operator} value
</p>
</p>
<b>Where</b>
Where:
<ul>
<ul>
<li>The ALPHABETICALLY keyword is optional and specifies that a character range retrieval should be performed. If this keyword is omitted, the type of retrieval performed is based upon the default type of the operator. For more information about the default type for each operator, refer to [[#Interpretation of values in retrievals|Interpretation of values in retrievals]].</li>
<li>The ALPHABETICALLY keyword is optional and specifies that a character range retrieval should be performed. If this keyword is omitted, the type of retrieval performed is based upon the default type of the operator. For more information about the default type for each operator, refer to [[#Interpretation of values in retrievals|Interpretation of values in retrievals]].</li>
</li>
 
<li>BEFORE is an operator that specifies records in which the field precedes the given value in EBCDIC collating sequence.  </li>
<li>BEFORE is an operator that specifies records in which the field precedes the given value in EBCDIC collating sequence.  </li>
</li>
 
<li>AFTER is an operator that specifies records in which the field follows the given value in EBCDIC collating sequence.                   </li>
<li>AFTER is an operator that specifies records in which the field follows the given value in EBCDIC collating sequence. </li>
</li>
 
<li>operator is one of the range retrieval operators listed earlier in this section in [[#Range retrieval operators|Range retrieval operators]]. The standard EBCDIC collating sequence is used when performing string comparisons. </li>
<li>operator is one of the range retrieval operators listed earlier in this section in [[#Range retrieval operators|Range retrieval operators]]. The standard EBCDIC collating sequence is used when performing string comparisons. </li>
</li>
</ul>
</ul>
   
   
Line 326: Line 347:
===Multiply occurring fields===
===Multiply occurring fields===
<p>
<p>
When a string retrieval is performed on a multiply occurring field, all records with at least one occurrence of the field meeting the specified condition are retrieved. See [[Operations on Multiply Occurring Fields#Operations on Multiply Occurring Fields|Operations on Multiply Occurring Fields]] for more information about retrieving multiply occurring fields.         </p>
When a string retrieval is performed on a multiply occurring field, all records with at least one occurrence of the field meeting the specified condition are retrieved. See [[Processing multiply occurring fields and field groups]] for more information about retrieving multiply occurring fields. </p>
   
   
===Negated string retrieval conditions===
===Negated string retrieval conditions===
<p>
<p>
Any of the string retrieval conditions can be negated by using the word NOT after the IS. For example:           </p>
Any of the string retrieval conditions can be negated by using the word NOT after the IS. For example: </p>
<p class="code">FULLNAME IS NOT AFTER 'Q'
<p class="code">FULLNAME IS NOT AFTER 'Q'
FULLNAME IS NOT LT JOHNSTON
FULLNAME IS NOT LT JOHNSTON
Line 352: Line 373:
  [FROM | AFTER] <span class="term">value1</span> {TO | [AND] BEFORE} <span class="term">value2</span>
  [FROM | AFTER] <span class="term">value1</span> {TO | [AND] BEFORE} <span class="term">value2</span>
</p>
</p>
<b>Where</b>
Where:
<ul>
<ul>
<li>The NUMERICALLY or ALPHABETICALLY keyword specifies the type of retrieval to be performed. NUMERICALLY indicates a numeric range retrieval; ALPHABETICALLY indicates a character range retrieval. NUMERICALLY is the default. </li>
<li>The NUMERICALLY or ALPHABETICALLY keyword specifies the type of retrieval to be performed. NUMERICALLY indicates a numeric range retrieval; ALPHABETICALLY indicates a character range retrieval. NUMERICALLY is the default. </li>


<li>The FROM or AFTER clause specify whether a field containing value1 should be included in the retrieval. FROM indicates that the retrieval includes value1. AFTER indicates that the retrieval begins at, but does not include, value1. FROM is the default if the FROM or AFTER keyword is not specified.               </li>
<li>The FROM or AFTER clause specify whether a field containing value1 should be included in the retrieval. FROM indicates that the retrieval includes value1. AFTER indicates that the retrieval begins at, but does not include, value1. FROM is the default if the FROM or AFTER keyword is not specified. </li>


<li><var class="term">value1</var> specifies the beginning value.</li>
<li><var class="term">value1</var> specifies the beginning value.</li>


<li>The TO or BEFORE clause specifies whether a field containing <var class="term">value2</var> should be included in the retrieval. TO indicates that the retrieval includes value2. BEFORE indicates that the retrieval ends at, but does not include, <var class="term">value2</var>.           </li>
<li>The TO or BEFORE clause specifies whether a field containing <var class="term">value2</var> should be included in the retrieval. TO indicates that the retrieval includes value2. BEFORE indicates that the retrieval ends at, but does not include, <var class="term">value2</var>. </li>


<li><var class="term">value2</var> specifies the ending value. </li>
<li><var class="term">value2</var> specifies the ending value. </li>
Line 369: Line 390:
<li>If the IN RANGE clause is used for ORDERED fields, the retrieval is optimized because the search on the Ordered Index is restricted between two values. For more information see [[#Summary of field attributes and retrieval optimization|Summary of field attributes and retrieval optimization]].</li>
<li>If the IN RANGE clause is used for ORDERED fields, the retrieval is optimized because the search on the Ordered Index is restricted between two values. For more information see [[#Summary of field attributes and retrieval optimization|Summary of field attributes and retrieval optimization]].</li>


<li>The IN RANGE clause is particularly useful when a beginning and ending range of values must be specified for a multiply occurring field. See [[Operations on Multiply Occurring Fields#Operations on Multiply Occurring Fields|Operations on Multiply Occurring Fields]] for more information about multiply occurring fields. </li>
<li>The IN RANGE clause is particularly useful when a beginning and ending range of values must be specified for a multiply occurring field. See [[Processing multiply occurring fields and field groups]] for more information about multiply occurring fields. </li>
</ul>
</ul>
   
   
==Boolean operators in retrieval statements==
==Boolean operators in retrieval statements==
<p>
<p>
You can use the AND, OR, NOT, NOR, and parentheses in FIND statements to combine retrieval conditions.   </p>
You can use the AND, OR, NOT, NOR, and parentheses in FIND statements to combine retrieval conditions. </p>
<p>
<p>
Boolean operators and parentheses are described in more detail on the following pages.</p>
Boolean operators and parentheses are described in more detail on the following pages.</p>
Line 407: Line 428:
<th>Operator</th>
<th>Operator</th>
</tr>
</tr>
<tr>
<tr>
<td>First</td>
<td>First</td>
<td>NOT</td>
<td>NOT</td>
</tr>
</tr>
<tr>
<tr>
<td>Second</td>
<td>Second</td>
<td>NOR</td>
<td>NOR</td>
</tr>
</tr>
<tr>
<tr>
<td>Third </td>
<td>Third </td>
<td>AND (explicitly stated)</td>
<td>AND (explicitly stated)</td>
</tr>
</tr>
<tr>
<tr>
<td>Fourth</td>
<td>Fourth</td>
<td>OR </td>
<td>OR </td>
</tr>
</tr>
<tr>
<tr>
<td>Fifth</td>
<td>Fifth</td>
Line 461: Line 487:
</p>
</p>
<p>
<p>
(describing people who have American-born mothers and fathers) is performed first and is then combined with the set of records describing Boston residents. A record that meets both criteria is included in the found set.     </p>
(describing people who have American-born mothers and fathers) is performed first and is then combined with the set of records describing Boston residents. A record that meets both criteria is included in the found set. </p>
<p>
<p>
The following example indicates how a somewhat more complex statement would be processed:</p>
The following example indicates how a somewhat more complex statement would be processed:</p>
Line 473: Line 499:
</p>
</p>
<p>
<p>
The first action <var class="product">Model&nbsp;204</var> takes is to perform the OR (STATE EQ MA OR STATE EQ CT), since the OR has higher precedence than the implied AND generated by the first selection criterion. Then the result of that OR is combined with the first selection criterion; again only records that meet both criteria are included in the found set. In other words, this example selects only those residents of Massachusetts or Connecticut whose decile value is in the specified range.</p>
The first action <var class="product">Model&nbsp;204</var> takes is to perform the <code>OR (STATE EQ MA OR STATE EQ CT)</code>, since the OR has higher precedence than the implied AND generated by the first selection criterion. Then the result of that OR is combined with the first selection criterion; again only records that meet both criteria are included in the found set. In other words, this example selects only those residents of Massachusetts or Connecticut whose decile value is in the specified range.</p>
   
   
==Interpretation of values in retrievals==
==Interpretation of values in retrievals==
Line 483: Line 509:
In an equality retrieval condition (fieldname = value), the interpretation of the value is based on whether the field has been defined with the KEY and FLOAT field attributes or the ORDERED NUMERIC attribute, as follows: </p>
In an equality retrieval condition (fieldname = value), the interpretation of the value is based on whether the field has been defined with the KEY and FLOAT field attributes or the ORDERED NUMERIC attribute, as follows: </p>
<ul>
<ul>
<li>If the field has been defined with the KEY and FLOAT field attributes or the ORDERED NUMERIC attribute, the value is examined to determine whether it is a number, exponent notation, or a character string. Comparisons are then performed as follows:</li>
<li>If the field has been defined with the KEY and FLOAT field attributes or the ORDERED NUMERIC attribute, the value is examined to determine whether it is a number, exponent notation, or a character string. Comparisons are then performed as follows:
<p>
<p>
If the value is a number or in exponent notation, a numerical comparison is performed.  </p>
If the value is a number or in exponent notation, a numerical comparison is performed.  </p>
<p>
<p>
If the value is a character string, a character string comparison is performed. </p>
If the value is a character string, a character string comparison is performed. </p> </li>


<li>If the field has not been defined with the KEY and FLOAT field attributes or the ORDERED NUMERIC attribute, the value is treated as a string and a character string comparison is performed. Exponent notation is not converted to a numerical form. For example, if a field contains a value of .1234E-3, the comparison .1234E-3 = .0001234 is not true if the field has not been defined with the KEY and FLOAT field attributes or the ORDERED NUMERIC attribute.       </li>
<li>If the field has not been defined with the KEY and FLOAT field attributes or the ORDERED NUMERIC attribute, the value is treated as a string and a character string comparison is performed. Exponent notation is not converted to a numerical form. For example, if a field contains a value of .1234E-3, the comparison .1234E-3 = .0001234 is not true if the field has not been defined with the KEY and FLOAT field attributes or the ORDERED NUMERIC attribute. </li>
</ul>
</ul>


===Range retrieval===
===Range retrieval===
<p>
<p>
In a range retrieval condition (in the form fieldname IS <var>[</var>operator<var>]</var> value) or a condition that uses the IN RANGE clause, the type of comparison performed is based on whether the NUMERICALLY or ALPHABETICALLY keyword is specified, as follows: </p>
In a range retrieval condition (in the form fieldname IS [operator] value) or a condition that uses the IN RANGE clause, the type of comparison performed is based on whether the NUMERICALLY or ALPHABETICALLY keyword is specified, as follows: </p>
<table>
<table>
<tr class="head">
<tr class="head">
Line 500: Line 526:
<th>The value is interpreted as...</th>
<th>The value is interpreted as...</th>
</tr>
</tr>
<tr>
<tr>
<td>NUMERICALLY </td>
<td>NUMERICALLY </td>
<td>Number and a numerical comparison is performed.       </td>
<td>Number and a numerical comparison is performed. </td>
</tr>
</tr>
<tr>
<tr>
<td>ALPHABETICALLY </td>
<td>ALPHABETICALLY </td>
<td>String and a character string comparison is performed. </td>
<td>String and a character string comparison is performed. </td>
</tr>
</tr>
<tr>
<tr>
<td>Neither
<td>Neither
Line 522: Line 551:
<th>Default type of comparison performed</th>
<th>Default type of comparison performed</th>
</tr>
</tr>
<tr>
<tr>
<td>IN RANGE </td>
<td>IN RANGE </td>
<td>Numeric  </td>
<td>Numeric  </td>
</tr>
</tr>
<tr>
<tr>
<td>EQ, =, or none </td>
<td>EQ, =, or none </td>
<td>Numeric    </td>
<td>Numeric    </td>
</tr>
</tr>
<tr>
<tr>
<td>NE or &#172;= </td>
<td>NE or &#172;= </td>
<td>Numeric  </td>
<td>Numeric  </td>
</tr>
</tr>
<tr>
<tr>
<td>LESS THAN, LT, or <</td>
<td>LESS THAN, LT, or <</td>
<td>Numeric  </td>
<td>Numeric  </td>
</tr>
</tr>
<tr>
<tr>
<td>LE or <= </td>
<td>LE or <= </td>
<td>Numeric  </td>
<td>Numeric  </td>
</tr>
</tr>
<tr>
<tr>
<td>GREATER THAN, GT, or > </td>
<td>GREATER THAN, GT, or > </td>
<td>Numeric  </td>
<td>Numeric  </td>
</tr>
</tr>
<tr>
<tr>
<td>GE or >= </td>
<td>GE or >= </td>
<td>Numeric  </td>
<td>Numeric  </td>
</tr>
</tr>
<tr>
<tr>
<td>BETWEEN </td>
<td>BETWEEN </td>
<td>Numeric  </td>
<td>Numeric  </td>
</tr>
</tr>
<tr>
<tr>
<td>AFTER </td>
<td>AFTER </td>
<td>Character string  </td>
<td>Character string  </td>
</tr>
</tr>
<tr>
<tr>
<td>BEFORE </td>
<td>BEFORE </td>
Line 568: Line 607:
The following results occur when an operator and value type are not matched:</p>
The following results occur when an operator and value type are not matched:</p>
<ul>
<ul>
<li>If a numeric operator is used with a nonnumeric value, as in:</li>
<li>If a numeric operator is used with a nonnumeric value, as in:
<p class="code">FIND ALL RECORDS FOR WHICH
<p class="code">FIND ALL RECORDS FOR WHICH
     LAST NAME IS GREATER THAN ANDREWS
     LAST NAME IS GREATER THAN ANDREWS
Line 578: Line 617:
</p>
</p>
<p>
<p>
every record in the file or group is retrieved.</p>
every record in the file or group is retrieved.</p></li>
</li>
 
<li>If a character string operator is used with a numeric value, as in:</li>
<li>If a character string operator is used with a numeric value, as in:
<p class="code">FIND ALL RECORDS FOR WHICH
<p class="code">FIND ALL RECORDS FOR WHICH
     YEAR IS BEFORE 1986
     YEAR IS BEFORE 1986
</p>
</p>
<p>
<p>
all numeric values in the field are converted to character strings and compared character-by-character to the retrieval criteria. Therefore, numbers such as 942, 700, or 2 would not be retrieved.             </p>
all numeric values in the field are converted to character strings and compared character-by-character to the retrieval criteria. Therefore, numbers such as 942, 700, or 2 would not be retrieved. </p></li>
</li>
</ul>
</ul>


Line 599: Line 637:
===Indexed searches for equality retrievals===
===Indexed searches for equality retrievals===
<p>
<p>
When an equality retrieval (fieldname = value) is performed on a field that has the KEY attribute, or when a numeric retrieval (fieldname IS [operator] value) is performed on a field that has the NUMERIC RANGE attribute, <var class="product">Model&nbsp;204</var> locates the records that meet the conditions by doing a search of the Table C index.                     </p>
When an equality retrieval (fieldname = value) is performed on a field that has the KEY attribute, or when a numeric retrieval (fieldname IS [operator] value) is performed on a field that has the NUMERIC RANGE attribute, <var class="product">Model&nbsp;204</var> locates the records that meet the conditions by doing a search of the Table C index. </p>
<p>
<p>
When an equality retrieval (fieldname = value) is performed on a field that has the ORDERED attribute but does not have the KEY attribute, the Ordered Index is searched. </p>
When an equality retrieval (fieldname = value) is performed on a field that has the ORDERED attribute but does not have the KEY attribute, the Ordered Index is searched. </p>
Line 608: Line 646:
<ul>
<ul>
<li>If a numeric range retrieval is performed and the field has the ORDERED NUMERIC attribute.</li>
<li>If a numeric range retrieval is performed and the field has the ORDERED NUMERIC attribute.</li>
</li>
 
<li>If a character range retrieval is performed and the field has the ORDERED CHARACTER attribute.     </li>
<li>If a character range retrieval is performed and the field has the ORDERED CHARACTER attribute. </li>
</li>
</ul>
</ul>
   
   
Line 628: Line 665:
<th colspan="7">Field attribute</th>
<th colspan="7">Field attribute</th>
</tr>
</tr>
<tr>
<tr>
<th rowspan="2">&nbsp;<br>KEY</th>
<th rowspan="2">&nbsp;<br>KEY</th>
Line 639: Line 677:
&nbsp;<br>Other</th>
&nbsp;<br>Other</th>
</tr>
</tr>
<tr>
<tr>
<th>ORDERED
<th>ORDERED
Line 645: Line 684:
CHARACTER</th>
CHARACTER</th>
</tr>
</tr>


<tr>
<tr>
Line 736: Line 776:
<p class="code">99983
<p class="code">99983
9
9
</p>
</p> </li>


<li>Input 2:
<li>Input 2:
Line 748: Line 788:
<p class="code">99983
<p class="code">99983
7
7
</p>
</p> </li>


<li>Input 3:
<li>Input 3:
Line 760: Line 800:
<p class="code">99983
<p class="code">99983
0
0
</p>
</p> </li>
</ul>
</ul>
<p>
<p>
Line 776: Line 816:
<p class="code">99983
<p class="code">99983
99974
99974
</p>
</p> </li>


<li>Input 5:
<li>Input 5:
Line 788: Line 828:
<p class="code">99983
<p class="code">99983
99976
99976
</p>
</p> </li>


<li>Input 6:
<li>Input 6:
Line 800: Line 840:
<p class="code">99983
<p class="code">99983
99967
99967
</p>
</p> </li>
</ul>  
</ul>  


Line 816: Line 856:
<p class="note"><b>Note:</b> The LCPDLST parameter, which controlled the stack area used by the pattern matcher in earlier releases, should be kept at its default value.</p>
<p class="note"><b>Note:</b> The LCPDLST parameter, which controlled the stack area used by the pattern matcher in earlier releases, should be kept at its default value.</p>
   
   
===Pattern characters===
<div id="likeSyntax"></div>
<div id="Pattern characters"></div>
===Pattern characters in the Is Like clause===
<p>
<p>
Beginning in <var class="product">Model&nbsp;204</var> Version 5.1, all characters X'00' through X'FF' are valid in a pattern presented to the User Language pattern matcher and the $CHKPAT function. </p>
The characters used for pattern retrieval are summarized in the following table, and discussed in detail in subsequent sections. Because a pattern is treated like a character value, it needs beginning and ending quotes to conform to the format requirements of User Language. See [[Request composition rules#Quotation marks|Quotation marks]] for a detailed discussion about the use of quotes in User Language. </p>
<p>
As a result all characters are treated as valid, literal characters. The following error messages are no longer invoked for these characters.</p>
<p class="code">M204.1688: errortype IN PATTERN 'pattern' AT CHARACTER char
   
   
M204.1689: errortype IN PATTERN 'pattern' AT CHARACTER char
</p>
<p class="note"><b>Note:</b> Patterns containing embedded escape characters preceding special characters function as they did in earlier releases of <var class="product">Model&nbsp;204</var>.</p>
<p>
The characters used for pattern retrieval are summarized in [[#PatternChars|Pattern matching characters]], below, and discussed in detail subsequently. Because a pattern is treated like a character value, it needs beginning and ending quotes to conform to the format requirements of User Language. See [[Request Composition Rules#Quotation marks|Quotation marks]] for a detailed discussion about the use of quotes in User Language. </p>
<div id="PatternChars"></div>
<div id="PatternChars"></div>
<table>
<table>
Line 835: Line 869:
<th>Description</th>
<th>Description</th>
</tr>
</tr>
 
<tr>
<tr>
<td align="center">*</td>
<td align="center">*</td>
<td>Wildcard character   </td>
<td>[[#Wildcard character|Wildcard character]]</td>
</tr>
</tr>
 
<tr>
<tr>
<td align="center">+</td>
<td align="center">+</td>
<td>Placeholder character       </td>
<td>[[#Placeholder character|Placeholder character]]</td>
</tr>
</tr>
 
<tr>
<tr>
<td align="center">,</td>
<td align="center">,</td>
<td>Or character       </td>
<td>[[#Or character|Or character]]</td>
</tr>
</tr>
 
<tr>
<tr>
<td align="center">( )</td>
<td align="center">( )</td>
<td>Set begin and end characters       </td>
<td>[[#Set begin and set end characters|Set begin and end characters]]</td>
</tr>
</tr>
 
<tr>
<tr>
<td align="center">-</td>
<td align="center">-</td>
<td>Range character     </td>
<td>[[#Range character|Range character]]</td>
</tr>
</tr>
 
<tr>
<tr>
<td align="center">/</td>
<td align="center">/</td>
<td>Repeat character       </td>
<td>[[#Repeat character|Repeat character]]</td>
</tr>
</tr>
 
<tr>
<tr>
<td align="center">!</td>
<td align="center">!</td>
<td>Escape character     </td>
<td>[[#Escape character|Escape character]]</td>
</tr>
</tr>
 
<tr>
<tr>
<td align="center">=</td>
<td align="center">=</td>
<td>Hexadecimal character   </td>
<td>[[#Hexadecimal character|Hexadecimal character]]</td>
</tr>
</tr>
 
<tr>
<tr>
<td align="center">#</td>
<td align="center">#</td>
<td>Numeric digit character     </td>
<td>[[#Numeric digit character|Numeric digit character]]</td>
</tr>
</tr>
 
<tr>
<tr>
<td align="center">@</td>
<td align="center">@</td>
<td>Alphabetic character   </td>
<td>[[#Alphabetic character|Alphabetic character]]</td>
</tr>
</tr>
</table>
</table>
   
   
===Continuing use of the escape character===
<p>
<p>
The escape character, which is the exclamation point (<tt>!</tt>), allows a pattern matching character to be interpreted as a literal character rather than as a pattern character. The escape character affects only the next character</p>
Beginning in <var class="product">Model&nbsp;204</var> Version 5.1, all characters X'00' through X'FF' are valid in a pattern presented to the User Language pattern matcher and the $ChkPat function. </p>
<p>
As a result all characters are treated as valid, literal characters. The following error messages are no longer invoked for these characters.</p>
<p class="code">M204.1688: errortype IN PATTERN 'pattern' AT CHARACTER char
M204.1689: errortype IN PATTERN 'pattern' AT CHARACTER char
</p>
<p class="note"><b>Note:</b> Patterns containing embedded escape characters preceding special characters function as they did in earlier releases of <var class="product">Model&nbsp;204</var>.</p>
   
   
===Wildcard character===
====Wildcard character====
<p>
<p>
The wildcard character serves as a placeholder for 0 to 255 characters. You can use wildcard characters as follows:</p>
The wildcard character serves as a placeholder for 0 to 255 characters. You can use wildcard characters as follows:</p>
<ul>
<ul>
<li>Embed wildcard characters within a repeat pattern.</li>
<li>Embed wildcard characters within a repeat pattern.</li>
</li>
 
<li>Use a repeat character (<tt>/</tt>) immediately after a wildcard character.</li>
<li>Use a repeat character (<tt>/</tt>) immediately after a wildcard character.</li>
</li>
 
<li>Include wildcard characters, either alone or in combination with other characters, in a set. </li>
<li>Include wildcard characters, either alone or in combination with other characters, in a set. </li>
</li>
</ul>
</ul>
<p>
<p>
Line 909: Line 948:
<th>Sample matching values might be...</th>
<th>Sample matching values might be...</th>
</tr>
</tr>
<tr>
<tr>
<td>A*SON  </td>
<td>A*SON  </td>
Line 915: Line 955:
</table>
</table>
   
   
===Placeholder character===
====Placeholder character====
<p>
<p>
The placeholder character serves as a placeholder for one character only. For example:</p>
The placeholder character serves as a placeholder for one character only. For example:</p>
Line 923: Line 963:
<th>Sample matching values might be...</th>
<th>Sample matching values might be...</th>
</tr>
</tr>
<tr>
<tr>
<td> C+RY</td>
<td> C+RY</td>
<td>CARY CORY</td>
<td>CARY CORY</td>
</tr>
</tr>
<tr>
<tr>
<td>J+++SON</td>
<td>J+++SON</td>
Line 933: Line 975:
</table>
</table>
   
   
===Or character===
====<b id="patOr"></b>Or character====
<p>
<p>
The Or character (a comma) allows the choice of more than one matching value. The Or character also can be used to define a null (<tt>,,</tt>) character. The values separated by the Or character are called members of the pattern. For example:</p>
The Or character (a comma) allows the choice of more than one matching value. The Or character also can be used to define a null (<tt>,,</tt>) character. The values separated by the Or character are called members of the pattern. For example:</p>
Line 941: Line 983:
<th>Sample matching values might be...</th>
<th>Sample matching values might be...</th>
</tr>
</tr>
<tr>
<tr>
<td>JONES,J+++SON </td>
<td>JONES,J+++SON </td>
Line 946: Line 989:
</tr>
</tr>
</table>
</table>
<p class="note"><b>Note:</b> The following type of pattern is illegal: </p>
<blockquote class="note"><b>Note:</b> The following type of pattern is illegal:
<p class="code">/A,B(XYZ)
<!-- The following example is used in QASHR tests SOUL.DME.263.QAR: -->  
</p>
<p class="code">/A,B(XYZ) </p>
</blockquote>
===Set begin and set end characters===
 
====Set begin and set end characters====
<p>
<p>
The set begin and end characters allow for a segregation of choices. Set characters are required when a repeat character or range characters are used (see below). A comma (the Or character) is used to separate the choices. For example:</p>
The set begin and end characters allow for a segregation of choices. Set characters are required when a repeat character or range characters are used (see below). A comma (the Or character) is used to separate the choices. For example:</p>
Line 958: Line 1,002:
<th>Sample matching values might be...</th>
<th>Sample matching values might be...</th>
</tr>
</tr>
<tr>
<tr>
<td>(JACK,JOHN)SON</td>
<td>(JACK,JOHN)SON</td>
Line 963: Line 1,008:
</tr>
</tr>
</table>
</table>
<p class="note"><b>Note:</b> You can include additional set begin and set end characters in a repeat pattern. The following type of pattern is illegal:</p>
<blockquote class="note"><b>Note:</b> You can include additional set begin and set end characters in a repeat pattern. The following type of pattern is illegal:
<p class="code">/A,B(XYZ)
<p class="code">/A,B(XYZ)
</p>
</p>
</blockquote>
===Range character===
 
====Range character====
<p>
<p>
The range character allows a single EBCDIC character range (including unprintable characters) to be specified. The range must either be enclosed with set begin and end characters or be a set member with a comma preceding and/or following it. In addition, the ending range character must be greater than the beginning range character. For example:</p>
The range character allows a single EBCDIC character range (including unprintable characters) to be specified. The range must either be enclosed with set begin and end characters or be a set member with a comma preceding and/or following it. In addition, the ending range character must be greater than the beginning range character. For example:</p>
Line 975: Line 1,021:
<th>Sample matching values might be...</th>
<th>Sample matching values might be...</th>
</tr>
</tr>
<tr>
<tr>
<td>(0-9)</td>
<td>(0-9)</td>
<td>0 3 5 8 9</td>
<td>0 3 5 8 9</td>
</tr>
</tr>
<tr>
<tr>
<td>(A,N-T,X)*SON</td>
<td>(A,N-T,X)*SON</td>
Line 985: Line 1,033:
</table>
</table>
   
   
===Repeat character===
====Repeat character====
<p>
<p>
The repeat character lets you specify one or more repetitions of a set. The repeat character must be followed by a single number or a range of two numbers. The number must be between 0 and 255. The second number, if specified, must be greater than the first. In addition, a set must begin immediately following the repeat number(s). Supported uses for repeat characters include the following: </p>
The repeat character lets you specify one or more repetitions of a set. The repeat character must be followed by a single number or a range of two numbers. The number must be between 0 and 255. The second number, if specified, must be greater than the first. In addition, a set must begin immediately following the repeat number(s). Supported uses for repeat characters include the following: </p>
<ul>
<ul>
<li>Including more than one repeat character within a pattern.</li>
<li>Including more than one repeat character within a pattern.</li>
 
<li>Including wildcard characters within a repeat pattern.</li>
<li>Including wildcard characters within a repeat pattern.</li>
 
<li>Using a repeat character (<tt>/</tt>) immediately after a wildcard character.</li>
<li>Using a repeat character (<tt>/</tt>) immediately after a wildcard character.</li>
 
<li>Including additional set begin and set end characters in a repeat pattern.</li>
<li>Including additional set begin and set end characters in a repeat pattern.</li>
</ul>
</ul>
Line 1,004: Line 1,052:
<th>Sample matching values might be...</th>
<th>Sample matching values might be...</th>
</tr>
</tr>
 
<tr>
<tr>
<td>/3(COPY)</td>
<td>/3(COPY)</td>
<td>COPYCOPYCOPY</td>
<td>COPYCOPYCOPY</td>
</tr>
</tr>
 
<tr>
<tr>
<td>/1-2(COPY)</td>
<td>/1-2(COPY)</td>
<td>COPY COPYCOPY </td>
<td>COPY COPYCOPY </td>
</tr>
</tr>
 
<tr>
<tr>
<td>/2(0-9)</td>
<td>/2(0-9)</td>
<td>01 98 23</td>
<td>01 98 23</td>
</tr>
</tr>
 
<tr>
<tr>
<td>/1-4(+)</td>
<td>/1-4(+)</td>
Line 1,025: Line 1,073:
</tr>
</tr>
</table>
</table>
<p class="note"><b>Note:</b> The following type of pattern is illegal:</p>
<blockquote class="note"><b>Note:</b> The following type of pattern is illegal:
<p class="code">/A,B(XYZ)
<p class="code">/A,B(XYZ)
</p>
</p>
</blockquote>
===Escape character===
 
====Escape character====
<p>
<p>
The escape character (<tt>!</tt>) allows a character to be interpreted as a literal character rather than as a pattern matching character. The escape character affects only the next character and is needed when you want to treat as a valid literal character one of the eleven pattern matching characters: <code>* + ' ( ) - / ! = # @</code>.</p>
The escape character (<tt>!</tt>) allows a character to be interpreted as a literal character rather than as a pattern matching character. The escape character affects only the next character and is needed when you want to treat as a valid literal character one of the eleven pattern matching characters: <code>* + ' ( ) - / ! = # @</code>.</p>
Line 1,039: Line 1,088:
<th>Sample matching values</th>
<th>Sample matching values</th>
</tr>
</tr>
 
<tr>
<tr>
<td>
<td>
Line 1,046: Line 1,095:
<td>(800)-244-3344</td>
<td>(800)-244-3344</td>
</tr>
</tr>
 
<tr>
<tr>
<td>
<td>
Line 1,058: Line 1,107:
<p class="note"><b>Note:</b> Patterns containing embedded escape characters preceding special characters function as they did in earlier releases of <var class="product">Model&nbsp;204</var>.</p>
<p class="note"><b>Note:</b> Patterns containing embedded escape characters preceding special characters function as they did in earlier releases of <var class="product">Model&nbsp;204</var>.</p>
   
   
===Hexadecimal character===
====Hexadecimal character====
<p>
<p>
The hexadecimal character allows for the hexadecimal representation of one or a range of unprintable EBCDIC characters. The character must be followed by two digits (0-9,A-F) that represent the hexadecimal value of the character to be retrieved. For example:</p>
The hexadecimal character allows for the hexadecimal representation of one or a range of unprintable EBCDIC characters. The character must be followed by two digits (0-9,A-F) that represent the hexadecimal value of the character to be retrieved. For example:</p>
Line 1,066: Line 1,115:
<th>Sample matching values might be...</th>
<th>Sample matching values might be...</th>
</tr>
</tr>
<tr>
<tr>
<td>=6B,=4D</td>
<td>=6B,=4D</td>
<td>, (</td>
<td>, (</td>
</tr>
</tr>
<tr>
<tr>
<td>(=7C-=7F)</td>
<td>(=7C-=7F)</td>
Line 1,076: Line 1,127:
</table>
</table>
   
   
===Numeric digit character===
====Numeric digit character====
<p>
<p>
The numeric digit character allows a numeric range (0-9) to be specified. For example: </p>
The numeric digit character allows a numeric range (0-9) to be specified. For example: </p>
Line 1,084: Line 1,135:
<th>Sample matching values might be...</th>
<th>Sample matching values might be...</th>
</tr>
</tr>
 
<tr>
<tr>
<td>###</td>
<td>###</td>
<td>099 378 1111</td>
<td>099 378 1111</td>
</tr>
</tr>
 
<tr>
<tr>
<td>/1-4(#)</td>
<td>/1-4(#)</td>
Line 1,097: Line 1,148:
<p class="note"><b>Note:</b> If the value of the <var>[[FLUSH parameter|FLUSH]]</var> parameter is <code>#</code>, <var>FLUSH</var> must be reset before the numeric digit pattern character can be used during line editing. </p>
<p class="note"><b>Note:</b> If the value of the <var>[[FLUSH parameter|FLUSH]]</var> parameter is <code>#</code>, <var>FLUSH</var> must be reset before the numeric digit pattern character can be used during line editing. </p>
   
   
===Alphabetic character===
====Alphabetic character====
<p>
<p>
The alphabetic character allows an alphabetic range (a-z, A-Z) to be specified. Unprintable characters are not included. For example:</p>
The alphabetic character allows an alphabetic range (a-z, A-Z) to be specified. Unprintable characters are not included. For example:</p>
Line 1,105: Line 1,156:
<th>Sample matching values might be...</th>
<th>Sample matching values might be...</th>
</tr>
</tr>
 
<tr>
<tr>
<td>@@@@@</td>
<td>@@@@@</td>
<td>JONES SARAH Frand</td>
<td>JONES SARAH Frand</td>
</tr>
</tr>
 
<tr>
<tr>
<td>/2-5(@)</td>
<td>/2-5(@)</td>
Line 1,116: Line 1,167:
</tr>
</tr>
</table>
</table>
<p class="note"><b>Note:</b> If the value of the <var>[[ERASE parameter|ERASE]]</var> parameter is <code>@</code>, <var>ERASE</var> must be reset before the alphabetic pattern character can be used during line editing. </p>


<p class="note"><b>Note:</b> If the value of the <var>[[ERASE parameter|ERASE]]</var> parameter is <code>@</code>, <var>ERASE</var> must be reset before the alphabetic pattern character can be used during line editing. </p>
===Pattern retrieval optimization===
===Pattern retrieval optimization===
<p>
<p>
Line 1,150: Line 1,201:
<p class="code">M204.2864: FULL SEARCH OF ORDERED INDEX PERFORMED
<p class="code">M204.2864: FULL SEARCH OF ORDERED INDEX PERFORMED
</p>
</p>
<p class="note"><b>Note:</b> Since message M204.2864 is issued at pattern evaluation time and will be issued each time the pattern is evaluated for each segment of the file, you may wish to remove the overhead of issuing the message entirely. Issue the following command to minimize the amount of CPU used:</p>
<blockquote class="note"><b>Note:</b> Since message M204.2864 is issued at pattern evaluation time and will be issued each time the pattern is evaluated for each segment of the file, you may want to remove the overhead of issuing the message entirely. Issue the following command to minimize the amount of CPU used:
<p class="code">MSGCTL M204.2864 NOACTION
<p class="code">MSGCTL M204.2864 NOACTION
</p>
</p>
</blockquote >
<b>Examples</b>
<b>Examples</b>
<p>
<p>
Line 1,174: Line 1,226:
<p>
<p>
Pattern retrieval performance also is affected by the complexity of the pattern specified. The more complex the pattern, the more processing is required.</p>
Pattern retrieval performance also is affected by the complexity of the pattern specified. The more complex the pattern, the more processing is required.</p>
 
===Use of full key for pattern matching===
===Use of full key for pattern matching===
<p>
<p>
Line 1,183: Line 1,235:
</p>
</p>
<p>
<p>
The pattern matcher now uses the full key <code>COMPANY01DEPT05ACCT02</code> to search for the initial found set, then sees what fields in the found set match the pattern selection criteria. Performance is improved by restricting how much of the Ordered Index is searched to acquire the initial found set. </p>
The pattern matcher uses the full key <code>COMPANY01DEPT05ACCT02</code> to search for the initial found set, then sees what fields in the found set match the pattern selection criteria. Performance is improved by restricting how much of the Ordered Index is searched to acquire the initial found set. </p>
<p>
Prior to Version 3.2, the pattern matcher would truncate <code>COMPANY01DEPT05ACCT02</code> to <code>COMPANY01*</code> for searching the Ordered Index for the initial found set. If <code>COMPANY01</code> is the entire corporation, but only accounts (<code>02</code>) from one department (<code>05</code>) are wanted, the found set would have included the entire corporation. The pattern matcher would then look at the found set and return only those records whose fields actually matched the pattern selection criteria.</p>


==Using expressions in FIND statements==
==Using expressions in FIND statements==
===EQ VALUE retrieval condition===
===EQ VALUE retrieval condition===
<b>Purpose</b>  
<b>Purpose</b>  
Line 1,193: Line 1,244:
<b>Syntax</b>
<b>Syntax</b>
<p>The expression is enclosed in parentheses after the EQ VALUE clause.</p>
<p>The expression is enclosed in parentheses after the EQ VALUE clause.</p>
<p class="syntax">
<p class="syntax">FIELDNAME EQ VALUE (<span class="term">expression</span>)
FIELDNAME EQ VALUE (<span class="term">expression</span>)
</p>
</p>
<b>Where</b>
Where:
<p>Any expression can be used with the EQ VALUE clause.</p>
<p>Any expression can be used with the EQ VALUE clause.</p>
<b>Example</b>
<b>Example</b>
<p class="code">
<p class="code">FD: IN FILE PEOPLE FD
FD: IN FILE PEOPLE FD
LAST EQ VALUE($READ('LAST NAME?'))
LAST EQ VALUE($READ('LAST NAME?'))
END FIND
END FIND
Line 1,206: Line 1,255:


===EQ WITH retrieval condition for concatenated fields===
===EQ WITH retrieval condition for concatenated fields===
The EQ WITH clause is available as of Model 204 version 7.5. To use the EQ WITH clause, set the <var>[[FILEORG parameter|FILEORG]]</var> X'100' bit.
<b>Purpose</b>  
<b>Purpose</b>  
<p>The EQ WITH clause retrieves CONCATENATION-OF fields. Model 204 automatically builds the concatenated value.</p>
<p>The EQ WITH clause retrieves CONCATENATION-OF fields. Model 204 automatically builds the concatenated value.</p>
<b>Syntax</b>  
<b>Syntax</b>  
<p>The FIND statement for retrieving data based on concatenated fields is:</p>
<p>The FIND statement for retrieving data based on concatenated fields is:</p>
<p class="code">
<p class="code">label: FIND ALL RECORDS
label: FIND ALL RECORDS
        CONCAT_FIELD EQ WITH ('field1 value',...,'field8 value')
        CONCAT_FIELD EQ WITH (’field1 value’,...,’ -
field8 value’)
</p>
</p>
<b>Usage</b>  
<b>Usage</b>  
<p>Although you must specify a minimum of two fields in the WITH clause, you can concatenate as many as eight. The number specified must match the
<p>
Although you must specify a minimum of two fields in the WITH clause, you can concatenate as many as eight. The number specified must match the
concatenated field contents. For example, if the field is composed of four fields, the retrieval condition must include four fields. Otherwise, the following error
concatenated field contents. For example, if the field is composed of four fields, the retrieval condition must include four fields. Otherwise, the following error
message is produced:</p>
message is produced:</p>
<p class="code">
<p class="code">M204.2877: WITH CLAUSE INVALID: CONCATENATION-OF FIELD
M204.2877: WITH CLAUSE INVALID: CONCATENATION-OF FIELD
HAS WRONG NUMBER OF COMPONENT FIELDS
HAS WRONG NUMBER OF COMPONENT FIELDS
</p>
</p>
Consider an example of a concatenated field FULL_NAME:
Consider an example of a concatenated field FULL_NAME:
<p class="code">
<p class="code">DEFINE FULL_NAME WITH CONCATENATION-OF FIRST WITH -
DEFINE FULL_NAME WITH CONCATENATION-OF FIRST WITH -
MIDDLE WITH LAST AND ORD SEP C'$'
MIDDLE WITH LAST AND ORD SEP C'$'
</p>
</p>


and a sample PAI of a record:
and a sample PAI of a record:
<p class="code">
<p class="code">FIRST = SAM
FIRST = SAM
MIDDLE = Q
MIDDLE = Q
LAST = SMITH
LAST = SMITH
Line 1,237: Line 1,284:
</p>
</p>
The record could be retrieved with:
The record could be retrieved with:
<p class="code">
<p class="code">FD: IN PEOPLE FD
FD: IN PEOPLE FD
    FULL_NAME = 'SAM$Q$SMITH'
      FULL_NAME = 'SAM$Q$SMITH'
END FIND
END FIND
</p>
</p>
Line 1,247: Line 1,293:
Alternatively, each part of the concatenated field could be used with the new EQ WITH syntax that does not require knowledge of the separator character:
Alternatively, each part of the concatenated field could be used with the new EQ WITH syntax that does not require knowledge of the separator character:


<p class="code">
<p class="code">FD: IN PEOPLE FD
FD: IN PEOPLE FD
    FULL_NAME EQ WITH ('SAM','Q','SMITH')
FULL_NAME EQ WITH ('SAM','Q','SMITH')
END FIND
END FIND
</p>
</p>


The values specified in the EQ WITH clause can be the results of expressions:
The values specified in the EQ WITH clause can be the results of expressions:
<p class="code">
<p class="code">%PARENTS_NAME = '...SMITH'
%PARENTS_NAME = '...SMITH'
FD: IN PEOPLE FD
FD: IN PEOPLE FD
FULL_NAME EQ WITH ('SAM','Q',$SUBSTR(%PARENTS_NAME,4))
    FULL_NAME EQ WITH ('SAM','Q',$SUBSTR(%PARENTS_NAME,4))
END FIND
END FIND
</p>
</p>


===Limitations of CONCATENATION-OF fields===
===Limitations of CONCATENATION-OF fields===
====Limitations when using SEPARATOR NONE====
====Limitations when using SEPARATOR NONE====
If you use SEPARATOR NONE in the CONCATENATION-OF field definition, your find set results may not be what you wish. For example:
If you use SEPARATOR NONE in the CONCATENATION-OF field definition, your find set results might not be what you wanted.  
<p class="code">
 
FD: IN FILE PITCHERS FD
For example:
      FOOBAR EQ WITH(’ABC’, ’XYZ’)
<p class="code">FD: IN FILE PITCHERS FD
    FOOBAR EQ WITH(&#x2019;ABC&#x2019;, &#x2019;XYZ&#x2019;)
END FIND
END FIND
</p>
</p>
Would match a record with:
Would match a record with:
<p class="code">
<p class="code">FOO = AB
FOO = AB
BAR = CXYZ
BAR = CXYZ
</p>
</p>
as well as records with
as well as records with
<p class="code">
<p class="code">FOO = ABC
FOO = ABC
BAR = XYZ
BAR = XYZ
</p>
</p>
====Limitations when using ESCAPE CANCEL====
====Limitations when using ESCAPE CANCEL====
If you define ESCAPE CANCEL on the CONCATENATION-OF field declaration, an attempt to store a field value with the separator character in a
If you define ESCAPE CANCEL on the CONCATENATION-OF field declaration, an attempt to store a field value with the separator character in a
Line 1,284: Line 1,329:


For example, if FULL_NAME is defined with
For example, if FULL_NAME is defined with
<p class="code">
<p class="code">(ORD CAT FIRST WITH MIDDLE WITH LAST SEP C'$' ESC CANCEL)
(ORD CAT FIRST WITH MIDDLE WITH LAST SEP C'$' ESC CANCEL)
</p>
</p>
Then the following request will cancel:
Then the following request will cancel:
<p class="code"><nowiki>
<p class="code"><nowiki>BEGIN
BEGIN
IN PEOPLE STORE RECORD
IN PEOPLE STORE RECORD
    FIRST='SAM'
  FIRST='SAM'
    MIDDLE='Q$'
  MIDDLE='Q$'
    LAST='SMITH'
  LAST='SMITH'
END STORE
END STORE
END
END
Line 1,300: Line 1,343:
CHARACTER
CHARACTER
</nowiki></p>
</nowiki></p>
====Limitations when using EQ WITH and file groups====
====Limitations when using EQ WITH and file groups====
An explicit CONCATENATION-OF field must be a CONCATENATION-OF field in any file where it is defined, although it does not have to be defined in every
An explicit CONCATENATION-OF field must be a CONCATENATION-OF field in any file where it is defined, although it does not have to be defined in every
Line 1,306: Line 1,350:


Defined FULL_NAME in file PEOPLE with component fields FIRST, MIDDLE, and LAST and $ for the separator character:
Defined FULL_NAME in file PEOPLE with component fields FIRST, MIDDLE, and LAST and $ for the separator character:
<p class="code">
<p class="code">IN PEOPLE DEFINE FULL_NAME WITH CONCATENATION-OF -
IN PEOPLE DEFINE FULL_NAME WITH CONCATENATION-OF -
FIRST WITH MIDDLE WITH LAST AND ORD SEP C'$' ESC CANCEL
FIRST WITH MIDDLE WITH LAST AND ORD SEP C'$' ESC CANCEL
</p>
</p>
Defined FULL_NAME in file PEOPLE2 with component fields FIRST_NAME, MIDDLE_INITIAL, and LAST_NAME and the asciicircum, (^) for the separator
Defined FULL_NAME in file PEOPLE2 with component fields FIRST_NAME, MIDDLE_INITIAL, and LAST_NAME and the asciicircum, (^) for the separator
character:
character:
<p class="code">
<p class="code">IN PEOPLE2 DEFINE FULL_NAME WITH CONCATENATION-OF -
IN PEOPLE2 DEFINE FULL_NAME WITH CONCATENATION-OF -
  FIRST_NAME WITH MIDDLE_INITIAL WITH LAST_NAME AND -
  FIRST_NAME WITH MIDDLE_INITIAL WITH LAST_NAME AND -
ORD SEP C'^' ESC CANCEL
ORD SEP C'^' ESC CANCEL
Line 1,319: Line 1,361:
Without the EQ WITH retrieval condition, in order to find SAM Q SMITH in both files, the FIND would require two separate strings and knowledge of the
Without the EQ WITH retrieval condition, in order to find SAM Q SMITH in both files, the FIND would require two separate strings and knowledge of the
separation character:
separation character:
<p class="code">
<p class="code">FD: IN PEOPLE, PEOPLE2 FD
FD: IN PEOPLE, PEOPLE2 FD
    FULL_NAME = 'SAM$Q$SMITH' OR 'SAM^Q^SMITH'
FULL_NAME = 'SAM$Q$SMITH' OR 'SAM^Q^SMITH'
END FIND
END FIND
</p>
</p>


Using the EQ WITH allows the much simpler syntax of:
Using the EQ WITH allows the much simpler syntax of:
<p class="code">
<p class="code">FD: IN PEOPLE, PEOPLE2 FD
FD: IN PEOPLE, PEOPLE2 FD
FULL_NAME EQ WITH ('SAM','Q','SMITH')
FULL_NAME EQ WITH ('SAM','Q','SMITH')
END FIND
END FIND
</p>
</p>
====Miscellaneous limitations when using EQ WITH====
====Miscellaneous limitations when using EQ WITH====
If an EQ WITH clause produces a value longer than 255 bytes, it truncates the value.
If an EQ WITH clause produces a value longer than 255 bytes, it truncates the value.


The EQ WITH clause usually issues any errors at compile time. For example, if you issue the following in file context:
The EQ WITH clause usually issues any errors at compile time. For example, if you issue the following in file context:
<p class="code">
<p class="code">FD: IN PEOPLE FD
FD: IN PEOPLE FD
    FULL_NAME EQ WITH ('SAM','Q')
      FULL_NAME EQ WITH ('SAM','Q')
END FIND
END FIND
</p>
</p>
Line 1,343: Line 1,383:


In contrast, if you use a field name variable in your query:
In contrast, if you use a field name variable in your query:
<p class="code">
<p class="code">%FIELD = 'FULL_NAME'
%FIELD = 'FULL_NAME'
FD: IN PEOPLE FD
FD: IN PEOPLE FD
      %%FIELD EQ WITH ('SAM','SMITH')
    %%FIELD EQ WITH ('SAM','SMITH')
END FIND
END FIND
</p>
</p>


This type of error would only be detected at evaluation time and the request would be canceled.
This type of error would only be detected at evaluation time and the request would be canceled.


==Transporting Large Object data using the Universal Buffer==
==Transporting Large Object data using the Universal Buffer==
<p>
<p>
The Universal Buffer is used to transport Large Object data to and from a client. The following is a sample request that moves Large Object data from a client to the <var class="product">Model&nbsp;204</var> server.</p>
The Universal Buffer is used to transport [[Large Object fields#BLOB.2C CLOB.2C and MINLOBE attributes|Large Object data]] to and from a client. The Universal Buffer is a one-per-user, temporary storage area that automatically expands to accommodate its data contents.
 
The following is a sample request that moves Large Object data from a client to the <var class="product">Model&nbsp;204</var> server.</p>
<ul>
<ul>
<li>Transporting data from a client to the <var class="product">Model&nbsp;204</var> server:
<li>Transporting data from a client to the <var class="product">Model&nbsp;204</var> server:
Line 1,370: Line 1,410:
END FOR
END FOR
END
END
</p>
</p> </li>


<li>Transporting Large Object data from the server to a client application:
<li>Transporting Large Object data from the server to a client application:
Line 1,382: Line 1,422:
END
END
/* A statement to send the BUFFER to a client*/
/* A statement to send the BUFFER to a client*/
</p>
</p> </li>
</ul>
</ul>


<p class="note"><b>Note:</b> Lob-name <code>NOVEL</code> in the first of these examples has a position and length parameter, which you must supply. You can extract a section of the Large Object field by supplying a position and a length within the data. If you want all the data, position is set to 1 and length is the actual size of the Large Object data obtained using <var>[[$LOBLEN]]</var>.</p>
<p class="note"><b>Note:</b> Lob-name <code>NOVEL</code> in the first of these examples has a position and length parameter, which you must supply. You can extract a section of the Large Object field by supplying a position and a length within the data. If you want all the data, position is set to 1 and length is the actual size of the Large Object data obtained using <var>[[$LobLen]]</var>.</p>


==IS PRESENT condition==
==IS PRESENT condition==
Line 1,398: Line 1,438:
===Performance considerations===
===Performance considerations===
<p>
<p>
A retrieval using the IS PRESENT condition is performed by directly searching the data records (Table B). To increase the efficiency of the search, the IS PRESENT condition should be preceded by a condition that uses a field with the KEY attribute.                   </p>
A retrieval using the <var>IS PRESENT</var> condition is performed by directly searching the data records (Table B). To increase the efficiency of the search, the <var>IS PRESENT</var> condition should be preceded by a condition that uses a field with the <var>KEY</var> attribute. </p>
<p>
<p>
For example, the following statement locates all records that contain one or more occurrences of the INCIDENT field. Because the RECTYPE field has the KEY attribute, the Table B search initiated by the IS PRESENT condition is performed only on the records retrieved by RECTYPE = DRIVER.</p>
For example, the following statement locates all records that contain one or more occurrences of the <code>INCIDENT</code> field. Because the <code>RECTYPE</code> field has the <var>KEY</var> attribute, the Table B search initiated by the <var>IS PRESENT</var> condition is performed only on the records retrieved by <code>RECTYPE = DRIVER</code>.</p>
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
            RECTYPE = DRIVER
            INCIDENT IS PRESENT
END FIND </p>
   
   
===IS NOT PRESENT condition===
===IS NOT PRESENT condition===
<p>
<p>
The condition IS NOT PRESENT retrieves only records in which the field is missing. For example, this statement locates all records that do not contain an INCIDENT field:</p>
The condition <var>IS NOT PRESENT</var> retrieves only records in which the field is missing. For example, this statement locates all records that do not contain an <code>INCIDENT</code> field:</p>
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
               RECTYPE = DRIVER
               RECTYPE = DRIVER
Line 1,411: Line 1,455:
</p>
</p>


==FIND$ condition==
==Find$ condition==
<p>
<p>
The FIND$ condition performs a FIND on a set of records that has been retrieved by a previous FIND statement. </p>
The <var>Find$</var> condition performs a <var>FIND</var> on a set of records that has been retrieved by a previous <var>FIND</var> statement. </p>
<b>Syntax</b>
<b>Syntax</b>
<p>
<p>
The format of FIND$ is:</p>
The format of <var>Find$</var> is:
<p class="syntax"><span class="literal">FIND$</span> <span class="term">label</span>
</p>
<p class="syntax"><span class="literal">Find$</span> <span class="term">label</span>
</p>
</p>
   
   
===When to use FIND$===
===When to use Find$===
<p>
<p>
The FIND$ condition is typically used when you want to AND, NOT, or OR a condition with an already found set. FIND$ also can be used when some of the records, fields, or values in the original found set have been deleted and you wish to refer to the set containing the deletions.</p>
The <var>Find$</var> condition is typically used when you want to <var>And</var>, <var>Not</var>, or <var>Or</var> a condition with an already found set. <var>Find$</var> also can be used when some of the records, fields, or values in the original found set have been deleted and you wish to refer to the set containing the deletions.</p>
<p>
<p>
For example:</p>
For example:</p>
Line 1,448: Line 1,493:
</p>
</p>
<p>
<p>
The GROUP1A statements retrieves records for SEX = M, DATE OF BIRTH between 200101 and 650101, and STATE = CALIFORNIA. The GROUP2A statements retrieves records for which SEX = MALE and either STATE not equal CALIFORNIA or DATE OF BIRTH not between 200101 and 650101.   </p>
The <code>GROUP1A</code> statements retrieves records for <code>SEX=M</code>, <code>DATE OF BIRTH</code> between 19200101 and 19650101, and <code>STATE=CALIFORNIA</code>. The <code>GROUP2A</code> statements retrieves records for which <code>SEX=MALE</code> and either <code>STATE</code> not equal <code>CALIFORNIA</code>, or <code>DATE OF BIRTH</code> not between 19200101 and 19650101. </p>


==SFL$ and SFGE$ conditions==
==Sfl$ and Sfge$ conditions==
   
   
===The SFL$ condition===
===The Sfl$ condition===
<p>
<p>
For sorted files, the condition:       </p>
For sorted files, the following condition retrieves all records for which the sort field is less than the stated value: </p>
<p class="code">SFL$ value
<p class="code">Sfl$ <i>value</i>
</p>
</p>
<p>
retrieves all records for which the sort field is less than the stated value.</p>
   
   
===The SFGE$ condition===
===The Sfge$ condition===
<p>
<p>
For sorted files, the condition:     </p>
For sorted files, the following condition retrieves all records for which the sort field is greater than or equal to the stated value: </p>
<p class="code">SFGE$ value
<p class="code">Sfge$ <i>value</i>
</p>
</p>
<p>
retrieves all records for which the sort field is greater than or equal to the stated value.</p>
   
   
===Example using SFL$ and SFGE$===
===Example using Sfl$ and Sfge$===
<p>
<p>
In a sorted file in which the sort field is FULLNAME, this statement:</p>
In a sorted file in which the sort field is <code>FULLNAME</code>, this statement retrieves all records of people who live in San Diego and whose full name starts with <code>R:</p>
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
               CITY = SAN DIEGO
               CITY = SAN DI</code>EGO
               SFGE$ R AND SFL$ RZZZ
               SFGE$ R AND SFL$ RZZZ
           END FIND
           END FIND
</p>
</p>
<p>
retrieves all records of people who live in San Diego and whose full name starts with R.                </p>
   
   
==POINT$ condition==
==Point$ condition==
<p>
<p>
When a record is stored it is assigned an internal record number. In a FIND statement, the POINT$ condition retrieves all records that have internal record numbers greater than or equal to the stated value. If the given value is not a number, no records are retrieved.</p>
When a record is stored it is assigned an internal record number. In a <var>Find</var> statement, the <var>Point$</var> condition retrieves all records that have internal record numbers greater than or equal to the stated value. If the given value is not a number, no records are retrieved.</p>
===When to use POINT$===
   
   
===When to use Point$===
<p>
<p>
The POINT$ condition should be used when ranges of record numbers must be retrieved. If individual record numbers need to be retrieved, the FOR RECORD NUMBER statement should be used. See [[Record Loops#Record Loops|Record Loops]] for a detailed discussion of the FOR RECORD NUMBER statement.  </p>
The <var>Point$</var> condition should be used when ranges of record numbers must be retrieved. If individual record numbers need to be retrieved, the <var>For Record Number</var> statement should be used. See [[Record loops]] for a detailed discussion of the <var>For Record Number</var> statement.  </p>
   
   
<b>Syntax</b>
<b>Syntax</b>
<p>
<p>
The format of POINT$ is:</p>
The format of <var>Point$</var> is:</p>
   
   
<p class="syntax"><span class="literal">POINT$</span> <span class="term">n</span>
<p class="syntax"><span class="literal">Point$</span> <span class="term">n</span>
</p>
</p>
   
   
<b>Example</b>
<b>Example</b>
<p>
<p>
This statement:</p>
This statement retrieves all records that have internal record numbers between 1500 and 2499, inclusive:</p>
   
   
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
Line 1,504: Line 1,542:
</p>
</p>
   
   
==File$ condition==
<p>
<p>
retrieves all records that have internal record numbers between 1500 and 2499, inclusive.</p>
The <var>File$</var> condition can be used as a condition in a <var>Find</var> statement to restrict the files from which records are selected. </p>
 
==FILE$ condition==
<p>
<p>
The restriction can apply to a group <var>Find</var> or to a <var>Find</var> that refers to the record set of an earlier group <var>Find</var>. <var>File$</var> can also be used to limit the reference context of a <var>Find</var> statement when using Parallel Query Option/204. See [[Files, groups, and reference context]].</p>
The FILE$ condition can be used as a condition in a FIND statement to restrict the files from which records are selected. </p>
<p>
The restriction can apply to a group FIND or to a FIND that refers to the record set of an earlier group FIND. FILE$ can also be used to limit the reference context of a FIND statement when using Parallel Query Option/204. See [[Files, Groups, and Reference Context#Files, Groups, and Reference Context|Files, Groups, and Reference Context]].</p>
   
   
<b>Syntax</b>
<b>Syntax</b>
<p>
<p>
The format of FILE$ is:</p>
The format of <var>File$</var> is:</p>
<p class="syntax"><span class="literal">FILE$</span> <span class="squareb">{</span><span class="term">filename</span> <span class="squareb">|</span> <span class="literal">=</span><span class="squareb">}</span>
<p class="syntax"><span class="literal">File$</span> <span class="squareb">{</span><span class="term">filename</span> <span class="squareb">|</span> <span class="literal">=</span><span class="squareb">}</span>
</p>
</p>
   
   
<b>Where</b>
Where:
<ul>
<ul>
<li><var class="term">filename</var> is one of the following:
<li><var class="term">filename</var> is one of the following:
Line 1,531: Line 1,562:
<li>A literal remote file specification or file synonym</li>
<li>A literal remote file specification or file synonym</li>
</ul>
</ul>
<p>
<p>
<var class="term">filename</var> cannot be represented by:</p>
<var class="term">filename</var> cannot be represented by:</p>
Line 1,538: Line 1,568:
<li>A %variable</li>
<li>A %variable</li>
   
   
<li>A remote file specification enclosed entirely in quotes (for example, 'CLIENTS AT BOSTON')</li>
<li>A remote file specification enclosed entirely in quotes (for example, <code>'CLIENTS AT BOSTON'</code>)</li>
</ul>
</ul></li>
   
   
<li>The equal sign (<tt>=</tt>) restricts the search to the node on which the request is running (the local node). The equal sign cannot be used in a FIND statement that uses a complex FILE$ clause (for example, <code>FILE$ CLAIMS89 OR FILE$ CLAIMS90</code>).</li>
<li>The equal sign (<tt>=</tt>) restricts the search to the node on which the request is running (the local node). The equal sign cannot be used in a FIND statement that uses a complex <var>File$</var> clause (for example, <code>File$ CLAIMS89 Or File$ CLAIMS90</code>).</li>
</ul>
</ul>
   
   
<p>
<p>
The FILE$ condition is valid only in group context, and is supported in scattered group context.</p>
The <var>File$</var> condition is valid only in group context, and it is supported in scattered group context.</p>
<b>Example</b>
   
   
<b>Example</b>
<p>
<p>
If a group consisting of the CLIENTS and CLAIMS90 files is open, the following statement retrieves records from CLIENTS but not CLAIMS90:</p>
If a group consisting of the <code>CLIENTS</code> and <code>CLAIMS90</code> files is open, the following statement retrieves records from <code>CLIENTS</code> but not <code>CLAIMS90</code>:</p>
   
   
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
Line 1,558: Line 1,587:
</p>
</p>


==LOCATION$ condition==
==Location$ condition==
<p>
<p>
The LOCATION$ condition is valid only when you are using Parallel Query Option/204. It is used to restrict the results of a group context FIND statement to a particular node or nodes. </p>
The <var>Location$</var> condition is valid only when you are using Parallel Query Option/204. It is used to restrict the results of a group context <var>Find</var> statement to a particular node or nodes. </p>
<b>Syntax</b>
<b>Syntax</b>
<p>
<p>
The format of LOCATION$ is:</p>
The format of <var>Location$</var> is:</p>
<p class="syntax"><span class="literal">LOCATION$</span> <span class="squareb">{</span><span class="term">location</span> <span class="squareb">|</span> <span class="literal">=</span><span class="squareb">}</span>
<p class="syntax"><span class="literal">Location$</span> <span class="squareb">{</span><span class="term">location</span> <span class="squareb">|</span> <span class="literal">=</span><span class="squareb">}</span>
</p>
</p>
<b>Where</b>
Where:
<ul>
<ul>
<li><var class="term">location</var> is the node name used in file synonyms and remote file specifications. <var class="term">location</var> can be coded as a literal location name, or accepted as input in response to a dummy string prompt. <var class="term">location</var> cannot be represented by a %variable.</li>
<li><var class="term">location</var> is the node name used in file synonyms and remote file specifications. <var class="term">location</var> can be coded as a literal location name, or accepted as input in response to a dummy string prompt. <var class="term">location</var> cannot be represented by a %variable.</li>


<li>The equal sign (<tt>=</tt>) indicates the node on which the request is running (the local node).</li>
<li>The equal sign (<tt>=</tt>) indicates the node on which the request is running (the local node).</li>
</ul>
</ul>
<p>
<p>
The LOCATION$ condition is supported in scattered group context, and is valid only in group context.</p>
The <var>Location$</var> condition is supported in scattered group context, and it is valid only in group context.</p>
<p>
<p>
If an optional member of a scattered group is unavailable, no records are found for that file and processing continues.</p>
If an optional member of a scattered group is unavailable, no records are found for that file and processing continues.
</p>
<b>Examples</b>
<b>Examples</b>
<p>
<p>
Suppose you have opened a scattered group consisting of the files CLIENTS AT BOSTON, CLIENTS AT WASHINGTON, and CLAIMS90 AT BOSTON. The following FIND statement retrieves records from CLIENTS AT BOSTON but not CLIENTS AT WASHINGTON:</p>
Suppose you have opened a scattered group consisting of the files <code>CLIENTS AT BOSTON</code>, <code>CLIENTS AT WASHINGTON</code>, and <code>CLAIMS90 AT BOSTON</code>. The following <var>Find</var> statement retrieves records from <code>CLIENTS AT BOSTON</code> but not from <code>CLIENTS AT WASHINGTON</code>:</p>
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
               FILE$ CLIENTS
               FILE$ CLIENTS
Line 1,587: Line 1,616:
</p>
</p>
<p>
<p>
You can use the LOCATION$ condition to specify, in a single FIND statement, multiple files which can be located at different nodes. The following FIND statement retrieves records from locations NORTHEAST and SOUTHWEST:</p>
You can use the <var>Location$</var> condition to specify, in a single <var>Find</var> statement, multiple files which can be located at different nodes. The following <var>Find</var> statement retrieves records from locations <code>NORTHEAST</code> and <code>SOUTHWEST</code>: </p>
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
               FILE$ VEHICLES
               FILE$ VEHICLES
Line 1,595: Line 1,624:
</p>
</p>


==LIST$ condition==
==List$ condition==
<p>
<p>
You can refer to the collection of records on a list by entering the LIST$ condition as a condition in a FIND statement. See [[Lists]] for a detailed discussion of lists and the LIST$ condition. </p>
You can refer to the collection of records on a list by entering the <var>List$</var> condition as a condition in a <var>Find</var> statement. See [[Lists]] for a detailed discussion of lists and the <var>List$</var> condition. </p>


==IN label retrieval==
==IN label retrieval==
<p>
<p>
The IN label clause with the FIND statement can be used to refer to a set of records that has been retrieved by a previous FIND statement.</p>
The <var>In <i>label</i></var> clause with the <var>Find</var> statement can be used to refer to a set of records that has been retrieved by a previous <var>Find</var> statement.
</p>
<b>Syntax</b>
<b>Syntax</b>
<p>
<p>
The format for the IN label clause used with a FIND statement is:</p>
The format for the <var>In <i>label</i></var> clause used with a <var>Find</var> statement is:</p>
<p class="syntax"><span class="term">label.b</span><span class="literal">: FIND ALL RECORDS IN</span> <span class="term">label.a</span> <span class="literal">FOR WHICH</span>
<p class="syntax"><span class="term">label.b</span><span class="literal">: FIND ALL RECORDS IN</span> <span class="term">label.a</span> <span class="literal">FOR WHICH</span>
   <span class="term">retrieval conditions</span>
   <span class="term">retrieval conditions</span>
</p>
</p>
<p>
<p>
where <var class="term">label.a</var> is the label of a preceding FIND statement.</p>
where <var class="term">label.a</var> is the label of a preceding <var>Find</var> statement.
</p>
<b>Example</b>
<b>Example</b>
<p class="code">BEGIN
<p class="code">BEGIN
Line 1,628: Line 1,659:
</p>
</p>
<p>
<p>
In this request, the FIND statement labeled DATE finds only the records in the previous found set that also contain an incident date later than December 31, 1990. </p>
In this request, the <var>Find</var> statement labeled <code>DATE</code> finds only the records in the previous found set that also contain an incident date later than December 31, 1990. </p>
 
</div> <!-- end of toc limit div -->


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

Latest revision as of 19:32, 11 May 2018

Overview

Basic SOUL statements and commands introduced basic FIND statements that could be used for record retrieval. This page discusses more advanced ways to locate records.

Reference context

The FIND statement is supported in remote file and scattered group contexts. See Files, groups, and reference context for additional information on the reference context.

Retrieval conditions

In addition to retrieval conditions that perform searches based on a specified value, User Language supports more complex retrieval conditions that allow more than one value to be used as a criterion for selecting records.

This page discusses the following types of record retrievals:

Record retrieval type Locates records...
Range With field values within a specified numeric or character value range.
Pattern With field values matching a specified pattern (for example, a c* pattern searches for all patterns beginning with the letter "c").
IS PRESENT Based on whether or not the specified field exists in a record.
FIND$ From a found or deleted set of records.
IN label From a found set of records.
SFL$ and SFGE$ In sorted files.
POINT$ Based on internal record number.
LIST$ From a list. Lists discusses lists and the LIST$ condition in detail.
IN MEMBER and FILE$ In file groups.
LOCATION$ Restricted to a retrieval in scattered group context to a specified node or nodes.

Handling a foundset

When a FIND statement refers back to a previous foundset and the foundset is empty, then the loop for the FIND statement is skipped. Performance for the FIND is enhanced because no retrieval is performed for the criteria specified in the FIND statement. Each FIND statement, however, for this particular case, requires one more word (four bytes) of QTBL space.

Numeric range retrievals

When a field contains mostly numerical values, the magnitude of the numbers can be tested by using this statement:

Syntax

FIND ALL RECORDS FOR WHICH fieldname IS [NUMERICALLY] [operator] value

Where:

  • The NUMERICALLY keyword is optional and specifies that a numeric range retrieval should be performed. If this keyword is omitted, the type of retrieval performed is based upon the default type of the operator. For more information about the default type for each operator, refer to Interpretation of values in retrievals.
  • operator is one of the range retrieval operators in Range retrieval operators.

Range retrieval operators

You can use the range retrieval operators described in the following table for numeric retrievals.

IS operator Requires the field value to be...
EQ
=
Equal to the value specified.
NE
¬=
Unequal to the value specified.
LESS THAN
LT
<
Less than the value specified.
LE
<=
Less than or equal to the value specified.
GREATER THAN
GT
>
Greater than the value specified.
GE
>=
Greater than or equal to the value specified.
BETWEEN value1 AND value2 Between value1 and value2.

If the field value is equal to value1 or value2, the record is not retrieved.

If value1 is greater than value2, no records are retrieved.

Numeric retrieval examples

FIND ALL RECORDS FOR WHICH SETTLEMENT DATE IS 20030305 END FIND FIND ALL RECORDS FOR WHICH SETTLEMENT DATE IS NUMERICALLY < 20030305 END FIND FIND ALL RECORDS FOR WHICH SETTLEMENT DATE IS BETWEEN 20030300 AND 20030332 END FIND

How numeric retrievals are processed

When the relation IS is used, a character-by-character match is not performed. Leading zeros to the left of the decimal point and trailing zeros to the right are ignored. Thus, SETTLEMENT AMOUNT IS 50 retrieves records that were stored with a SETTLEMENT AMOUNT field value of 050, 50.0, +50, and so on, whereas the statement SETTLEMENT AMOUNT = 50 does not.

If the value of the named field in a given record has the stated relation to the number in the retrieval condition, then the record is retrieved. For example, AGE IS BETWEEN 21 AND 35 does retrieve a record with the pair AGE = 27.

When a numeric retrieval is performed on a multiply occurring field, all records with at least one occurrence of the field that meets the specified condition are retrieved. See Processing multiply occurring fields and field groups for more information about retrieval of multiply occurring fields.

NUMERIC RANGE and ORDERED NUMERIC attributes

In a manner similar to the effect of the IS relation (see above), field values for NUMERIC RANGE and ORDERED NUMERIC fields are indexed by their numeric equivalents; leading plus signs and leading and trailing zeros are ignored. The field values 1, 1.0, 01.0, +1, and +0001, for example, are considered identical when indexed numerically. When leading zeros are meaningful to your application, the field should be ORDERED CHARACTER. See also:

Records selected

If a numeric range retrieval is performed on a field that is defined with the NUMERIC RANGE or ORDERED NUMERIC attribute, the retrieval conditions find only those records where the numerical fields contain:

  • Optional leading plus sign (+) or minus sign (-).
  • Digits 0-9 and an optional decimal point.
  • No more than 10 digits on either side of the decimal point (20 digits maximum) for NUMERIC RANGE fields.
  • No more than 10 digits on either side of the decimal point for ORDERED NUMERIC fields; only the first 15 significant digits are used. (Exponent notation can be used for ORDERED NUMERIC fields; see the discussion in Exponent notation.)

Examples

Examples of valid NUMERIC RANGE or ORDERED NUMERIC fieldname = value pairs are provided here:

PRICE = 3317 SETTLEMENT AMOUNT = 050 VOLUME = 517.6473 WAVE LENGTH = +.0072 LOSS = 0 TEMP = -40

Restrictions

Note these restrictions on adding NUMERIC RANGE or ORDERED fields to a file:

  • If you try to add a NUMERIC RANGE field to a record in which that field name already appears, an error occurs. The field is not added and a message is displayed during execution of the request.
  • If you try to add an illegal value to a NUMERIC RANGE or ORDERED NUMERIC field, the value is nevertheless stored as entered. For example, if AGE is defined as a NUMERIC RANGE retrieval field type and AGE = TWO is added to the record, it is stored. In order to retrieve a field with an illegal value, the following methods must be used.

Locating and printing illegal values

Illegal values in a NUMERIC RANGE field can be located by using the following fieldname = value pair:

NUMERICAL FIELD++ = NON NUMERICAL

For example, a record that contains AGE = A3 or AGE = TWO can be found by the statement:

AGE++ = NON NUMERICAL

To print an illegal value in a NUMERIC RANGE field, the regular name of the field should be used. PRINT AGE outputs all the values of the AGE field in a record, including A3 and TWO. Illegal values in an ORDERED NUMERIC field can be found by locating a value alphabetically greater than nulls and less than zero.

Negated numeric range retrievals

Any of the numeric retrieval conditions can be negated by using the word NOT after the IS. For example:

AGE IS NOT 3 AGE IS NOT NUMERICALLY LT 21 AGE IS NOT NUMERICALLY > 65 AGE IS NOT BETWEEN -18 AND 10

In the last example above, the condition IS NOT BETWEEN retrieves any record that has AGE less than or equal to -18 and any record that has AGE greater than or equal to 10. Notice that in this example, if a record has AGE equal to -18 or 10, it is retrieved.

Negating a negated operator

Negating a negated operator results in a positive operator. For example:

FIND ALL RECORDS FOR WHICH AGE IS NOT ¬= 35 END FIND

results in the same found set as:

FIND ALL RECORDS FOR WHICH AGE IS 35 END FIND

How negated conditions are processed

The result of a numeric retrieval on a field defined by the file manager as NUMERIC RANGE differs from the result on a field defined as NON-RANGE or ORDERED when the condition is negated. Suppose this condition is specified:

AGE IS NOT LESS THAN 10

The set of records retrieved depends on whether AGE is defined with the NUMERIC RANGE, ORDERED, or NON-RANGE attribute, as follows:

If AGE is defined as... Then records are...
NUMERIC RANGE Retrieved by this statement are those with AGE fields whose values are numerical and greater than or equal to 10.
NON-RANGE or ORDERED Retrieved by this condition are those that do not contain AGE fields less than 10. These records include those containing nonnumerical AGE fields and those containing no AGE field.
NON-RANGE and INVISIBLE Not retrieved.

Exponent notation

A numerical value in a retrieval statement can be defined in exponent notation.

Syntax

Exponent notation has this format:

[+ | -] {whole-number | whole-number.fractional-number | fractional-number} E [+ | -]

Where:

  • The value of the whole number and/or the fractional number should be a maximum of 15 significant decimal digits. If the number of significant digits exceeds 15, the remaining precision is lost.
  • The exponent expression must be between 75 and -74.

Embedded spaces are not allowed within the exponent string.

Example

These values are legal:

-1322.444E14 15E-47 +99233.0332E-66 222E+11

Character string retrievals

When a field contains string values, for example, FULLNAME or ADDRESS, the field can be tested to determine whether a record containing values within a particular range should be retrieved, rather than simply retrieving records based on equality conditions. An example of string equality retrieval is this Find All Records For Which statement to retrieve records of blue Fords:

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

Syntax

In addition to the equality condition, the following statement is used for the retrieval of string values:

FIND ALL RECORDS FOR WHICH fieldname IS [ALPHABETICALLY] {BEFORE | AFTER | operator} value

Where:

  • The ALPHABETICALLY keyword is optional and specifies that a character range retrieval should be performed. If this keyword is omitted, the type of retrieval performed is based upon the default type of the operator. For more information about the default type for each operator, refer to Interpretation of values in retrievals.
  • BEFORE is an operator that specifies records in which the field precedes the given value in EBCDIC collating sequence.
  • AFTER is an operator that specifies records in which the field follows the given value in EBCDIC collating sequence.
  • operator is one of the range retrieval operators listed earlier in this section in Range retrieval operators. The standard EBCDIC collating sequence is used when performing string comparisons.

Examples

FIND ALL RECORDS FOR WHICH FULLNAME IS BEFORE 'M' END FIND FIND ALL RECORDS FOR WHICH FULLNAME IS AFTER BAKER END FIND FIND ALL RECORDS FOR WHICH FULLNAME IS ALPHABETICALLY GE JONES END FIND

If the value of the named field in a given record has the stated relation to the string in the retrieval condition, then the record is retrieved. Thus, NAME IS AFTER 'M' retrieves all records containing names that begin with M through Z.

Multiply occurring fields

When a string retrieval is performed on a multiply occurring field, all records with at least one occurrence of the field meeting the specified condition are retrieved. See Processing multiply occurring fields and field groups for more information about retrieving multiply occurring fields.

Negated string retrieval conditions

Any of the string retrieval conditions can be negated by using the word NOT after the IS. For example:

FULLNAME IS NOT AFTER 'Q' FULLNAME IS NOT LT JOHNSTON

Combining retrieval conditions

Beginning and ending values in retrievals can be specified or retrievals can be combined in the following two ways:

  • By using the IN RANGE clause with the FIND statement
  • By using Boolean operators or parentheses

IN RANGE clause

The following form of the FIND statement can be used if a beginning and ending range of values is desired:

Syntax

FIND ALL RECORDS FOR WHICH fieldname IS [NOT] [NUMERICALLY | ALPHABETICALLY] IN RANGE [FROM | AFTER] value1 {TO | [AND] BEFORE} value2

Where:

  • The NUMERICALLY or ALPHABETICALLY keyword specifies the type of retrieval to be performed. NUMERICALLY indicates a numeric range retrieval; ALPHABETICALLY indicates a character range retrieval. NUMERICALLY is the default.
  • The FROM or AFTER clause specify whether a field containing value1 should be included in the retrieval. FROM indicates that the retrieval includes value1. AFTER indicates that the retrieval begins at, but does not include, value1. FROM is the default if the FROM or AFTER keyword is not specified.
  • value1 specifies the beginning value.
  • The TO or BEFORE clause specifies whether a field containing value2 should be included in the retrieval. TO indicates that the retrieval includes value2. BEFORE indicates that the retrieval ends at, but does not include, value2.
  • value2 specifies the ending value.

Usage

Boolean operators in retrieval statements

You can use the AND, OR, NOT, NOR, and parentheses in FIND statements to combine retrieval conditions.

Boolean operators and parentheses are described in more detail on the following pages.

Some examples are listed below:

  • fieldname IS BETWEEN value1 AND value2 OR IS BETWEEN value3 AND value4
  • fieldname IS value1 OR IS LESS THAN value2
  • fieldname IS GREATER THAN value1 OR IS NOT GREATER THAN value2 OR IS value3
  • fieldname = value1 OR IS LESS THAN value2
  • fieldname1 IS LESS THAN value2 OR fieldname2 IS BETWEEN value3 AND value4
  • fieldname1 IS LESS THAN value1 OR fieldname2 = value2
  • fieldname IS BEFORE value1 OR = value2
  • fieldname IS AFTER value1 AND BEFORE value2 OR IS AFTER value3
  • fieldname = value1 OR IS BEFORE value2 AND AFTER value3

Interpretation of Boolean operators in retrievals

When a retrieval involves several Boolean operations, Model 204 performs them in the following sequence:

Order of precedence Operator
First NOT
Second NOR
Third AND (explicitly stated)
Fourth OR
Fifth AND (implied by a new line)

You can use parentheses to change the order of precedence: conditions within parentheses take the highest precedence.

Example

Consider this example: search a CENSUS file to find Boston residents whose mother and father were both American-born. The FIND statement to do this can be written:

FIND.RECS: FIND ALL RECORDS FOR WHICH CITY EQ BOSTON (MOTHERS BIRTHPLACE = U.S. FATHERS BIRTHPLACE = U.S.) END FIND

Line continuation and implied ANDs

Do not use a hyphen for line continuation when you are using the implied AND.

Only use a hyphen to indicate line continuation when you have coded an explicit AND or OR at the end of a line.

No continuation hyphen is required in the previous example. Since there is no Boolean operator at the end of the second line, Model 204 processes the statement with the implied AND.

Implied ANDs are at the end of lines 2 and 3.

Note: Technical Support strongly recommends that you do not use parentheses as a line continuation method in a FIND statement; use parentheses only where you need to change the normal order of precedence of Boolean operators.

Order in which expressions are interpreted

Interpretation of a Boolean expression proceeds from left to right, except where the order of precedence (shown in Interpretation of Boolean operators in retrievals) dictates otherwise. An operation of higher precedence following an operation of lower precedence is performed first.

Thus, from the example in the previous page, the following code shows:

(MOTHERS BIRTHPLACE = U.S. FATHERS BIRTHPLACE = U.S.)

(describing people who have American-born mothers and fathers) is performed first and is then combined with the set of records describing Boston residents. A record that meets both criteria is included in the found set.

The following example indicates how a somewhat more complex statement would be processed:

BEGIN FIND.RECS: FIND AND PRINT COUNT DECILE IS ALPHA IN RANGE FROM 3320 TO 3390 STATE EQ MA OR - STATE EQ CT END FIND END

The first action Model 204 takes is to perform the OR (STATE EQ MA OR STATE EQ CT), since the OR has higher precedence than the implied AND generated by the first selection criterion. Then the result of that OR is combined with the first selection criterion; again only records that meet both criteria are included in the found set. In other words, this example selects only those residents of Massachusetts or Connecticut whose decile value is in the specified range.

Interpretation of values in retrievals

The manner in which values (numbers, strings, and exponent notation) are interpreted depends on whether the retrieval is an equality retrieval or a range retrieval.

Equality retrievals

In an equality retrieval condition (fieldname = value), the interpretation of the value is based on whether the field has been defined with the KEY and FLOAT field attributes or the ORDERED NUMERIC attribute, as follows:

  • If the field has been defined with the KEY and FLOAT field attributes or the ORDERED NUMERIC attribute, the value is examined to determine whether it is a number, exponent notation, or a character string. Comparisons are then performed as follows:

    If the value is a number or in exponent notation, a numerical comparison is performed.

    If the value is a character string, a character string comparison is performed.

  • If the field has not been defined with the KEY and FLOAT field attributes or the ORDERED NUMERIC attribute, the value is treated as a string and a character string comparison is performed. Exponent notation is not converted to a numerical form. For example, if a field contains a value of .1234E-3, the comparison .1234E-3 = .0001234 is not true if the field has not been defined with the KEY and FLOAT field attributes or the ORDERED NUMERIC attribute.

Range retrieval

In a range retrieval condition (in the form fieldname IS [operator] value) or a condition that uses the IN RANGE clause, the type of comparison performed is based on whether the NUMERICALLY or ALPHABETICALLY keyword is specified, as follows:

If the keyword is... The value is interpreted as...
NUMERICALLY Number and a numerical comparison is performed.
ALPHABETICALLY String and a character string comparison is performed.
Neither NUMERICALLY nor ALPHABETICALLY Default comparison type of the operator and the corresponding type of comparison is performed. See the Range retrieval comparisons table.

The default comparison type for each operator is provided in the following table:

Range retrieval comparisons
IS operator Default type of comparison performed
IN RANGE Numeric
EQ, =, or none Numeric
NE or ¬= Numeric
LESS THAN, LT, or < Numeric
LE or <= Numeric
GREATER THAN, GT, or > Numeric
GE or >= Numeric
BETWEEN Numeric
AFTER Character string
BEFORE Character string

Results when operator and value type are not matched

The following results occur when an operator and value type are not matched:

  • If a numeric operator is used with a nonnumeric value, as in:

    FIND ALL RECORDS FOR WHICH LAST NAME IS GREATER THAN ANDREWS

    no records are retrieved. If this operator is negated, as follows:

    FIND ALL RECORDS FOR WHICH LAST NAME IS NOT GREATER THAN ANDREWS

    every record in the file or group is retrieved.

  • If a character string operator is used with a numeric value, as in:

    FIND ALL RECORDS FOR WHICH YEAR IS BEFORE 1986

    all numeric values in the field are converted to character strings and compared character-by-character to the retrieval criteria. Therefore, numbers such as 942, 700, or 2 would not be retrieved.

Range retrieval optimization

The attributes defined for a field can impact how quickly a retrieval is performed.

Indexed searches

Retrieval based on KEY, NUMERIC RANGE, or ORDERED fields is particularly fast and efficient because Model 204 goes directly to the appropriate index entry to find the records that satisfy the selection criteria. The effect of these attributes on retrieval is discussed in detail in the following sections.

Indexed searches for equality retrievals

When an equality retrieval (fieldname = value) is performed on a field that has the KEY attribute, or when a numeric retrieval (fieldname IS [operator] value) is performed on a field that has the NUMERIC RANGE attribute, Model 204 locates the records that meet the conditions by doing a search of the Table C index.

When an equality retrieval (fieldname = value) is performed on a field that has the ORDERED attribute but does not have the KEY attribute, the Ordered Index is searched.

Indexed searches for range retrievals

Range retrievals for a field defined with the ORDERED attribute are dependent on the type of retrieval to be performed and whether the NUMERIC or CHARACTER option has been selected for the ORDERED attribute. The Ordered Index is searched if one of the following conditions exists:

  • If a numeric range retrieval is performed and the field has the ORDERED NUMERIC attribute.
  • If a character range retrieval is performed and the field has the ORDERED CHARACTER attribute.

Direct (Table B) searches

In all other cases, Model 204 performs a direct search of the data (Table B) to locate the records. For example, if the field has the ORDERED NUMERIC attribute but the type of retrieval is nonnumeric, a direct search of the data is performed.

Note: The user-resettable parameter, MBSCAN, can be used to warn the user when a direct search of more than a specified number of records is required. The user then has the option of proceeding with the search or cancelling the request.

Summary of field attributes and retrieval optimization

The following table summarizes the field attribute and retrieval combinations for retrieval optimization.

Evaluation chart for the FIND statement
FIND condition
 
Field attribute
 
KEY
 
NUMERIC RANGE
 
ORDERED NUMERIC
 
ORDERED CHARACTER
KEY and  
Other
ORDERED NUMERIC ORDERED CHARACTER
Equality Table C Index Table C Index Ordered Index Ordered Index Table C Index Table C Index Data
Character range Data Data Data Ordered Index Data Ordered Index Data
Numeric range Data Table C Index Ordered Index Data Ordered Index Data Data
Character and
numeric range
Data Data Data Ordered Index Data Ordered Index Data

Pattern matching

Retrieval can be performed using a field value that is specified in the form of a pattern.

Pattern retrieval (pattern matching) can be performed on fields of any type. However, pattern retrieval does not process records for which the specified retrieval field is not present.

The pattern is evaluated for each value in the specified field to determine the selection of records. Those values that match the criteria in the pattern are selected. Retrieved values must match patterns character by character, including blanks, except when special characters are used.

Patterns also can be specified in the FOR EACH RECORD, FOR EACH VALUE, and IF statements, which are described later in this manual.

Syntax

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

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

where the keyword LIKE indicates that whatever follows is a pattern. The pattern must be enclosed in quotation marks.

Note: The syntax "fieldname IS NOT LIKE pattern" operates only on records for which fieldname actually exists in the record. For example, a file contains 99983 records. If fieldname ORDCHAR has the value 'A' in 9 records and the value 'B' in 7 different records and does not exist in any other records, the following requests will provide the results indicated:

  • Input 1:

    BEGIN ALL: FPC A: FPC ORDCHAR IS NOT LIKE 'B' END

    Output 1:

    99983 9

  • Input 2:

    BEGIN ALL: FPC B: FPC ORDCHAR IS NOT LIKE 'A' END

    Output 2:

    99983 7

  • Input 3:

    BEGIN ALL: FPC AB: FPC ORDCHAR IS NOT LIKE '*' END

    Output 3:

    99983 0

Moving the NOT operator before the field name provides the complement or NOT of the found set and operates on all records, even those not containing the field:

  • Input 4:

    BEGIN ALL: FPC NOTA: FPC NOT ORDCHAR IS LIKE 'A' END

    Output 4:

    99983 99974

  • Input 5:

    BEGIN ALL: FPC NOTB: FPC NOT ORDCHAR IS LIKE 'B' END

    Output 5:

    99983 99976

  • Input 6:

    BEGIN ALL: FPC NOTA: FPC NOT ORDCHAR IS LIKE '*' END

    Output 6:

    99983 99967

Providing sufficient stack space for complex patterns

If a pattern retrieval produces one of the following error messages, you might need to increase the length of the Model 204 pushdown list area, a stack area which contains dynamic storage used by both the pattern matcher and the Model 204 assembler:

M204.2104 THE STACK IS TOO SMALL TO PROCESS YOUR REQUEST M204.2106 STACK OVERFLOW. RECURSION TOO DEEP. SIMPLIFY PATTERN

This stack area is controlled by the LPDLST parameter, a SYSTEM parameter that can be set (or reset) on the User 0 line or with the UTABLE command. For extremely complex patterns (for example, patterns involving nested repeated patterns or nested wildcards) you might need to reset LPDLST to 32K.

For sites using MP/204 (the multiprocessor feature), LPDLST is set once, but is allocated for each processor of the CPU.

Note: The LCPDLST parameter, which controlled the stack area used by the pattern matcher in earlier releases, should be kept at its default value.

Pattern characters in the Is Like clause

The characters used for pattern retrieval are summarized in the following table, and discussed in detail in subsequent sections. Because a pattern is treated like a character value, it needs beginning and ending quotes to conform to the format requirements of User Language. See Quotation marks for a detailed discussion about the use of quotes in User Language.

Pattern matching characters
Character Description
* Wildcard character
+ Placeholder character
, Or character
( ) Set begin and end characters
- Range character
/ Repeat character
! Escape character
= Hexadecimal character
# Numeric digit character
@ Alphabetic character

Beginning in Model 204 Version 5.1, all characters X'00' through X'FF' are valid in a pattern presented to the User Language pattern matcher and the $ChkPat function.

As a result all characters are treated as valid, literal characters. The following error messages are no longer invoked for these characters.

M204.1688: errortype IN PATTERN 'pattern' AT CHARACTER char M204.1689: errortype IN PATTERN 'pattern' AT CHARACTER char

Note: Patterns containing embedded escape characters preceding special characters function as they did in earlier releases of Model 204.

Wildcard character

The wildcard character serves as a placeholder for 0 to 255 characters. You can use wildcard characters as follows:

  • Embed wildcard characters within a repeat pattern.
  • Use a repeat character (/) immediately after a wildcard character.
  • Include wildcard characters, either alone or in combination with other characters, in a set.

For example:

If pattern is... Sample matching values might be...
A*SON ANDERSON ABLESON ASON

Placeholder character

The placeholder character serves as a placeholder for one character only. For example:

If pattern is... Sample matching values might be...
C+RY CARY CORY
J+++SON JOHNSON JACKSON J%&$SON.

Or character

The Or character (a comma) allows the choice of more than one matching value. The Or character also can be used to define a null (,,) character. The values separated by the Or character are called members of the pattern. For example:

If pattern is... Sample matching values might be...
JONES,J+++SON JONES JOHNSON JACKSON

Note: The following type of pattern is illegal:

/A,B(XYZ)

Set begin and set end characters

The set begin and end characters allow for a segregation of choices. Set characters are required when a repeat character or range characters are used (see below). A comma (the Or character) is used to separate the choices. For example:

If pattern is... Sample matching values might be...
(JACK,JOHN)SON JACKSON JOHNSON

Note: You can include additional set begin and set end characters in a repeat pattern. The following type of pattern is illegal:

/A,B(XYZ)

Range character

The range character allows a single EBCDIC character range (including unprintable characters) to be specified. The range must either be enclosed with set begin and end characters or be a set member with a comma preceding and/or following it. In addition, the ending range character must be greater than the beginning range character. For example:

If pattern is... Sample matching values might be...
(0-9) 0 3 5 8 9
(A,N-T,X)*SON OLSON TILSON XSON

Repeat character

The repeat character lets you specify one or more repetitions of a set. The repeat character must be followed by a single number or a range of two numbers. The number must be between 0 and 255. The second number, if specified, must be greater than the first. In addition, a set must begin immediately following the repeat number(s). Supported uses for repeat characters include the following:

  • Including more than one repeat character within a pattern.
  • Including wildcard characters within a repeat pattern.
  • Using a repeat character (/) immediately after a wildcard character.
  • Including additional set begin and set end characters in a repeat pattern.

For example:

If pattern is... Sample matching values might be...
/3(COPY) COPYCOPYCOPY
/1-2(COPY) COPY COPYCOPY
/2(0-9) 01 98 23
/1-4(+) ONE 7X FOUR A 42

Note: The following type of pattern is illegal:

/A,B(XYZ)

Escape character

The escape character (!) allows a character to be interpreted as a literal character rather than as a pattern matching character. The escape character affects only the next character and is needed when you want to treat as a valid literal character one of the eleven pattern matching characters: * + ' ( ) - / ! = # @.

The escape character works as shown in the following example.

If pattern is... Sample matching values

!(800!)-*

(800)-244-3344

J+++.

JUMP. JILT. J%&$.

The eleven special pattern characters, listed in Pattern characters, invoke special pattern operations, unless preceded by the escape character (!).

Note: Patterns containing embedded escape characters preceding special characters function as they did in earlier releases of Model 204.

Hexadecimal character

The hexadecimal character allows for the hexadecimal representation of one or a range of unprintable EBCDIC characters. The character must be followed by two digits (0-9,A-F) that represent the hexadecimal value of the character to be retrieved. For example:

If pattern is... Sample matching values might be...
=6B,=4D , (
(=7C-=7F) @ ' = "

Numeric digit character

The numeric digit character allows a numeric range (0-9) to be specified. For example:

If pattern is... Sample matching values might be...
### 099 378 1111
/1-4(#) 2 9834 23

Note: If the value of the FLUSH parameter is #, FLUSH must be reset before the numeric digit pattern character can be used during line editing.

Alphabetic character

The alphabetic character allows an alphabetic range (a-z, A-Z) to be specified. Unprintable characters are not included. For example:

If pattern is... Sample matching values might be...
@@@@@ JONES SARAH Frand
/2-5(@) TO Harry JAN

Note: If the value of the ERASE parameter is @, ERASE must be reset before the alphabetic pattern character can be used during line editing.

Pattern retrieval optimization

For faster retrievals you can take advantage of the ORDERED CHARACTER attribute on fields:

  • Without the ORDERED CHARACTER attribute the data (Table B) is searched directly.
  • With the ORDERED CHARACTER attribute the Ordered Index is searched first.

Pattern retrieval optimization cannot be performed if the pattern and any of its members begin with:

  • set characters (parentheses)
  • range characters (hyphens)
  • hexadecimal value 'FF'
  • a wildcard character (*)
  • a placeholder character (+)
  • a numeric digit character (#)
  • an alphabetic pattern character (@)

When pattern retrieval optimization cannot be performed, an informational message is issued to the audit trail to notify you that the entire Ordered Index will be searched:

M204.2864: FULL SEARCH OF ORDERED INDEX PERFORMED

Note: Since message M204.2864 is issued at pattern evaluation time and will be issued each time the pattern is evaluated for each segment of the file, you may want to remove the overhead of issuing the message entirely. Issue the following command to minimize the amount of CPU used:

MSGCTL M204.2864 NOACTION

Examples

These patterns would result in an optimized retrieval:

SMITH* ABBY,DAVID,DANNY A*,ERNIE,BERT,OS* 213,433,99 SP/2(E)D,VAC/2(U)M ALPHAB(ET,ITS) BOS(TON,CO),NEW( YORK,ARK)

while these patterns would search the entire Ordered Index:

/1-5(A) +++SON @@@@NATHA (A-N)+++ *SMITH

Pattern retrieval performance also is affected by the complexity of the pattern specified. The more complex the pattern, the more processing is required.

Use of full key for pattern matching

The full key is used when you acquire an Ordered Index found set by pattern matching. For example, in a FIND statement, your selection criteria could use the pattern matcher in this way:

FIND ALL RECORDS FOR WHICH fieldname IS LIKE 'COMPANY01DEPT05ACCT02*' END FIND

The pattern matcher uses the full key COMPANY01DEPT05ACCT02 to search for the initial found set, then sees what fields in the found set match the pattern selection criteria. Performance is improved by restricting how much of the Ordered Index is searched to acquire the initial found set.

Using expressions in FIND statements

EQ VALUE retrieval condition

Purpose

The EQ VALUE clause provides support for the general use of expressions in FIND statements.

Syntax

The expression is enclosed in parentheses after the EQ VALUE clause.

FIELDNAME EQ VALUE (expression)

Where:

Any expression can be used with the EQ VALUE clause.

Example

FD: IN FILE PEOPLE FD LAST EQ VALUE($READ('LAST NAME?')) END FIND

EQ WITH retrieval condition for concatenated fields

The EQ WITH clause is available as of Model 204 version 7.5. To use the EQ WITH clause, set the FILEORG X'100' bit.

Purpose

The EQ WITH clause retrieves CONCATENATION-OF fields. Model 204 automatically builds the concatenated value.

Syntax

The FIND statement for retrieving data based on concatenated fields is:

label: FIND ALL RECORDS CONCAT_FIELD EQ WITH ('field1 value',...,'field8 value')

Usage

Although you must specify a minimum of two fields in the WITH clause, you can concatenate as many as eight. The number specified must match the concatenated field contents. For example, if the field is composed of four fields, the retrieval condition must include four fields. Otherwise, the following error message is produced:

M204.2877: WITH CLAUSE INVALID: CONCATENATION-OF FIELD HAS WRONG NUMBER OF COMPONENT FIELDS

Consider an example of a concatenated field FULL_NAME:

DEFINE FULL_NAME WITH CONCATENATION-OF FIRST WITH - MIDDLE WITH LAST AND ORD SEP C'$'

and a sample PAI of a record:

FIRST = SAM MIDDLE = Q LAST = SMITH FULL_NAME = SAM$Q$SMITH

The record could be retrieved with:

FD: IN PEOPLE FD FULL_NAME = 'SAM$Q$SMITH' END FIND

The separator, a dollar sign ($) in this case, must be included in the string.

Alternatively, each part of the concatenated field could be used with the new EQ WITH syntax that does not require knowledge of the separator character:

FD: IN PEOPLE FD FULL_NAME EQ WITH ('SAM','Q','SMITH') END FIND

The values specified in the EQ WITH clause can be the results of expressions:

%PARENTS_NAME = '...SMITH' FD: IN PEOPLE FD FULL_NAME EQ WITH ('SAM','Q',$SUBSTR(%PARENTS_NAME,4)) END FIND

Limitations of CONCATENATION-OF fields

Limitations when using SEPARATOR NONE

If you use SEPARATOR NONE in the CONCATENATION-OF field definition, your find set results might not be what you wanted.

For example:

FD: IN FILE PITCHERS FD FOOBAR EQ WITH(’ABC’, ’XYZ’) END FIND

Would match a record with:

FOO = AB BAR = CXYZ

as well as records with

FOO = ABC BAR = XYZ

Limitations when using ESCAPE CANCEL

If you define ESCAPE CANCEL on the CONCATENATION-OF field declaration, an attempt to store a field value with the separator character in a component field for a CONCATENATION-OF field results in request cancellation.

For example, if FULL_NAME is defined with

(ORD CAT FIRST WITH MIDDLE WITH LAST SEP C'$' ESC CANCEL)

Then the following request will cancel:

BEGIN IN PEOPLE STORE RECORD FIRST='SAM' MIDDLE='Q$' LAST='SMITH' END STORE END *** 1 CANCELLING REQUEST: M204.2873: CONCATENATION FIELD FULL_NAME COMPONENT FIELD MIDDLE CONTAINS SEPARATOR CHARACTER

Limitations when using EQ WITH and file groups

An explicit CONCATENATION-OF field must be a CONCATENATION-OF field in any file where it is defined, although it does not have to be defined in every file in the group, just as with any other field reference in a group find. The number of component fields must also be the same in every file, however, the separator and escape characters can be different as can the actual component field names. For example, if one:

Defined FULL_NAME in file PEOPLE with component fields FIRST, MIDDLE, and LAST and $ for the separator character:

IN PEOPLE DEFINE FULL_NAME WITH CONCATENATION-OF - FIRST WITH MIDDLE WITH LAST AND ORD SEP C'$' ESC CANCEL

Defined FULL_NAME in file PEOPLE2 with component fields FIRST_NAME, MIDDLE_INITIAL, and LAST_NAME and the asciicircum, (^) for the separator character:

IN PEOPLE2 DEFINE FULL_NAME WITH CONCATENATION-OF - FIRST_NAME WITH MIDDLE_INITIAL WITH LAST_NAME AND - ORD SEP C'^' ESC CANCEL

Without the EQ WITH retrieval condition, in order to find SAM Q SMITH in both files, the FIND would require two separate strings and knowledge of the separation character:

FD: IN PEOPLE, PEOPLE2 FD FULL_NAME = 'SAM$Q$SMITH' OR 'SAM^Q^SMITH' END FIND

Using the EQ WITH allows the much simpler syntax of:

FD: IN PEOPLE, PEOPLE2 FD FULL_NAME EQ WITH ('SAM','Q','SMITH') END FIND

Miscellaneous limitations when using EQ WITH

If an EQ WITH clause produces a value longer than 255 bytes, it truncates the value.

The EQ WITH clause usually issues any errors at compile time. For example, if you issue the following in file context:

FD: IN PEOPLE FD FULL_NAME EQ WITH ('SAM','Q') END FIND

And FULL_NAME is a concatenation of three fields, a compile time error is issued.

In contrast, if you use a field name variable in your query:

%FIELD = 'FULL_NAME' FD: IN PEOPLE FD  %%FIELD EQ WITH ('SAM','SMITH') END FIND

This type of error would only be detected at evaluation time and the request would be canceled.

Transporting Large Object data using the Universal Buffer

The Universal Buffer is used to transport Large Object data to and from a client. The Universal Buffer is a one-per-user, temporary storage area that automatically expands to accommodate its data contents. The following is a sample request that moves Large Object data from a client to the Model 204 server.

  • Transporting data from a client to the Model 204 server:

    BEGIN ... FD: retrieval-criteria ... /* a statement populates BUFFER with data */ %POSITION=23 %LENGTH=8000 FOR 1 RECORD IN FD ADD NOVEL=BUFFER,%POSITION,%LENGTH END FOR END

  • Transporting Large Object data from the server to a client application:

    BEGIN FD: IN FILE xx FD retrieval criteria END FIND FR FD BUFFER,position,length=NOVEL,position,length END FOR END /* A statement to send the BUFFER to a client*/

Note: Lob-name NOVEL in the first of these examples has a position and length parameter, which you must supply. You can extract a section of the Large Object field by supplying a position and a length within the data. If you want all the data, position is set to 1 and length is the actual size of the Large Object data obtained using $LobLen.

IS PRESENT condition

The phrase, IS PRESENT, can be used as a condition of a FIND statement to specify whether or not a field exists in a record. IS PRESENT retrieves only records that contain at least one occurrence of the specified field, regardless of its value.

Syntax

The format of the condition is:

fieldname IS [NOT] PRESENT

Performance considerations

A retrieval using the IS PRESENT condition is performed by directly searching the data records (Table B). To increase the efficiency of the search, the IS PRESENT condition should be preceded by a condition that uses a field with the KEY attribute.

For example, the following statement locates all records that contain one or more occurrences of the INCIDENT field. Because the RECTYPE field has the KEY attribute, the Table B search initiated by the IS PRESENT condition is performed only on the records retrieved by RECTYPE = DRIVER.

FIND.RECS: FIND ALL RECORDS FOR WHICH RECTYPE = DRIVER INCIDENT IS PRESENT END FIND

IS NOT PRESENT condition

The condition IS NOT PRESENT retrieves only records in which the field is missing. For example, this statement locates all records that do not contain an INCIDENT field:

FIND.RECS: FIND ALL RECORDS FOR WHICH RECTYPE = DRIVER INCIDENT IS NOT PRESENT END FIND

Find$ condition

The Find$ condition performs a FIND on a set of records that has been retrieved by a previous FIND statement.

Syntax

The format of Find$ is:

Find$ label

When to use Find$

The Find$ condition is typically used when you want to And, Not, or Or a condition with an already found set. Find$ also can be used when some of the records, fields, or values in the original found set have been deleted and you wish to refer to the set containing the deletions.

For example:

BEGIN GROUP1: FIND ALL RECORDS FOR WHICH DATE OF BIRTH IS BETWEEN 19200101 AND - 19650101 STATE = CALIFORNIA END FIND GROUP1.CT: COUNT RECORDS IN GROUP1 GROUP1A: FIND ALL RECORDS FOR WHICH SEX = M AND FIND$ GROUP1 END FIND FOR EACH RECORD IN GROUP1A PRINT FULLNAME SKIP 1 LINE END FOR GROUP2A: FIND ALL RECORDS FOR WHICH SEX = M AND NOT FIND$ GROUP1 END FIND GROUP2A.CT: COUNT RECORDS IN GROUP2A PRINT COUNT IN GROUP1.CT PRINT COUNT IN GROUP2A.CT END

The GROUP1A statements retrieves records for SEX=M, DATE OF BIRTH between 19200101 and 19650101, and STATE=CALIFORNIA. The GROUP2A statements retrieves records for which SEX=MALE and either STATE not equal CALIFORNIA, or DATE OF BIRTH not between 19200101 and 19650101.

Sfl$ and Sfge$ conditions

The Sfl$ condition

For sorted files, the following condition retrieves all records for which the sort field is less than the stated value:

Sfl$ value

The Sfge$ condition

For sorted files, the following condition retrieves all records for which the sort field is greater than or equal to the stated value:

Sfge$ value

Example using Sfl$ and Sfge$

In a sorted file in which the sort field is FULLNAME, this statement retrieves all records of people who live in San Diego and whose full name starts with R:

FIND.RECS: FIND ALL RECORDS FOR WHICH CITY = SAN DIEGO SFGE$ R AND SFL$ RZZZ END FIND

Point$ condition

When a record is stored it is assigned an internal record number. In a Find statement, the Point$ condition retrieves all records that have internal record numbers greater than or equal to the stated value. If the given value is not a number, no records are retrieved.

When to use Point$

The Point$ condition should be used when ranges of record numbers must be retrieved. If individual record numbers need to be retrieved, the For Record Number statement should be used. See Record loops for a detailed discussion of the For Record Number statement.

Syntax

The format of Point$ is:

Point$ n

Example

This statement retrieves all records that have internal record numbers between 1500 and 2499, inclusive:

FIND.RECS: FIND ALL RECORDS FOR WHICH POINT$ 1500 AND NOT POINT$ 2500 END FIND

File$ condition

The File$ condition can be used as a condition in a Find statement to restrict the files from which records are selected.

The restriction can apply to a group Find or to a Find that refers to the record set of an earlier group Find. File$ can also be used to limit the reference context of a Find statement when using Parallel Query Option/204. See Files, groups, and reference context.

Syntax

The format of File$ is:

File$ {filename | =}

Where:

  • filename is one of the following:
    • A literal group member name, either without quotation marks or enclosed in single quotes
    • A literal remote file specification or file synonym

    filename cannot be represented by:

    • A %variable
    • A remote file specification enclosed entirely in quotes (for example, 'CLIENTS AT BOSTON')
  • The equal sign (=) restricts the search to the node on which the request is running (the local node). The equal sign cannot be used in a FIND statement that uses a complex File$ clause (for example, File$ CLAIMS89 Or File$ CLAIMS90).

The File$ condition is valid only in group context, and it is supported in scattered group context.

Example

If a group consisting of the CLIENTS and CLAIMS90 files is open, the following statement retrieves records from CLIENTS but not CLAIMS90:

FIND.RECS: FIND ALL RECORDS FOR WHICH FILE$ CLIENTS POLICY NO = 100340 END FIND

Location$ condition

The Location$ condition is valid only when you are using Parallel Query Option/204. It is used to restrict the results of a group context Find statement to a particular node or nodes.

Syntax

The format of Location$ is:

Location$ {location | =}

Where:

  • location is the node name used in file synonyms and remote file specifications. location can be coded as a literal location name, or accepted as input in response to a dummy string prompt. location cannot be represented by a %variable.
  • The equal sign (=) indicates the node on which the request is running (the local node).

The Location$ condition is supported in scattered group context, and it is valid only in group context.

If an optional member of a scattered group is unavailable, no records are found for that file and processing continues.

Examples

Suppose you have opened a scattered group consisting of the files CLIENTS AT BOSTON, CLIENTS AT WASHINGTON, and CLAIMS90 AT BOSTON. The following Find statement retrieves records from CLIENTS AT BOSTON but not from CLIENTS AT WASHINGTON:

FIND.RECS: FIND ALL RECORDS FOR WHICH FILE$ CLIENTS LOCATION$ BOSTON POLICY NO = 100340 END FIND

You can use the Location$ condition to specify, in a single Find statement, multiple files which can be located at different nodes. The following Find statement retrieves records from locations NORTHEAST and SOUTHWEST:

FIND.RECS: FIND ALL RECORDS FOR WHICH FILE$ VEHICLES LOCATION$ NORTHEAST OR LOCATION$ SOUTHWEST MAKE = 'FORD' END FIND

List$ condition

You can refer to the collection of records on a list by entering the List$ condition as a condition in a Find statement. See Lists for a detailed discussion of lists and the List$ condition.

IN label retrieval

The In label clause with the Find statement can be used to refer to a set of records that has been retrieved by a previous Find statement.

Syntax

The format for the In label clause used with a Find statement is:

label.b: FIND ALL RECORDS IN label.a FOR WHICH retrieval conditions

where label.a is the label of a preceding Find statement.

Example

BEGIN N.Y.DRIVERS: FIND ALL RECORDS FOR WHICH RECTYPE = DRIVER STATE = NEW YORK END FIND CT.1: COUNT RECORDS IN N.Y.DRIVERS PRINT COUNT IN CT.1 - WITH ' NEW YORK DRIVERS' DATE: FIND ALL RECORDS IN N.Y.DRIVERS - FOR WHICH INCIDENT DATE IS > 19901231 END FIND CT.2: COUNT RECORDS IN DATE PRINT COUNT IN CT.2 - WITH ' WITH INCIDENTS AFTER 12/31/1990' END

In this request, the Find statement labeled DATE finds only the records in the previous found set that also contain an incident date later than December 31, 1990.