Record retrievals: Difference between revisions

From m204wiki
Jump to navigation Jump to search
mNo edit summary
m (mixed case for statements)
 
(84 intermediate revisions by 7 users not shown)
Line 1: Line 1:
===Overview===
<div class="toclimit-3">
<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>
 
====Reference context====
==Overview==
<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>
<p>
====Retrieval conditions====
[[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>
<p>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.</p>
<p>This page discusses the following types of record retrievals:</p>
===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===
<p>
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.</p>
<p>
This page discusses the following types of record retrievals:</p>
 
<table>
<table>
<tr class="head">
<tr class="head">
Line 11: 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 pattern of "c*" 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 52: Line 71:
</tr>
</tr>
</table>
</table>
====Handling a foundset====
 
<p>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.</p>
===Handling a foundset===
===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 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.</p>
==Numeric range retrievals==
<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="code">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>
   fieldname IS <var>[</var>NUMERICALLY<var>]</var> <var>[</var>operator<var>]</var> value
</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>
 
<li>operator 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>
====Range retrieval operators====
<p>You can use the range retrieval operators described in [[#Range retrieval operators|Range retrieval operators]] for numeric retrievals.    </p>
===Range retrieval operators===
<p>
You can use the range retrieval operators described in the following table for numeric retrievals.    </p>
<table>
<table>
<p class="caption">Range retrieval operators</p>
<tr class="head">
<tr class="head">
<th>IS operator</th>
<th>IS operator</th>
<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>
<td>NE<br>&#172;=</td>
&#xFFFD;=</td>
<td>Unequal to the value specified.</td>
<td>Unequal to the value specified.</td>
</tr>
</tr>
<tr>
<tr>
<td>LESS THAN<br>
<td>LESS THAN<br>LT<br><</td>
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
<td>GREATER THAN<br>GT<br>></td>
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 value1 AND value2</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>Between value1 and value2. </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 the field value is equal to value1 or value2, the record is not retrieved. </p>
<p>
<p>If value1 is greater than value2, no records are retrieved.</p>
If value1 is greater than <var class="term">value2</var>, no records are retrieved.</p></td>
</td>
</tr>
</tr>
</table>
</table>
====Numeric retrieval examples====
 
===Numeric retrieval examples===
<p class="code">FIND ALL RECORDS FOR WHICH
<p class="code">FIND ALL RECORDS FOR WHICH
     SETTLEMENT DATE IS 20030305
     SETTLEMENT DATE IS 20030305
END FIND
END FIND
 
FIND ALL RECORDS FOR WHICH
FIND ALL RECORDS FOR WHICH
     SETTLEMENT DATE IS NUMERICALLY < 20030305
     SETTLEMENT DATE IS NUMERICALLY < 20030305
END FIND
END FIND
 
FIND ALL RECORDS FOR WHICH
FIND ALL RECORDS FOR WHICH
     SETTLEMENT DATE IS BETWEEN 20030300 AND 20030332
     SETTLEMENT DATE IS BETWEEN 20030300 AND 20030332
END FIND  
END FIND
</p>
</p>
====How numeric retrievals are processed====
<p>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.</p>
===How numeric retrievals are processed===
<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 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.</p>
====NUMERIC RANGE and ORDERED NUMERIC attributes====
<p>
<p>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:</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>
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===
<p>
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:</p>
<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>
<b>Records selected</b>
<p>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:</p>
===Records selected===
<p>
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:</p>
<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>
<b>Examples</b>
<p>Examples of valid NUMERIC RANGE or ORDERED NUMERIC  
===Examples===
<p>
Examples of valid NUMERIC RANGE or ORDERED NUMERIC
fieldname = value pairs are provided here:</p>
fieldname = value pairs are provided here:</p>
<p class="code">PRICE = 3317
<p class="code">PRICE = 3317
Line 157: Line 191:
WAVE LENGTH = +.0072
WAVE LENGTH = +.0072
LOSS = 0
LOSS = 0
TEMP = -40  
TEMP = -40
</p>
</p>
<b>Restrictions</b>
<p>Note these restrictions on adding NUMERIC RANGE or ORDERED fields to a file:</p>
===Restrictions===
<p>
Note these restrictions on adding NUMERIC RANGE or ORDERED fields to a file:</p>
<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>
<b>Locating and printing illegal values</b>
<p>Illegal values in a NUMERIC RANGE field can be located by using the following fieldname = value pair:</p>
===Locating and printing illegal values===
<p class="code">NUMERICAL FIELD++ = NON NUMERICAL  
<p>
Illegal values in a NUMERIC RANGE field can be located by using the following fieldname = value pair:</p>
<p class="code">NUMERICAL FIELD++ = NON NUMERICAL
</p>
</p>
<p>For example, a record that contains AGE = A3 or AGE = TWO can be found by the statement:</p>
<p>
<p class="code">AGE++ = NON NUMERICAL  
For example, a record that contains AGE = A3 or AGE = TWO can be found by the statement:</p>
<p class="code">AGE++ = NON NUMERICAL
</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>
<p>
===Negated numeric range retrievals===
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>
<p>Any of the numeric retrieval conditions can be negated by using the word NOT after the IS. For example:</p>
==Negated numeric range retrievals==
<p>
Any of the numeric retrieval conditions can be negated by using the word NOT after the IS. For example:</p>
<p class="code">AGE IS NOT 3
<p class="code">AGE IS NOT 3
AGE IS NOT NUMERICALLY LT 21
AGE IS NOT NUMERICALLY LT 21
AGE IS NOT NUMERICALLY > 65
AGE IS NOT NUMERICALLY > 65
AGE IS NOT BETWEEN -18 AND 10  
AGE IS NOT BETWEEN -18 AND 10
</p>
</p>
<p>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.</p>
<p>
====Negating a negated operator====
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.</p>
<p>Negating a negated operator results in a positive operator. For example:</p>
===Negating a negated operator===
<p>
Negating a negated operator results in a positive operator. For example:</p>
<p class="code">FIND ALL RECORDS FOR WHICH
<p class="code">FIND ALL RECORDS FOR WHICH
     AGE IS NOT &#xFFFD;= 35
     AGE IS NOT &#172;= 35
END FIND  
END FIND
</p>
</p>
<p>results in the same found set as:</p>
<p>
results in the same found set as:</p>
<p class="code">FIND ALL RECORDS FOR WHICH
<p class="code">FIND ALL RECORDS FOR WHICH
     AGE IS 35
     AGE IS 35
END FIND    
END FIND
</p>
</p>
====How negated conditions are processed====
<p>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:</p>
===How negated conditions are processed===
<p class="code">AGE IS NOT LESS THAN 10      
<p>
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:</p>
<p class="code">AGE IS NOT LESS THAN 10
</p>
</p>
<p>The set of records retrieved depends on whether AGE is defined with the NUMERIC RANGE, ORDERED, or NON-RANGE attribute, as follows:</p>
<p>
The set of records retrieved depends on whether AGE is defined with the NUMERIC RANGE, ORDERED, or NON-RANGE attribute, as follows:</p>
<table>
<table>
<tr class="head">
<tr class="head">
Line 204: 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>
===Exponent notation===
<p>A numerical value in a retrieval statement can be defined in exponent notation. </p>
==Exponent notation==
<p>
A numerical value in a retrieval statement can be defined in exponent notation. </p>
<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>
<p>Exponent notation has this format:</p>
<p>
<p class="code">[+ | -] {whole-number  
Exponent notation has this format:</p>
 
<p class="syntax"><span class="squareb">[</span>+ <span class="squareb">|</span> -<span class="squareb">]</span> <span class="squareb">{</span><span class="term">whole-number</span>
  | whole-number.fractional-number
 
  <span class="squareb">|</span> <span class="term">whole-number.fractional-number</span>
  | fractional-number} E [+ | -]
  <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>
</li>
 
<li>The exponent expression must be between 75 and -74. </li>
<li>The exponent expression must be between 75 and -74. </li>
</li>
</ul>
</ul>
<p>Embedded spaces are not allowed within the exponent string.</p>
<p>
Embedded spaces are not allowed within the exponent string.</p>
<b>Example</b>
<b>Example</b>
<p>These values are legal:</p>
<p>
<p class="code"><b></b>-1322.444E14
These values are legal:</p>
<p class="code">-1322.444E14
15E-47
15E-47
+99233.0332E-66
+99233.0332E-66
222E+11             
222E+11
</p>
==Character string retrievals==
<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>
</p>
===Character string retrievals===
<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>
<b>Syntax</b>
<b>Syntax</b>
<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>
<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>
<b>Examples</b>
===Examples===
<p class="code">FIND ALL RECORDS FOR WHICH
<p class="code">FIND ALL RECORDS FOR WHICH
     FULLNAME IS BEFORE 'M'
     FULLNAME IS BEFORE 'M'
END FIND
END FIND
 
FIND ALL RECORDS FOR WHICH
FIND ALL RECORDS FOR WHICH
     FULLNAME IS AFTER BAKER
     FULLNAME IS AFTER BAKER
END FIND
END FIND
 
FIND ALL RECORDS FOR WHICH
FIND ALL RECORDS FOR WHICH
     FULLNAME IS ALPHABETICALLY GE JONES
     FULLNAME IS ALPHABETICALLY GE JONES
END FIND  
END FIND
</p>
</p>
<p>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.</p>
<p>
====Multiply occurring fields====
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.</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>
====Negated string retrieval conditions====
===Multiply occurring fields===
<p>Any of the string retrieval conditions can be negated by using the word NOT after the IS. For example:           </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 [[Processing multiply occurring fields and field groups]] for more information about retrieving multiply occurring fields. </p>
===Negated string retrieval conditions===
<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
</p>
</p>
===Combining retrieval conditions===
<p>Beginning and ending values in retrievals can be specified or retrievals can be combined in the following two ways:</p>
==Combining retrieval conditions==
<p>
Beginning and ending values in retrievals can be specified or retrievals can be combined in the following two ways:</p>
<ul>
<ul>
<li>By using the IN RANGE clause with the FIND statement</li>
<li>By using the IN RANGE clause with the FIND statement</li>
<li>By using Boolean operators or parentheses</li>
<li>By using Boolean operators or parentheses</li>
</ul>
</ul>
====IN RANGE clause====
<p>The following form of the FIND statement can be used if a beginning and ending range of values is desired:  </p>
===IN RANGE clause===
<p>
The following form of the FIND statement can be used if a beginning and ending range of values is desired:  </p>
<b>Syntax</b>
<b>Syntax</b>
<p class="code">FIND ALL RECORDS FOR WHICH  
<p class="syntax">FIND ALL RECORDS FOR WHICH  
<span class="term">fieldname</span> IS [NOT] [NUMERICALLY | ALPHABETICALLY]
IN RANGE
[FROM | AFTER] <span class="term">value1</span> {TO | [AND] BEFORE} <span class="term">value2</span>
</p>
Where:
<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 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>


fieldname IS [NOT] [NUMERICALLY | ALPHABETICALLY]
<li><var class="term">value1</var> specifies the beginning value.</li>


IN RANGE
<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>


[FROM | AFTER] value1 {TO | [AND] BEFORE} value2
<li><var class="term">value2</var> specifies the ending value. </li>
</p>
<b>Where</b>
<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>
<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>
<li>value1 specifies the beginning value.</li>
</li>
<li>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.            </li>
</li>
<li>value2 specifies the ending value. </li>
</li>
</ul>
</ul>
<b>Usage</b>
<b>Usage</b>
<ul>
<ul>
<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>
 
<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>
</li>
</ul>
</ul>
===Boolean operators in retrieval statements===
<p>You can use the AND, OR, NOT, NOR, and parentheses in FIND statements to combine retrieval conditions.           </p>
==Boolean operators in retrieval statements==
<p>Boolean operators and parentheses are described in more detail on the following pages.</p>
<p>
<p>Some examples are listed below:</p>
You can use the AND, OR, NOT, NOR, and parentheses in FIND statements to combine retrieval conditions. </p>
<p>
Boolean operators and parentheses are described in more detail on the following pages.</p>
<p>
Some examples are listed below:</p>
<ul>
<ul>
<li>fieldname IS BETWEEN value1 AND value2 OR IS BETWEEN value3 AND value4</li>
<li><var class="term">fieldname</var> IS BETWEEN <var class="term">value1</var> AND <var class="term">value2</var> OR IS BETWEEN <var class="term">value3</var> AND <var class="term">value4</var></li>
<li>fieldname IS value1 OR IS LESS THAN value2</li>
 
<li>fieldname IS GREATER THAN value1 OR IS NOT GREATER THAN value2 OR IS value3</li>
<li><var class="term">fieldname</var> IS <var class="term">value1</var> OR IS LESS THAN <var class="term">value2</var></li>
<li>fieldname = value1 OR IS LESS THAN value2</li>
 
<li>fieldname1 IS LESS THAN value2 OR fieldname2 IS BETWEEN value3 AND value4</li>
<li><var class="term">fieldname</var> IS GREATER THAN <var class="term">value1</var> OR IS NOT GREATER THAN <var class="term">value2</var> OR IS <var class="term">value3</var></li>
<li>fieldname1 IS LESS THAN value1 OR fieldname2 = value2</li>
 
<li>fieldname IS BEFORE value1 OR = value2</li>
<li><var class="term">fieldname</var> = <var class="term">value1</var> OR IS LESS <var class="term">THAN value2</var></li>
<li>fieldname IS AFTER value1 AND BEFORE value2 OR IS AFTER value3</li>
 
<li>fieldname = value1 OR IS BEFORE value2 AND AFTER value3               </li>
<li><var class="term">fieldname1</var> IS LESS THAN <var class="term">value2</var> OR <var class="term">fieldname2</var> IS BETWEEN <var class="term">value3</var> AND <var class="term">value4</var></li>
 
<li><var class="term">fieldname1</var> IS LESS THAN <var class="term">value1</var> OR <var class="term">fieldname2</var> = <var class="term">value2</var></li>
 
<li><var class="term">fieldname</var> IS BEFORE <var class="term">value1</var> OR = <var class="term">value2</var></li>
 
<li><var class="term">fieldname</var> IS AFTER <var class="term">value1</var> AND BEFORE <var class="term">value2</var> OR IS AFTER <var class="term">value3</var></li>
 
<li><var class="term">fieldname</var> = <var class="term">value1</var> OR IS BEFORE <var class="term">value2</var> AND AFTER <var class="term">value3</var>  </li>
</ul>
</ul>
====Interpretation of Boolean operators in retrievals====
<p>When a retrieval involves several Boolean operations, <var class="product">Model&nbsp;204</var> performs them in the sequence listed in [[#Interpretation of Boolean operators in retrievals|Interpretation of Boolean operators in retrievals]]:     </p>
===Interpretation of Boolean operators in retrievals===
<p>
When a retrieval involves several Boolean operations, <var class="product">Model&nbsp;204</var> performs them in the following sequence: </p>
<table>
<table>
<caption>Order of precedence for Boolean operators</caption>
<tr class="head">
<tr class="head">
<th>Order of precedence</th>
<th>Order of precedence</th>
<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 369: Line 454:
</tr>
</tr>
</table>
</table>
<p>You can use parentheses to change the order of precedence: conditions within parentheses take the highest precedence. </p>
<p>
You can use parentheses to change the order of precedence: conditions within parentheses take the highest precedence. </p>
<b>Example</b>
<b>Example</b>
<p>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:</p>
<p>
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:</p>
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
               CITY EQ BOSTON  
               CITY EQ BOSTON
               (MOTHERS BIRTHPLACE = U.S.
               (MOTHERS BIRTHPLACE = U.S.
                 FATHERS BIRTHPLACE = U.S.)
                 FATHERS BIRTHPLACE = U.S.)
           END FIND  
           END FIND
</p>
</p>
====Line continuation and implied ANDs====
<p><var>Do not use a hyphen for line continuation when you are using the implied AND. </var></p>
===Line continuation and implied ANDs===
<p>Only use a hyphen to indicate line continuation when you have coded an explicit AND or OR at the end of a line.</p>
<p>
<p>No continuation hyphen is required in the previous example. Since there is no Boolean operator at the end of the second line, <var class="product">Model&nbsp;204</var> processes the statement with the implied AND. </p>
Do <b>not</b> use a hyphen for line continuation when you are using the implied AND. </p>
<p>Implied ANDs are at the end of lines 2 and 3.</p>
<p>
<p class="note"><b>Note:</b> 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.</p>
Only use a hyphen to indicate line continuation when you have coded an explicit AND or OR at the end of a line.</p>
====Order in which expressions are interpreted====
<p>
<p>Interpretation of a Boolean expression proceeds from left to right, except where the order of precedence (shown in [[#Interpretation of Boolean operators in retrievals|Interpretation of Boolean operators in retrievals]]) dictates otherwise. An operation of higher precedence following an operation of lower precedence is performed first. </p>
No continuation hyphen is required in the previous example. Since there is no Boolean operator at the end of the second line, <var class="product">Model&nbsp;204</var> processes the statement with the implied AND. </p>
<p>Thus, from the example in the previous page, the following code shows:</p>
<p>
<p class="code">(MOTHERS BIRTHPLACE = U.S.  
Implied ANDs are at the end of lines 2 and 3.</p>
  FATHERS BIRTHPLACE = U.S.)  
<p class="note">
<b>Note:</b> [[Contacting Rocket Software Technical Support|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.</p>
===Order in which expressions are interpreted===
<p>
Interpretation of a Boolean expression proceeds from left to right, except where the order of precedence (shown in [[#Interpretation of Boolean operators in retrievals|Interpretation of Boolean operators in retrievals]]) dictates otherwise. An operation of higher precedence following an operation of lower precedence is performed first. </p>
<p>
Thus, from the example in the previous page, the following code shows:</p>
<p class="code">(MOTHERS BIRTHPLACE = U.S.
  FATHERS BIRTHPLACE = U.S.)
</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>
<p>
<p>The following example indicates how a somewhat more complex statement would be processed:</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>
The following example indicates how a somewhat more complex statement would be processed:</p>
<p class="code">BEGIN
<p class="code">BEGIN
FIND.RECS:  FIND AND PRINT COUNT
FIND.RECS:  FIND AND PRINT COUNT
                 DECILE IS ALPHA IN RANGE FROM 3320 TO 3390  
                 DECILE IS ALPHA IN RANGE FROM 3320 TO 3390
                 STATE EQ MA OR -
                 STATE EQ MA OR -
                 STATE EQ CT
                 STATE EQ CT
Line 400: Line 498:
END
END
</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>
<p>
===Interpretation of values in retrievals===
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>
<p>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. </p>
====Equality retrievals====
==Interpretation of values in retrievals==
<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>
<p>
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. </p>
===Equality retrievals===
<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:
</li>
<p>
If the value is a number or in exponent notation, a numerical comparison is performed.  </p>
<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>
</ul>
</ul>
<p>If the value is a number or in exponent notation, a numerical comparison is performed.  </p>
 
<p>If the value is a character string, a character string comparison is performed. </p>
===Range retrieval===
<ul>
<p>
<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>
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>
</li>
</ul>
====Range retrieval====
<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>
<table>
<table>
<tr class="head">
<tr class="head">
Line 422: 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
NUMERICALLY nor ALPHABETICALLY </td>
NUMERICALLY nor ALPHABETICALLY </td>
<td>Default comparison type of the operator and the corresponding type of comparison is performed. See [[#Range retrieval|Range retrieval]].</td>
<td>Default comparison type of the operator and the corresponding type of comparison is performed. See the [[#rrcomp|Range retrieval comparisons]] table.</td>
</tr>
</tr>
</table>
</table>
<p>The default comparison type for each operator is provided in [[#Range retrieval|Range retrieval]]. </p>
<p>
The default comparison type for each operator is provided in the following table: </p>
<table>
<table>
<caption>Range retrieval comparisons</caption>
<caption><div id="rrcomp">Range retrieval comparisons</div></caption>
<tr class="head">
<tr class="head">
<th>IS operator</th>
<th>IS operator</th>
<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 &#xFFFD;= </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>
<td>Character string   </td>
<td>Character string </td>
</tr>
</tr>
</table>
</table>
====Results when operator and value type are not matched====
 
<p>The following results occur when an operator and value type are not matched:</p>
===Results when 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
</p>
</p>
<p>no records are retrieved. If this operator is negated, as follows:</p>
<p>
no records are retrieved. If this operator is negated, as follows:</p>
<p class="code">FIND ALL RECORDS FOR WHICH
<p class="code">FIND ALL RECORDS FOR WHICH
     LAST NAME IS NOT GREATER THAN ANDREWS  
     LAST NAME IS NOT GREATER THAN ANDREWS
</p>
</p>
<p>every record in the file or group is retrieved.</p>
<p>
</li>
every record in the file or group is retrieved.</p></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>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>
<p>
</li>
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>
</ul>
</ul>
===Range retrieval optimization===
 
<p>The attributes defined for a field can impact how quickly a retrieval is performed. </p>
==Range retrieval optimization==
====Indexed searches====
<p>
<p>Retrieval based on KEY, NUMERIC RANGE, or ORDERED fields is particularly fast and efficient because <var class="product">Model&nbsp;204</var> 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.</p>
The attributes defined for a field can impact how quickly a retrieval is performed. </p>
====Indexed searches for equality retrievals====
<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>
===Indexed searches===
<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>
<p>
====Indexed searches for range retrievals====
Retrieval based on KEY, NUMERIC RANGE, or ORDERED fields is particularly fast and efficient because <var class="product">Model&nbsp;204</var> 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.</p>
<p>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: </p>
===Indexed searches for equality retrievals===
<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>
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>
===Indexed searches for range retrievals===
<p>
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: </p>
<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>
====Direct (Table B) searches====
<p>In all other cases, <var class="product">Model&nbsp;204</var> 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. </p>
===Direct (Table B) searches===
<p class="note"><b>Note:</b> 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. For more information on MBSCAN, refer to the Rocket <var class="product">Model&nbsp;204</var> Parameter and Command Reference Manual.  </p>
<p>
====Summary of field attributes and retrieval optimization====
In all other cases, <var class="product">Model&nbsp;204</var> 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. </p>
<p>[[#Summary of field attributes and retrieval optimization|Summary of field attributes and retrieval optimization]] summarizes the field attribute and retrieval combinations for retrieval optimization.</p>
<p class="note"><b>Note:</b> The user-resettable parameter, <var>[[MBSCAN parameter|MBSCAN]]</var>, 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. </p>
===Summary of field attributes and retrieval optimization===
<p>
The following table summarizes the field attribute and retrieval combinations for retrieval optimization.</p>
<table>
<table>
<caption>Evaluation chart for the FIND statement</caption>
<caption>Evaluation chart for the FIND statement</caption>
<tr class="head">
<tr class="head">
<th rowspan="3">
<th rowspan="3">FIND condition<br>&nbsp;</th>
 
 
 
FIND
condition</th>
<th colspan="7">Field attribute</th>
<th colspan="7">Field attribute</th>
</tr>
</tr>
<tr>
<tr>
<th rowspan="2">
<th rowspan="2">&nbsp;<br>KEY</th>
<th rowspan="2" nowrap>&nbsp;<br>NUMERIC RANGE</th>


<th rowspan="2">&nbsp;<br>ORDERED NUMERIC</th>


KEY</th>
<th rowspan="2">&nbsp;<br>ORDERED CHARACTER</th>
<th rowspan="2">
 
NUMERIC
RANGE</th>
<th rowspan="2">
 
ORDERED
NUMERIC</th>
<th rowspan="2">
 
ORDERED
CHARACTER</th>
<th colspan="2">KEY and</th>
<th colspan="2">KEY and</th>
<th rowspan="2">
<th rowspan="2">
&nbsp;<br>Other</th>
</tr>


Other</th>
</tr>
<tr>
<tr>
<th>ORDERED
<th>ORDERED
Line 564: Line 684:
CHARACTER</th>
CHARACTER</th>
</tr>
</tr>
<tr>
<tr>
<td>Equality</td>
<td>Equality</td>
Line 580: Line 702:
<td>Data</td>
<td>Data</td>
</tr>
</tr>
<tr>
<tr>
<td>Character
<td nowrap>Character range</td>
range</td>
<td>Data</td>
<td>Data</td>
<td>Data</td>
<td>Data</td>
Line 593: Line 715:
<td>Data</td>
<td>Data</td>
</tr>
</tr>
<tr>
<tr>
<td>Numeric
<td>Numeric
Line 607: Line 730:
<td>Data</td>
<td>Data</td>
</tr>
</tr>
<tr>
<tr>
<td>Character
<td>Character and
and
<br>numeric range</td>
numeric
range</td>
<td>Data</td>
<td>Data</td>
<td>Data</td>
<td>Data</td>
Line 623: Line 745:
</tr>
</tr>
</table>
</table>
===Pattern matching===
 
<p>Retrieval can be performed using a field value that is specified in the form of a pattern. </p>
==Pattern matching==
<p>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.</p>
<p>
<p>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.</p>
Retrieval can be performed using a field value that is specified in the form of a pattern. </p>
<p>Patterns also can be specified in the FOR EACH RECORD, FOR EACH VALUE, and IF statements, which are described later in this manual.</p>
<p>
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.</p>
<p>
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.</p>
<p>
Patterns also can be specified in the FOR EACH RECORD, FOR EACH VALUE, and IF statements, which are described later in this manual.</p>
<b>Syntax</b>
<b>Syntax</b>
<p>The format of the FIND statement used to perform pattern matching is:</p>
<p>
<p class="code">FIND [ALL] RECORDS
The format of the FIND statement used to perform pattern matching is:</p>
<p class="syntax">FIND [ALL] RECORDS
{FOR WHICH | WITH} <span class="term">fieldname</span> IS [NOT] LIKE '<span class="term">pattern</span>'
</p>
<p>
where the keyword LIKE indicates that whatever follows is a pattern. The pattern must be enclosed in quotation marks. </p>


{FOR WHICH | WITH} fieldname IS [NOT] LIKE 'pattern'
<p class="note"><b>Note:</b> The syntax "<var class="term">fieldname</var> IS NOT LIKE <var class="term">pattern</var>" operates only on records for which <var class="term">fieldname</var> actually exists in the record. For example, a file contains 99983 records. If <var class="term">fieldname</var> <code>ORDCHAR</code> 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:</p>
</p>
<ul>
<p>where the keyword LIKE indicates that whatever follows is a pattern. The pattern must be enclosed in quotation marks. </p>
<li>Input 1:
<p class="note"><b>Note:</b> The syntax "fieldname IS NOT LIKE pattern" operates only on records for which the 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:</p>
<b>Input 1</b>
<p class="code">BEGIN
<p class="code">BEGIN
ALL: FPC
ALL: FPC
Line 642: Line 772:
END
END
</p>
</p>
<b>Output 1</b>
 
Output 1:
<p class="code">99983
<p class="code">99983
9
9
</p>
</p> </li>
<b>Input 2</b>
 
<li>Input 2:
<p class="code">BEGIN
<p class="code">BEGIN
ALL: FPC
ALL: FPC
Line 652: Line 784:
END
END
</p>
</p>
<b>Output 2</b>
 
Output 2:
<p class="code">99983
<p class="code">99983
7
7
</p>
</p> </li>
<b>Input 3</b>
 
<li>Input 3:
<p class="code">BEGIN
<p class="code">BEGIN
ALL: FPC
ALL: FPC
Line 662: Line 796:
END
END
</p>
</p>
<b>Output 3</b>
 
Output 3:
<p class="code">99983
<p class="code">99983
0
0
</p>
</p> </li>
<p>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: </p>
</ul>
<b>Input 4</b>
<p>
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: </p>
 
<ul>
<li>Input 4:
<p class="code">BEGIN
<p class="code">BEGIN
ALL: FPC
ALL: FPC
Line 673: Line 812:
END
END
</p>
</p>
<b>Output 4</b>
 
Output 4:
<p class="code">99983
<p class="code">99983
99974
99974
</p>
</p> </li>
<b>Input 5</b>
 
<li>Input 5:
<p class="code">BEGIN
<p class="code">BEGIN
ALL: FPC
ALL: FPC
Line 683: Line 824:
END
END
</p>
</p>
<b>Output 5</b>
 
Output 5:
<p class="code">99983
<p class="code">99983
99976
99976
</p>
</p> </li>
<b>Input 6</b>
 
<li>Input 6:
<p class="code">BEGIN
<p class="code">BEGIN
ALL: FPC
ALL: FPC
Line 693: Line 836:
END
END
</p>
</p>
<b>Output 6</b>
 
Output 6:
<p class="code">99983
<p class="code">99983
99967
99967
</p>
</p> </li>
====Providing sufficient stack space for complex patterns====
</ul>
<p>If a pattern retrieval produces one of the following error messages, you might need to increase the length of the <var class="product">Model&nbsp;204</var> pushdown list area, a stack area which contains dynamic storage used by both the pattern matcher and the <var class="product">Model&nbsp;204</var> assembler:</p>
 
===Providing sufficient stack space for complex patterns===
<p>
If a pattern retrieval produces one of the following error messages, you might need to increase the length of the <var class="product">Model&nbsp;204</var> pushdown list area, a stack area which contains dynamic storage used by both the pattern matcher and the <var class="product">Model&nbsp;204</var> assembler:</p>
<p class="code">M204.2104 THE STACK IS TOO SMALL TO PROCESS YOUR REQUEST
<p class="code">M204.2104 THE STACK IS TOO SMALL TO PROCESS YOUR REQUEST
 
M204.2106 STACK OVERFLOW. RECURSION TOO DEEP. SIMPLIFY PATTERN
M204.2106 STACK OVERFLOW. RECURSION TOO DEEP. SIMPLIFY PATTERN
</p>
</p>
<p>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. </p>
<p>
<p>For sites using MP/204 (the multiprocessor feature), LPDLST is set once, but is allocated for each processor of the CPU.</p>
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. </p>
<p>
For sites using MP/204 (the multiprocessor feature), LPDLST is set once, but is allocated for each processor of the CPU.</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>
<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====
<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>
<div id="likeSyntax"></div>
<p>As a result all characters are treated as valid, literal characters. The following error messages are no longer invoked for these characters.</p>
<div id="Pattern characters"></div>
<p class="code">M204.1688: errortype IN PATTERN 'pattern'
===Pattern characters in the Is Like clause===
AT CHARACTER char
<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>
M204.1689: errortype IN PATTERN 'pattern'
AT CHARACTER char
<div id="PatternChars"></div>
</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 [[#Pattern characters|Pattern characters]] 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>
<table>
<table>
<caption>Pattern matching characters</caption>
<caption>Pattern matching characters</caption>
Line 723: Line 869:
<th>Description</th>
<th>Description</th>
</tr>
</tr>
<tr>
<tr>
<td>*</td>
<td align="center">*</td>
<td>Wildcard character   </td>
<td>[[#Wildcard character|Wildcard character]]</td>
</tr>
</tr>
<tr>
<tr>
<td>+</td>
<td align="center">+</td>
<td>Placeholder character       </td>
<td>[[#Placeholder character|Placeholder character]]</td>
</tr>
</tr>
<tr>
<tr>
<td>,</td>
<td align="center">,</td>
<td>Or character       </td>
<td>[[#Or character|Or character]]</td>
</tr>
</tr>
<tr>
<tr>
<td>( )</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="right">-</td>
<td align="center">-</td>
<td>Range character     </td>
<td>[[#Range character|Range character]]</td>
</tr>
</tr>
<tr>
<tr>
<td>/</td>
<td align="center">/</td>
<td>Repeat character       </td>
<td>[[#Repeat character|Repeat character]]</td>
</tr>
</tr>
<tr>
<tr>
<td>!</td>
<td align="center">!</td>
<td>Escape character     </td>
<td>[[#Escape character|Escape character]]</td>
</tr>
</tr>
<tr>
<tr>
<td>=</td>
<td align="center">=</td>
<td>Hexadecimal character   </td>
<td>[[#Hexadecimal character|Hexadecimal character]]</td>
</tr>
</tr>
<tr>
<tr>
<td>#</td>
<td align="center">#</td>
<td>Numeric digit character     </td>
<td>[[#Numeric digit character|Numeric digit character]]</td>
</tr>
</tr>
<tr>
<tr>
<td>@</td>
<td align="center">@</td>
<td>Alphabetic character   </td>
<td>[[#Alphabetic character|Alphabetic character]]</td>
</tr>
</tr>
</table>
</table>
<b>Continuing use of the escape character</b>
<p>The escape character, which is the exclamation point (!), 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>
<p>
<b>Wildcard character</b>
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>The wildcard character serves as a placeholder for 0 to 255 characters. You can use wildcard characters as follows:</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====
<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 (/) 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>For example:</p>
<p>
For example:</p>
<table>
<table>
<tr class="head">
<tr class="head">
Line 782: 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 787: Line 954:
</tr>
</tr>
</table>
</table>
<b>Placeholder character</b>
<p>The placeholder character serves as a placeholder for one character only. For example:</p>
====Placeholder character====
<p>
The placeholder character serves as a placeholder for one character only. For example:</p>
<table>
<table>
<tr class="head">
<tr class="head">
Line 794: 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 803: Line 974:
</tr>
</tr>
</table>
</table>
<b>Or character</b>
<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 (',,') character. The values separated by the Or character are called members of the pattern. For example:</p>
====<b id="patOr"></b>Or character====
<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>
<table>
<table>
<tr class="head">
<tr class="head">
Line 810: 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 815: 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>
<b>Set begin and set end characters</b>
</blockquote>
<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>
 
====Set begin and set end characters====
<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>
<table>
<table>
<tr class="head">
<tr class="head">
Line 825: 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 830: 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>
<b>Range character</b>
</blockquote>
<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>
 
====Range character====
<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>
<table>
<table>
<tr class="head">
<tr class="head">
Line 840: 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 849: Line 1,032:
</tr>
</tr>
</table>
</table>
<b>Repeat character</b>
<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>
====Repeat character====
<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>
<li>Including wildcard characters within a repeat pattern.</li>
<li>Including wildcard characters within a repeat pattern.</li>
</li>
<li>Using a repeat character (/) immediately after a wildcard character.</li>
<li>Using a repeat character (<tt>/</tt>) immediately after a wildcard character.</li>
</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>
</li>
</ul>
</ul>
<p>For example:</p>
<p>
For example:</p>
<table>
<table>
<tr class="head">
<tr class="head">
Line 867: 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 884: 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>
<b>Escape character</b>
</blockquote>
<p>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 for the eleven pattern matching characters--* + ' ( ) - / ! = # @--when you want them treated as a valid literal characters.</p>
 
<p>The escape character works as shown in the following example.</p>
====Escape character====
<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>
<p>
The escape character works as shown in the following example.</p>
<table>
<table>
<tr class="head">
<tr class="head">
Line 895: Line 1,088:
<th>Sample matching values</th>
<th>Sample matching values</th>
</tr>
</tr>
<tr>
<tr>
<td>
<td>
<p class="code">!(800!)-*  
!(800!)-*
</p></td>
</td>
<td>(800)-244-3344</td>
<td>(800)-244-3344</td>
</tr>
</tr>
<tr>
<tr>
<td>
<td>
<p class="code">J+++.  
J+++.
</p></td>
</td>
<td>JUMP.  JILT. J%&amp;$.</td>
<td>JUMP.  JILT. J%&amp;$.</td>
</tr>
</tr>
</table>
</table>
<p>The eleven special pattern characters, listed in [[#Pattern characters|Pattern characters]], invoke special pattern operations, unless preceded by the escape character (!).</p>
<p>
The eleven special pattern characters, listed in [[#Pattern characters|Pattern characters]], invoke special pattern operations, unless preceded by the escape character (!).</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 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>
<b>Hexadecimal character</b>
<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>
====Hexadecimal character====
<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>
<table>
<table>
<tr class="head">
<tr class="head">
Line 917: 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 926: Line 1,126:
</tr>
</tr>
</table>
</table>
<b>Numeric digit character</b>
<p>The numeric digit character allows a numeric range (0-9) to be specified. For example: </p>
====Numeric digit character====
<p>
The numeric digit character allows a numeric range (0-9) to be specified. For example: </p>
<table>
<table>
<tr class="head">
<tr class="head">
Line 933: 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 942: Line 1,146:
</tr>
</tr>
</table>
</table>
<p class="note"><b>Note:</b> f the value of the FLUSH parameter is #, the FLUSH parameter must be reset before the numeric digit pattern character can be used during line editing. For more information on the FLUSH parameter, refer to the Rocket <var class="product">Model&nbsp;204</var> Parameter and Command Reference Manual.        </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>
<b>Alphabetic character</b>
<p>The alphabetic character allows an alphabetic range (a-z, A-Z) to be specified. Unprintable characters are not included. For example:</p>
====Alphabetic character====
<p>
The alphabetic character allows an alphabetic range (a-z, A-Z) to be specified. Unprintable characters are not included. For example:</p>
<table>
<table>
<tr class="head">
<tr class="head">
Line 950: 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 959: Line 1,167:
</tr>
</tr>
</table>
</table>
<p class="note"><b>Note:</b> If the value of the ERASE parameter is @, the ERASE parameter must be reset before the alphabetic pattern character can be used during line editing. For more information on the ERASE parameter, refer to the Rocket <var class="product">Model&nbsp;204</var> Parameter and Command Reference Manual.    </p>
====Pattern retrieval optimization====
<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>For faster retrievals you can take advantage of the ORDERED CHARACTER attribute on fields:</p>
 
===Pattern retrieval optimization===
<p>
For faster retrievals you can take advantage of the ORDERED CHARACTER attribute on fields:</p>
<ul>
<ul>
<li>Without the ORDERED CHARACTER attribute the data (Table B) is searched directly. </li>
<li>Without the ORDERED CHARACTER attribute the data (Table B) is searched directly. </li>
</li>
 
<li>With the ORDERED CHARACTER attribute the Ordered Index is searched first.               </li>
<li>With the ORDERED CHARACTER attribute the Ordered Index is searched first. </li>
</li>
</ul>
</ul>
<p>Pattern retrieval optimization cannot be performed if the pattern and any of its members begin with:</p>
 
<p>
Pattern retrieval optimization cannot be performed if the pattern and any of its members begin with:</p>
<ul>
<ul>
<li>set characters (parentheses)</li>
<li>set characters (parentheses)</li>
</li>
 
<li>range characters (hyphens)</li>
<li>range characters (hyphens)</li>
</li>
 
<li>hexadecimal value 'FF'</li>
<li>hexadecimal value 'FF'</li>
</li>
 
<li>a wildcard character (*) </li>
<li>a wildcard character (<tt>*</tt>) </li>
</li>
 
<li>a placeholder character (+)</li>
<li>a placeholder character (<tt>+</tt>)</li>
</li>
 
<li>a numeric digit character (#)</li>
<li>a numeric digit character (<tt>#</tt>)</li>
</li>
 
<li>an alphabetic pattern character (@)</li>
<li>an alphabetic pattern character (<tt>@</tt>)</li>
</li>
</ul>
</ul>
<p>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:</p>
 
<p>
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:</p>
<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>These patterns would result in an optimized retrieval:</p>
<p>
These patterns would result in an optimized retrieval:</p>
<p class="code">SMITH*
<p class="code">SMITH*
ABBY,DAVID,DANNY
ABBY,DAVID,DANNY
Line 999: Line 1,214:
SP/2(E)D,VAC/2(U)M
SP/2(E)D,VAC/2(U)M
ALPHAB(ET,ITS)
ALPHAB(ET,ITS)
BOS(TON,CO),NEW( YORK,ARK)  
BOS(TON,CO),NEW( YORK,ARK)
</p>
</p>
<p>while these patterns would search the entire Ordered Index:</p>
<p>
while these patterns would search the entire Ordered Index:</p>
<p class="code">/1-5(A)
<p class="code">/1-5(A)
+++SON
+++SON
@@@@NATHA
@@@@NATHA
(A-N)+++
(A-N)+++
<b></b>*SMITH  
<b></b>*SMITH
</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>
 
===Use of full key for pattern matching===
<p>
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: </p>
<p class="code">FIND ALL RECORDS FOR WHICH
fieldname IS LIKE 'COMPANY01DEPT05ACCT02*'
END FIND
</p>
<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>
 
==Using expressions in FIND statements==
 
===EQ VALUE retrieval condition===
<b>Purpose</b>
<p>The EQ VALUE clause provides support for the general use of expressions in FIND statements.</p>
<b>Syntax</b>
<p>The expression is enclosed in parentheses after the EQ VALUE clause.</p>
<p class="syntax">FIELDNAME EQ VALUE (<span class="term">expression</span>)
</p>
Where:
<p>Any expression can be used with the EQ VALUE clause.</p>
<b>Example</b>
<p class="code">FD: IN FILE PEOPLE FD
LAST EQ VALUE($READ('LAST NAME?'))
END FIND
</p>
 
===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>
<p>The EQ WITH clause retrieves CONCATENATION-OF fields. Model 204 automatically builds the concatenated value.</p>
<b>Syntax</b>
<p>The FIND statement for retrieving data based on concatenated fields is:</p>
<p class="code">label: FIND ALL RECORDS
        CONCAT_FIELD EQ WITH ('field1 value',...,'field8 value')
</p>
<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
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>
<p class="code">M204.2877: WITH CLAUSE INVALID: CONCATENATION-OF FIELD
HAS WRONG NUMBER OF COMPONENT FIELDS
</p>
Consider an example of a concatenated field FULL_NAME:
<p class="code">DEFINE FULL_NAME WITH CONCATENATION-OF FIRST WITH -
MIDDLE WITH LAST AND ORD SEP C'$'
</p>
 
and a sample PAI of a record:
<p class="code">FIRST = SAM
MIDDLE = Q
LAST = SMITH
FULL_NAME = SAM$Q$SMITH
</p>
The record could be retrieved with:
<p class="code">FD: IN PEOPLE FD
    FULL_NAME = 'SAM$Q$SMITH'
END FIND
</p>
 
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:
 
<p class="code">FD: IN PEOPLE FD
    FULL_NAME EQ WITH ('SAM','Q','SMITH')
END FIND
</p>
 
The values specified in the EQ WITH clause can be the results of expressions:
<p class="code">%PARENTS_NAME = '...SMITH'
FD: IN PEOPLE FD
    FULL_NAME EQ WITH ('SAM','Q',$SUBSTR(%PARENTS_NAME,4))
END FIND
</p>
 
===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:
<p class="code">FD: IN FILE PITCHERS FD
    FOOBAR EQ WITH(&#x2019;ABC&#x2019;, &#x2019;XYZ&#x2019;)
END FIND
</p>
Would match a record with:
<p class="code">FOO = AB
BAR = CXYZ
</p>
as well as records with
<p class="code">FOO = ABC
BAR = XYZ
</p>
 
====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
<p class="code">(ORD CAT FIRST WITH MIDDLE WITH LAST SEP C'$' ESC CANCEL)
</p>
Then the following request will cancel:
<p class="code"><nowiki>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
</nowiki></p>
 
====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:
<p class="code">IN PEOPLE DEFINE FULL_NAME WITH CONCATENATION-OF -
FIRST WITH MIDDLE WITH LAST AND ORD SEP C'$' ESC CANCEL
</p>
</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>
Defined FULL_NAME in file PEOPLE2 with component fields FIRST_NAME, MIDDLE_INITIAL, and LAST_NAME and the asciicircum, (^) for the separator
====Use of full key for pattern matching====
character:
<p>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: </p>
<p class="code">IN PEOPLE2 DEFINE FULL_NAME WITH CONCATENATION-OF -
<p class="code">FIND ALL RECORDS FOR WHICH
  FIRST_NAME WITH MIDDLE_INITIAL WITH LAST_NAME AND -
  fieldname IS LIKE 'COMPANY01DEPT05ACCT02*'    
ORD SEP C'^' ESC CANCEL
END FIND
</p>
</p>
<p>The pattern matcher now 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. </p>
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
<p>Prior to Version 3.2, the pattern matcher would truncate COMPANY01DEPT05ACCT02 to COMPANY01* for searching the Ordered Index for the initial found set. If COMPANY01 is the entire corporation, but only accounts (02) from one department (05) 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>
separation character:
===Transporting Large Object data using the Universal Buffer===
<p class="code">FD: IN PEOPLE, PEOPLE2 FD
<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>
    FULL_NAME = 'SAM$Q$SMITH' OR 'SAM^Q^SMITH'
END FIND
</p>
 
Using the EQ WITH allows the much simpler syntax of:
<p class="code">FD: IN PEOPLE, PEOPLE2 FD
FULL_NAME EQ WITH ('SAM','Q','SMITH')
END FIND
</p>
 
====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:
<p class="code">FD: IN PEOPLE FD
    FULL_NAME EQ WITH ('SAM','Q')
END FIND
</p>
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:
<p class="code">%FIELD = 'FULL_NAME'
FD: IN PEOPLE FD
    %%FIELD EQ WITH ('SAM','SMITH')
END FIND
</p>
 
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==
<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>
<li>Transporting data from a client to the <var class="product">Model&nbsp;204</var> server:
</ul>
 
<p class="code">BEGIN
<p class="code">BEGIN
.
...
FD: retrieval-criteria
FD: retrieval-criteria
.
...
/* a statement populates BUFFER with data */
/* a statement populates BUFFER with data */
%POSITION=23
%POSITION=23
Line 1,033: Line 1,410:
END FOR
END FOR
END
END
</p>
</p> </li>
<ul>
 
<li>Transporting Large Object data from the server to a client application</li>
<li>Transporting Large Object data from the server to a client application:
</ul>
 
<p class="code">BEGIN
<p class="code">BEGIN
FD: IN FILE xx FD retrieval criteria
FD: IN FILE xx FD retrieval criteria
Line 1,045: 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>
<p>Note: In the previous code lob-name NOVEL 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.</p>
</ul>
===IS PRESENT condition===
 
<p>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.</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==
<p>
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.</p>
<b>Syntax</b>
<b>Syntax</b>
<p>The format of the condition is:</p>
<p>
<p class="code">fieldname IS <var>[</var>NOT<var>]</var> PRESENT  
The format of the condition is:</p>
<p class="syntax"><span class="term">fieldname</span> <span class="literal">IS</span> <span class="squareb">[</span><span class="literal">NOT</span><span class="squareb">]</span> <span class="literal">PRESENT</span>
</p>
</p>
====Performance considerations====
<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>
===Performance considerations===
<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>
<p>
====IS NOT PRESENT condition====
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>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>
<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===
<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
               INCIDENT IS NOT PRESENT
               INCIDENT IS NOT PRESENT
           END FIND              
           END FIND
</p>
</p>
===FIND$ condition===
 
<p>The FIND$ condition performs a FIND on a set of records that has been retrieved by a previous FIND statement. </p>
==Find$ condition==
<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>The format of FIND$ is:</p>
<p>
<p class="code">FIND$ label  
The format of <var>Find$</var> is:
</p>
<p class="syntax"><span class="literal">Find$</span> <span class="term">label</span>
</p>
</p>
====When to use FIND$====
<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>
===When to use Find$===
<p>For example:</p>
<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>
For example:</p>
<p class="code">BEGIN
<p class="code">BEGIN
GROUP1:    FIND ALL RECORDS FOR WHICH
GROUP1:    FIND ALL RECORDS FOR WHICH
Line 1,085: Line 1,483:
               PRINT FULLNAME
               PRINT FULLNAME
               SKIP 1 LINE
               SKIP 1 LINE
             END FOR  
             END FOR
GROUP2A:    FIND ALL RECORDS FOR WHICH
GROUP2A:    FIND ALL RECORDS FOR WHICH
               SEX = M AND NOT FIND$ GROUP1
               SEX = M AND NOT FIND$ GROUP1
Line 1,092: Line 1,490:
             PRINT COUNT IN GROUP1.CT
             PRINT COUNT IN GROUP1.CT
             PRINT COUNT IN GROUP2A.CT
             PRINT COUNT IN GROUP2A.CT
END  
END
</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>
<p>
===SFL$ and SFGE$ conditions===
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>
====The SFL$ condition====
 
<p>For sorted files, the condition:       </p>
==Sfl$ and Sfge$ conditions==
<p class="code">SFL$ value  
===The Sfl$ 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$ <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>For sorted files, the condition:     </p>
<p>
<p class="code">SFGE$ value  
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$ <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>In a sorted file in which the sort field is FULLNAME, this statement:</p>
<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>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>
<p>
====When to use POINT$====
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>
<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>
===When to use Point$===
<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>The format of POINT$ is:</p>
<p>
<p class="code">POINT$ n  
The format of <var>Point$</var> is:</p>
<p class="syntax"><span class="literal">Point$</span> <span class="term">n</span>
</p>
</p>
<b>Example</b>
<b>Example</b>
<p>This statement:</p>
<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
               POINT$ 1500 AND NOT POINT$ 2500
               POINT$ 1500 AND NOT POINT$ 2500
           END FIND  
           END FIND
</p>
</p>
<p>retrieves all records that have internal record numbers between 1500 and 2499, inclusive.</p>
===FILE$ condition===
==File$ condition==
<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>
<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>
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>
<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>
<b>Syntax</b>
<b>Syntax</b>
<p>The format of FILE$ is:</p>
<p>
<p class="code">FILE$ {filename | =}
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>
</p>
<b>Where</b>
Where:
<ul>
<ul>
<li>filename is one of the following:</li>
<li><var class="term">filename</var> is one of the following:
</li>
<ul>
<li>A literal group member name, either without quotation marks or enclosed in single quotes</li>
<li>A literal remote file specification or file synonym</li>
</ul>
</ul>
<p>A literal group member name, either without quotation marks or enclosed in single quotes</p>
<p>
<p>A literal remote file specification or file synonym</p>
<var class="term">filename</var> cannot be represented by:</p>
<p>filename can not be represented by:</p>
<p>A %variable</p>
<p>A remote file specification enclosed entirely in quotes (for example, 'CLIENTS AT BOSTON')</p>
<ul>
<ul>
<li>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,  
<li>A %variable</li>
FILE$ CLAIMS89 OR FILE$ CLAIMS90).</li>
</li>
<li>A remote file specification enclosed entirely in quotes (for example, <code>'CLIENTS AT BOSTON'</code>)</li>
</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 <var>File$</var> clause (for example, <code>File$ CLAIMS89 Or File$ CLAIMS90</code>).</li>
</ul>
</ul>
<p>The FILE$ condition is valid only in group context, and is supported in scattered group context.</p>
<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>
The <var>File$</var> condition is valid only in group context, and it is supported in scattered group context.</p>
<b>Example</b>  
<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
               FILE$ CLIENTS
               FILE$ CLIENTS
Line 1,159: Line 1,586:
           END FIND
           END FIND
</p>
</p>
===LOCATION$ condition===
 
<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>
==Location$ condition==
<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>The format of LOCATION$ is:</p>
<p>
<p class="code">LOCATION$ {location | =}
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>
</p>
<b>Where</b>
Where:
<ul>
<ul>
<li>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.</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>
 
<li>The equal sign (=) 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>
</li>
</ul>
</ul>
<p>The LOCATION$ condition is supported in scattered group context, and 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>
The <var>Location$</var> condition is supported in scattered group context, and it is valid only in group context.</p>
<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>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>
<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
               LOCATION$ BOSTON
               LOCATION$ BOSTON
               POLICY NO = 100340
               POLICY NO = 100340
           END FIND  
           END FIND
</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>
<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,189: Line 1,623:
           END FIND
           END FIND
</p>
</p>
===LIST$ condition===
 
<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#Lists|Lists]] for a detailed discussion of lists and the LIST$ condition.                     </p>
==List$ condition==
<p>&nbsp;</p>
<p>
===IN label retrieval===
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>
<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>
 
==IN label retrieval==
<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>The format for the IN label clause used with a FIND statement is:</p>
<p>
<p class="code">label.b: FIND ALL RECORDS IN label.a FOR WHICH
The format for the <var>In <i>label</i></var> clause used with a <var>Find</var> statement is:</p>
   retrieval conditions  
<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>
</p>
<p>
where <var class="term">label.a</var> is the label of a preceding <var>Find</var> statement.
</p>
</p>
<p>where label.a is the label of a preceding FIND statement.</p>
<b>Example</b>
<b>Example</b>
<p class="code">BEGIN
<p class="code">BEGIN
Line 1,205: Line 1,646:
                 RECTYPE = DRIVER
                 RECTYPE = DRIVER
                 STATE = NEW YORK
                 STATE = NEW YORK
             END FIND  
             END FIND
CT.1:        COUNT RECORDS IN N.Y.DRIVERS
CT.1:        COUNT RECORDS IN N.Y.DRIVERS
             PRINT COUNT IN CT.1 -
             PRINT COUNT IN CT.1 -
Line 1,215: Line 1,656:
             PRINT COUNT IN CT.2 -
             PRINT COUNT IN CT.2 -
                   WITH ' WITH INCIDENTS AFTER 12/31/1990'
                   WITH ' WITH INCIDENTS AFTER 12/31/1990'
END  
END
</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>
<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.