PURPOSE: The FIND command is used to identify a group of records, called a Find Subset (FS), that may then be operated on by subsequent commands. FIND commands may be issued one after the other to refine the selected group of records. Each successive FIND command will either eliminate records from the current group or add new records to it.
The FIND command makes selective recordings and retrieval of records very simple. The commands KEEP, RESTORE and EVALUATE, operate on Find Subsets and allow subsets of records to be retained for later sessions, recall a saved subset or to create new FS’s from existing subsets.
The FIND command uses one of two methods for retrieving records:
It uses standard conditional expressions (IF; UNLESS or WHEN.
It searches on the basis of duplicate values in selected fields.
When a FIND command is issued, ACCENT R creates and maintains a set of pointers to locate selected records for the new FS. Successive FIND and SORT commands will not require any movement of records. Only the pointers are manipulated, resulting in significant performance advantages.
The FIND command has three forms which are treated separately in the SYNTAX description.
The first form of the FIND command defines a new FS by using a conditional clause to specify the selection criteria. FIND… IF; UNLESS can be used repeatedly to further define the retrieval conditions needed to get a particular selection of records. FIND… WHEN can only be used on the DS when an index is declared, not on FS’s.
The second form of the FIND command identifies records for the FS by checking for duplicate values in the specified field or fields. This command may be used to retrieve all records with duplicate field values, all records without duplicate field values, all records having the first occurrence of a field value, or all records not having the first occurrence of a field value. This form of the FIND command requires that the DS be sorted or indexed on the field(s) named.
This third form of the FIND command clears the FS or restores the previous FS.
FORM 1:
FIND [INCLUDE; ALSO] [/USE/ DOMAIN name] [WHEN clause] [{IF; UNLESS} clause]
|
are synonymous. This clause is used to search the DS on another criteria and add these record to the current subset. Without this option, the new FS contains only those records in the original FS that satisfy the new selection conditional. If INCLUDE or ALSO is specified, the new FS will contain all the records in the original FS, as well as all other records in the DS that satisfy the new selection conditional. No records will be included twice. In more technical terms: Without the option, the new FS is the intersection between the original FS and the new Find conditional. With the option, the new FS is the union between the two. |
|
|
/USE/ DOMAIN name |
specifies the domain to be used when an index is active. By default, the first domain will be used. This clause must precede the conditional clause(s). With FIND Form 1, the USE DOMAIN clause may be used to change domains in successive FIND commands. |
|
specifies record retrieval on keyed fields, using an index. This is the preferred retrieval method whenever the field is keyed in the index. Refer to the WHEN clause in Chapter 7. |
|
|
{IF; UNLESS} clause |
specifies record retrieval using a sequential search. This retrieval method can be used to select from either a DS or a FS. It can also be used when retrieval is through an index, either with a WHEN clause or alone. Refer to the IF; UNLESS clause in Chapter 7. |
FORM 2:
FIND [/USE/ DOMAIN name] {DUP/LICATE/; NONDUP/LICATE/; PRIME NONPRIME}
ON \\fields\
|
/USE/ DOMAIN name |
specifies the domain to be used when an index (DI) is active. By default, the first domain will be used. This clause must be specified first. With Form 2, the USE DOMAIN clause may only be used to create the first Find Subset from a Data Set. ACCENT R will process records in the logical order of the domain. Once a FS is active, subsequent FIND commands in Form 2 may not contain a USE DOMAIN clause. |
|
retrieves all records that have the same value in the specified fields. |
|
|
retrieves all records whose values in the specified fields are unique, i.e., are not duplicated by any other record in the DS. |
|
|
retrieves all records having the first occurrence of a field value for the specified fields. |
|
|
retrieves all records not having the first occurrence of a field value for the specified fields. |
|
|
ON \\fields\\ |
specifies the fields on which the DUPLICATE or PRIME operation will be performed. For non-indexed retrieval, the records must be sorted in the order of the ON clause. For indexed retrieval, the Domain specified must have the same sorted order. |
FORM 3:
FIND {PREVIOUS; LAST; ALL}
|
are synonymous, and either one restores the previous Find Subset. ACCENT R retains up to six previous FS’s at one time, and can back up a maximum of five times to restore these FS’s. If backing up beyond the number of retained FS’s, ACCENT R automatically clears all retained FS’s. |
|
|
clears all retained FS’s. FS’s are also automatically cleared by the QUIT, USE DS, USE NO DS, CLEAR DS, USE DBL, and USE NO DBL. |
Once a group of records has been identified as a Find Subset (FS), it may be used and processed by other ACCENT R commands. The SORT command affects only the presentation of the FS, whereas the ALTER command affects the Data Set (DS).
*USE DS BOOKS_DBM2
*EXTRACT SHOW TITLE_CODE,1B,PRICE
CP2264 20.00
CP3896 19.99
FL0839 7.00
FL1757 10.95
SF4567 7.70
SF6555 8.79
TR1234 5.50
TR9876 6.60
8 Record(s) Processed
*FIND IF PRICE IS BETWEEN 7.00 AND 11.00
4 Record(s) Found
F*ALTER SET PRICE*10 TO PRICE
4 Record(s) Processed
F*EXTRACT SHOW TITLE_CODE,1B,PRICE
FL0839 70.00
FL1757 109.50
SF4567 77.00
SF6555 87.90
4 Record(s) Processed
F*SORT ON -COST
4 Record(s) Processed
F*EXTRACT SHOW TITLE_CODE,1B,PRICE
FL1757 109.50
SF6555 87.90
SF4567 77.00
FL0839 70.00
4 Record(s) Processed
F*FIND ALL
*EXTRACT SHOW TITLE_CODE,1B,PRICE
CP2264 20.00
CP3896 19.99
FL0839 70.00
FL1757 109.50
SF4567 77.00
SF6555 87.90
TR1234 5.50
TR9876 6.60
8 Record(s) Processed
The following example shows the effect of the INCLUDE option. If a FS is current, a new FIND command without INCLUDE acts only on the records in the current FS. When the new FIND command has INCLUDE in it, it applies to the current FS plus the DS. Notice the use of FIND LAST to declare the last FS as the current one.
*USE DS BOOKS_DBM2
*EXTRACT SHOW TITLE_CODE,1B,PRICE,1B,TYPE_CODE
CP2264 20.00 computer
CP3896 19.99 computer
FL0839 70.00 for_lang
FL1757 109.50 for_lang
SF4567 77.00 sci_fi
SF6555 87.90 sci_fi
6 Record(s) Processed
*FIND IF TYPE_CODE = "computer"
2 Record(s) Found
F*EXTRACT SHOW TITLE_CODE,1B,PRICE
CP2264 20.00
CP3896 19.99
2 Record(s) Processed
F*FIND IF PRICE = 20.00
1 Record(s) Found
F*EXTRACT
CP2264 20.00
1 Record(s) Processed
F*FIND LAST
2 Record(s) Found
F*EXTRACT
CP2264 20.00
CP3896 19.99
2 Record(s) Processed
F*FIND INCLUDE IF SALES < 100.00
5 Record(s) Found
F*EXTRACT
CP2264 20.00
CP3896 19.99
FL0839 70.00
SF4567 77.00
SF6555 87.90
5 Record(s) Processed
F*
The FIND command can be used to easily retrieve and delete duplicate records in, for instance, a company EMPLOYEE DS. Only the first record with each Employee Number is retained. With the FIND command, retrieval on duplicate records can be accomplished with a single command. The DELETE command deletes the entire FS and also deletes the same records from the DS.
*USE DS NEW_BOOK
*FIND DUPLICATES ON BOOK_CODE
9 Record(s) Found
F*EXTRACT SHOW BOOK_CODE,1B,TITLE
CH0158 THE RED BALLON
CH0158 RAINBOW PONIES
NF0243 HIDDEN ISLANDS
NF0243 SEA LIFE
NF0243 VOLCANOES
SF0856 ROBOT GUIDE
SF0856 FUTURE MYSTERIES
TR0912 CAVERN MAPS
TR0912 OFF THE ROAD
9 Record(s) Processed
F*FIND NONPRIME ON EMPL_NUM
5 Record(s) Found
F*EXTRACT
CH0158 RAINBOW PONIES
NF0243 SEA LIFE
NF0243 VOLCANOES
SF0856 FUTURE MYSTERIES
TR0912 OFF THE ROAD
5 Record(s) Processed
F*DELETE
5 Record(s) Purged
F*FIND ALL
*EXTRACT
CH0158 THE RED BALLON
NF0243 HIDDEN ISLANDS
SF0856 ROBOT GUIDE
TR0912 CAVERN MAPS
4 Record(s) Processed
NOTES: Only records included in an active Find Subset (FS) will be affected by subsequent ACCENT R commands.
The system field @QUALIFIED will contain the number of records in the current FS when it is newly created.
FS’s may be saved in system files by the KEEP command and restored for use by the RESTORE command. Refer to these commands later in this chapter for more information.
The following table clarifies the effect of DUPLICATE, NONDUPLICATE, PRIME and ONPRIME. An 'x' indicates those records that are selected by each option. Note that the Data Set (DS) must be sorted or indexed on the field(s) named in the FIND command.
| On Field Value | Duplicate | Non-Duplicate | Prime | Nonprime |
| 100 | X | - | X | - |
| 100 | X | - | - | X |
| 100 | X | - | - | X |
| 105 | - | X | X | - |
| 225 | X | - | X | - |
| 225 | X | - | - | X |
EFFECTS OF OTHER COMMANDS ON FIND SUBSETS: When a Find Subset (FS) is active, the ACCENT R prompt is changed from "*" to "F*", to indicate that subsequent commands will not operate on the entire Data Set (DS). These two prompts are stored in the system fields @PROMPT and @FIND_PROMPT, respectively. It is possible to change these fields by using the SET command.
Almost all ACCENT R commands can operate on FS’s:
CHECK, CONVERT, COUNT, EXTRACT, MERGE, REPORT, SELECT, ALTER, CHANGE, UPDATE, DELETE, PURGE, ENTER and LOAD.
When an FS is active, only records in the DS that are included in the current Find Subset will be affected by these commands.
DELETE removes all previous FS’s, and saved FS objects (which are cataloged for this DS) are marked as obsolete.
All new records are appended to both the DS and the FS.
If a Data Index (DI) is active, all domains will be updated.
A DI can be activated through a USE DI command after a FS is active. The sequence of record retrieval will still be controlled by the FS, not by the DI domain; if further record retrieval criteria are to be specified, the WHEN clause should not be used, but an IF; UNLESS clause can be.
Only the FS is sorted with SORT. (i.e., only the pointers referencing the records in the FS are sorted). The actual DS (the physical order of the records) is not altered.
APPEND, COPY and RECORDS cannot operate with a FS.
QUIT, USE DS, USE NO DS, CLEAR DS, USE DBL, USE NO DBL, LINK (without the AND RETURN clause), and FIND ALL automatically remove the current FS unless it has been saved by the KEEP command.
SEE ALSO: KEEP, RESTORE, EVALUATE Commands