Record retrievals: Difference between revisions

From m204wiki
Jump to navigation Jump to search
mNo edit summary
mNo edit summary
Line 1: Line 1:
===Overview===
==Overview==
<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>
<p>
====Reference context====
[[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>
<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>
====Retrieval conditions====
===Reference context===
<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>
<p>This page discusses the following types of record retrievals:</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>
===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 52: Line 58:
</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="code">FIND ALL RECORDS FOR WHICH
 
   fieldname IS <var>[</var>NUMERICALLY<var>]</var> <var>[</var>operator<var>]</var> value
   fieldname IS <var>[</var>NUMERICALLY<var>]</var> <var>[</var>operator<var>]</var> value
</p>
</p>
Line 68: Line 78:
</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 [[#Range retrieval operators|Range retrieval operators]] for numeric retrievals.    </p>
<table>
<table>
<p class="caption">Range retrieval operators</p>
<p class="caption">Range retrieval operators</p>
Line 106: Line 118:
<td>BETWEEN value1 AND value2</td>
<td>BETWEEN value1 AND value2</td>
<td>
<td>
<p>Between value1 and value2. </p>
<p>
<p>If the field value is equal to value1 or value2, the record is not retrieved. </p>
Between value1 and value2. </p>
<p>If value1 is greater than value2, no records are retrieved.</p>
<p>
If the field value is equal to value1 or value2, the record is not retrieved. </p>
<p>
If value1 is greater than value2, 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 [[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>
===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>
Line 137: Line 159:
</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>
Line 149: Line 173:
</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 183:
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>
Line 167: Line 195:
</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 &#xFFFD;= 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 217: Line 258:
</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>
Line 224: Line 267:
</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="code">[+ | -] {whole-number
  | whole-number.fractional-number
  | whole-number.fractional-number
 
  | fractional-number} E [+ | -]
  | fractional-number} E [+ | -]
</p>
</p>
Line 238: Line 282:
</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>
These values are legal:</p>
<p class="code"><b></b>-1322.444E14
<p class="code"><b></b>-1322.444E14
15E-47
15E-47
+99233.0332E-66
+99233.0332E-66
222E+11          
222E+11
</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>
==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>
<p class="code">FIND ALL RECORDS FOR WHICH fieldname IS  
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 class="code">FIND ALL RECORDS FOR WHICH fieldname IS
  [ALPHABETICALLY]
  [ALPHABETICALLY]
 
  {BEFORE | AFTER | operator} value
  {BEFORE | AFTER | operator} value
</p>
</p>
Line 267: Line 316:
</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 [[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===
<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="code">FIND ALL RECORDS FOR WHICH
 
  fieldname IS [NOT] [NUMERICALLY | ALPHABETICALLY]  
  fieldname IS [NOT] [NUMERICALLY | ALPHABETICALLY]
 
  IN RANGE
  IN RANGE
 
  [FROM | AFTER] value1 {TO | [AND] BEFORE} value2
  [FROM | AFTER] value1 {TO | [AND] BEFORE} value2
</p>
</p>
Line 325: Line 384:
</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>fieldname IS BETWEEN value1 AND value2 OR IS BETWEEN value3 AND value4</li>
Line 340: Line 403:
<li>fieldname = value1 OR IS BEFORE value2 AND AFTER value3                </li>
<li>fieldname = value1 OR IS BEFORE value2 AND AFTER value3                </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 sequence listed in [[#Interpretation of Boolean operators in retrievals|Interpretation of Boolean operators in retrievals]]:    </p>
<table>
<table>
<caption>Order of precedence for Boolean operators</caption>
<caption>Order of precedence for Boolean operators</caption>
Line 369: Line 434:
</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>
<var>Do not use a hyphen for line continuation when you are using the implied AND. </var></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> 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 478:
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 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>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>
</li>
</li>
</ul>
</ul>
<p>If the value is a number or in exponent notation, a numerical comparison is performed.  </p>
<p>
<p>If the value is a character string, a character string comparison is performed. </p>
If the value is a 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>
<ul>
<ul>
<li>If the field has not been defined with the KEY and FLOAT field attributes or the ORDERED NUMERIC attribute, the value is treated as a string and a character string comparison is performed. Exponent notation is not converted to a numerical form. For example, if a field contains a value of .1234E-3, the comparison .1234E-3 = .0001234 is not true if the field has not been defined with the KEY and FLOAT field attributes or the ORDERED NUMERIC attribute.      </li>
<li>If the field has not been defined with the KEY and FLOAT field attributes or the ORDERED NUMERIC attribute, the value is treated as a string and a character string comparison is performed. Exponent notation is not converted to a numerical form. For example, if a field contains a value of .1234E-3, the comparison .1234E-3 = .0001234 is not true if the field has not been defined with the KEY and FLOAT field attributes or the ORDERED NUMERIC attribute.      </li>
</li>
</li>
</ul>
</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>
===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 431: Line 518:
</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 [[#Range retrieval|Range retrieval]].</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 [[#Range retrieval|Range retrieval]]. </p>
<table>
<table>
<caption>Range retrieval comparisons</caption>
<caption>Range retrieval comparisons</caption>
Line 484: Line 572:
</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:</li>
<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>
every record in the file or group is retrieved.</p>
</li>
</li>
<li>If a character string operator is used with a numeric value, as in:</li>
<li>If a character string operator is used with a numeric value, as in:</li>
<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>
all numeric values in the field are converted to character strings and compared character-by-character to the retrieval criteria. Therefore, numbers such as 942, 700, or 2 would not be retrieved.            </p>
</li>
</li>
</ul>
</ul>
===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>
Line 519: Line 621:
</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.
===Summary of field attributes and retrieval optimization===
<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>
<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
FIND
condition</th>
condition</th>
Line 537: Line 644:
<tr>
<tr>
<th rowspan="2">
<th rowspan="2">
 
 
KEY</th>
KEY</th>
<th rowspan="2">
<th rowspan="2">
 
NUMERIC
NUMERIC
RANGE</th>
RANGE</th>
<th rowspan="2">
<th rowspan="2">
 
ORDERED
ORDERED
NUMERIC</th>
NUMERIC</th>
<th rowspan="2">
<th rowspan="2">
 
ORDERED
ORDERED
CHARACTER</th>
CHARACTER</th>
<th colspan="2">KEY and</th>
<th colspan="2">KEY and</th>
<th rowspan="2">
<th rowspan="2">
 
 
Other</th>
Other</th>
</tr>
</tr>
Line 623: Line 730:
</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>
The format of the FIND statement used to perform pattern matching is:</p>
<p class="code">FIND [ALL] RECORDS
<p class="code">FIND [ALL] RECORDS
 
  {FOR WHICH | WITH} fieldname IS [NOT] LIKE 'pattern'
  {FOR WHICH | WITH} fieldname IS [NOT] LIKE 'pattern'
</p>
</p>
<p>where the keyword LIKE indicates that whatever follows is a pattern. The pattern must be enclosed in quotation marks. </p>
<p>
where the keyword LIKE indicates that whatever follows is a pattern. The pattern must be enclosed in quotation marks. </p>
<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>
<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>
<b>Input 1</b>
Line 666: Line 780:
0
0
</p>
</p>
<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>
<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>
<b>Input 4</b>
<b>Input 4</b>
<p class="code">BEGIN
<p class="code">BEGIN
Line 697: Line 812:
99967
99967
</p>
</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>
===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>
===Pattern characters===
<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>
<p class="code">M204.1688: errortype IN PATTERN 'pattern'  
Beginning in <var class="product">Model&nbsp;204</var> Version 5.1, all characters X'00' through X'FF' are valid in a pattern presented to the User Language pattern matcher and the $CHKPAT function. </p>
<p>
As a result all characters are treated as valid, literal characters. The following error messages are no longer invoked for these characters.</p>
<p class="code">M204.1688: errortype IN PATTERN 'pattern'
AT CHARACTER char
AT CHARACTER char
 
M204.1689: errortype IN PATTERN 'pattern'  
M204.1689: errortype IN PATTERN 'pattern'
AT CHARACTER char
AT CHARACTER char
</p>
</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>
<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>
<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 764: Line 887:
</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>
===Continuing use of the escape character===
<b>Wildcard character</b>
<p>
<p>The wildcard character serves as a placeholder for 0 to 255 characters. You can use wildcard characters as follows:</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>
===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>
Line 776: Line 903:
</li>
</li>
</ul>
</ul>
<p>For example:</p>
<p>
For example:</p>
<table>
<table>
<tr class="head">
<tr class="head">
Line 787: Line 915:
</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 803: Line 933:
</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>
===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 (',,') 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 818: Line 950:
<p class="code">/A,B(XYZ)
<p class="code">/A,B(XYZ)
</p>
</p>
<b>Set begin and set end characters</b>
<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 833: Line 967:
<p class="code">/A,B(XYZ)
<p class="code">/A,B(XYZ)
</p>
</p>
<b>Range character</b>
<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 841: Line 977:
</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>
Line 849: Line 985:
</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>
Line 861: Line 999:
</li>
</li>
</ul>
</ul>
<p>For example:</p>
<p>
For example:</p>
<table>
<table>
<tr class="head">
<tr class="head">
Line 887: Line 1,026:
<p class="code">/A,B(XYZ)
<p class="code">/A,B(XYZ)
</p>
</p>
<b>Escape character</b>
<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>
===Escape character===
<p>The escape character works as shown in the following example.</p>
<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 &mdash; * + ' ( ) - / ! = # @--when you want them treated as a valid literal characters.</p>
<p>
The escape character works as shown in the following example.</p>
<table>
<table>
<tr class="head">
<tr class="head">
Line 897: Line 1,039:
<tr>
<tr>
<td>
<td>
<p class="code">!(800!)-*  
<p class="code">!(800!)-*
</p></td>
</p></td>
<td>(800)-244-3344</td>
<td>(800)-244-3344</td>
Line 903: Line 1,045:
<tr>
<tr>
<td>
<td>
<p class="code">J+++.  
<p class="code">J+++.
</p></td>
</p></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 926: Line 1,071:
</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 942: Line 1,089:
</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> is <code>#</code>, the FLUSH parameter must be reset before the numeric digit pattern character can be used during line editing.
<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 959: Line 1,108:
</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>
<p class="note"><b>Note:</b> If the value of the <var>[[ERASE parameter|ERASE]]</var> is <code>@</code>, the ERASE parameter must be reset before the alphabetic pattern character can be used during line editing.
====Pattern retrieval optimization====
<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>
Line 968: Line 1,119:
</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>
Line 985: Line 1,137:
</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>
Line 992: Line 1,145:
</p>
</p>
<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,153:
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>
<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>
<p>
====Use of full key for pattern matching====
Pattern retrieval performance also is affected by the complexity of the pattern specified. The more complex the pattern, the more processing is required.</p>
<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  
===Use of full key for pattern matching===
  fieldname IS LIKE 'COMPANY01DEPT05ACCT02*'    
<p>
END FIND  
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>
<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>
<p>
<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>
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>
===Transporting Large Object data using the Universal Buffer===
<p>
<p>The Universal Buffer is used to transport Large Object data to and from a client. The following is a sample request that moves Large Object data from a client to the <var class="product">Model&nbsp;204</var> server.</p>
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>
==Transporting Large Object data using the Universal Buffer==
<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>
<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</li>
Line 1,046: Line 1,208:
/* A statement to send the BUFFER to a client*/
/* A statement to send the BUFFER to a client*/
</p>
</p>
<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>
<p>
===IS PRESENT condition===
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>
<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>
==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="code">fieldname IS <var>[</var>NOT<var>]</var> PRESENT
</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 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>
<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 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>
===IS NOT PRESENT condition===
<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 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 FIND$ condition performs a FIND on a set of records that has been retrieved by a previous FIND statement. </p>
<b>Syntax</b>
<b>Syntax</b>
<p>The format of FIND$ is:</p>
<p>
<p class="code">FIND$ label  
The format of FIND$ is:</p>
<p class="code">FIND$ label
</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 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>
<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,262:
               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,269:
             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 GROUP1A statements retrieves records for SEX = M, DATE OF BIRTH between 200101 and 650101, and STATE = CALIFORNIA. The GROUP2A statements retrieves records for which SEX = MALE and either STATE not equal CALIFORNIA or DATE OF BIRTH not between 200101 and 650101.  </p>
====The 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 condition:      </p>
<p class="code">SFL$ value
</p>
</p>
<p>retrieves all records for which the sort field is less than the stated value.</p>
<p>
====The SFGE$ condition====
retrieves all records for which the sort field is less than the stated value.</p>
<p>For sorted files, the condition:    </p>
<p class="code">SFGE$ value  
===The SFGE$ condition===
<p>
For sorted files, the condition:    </p>
<p class="code">SFGE$ value
</p>
</p>
<p>retrieves all records for which the sort field is greater than or equal to the stated value.</p>
<p>
====Example using SFL$ and SFGE$====
retrieves all records for which the sort field is greater than or equal to the stated value.</p>
<p>In a sorted file in which the sort field is FULLNAME, this statement:</p>
===Example using SFL$ and SFGE$===
<p>
In a sorted file in which the sort field is FULLNAME, this statement:</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 DIEGO
               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>
<p>
===POINT$ condition===
retrieves all records of people who live in San Diego and whose full name starts with R.                </p>
<p>When a record is stored it is assigned an internal record number. In a FIND statement, the POINT$ condition retrieves all records that have internal record numbers greater than or equal to the stated value. If the given value is not a number, no records are retrieved.</p>
====When to use POINT$====
==POINT$ condition==
<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>
<p>
When a record is stored it is assigned an internal record number. In a FIND statement, the POINT$ condition retrieves all records that have internal record numbers greater than or equal to the stated value. If the given value is not a number, no records are retrieved.</p>
===When to use POINT$===
<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>
<b>Syntax</b>
<b>Syntax</b>
<p>The format of POINT$ is:</p>
<p class="code">POINT$ n  
<p>
The format of POINT$ is:</p>
<p class="code">POINT$ n
</p>
</p>
<b>Example</b>
<b>Example</b>
<p>This statement:</p>
<p>
This statement:</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===
<p>
<p>The FILE$ condition can be used as a condition in a FIND statement to restrict the files from which records are selected. </p>
<p>The restriction can apply to a group FIND or to a FIND that refers to the record set of an earlier group FIND. FILE$ can also be used to limit the reference context of a FIND statement when using Parallel Query Option/204. See [[Files, Groups, and Reference Context#Files, Groups, and Reference Context|Files, Groups, and Reference Context]].</p>
retrieves all records that have internal record numbers between 1500 and 2499, inclusive.</p>
==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>
The restriction can apply to a group FIND or to a FIND that refers to the record set of an earlier group FIND. FILE$ can also be used to limit the reference context of a FIND statement when using Parallel Query Option/204. See [[Files, Groups, and Reference Context#Files, Groups, and Reference Context|Files, Groups, and Reference Context]].</p>
<b>Syntax</b>
<b>Syntax</b>
<p>The format of FILE$ is:</p>
<p>
The format of FILE$ is:</p>
<p class="code">FILE$ {filename | =}
<p class="code">FILE$ {filename | =}
</p>
</p>
<b>Where</b>
<b>Where</b>
<ul>
<ul>
<li>filename is one of the following:</li>
<li>filename is one of the following:</li>
</li>
</li>
</ul>
</ul>
<p>A literal group member name, either without quotation marks or enclosed in single quotes</p>
<p>A literal remote file specification or file synonym</p>
<p>
<p>filename can not be represented by:</p>
<p>A %variable</p>
A literal group member name, either without quotation marks or enclosed in single quotes</p>
<p>A remote file specification enclosed entirely in quotes (for example, 'CLIENTS AT BOSTON')</p>
<p>
A literal remote file specification or file synonym</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>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).</li>
FILE$ CLAIMS89 OR FILE$ CLAIMS90).</li>
</li>
</li>
</ul>
</ul>
<p>The FILE$ condition is valid only in group context, and is supported in scattered group context.</p>
<p>
The FILE$ condition is valid only in group context, and is supported in scattered group context.</p>
<b>Example</b>
<b>Example</b>
<p>If a group consisting of the CLIENTS and CLAIMS90 files is open, the following statement retrieves records from CLIENTS but not CLAIMS90:</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>
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
<p class="code">FIND.RECS: FIND ALL RECORDS FOR WHICH
               FILE$ CLIENTS
               FILE$ CLIENTS
               POLICY NO = 100340
               POLICY NO = 100340
           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 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>
<b>Syntax</b>
<b>Syntax</b>
<p>The format of LOCATION$ is:</p>
<p>
The format of LOCATION$ is:</p>
<p class="code">LOCATION$ {location | =}
<p class="code">LOCATION$ {location | =}
</p>
</p>
Line 1,172: Line 1,433:
</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 LOCATION$ condition is supported in scattered group context, and 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 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 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 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 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,454:
           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 LIST$ condition as a condition in a FIND statement. See [[Lists#Lists|Lists]] for a detailed discussion of lists and the LIST$ 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>
<p>
&nbsp;</p>
==IN label retrieval==
<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>
<b>Syntax</b>
<b>Syntax</b>
<p>The format for the IN label clause used with a FIND statement is:</p>
<p>
The format for the IN label clause used with a FIND statement is:</p>
<p class="code">label.b: FIND ALL RECORDS IN label.a FOR WHICH
<p class="code">label.b: FIND ALL RECORDS IN label.a FOR WHICH
   retrieval conditions  
   retrieval conditions
</p>
</p>
<p>where label.a is the label of a preceding FIND statement.</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,477:
                 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,487:
             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 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>
[[Category:SOUL]]
[[Category:SOUL]]

Revision as of 16:21, 3 June 2013

Overview

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.

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 pattern of "c*" 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 Range retrieval operators for numeric retrievals.

Range retrieval operators

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 Operations on Multiply Occurring Fields 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.

Syntax

In addition to the equality condition, NAME = SMITH that is described in FIND statement, 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 Operations on Multiply Occurring Fields 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

  • 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.
  • 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 for more information about multiply occurring fields.

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 sequence listed in Interpretation of Boolean operators in retrievals:

Order of precedence for Boolean operators
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 Range retrieval.

The default comparison type for each operator is provided in Range retrieval.

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

Summary of field attributes and retrieval optimization 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 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:

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

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.

The characters used for pattern retrieval are summarized in 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 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

Continuing use of the escape character

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

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 for the eleven pattern matching characters — * + ' ( ) - / ! = # @--when you want them treated as a valid literal 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 is #, the FLUSH parameter 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 is @, the ERASE parameter 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 wish 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 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.

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.

Transporting Large Object data using the Universal Buffer

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 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: 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.

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.

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 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.

SFL$ and SFGE$ conditions

The SFL$ condition

For sorted files, the condition:

SFL$ value

retrieves all records for which the sort field is less than the stated value.

The SFGE$ condition

For sorted files, the condition:

SFGE$ value

retrieves all records for which the sort field is greater than or equal to the stated value.

Example using SFL$ and SFGE$

In a sorted file in which the sort field is FULLNAME, this statement:

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

retrieves all records of people who live in San Diego and whose full name starts with R.

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:

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

retrieves all records that have internal record numbers between 1500 and 2499, inclusive.

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 can not 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 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 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 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.