FIND statement

Locates a single record in a table and moves that record into a record buffer.

Data movement

Syntax

FIND [ FIRST | LAST | NEXT | PREV ] record 
     [constant ]
     [ OF table ]
     [ WHERE expression ]
     [ USE-INDEX index ]
     [ USING [ FRAME frame ] field 
       [ AND [ FRAME frame ] field ] ...
     ]
     [ SHARE-LOCK | EXCLUSIVE-LOCK | NO-LOCK ] 
     [ NO-WAIT ]
     [ NO-PREFETCH ]
     [ NO-ERROR ]

You can specify the OF, WHERE, USE-INDEX, and USING options in any order.

FIND CURRENT record 
     [ SHARE-LOCK | EXCLUSIVE-LOCK | NO-LOCK ]
     [ NO-WAIT ]
     [ NO-ERROR ]

FIRST

Finds the first record in the table that meets the characteristics you might have specified with record. If the buffer named in the record was preselected in a DO or REPEAT statement, FIND locates the first record in that preselected subset of records.

LAST

Finds the last record in the table that meets the specified characteristics of the record. If the buffer named in the record was preselected in a DO or REPEAT statement, FIND locates the last record in that preselected subset of records.

NEXT

Finds the next record in the table that meets the specified characteristics of the record. If no record has been found, the buffer contents specified by record are unknown, with a ROWID equal to the Unknown value (?). If the buffer named in the record was preselected in a DO or REPEAT statement, FIND locates the next record in that preselected subset of records.

PREV

Finds the previous record in the table. If no record has been found, the buffer contents specified by record are unknown, with a ROWID equal to the Unknown value (?). If the buffer named in the record was preselected in a DO or REPEAT statement, FIND locates the previous record in that preselected subset of records.

CURRENT

Refetches the current record in the buffer with the specified lock status.

record

Identifies the record you want to retrieve. The record parameter can be a reference to a database table or a defined buffer.

constant

The value of a single component, unique, primary index for the record you want.

FIND Customer 1.

ABL converts this FIND statement with the constant option of 1.

FIND Customer WHERE Customer.CustNum = 1.

The CustNum field is the only component of the primary index of the Customer table. If you use the constant option, you must use it once in a single Record phrase, and it must precede any other options in the Record phrase.

OF table

Qualifies the records by relating the record to a record in another table.

PROMPT-FOR Order.OrderNum. FIND Order USING OrderNum. DISPLAY Order. FIND Customer OF Order. DISPLAY Customer.

The OF option relates the order table to the Customer table, telling the AVM to select the Customer record related to the Order record currently being used. When you use OF, all fields participate in match criteria, if an index is multi-field. The relationship is based on having a UNIQUE index in one table. ABL converts the FIND statement with the OF option to the following:

FIND Customer WHERE Customer.CustNum = Order.CustNum.

You can access related tables using WHERE, whether or not the field names of the field or fields that relate the tables have the same name.

WHERE expression

Qualifies the records you want to access. The expression is a constant, field name, variable name, or expression whose value you want to use to select records. You can use the WHERE keyword even if you do not supply an expression.

FOR EACH Customer WHERE {*}

The WHERE clause may not work the same way against a DataServer as it does against the OpenEdge database. Refer to the appropriate DataServer Guide (Use the Microsoft SQL Data Server or Use the Oracle Data Server) for additional information on how this feature will perform.

Note: You cannot reference an OpenEdge BLOB or CLOB field in a WHERE clause. However, in OpenEdge DataServer for MS SQL Server, a non-legacy BLOB or CLOB server data type can be mapped to the OpenEdge CHARACTER data type in a WHERE clause operand as long as the total size of the resolved WHERE clause (which might include the CHARACTER-mapped value) does not exceed 30000 bytes.

USE-INDEX index

Identifies the index you want to use while selecting records. If you do not use this option, the AVM selects an index to use based on the criteria specified with the WHERE, USING, OF, or constant options.

USING [ FRAME frame ] field [ AND [ FRAME frame ] field ] . . .

One or more names of fields for selecting records. The field you name in this option must have been entered previously, usually with a PROMPT-FOR statement. The field must be viewed as a fill-in or text widget.

The USING option translates into an equivalent WHERE option.

PROMPT-FOR Customer.CustNum. FIND Customer USING Customer.CustNum.

This FIND statement is the same as the following statement:

FIND Customer WHERE Customer.CustNum = INPUT Customer.CustNum.

The CustNum field is a non-abbreviated index. However, consider this example:

PROMPT-FOR Customer.Name. FIND Customer USING Customer.CustName.

If the name field is an abbreviated index of the Customer table, ABL converts the FIND statement with the USING option into this following statement:

FIND Customer WHERE Customer.Name BEGINS INPUT Customer.Name.

Note that field can be expanded to be FRAME framefield.

SHARE-LOCK

Tells the AVM to put a SHARE-LOCK on records as they are read. Other users can still read a record that is share locked, but they cannot update it. By default, the AVM puts a SHARE-LOCK on a record when it is read, and automatically puts an EXCLUSIVE-LOCK on a record when it is modified (unless the record is already EXCLUSIVE-LOCKed).

If you use the SHARE-LOCK option and the AVM tries to read a record that is EXCLUSIVE-LOCKed by another user, the AVM waits to read the record until the EXCLUSIVE-LOCK is released. The AVM displays a message to the user of that procedure, identifying the table that is in use, the user ID of the user, and the tty of the terminal using the table.

If you are using a record from a work table, the AVM disregards the SHARE-LOCK option.

EXCLUSIVE-LOCK

Tells the AVM to put an EXCLUSIVE-LOCK on records as they are read. Other users cannot read or update a record that is EXCLUSIVE-LOCKed, except by using the NO-LOCK option. They can access that record only when the EXCLUSIVE-LOCK is released. The AVM automatically puts a SHARE-LOCK on a record when it is read and automatically puts an EXCLUSIVE-LOCK on a record when it is updated.

If a record is read specifying EXCLUSIVE-LOCK, or if a lock is automatically changed to EXCLUSIVE-LOCK by an update, a user's read or update will wait if any other user has the record SHARE-LOCKed or EXCLUSIVE-LOCKed.

When a procedure tries to use a record that is EXCLUSIVE-LOCKed by another user, the AVM displays a message identifying the table that is in use, the user ID of the user, and the tty of the terminal using the table.

If you are using a record from a work table, the AVM disregards the EXCLUSIVE-LOCK option.

NO-LOCK

Tells the AVM to put no locks on records as they are read, and to read a record even if another user has it EXCLUSIVE-LOCKed.

Other users can read and update a record that is not locked. By default, the AVM puts a SHARE-LOCK on a record when it is read (unless it is using a CAN-FIND function, which defaults to NO-LOCK), and automatically puts an EXCLUSIVE-LOCK on a record when it is updated (unless the record is already EXCLUSIVE-LOCKed). A record that has been read NO-LOCK must be reread before it can be updated, as shown in this example:

DEFINE VARIABLE rid AS ROWID NO-UNDO. FIND FIRST Customer NO-LOCK. rid = ROWID(Customer). FIND Customer WHERE ROWID(Customer) = rid EXCLUSIVE-LOCK.

If a procedure finds a record and it places it in a buffer using NO-LOCK and you then re-find that record using NO-LOCK, the AVM does not reread the record. Instead, it uses the copy of the record that is already stored in the buffer.

When you read records with NO-LOCK, you have no guarantee of the overall consistency of those records because another user might be in the process of changing them. For example, when a record is updated, changes to indexed fields are written immediately, but changes to other fields are deferred. In the meantime, the record is in an inconsistent state. For example, the following procedure might display a CustNum of 0 if another user's active transaction has created a record and assigned a value to the indexed field CustNum that is greater than 100:

FOR EACH Customer WHERE Customer.CustNum > 100 NO-LOCK: DISPLAY Customer.CustNum. END.

If you are using a record from a work table, the AVM disregards the NO-LOCK option.

NO-WAIT

Causes FIND to return immediately and raise an error condition if the record is locked by another user (unless you use the NO-ERROR option on the same FIND statement). For example:

FIND Customer USING cust-name NO-ERROR NO-WAIT.

Without the NO-WAIT option, the AVM waits until the record is available.

The AVM ignores NO-WAIT when it is used with work tables and databases that are only accessed by a single user.

NO-PREFETCH

Specifies that only one record is sent across the network at a time. If you are accessing a remote server and do not specify this option, the AVM might send more than one record from the server to the client in each network packet.

NO-ERROR

The NO-ERROR option is used to prevent the statement from raising ERROR and displaying error messages.

For the FIND statement with NO-ERROR, you can use the AVAILABLE function to test if FIND found a record.

Examples

This procedure produces a report that shows all the customers who bought a particular item, and the quantity that they bought. The procedure finds an item record, the order-lines that use that item, the order associated with each order-line, and the customer associated with each order.

r-find.p

REPEAT:
  PROMPT-FOR Item.ItemNum.
  FIND Item USING Item.ItemNum.
  DISPLAY Item.ItemNum Item.ItemName.
  REPEAT:
    FIND NEXT OrderLine OF Item.
    FIND Order OF OrderLine.
    FIND Customer WHERE Customer.CustNum = Order.CustNum.
    DISPLAY Customer.Name Order.OrderNum OrderLine.Qty (TOTAL).
  END.
END.

The FIND FIRST statement in the following procedure finds the first record with a name field value that alphabetically follows the name supplied by the user. The FIND NEXT statement uses the name index to find the next record in the table, using the name index.

r-find2.p

DEFINE VARIABLE start-name NO-UNDO LIKE Customer.Name.

REPEAT:
  SET start-name.
  FIND FIRST Customer WHERE Customer.Name >= start-name.
  REPEAT:
    DISPLAY Customer.Name.
    FIND NEXT Customer USE-INDEX name.
  END.
END.

Notes

• If a FIND statement fails, it indicates that the buffer named in record contains no record.
• If the AVM finds an old record in the record buffer when executing a FIND, it validates the record then writes it out. (If the record fails validation, the AVM returns an error message.) Then it clears the buffer and stores the located record in the record buffer.
• A FIND statement that does not supply FIRST, LAST, NEXT, or PREV is a unique FIND and must be able to locate, at most, one record based solely on the conditions in the expression or WHERE clause it is using.
• Fields referenced in the WHERE clause do not have to be indexed.
• WHERE conditions can include Boolean operations.
• Use caution when using conversion functions like STRING and DATE in WHERE expressions. If, for example, the -d startup parameter is used differently on the client and server sides, any conversion performed by DATE could cause different results depending on the settings.
• If a FIND NEXT or FIND PREV does not find another record, the AVM takes the end-key action. By default, this action is UNDO, LEAVE for a FOR EACH, REPEAT, or procedure block.
• See the DEFINE BUFFER statement reference entry for a description of how to use FIND on a PRESELECTed set of records.
• When you use the FIND statement, the AVM selects an index to use based on the WHERE condition or the USE-INDEX option.
• Your position in an index is established when you find a record and is only modified by subsequent record retrievals, not by CREATEs or by changing indexed field values. After a failed FIND, you cannot be certain of the cursor position.
• If you are using the FIND statement to find a record in a work table, you must use the FIRST, LAST, NEXT, or PREV option with the FIND statement.
• In a REPEAT block, if you use the FIND NEXT statement to find a record and then do an UNDO, RETRY of a block, the FIND NEXT statement reads the next record in the table, rather than the one found in the block iteration where the error occurred:

  REPEAT: FIND NEXT Order. DISPLAY Order. SET Order.OrderNum. SET Order.OrderDate Order.PromiseDate. END.

  The AVM does an UNDO, RETRY if there is an error and you explicitly use the UNDO, RETRY statement, or if you press END-ERROR on the second or later windows interaction in a block.

  Here, if you press END-ERROR during the second SET statement, the AVM displays the next record in the table.

  If you are using a FOR EACH block to read records, and do an UNDO, RETRY during the block, you see the same record again rather than the next record.

  If you want to use a REPEAT block and want to see the same record in the event of an error, use the RETRY function:

  REPEAT: IF NOT RETRY THEN FIND NEXT Order. DISPLAY Order. SET Order.OrderNum. SET Order.OrderDate Order.PromiseDate. END.

• When you use FIND NEXT or FIND PREV to find a record after updating another record, be careful not to lose your updates in case the record you want to find is unavailable.

  FIND FIRST Customer. REPEAT: UPDATE Customer. FIND NEXT Customer. END.

  In this example, if the FIND NEXT statement fails to find the customer record, any changes made during the UPDATE statement are undone. To avoid this, use the following technique:

  FIND FIRST Customer. REPEAT: UPDATE Customer. FIND NEXT Customer NO-ERROR. IF NOT AVAILABLE Customer THEN LEAVE. END.

• After you use the FIND LAST statement to find the last record in a table, the AVM positions the index cursor on that record. Any references within the same record scope to the next record fail. For example:

  FIND LAST Customer. RELEASE Customer. DISPLAY AVAILABLE Customer. REPEAT: FIND NEXT Customer. DISPLAY Customer.Name. END.

  In this example, the RELEASE statement releases the last Customer record from the Customer record buffer and the following DISPLAY statement displays FALSE because the Customer record is no longer available. However, the index cursor is still positioned on that last record. Therefore, the FIND NEXT statement fails.

• If you use FIND . . . WHERE ROWID rowid = . . . on a PRESELECTed list of records, the temporary preselect index cursor is not reset. So, FIND NEXT does not find the record that follows record rowid in the preselected list. (See the DO statement and REPEAT statement reference entries for details.)
• When you use a FIND NEXT or FIND PREV statement in a subprocedure to access a record from a shared buffer, remember the following:
  • When you run an ABL procedure, the AVM creates a cursor indicator for each index accessed through a FIND statement in the procedure and each NEW buffer defined in the procedure. A cursor indicator serves as an anchor for index cursors associated with a table or buffer. An index cursor is attached to the cursor indicator when you enter a block of code where a record buffer is scoped. If two different indexes are used for the same record buffer within a single block of code, two index cursors are attached to the same cursor indicator. When the program control leaves the block where a record buffer is scoped, all index cursors attached to the cursor indicator are released.
  • When the AVM encounters a subprocedure in a procedure, it constant, field name, variable name, or checks through the existing index cursors before creating any other index cursors required by the statements in the subprocedure.
  • If the USE-INDEX of the FIND NEXT or FIND PREV statement in a subprocedure accesses an index cursor for a shared buffer that existed prior to the beginning of the subprocedure, the FIND NEXT or FIND PREV statement returns the next or previous record for the shared buffer, based upon the last record found in that buffer and the USE-INDEX of the FIND statement.
  • If the USE-INDEX of the FIND NEXT or FIND PREV statement in a subprocedure accesses an index cursor created for a shared buffer at the beginning of the subprocedure, the FIND NEXT or FIND PREV statement returns the first or last record for the shared buffer, based upon the USE-INDEX of the FIND statement.
• If a field or variable referenced with FIND is used in more than one frame, then the AVM uses the value in the frame most recently introduced in the procedure. To make sure you are using the appropriate frame, use the FRAME option with the FIND function to reference a particular frame.
• When a FIND statement executes, any FIND trigger defined for the table is executed.
• The FIND CURRENT statement is useful for maintaining small transaction size in updates. For an example, see the CURRENT-CHANGED function reference entry.
• FIND triggers do not execute for a FIND CURRENT statement.
• ABL does not allow a FIND statement within a FOR EACH block unless you specify a different table than the one referenced in the FOR EACH block. When you attempt to compile the following example, ABL returns the error message "FIND cannot be processed for a FOR EACH mode record":

  FOR EACH Customer NO-LOCK:   FIND CURRENT Customer. END.

• ABL restricts the FIND statement within a PRESELECT block in the following situations:
  • You cannot specify a lock option on the FIND statement. You must specify it in the PRESELECT phrase. Attempting to compile the following example produces the error message "LOCK keyword illegal on FIND within a PRESELECT for the same table":

    DO PRESELECT EACH Customer:   FIND NEXT Customer NO-LOCK. END.

  • You cannot specify a unique FIND or a

    FIND CURRENT

    for the same table. The following example produces the error message "Unique FIND not allowed within a PRESELECT on the same table" when you try to compile it:

    DO PRESELECT EACH Customer:   FIND Customer 5. END.

See also

AMBIGUOUS function, AVAILABLE function, CAN-FIND function, CURRENT-CHANGED function, DEFINE BUFFER statement, ERROR-STATUS system handle, FOR statement,GET statement, LOCKED function, NEW function (record buffers), NO-ERROR option, PRESELECT phrase