In addition to all of these ways to retrieve and iterate through a set of related records, ABL has a very powerful way to retrieve single records without needing a query or result set definition of any kind. This is the FIND statement.

The FIND statement uses this basic syntax:

Syntax

FIND [ FIRST | NEXT| PREV | LAST ]record[ WHERE ...] 
     [ USE-INDEX index-name]

Using the FIND statement to fetch a single record from the database is pretty straightforward. This statement reads the first Customer and makes it available to the procedure:

FIND FIRST Customer.

This statement fetches the first Customer in New Hampshire: 

FIND FIRST Customer WHERE Customer.State = "NH".

It gets more interesting when you FIND the NEXT record or the PREV record. This should immediately lead you to the question: NEXT or PREV relative to what? Even the FIND FIRST statement has to pick a sequence of Customers in which one of them is first. Although it might seem intuitively obvious that Customer 1 is the first Customer, given that the Customers have an integer key identifier, this is the record you get back only because the CustNum index is the primary index for the table (you could verify this by looking in the Data Dictionary). Without any other instructions to go on, and with no WHERE clause to make it use another index, the FIND statement uses the primary index. You can use the USE-INDEX syntax to force the AVM to use a particular index.

If you include a WHERE clause, the AVM chooses one or more indexes to optimize locating the record. This might have very counter-intuitive results. For example, here's a simple procedure with a FIND statement:

FIND FIRST Customer.
DISPLAY Customer.CustNum Customer.Name Customer.Country.

The following figure shows the expected result.

Figure 1. Result of a simple FIND procedure


You can see that Customer 1 is in the USA. Here's a variation of the procedure:

FIND FIRST Customer WHERE Customer.Country = "USA".
DISPLAY Customer.CustNum Customer.Name Customer.Country.

The following figure shows the not-so-expected result.

Figure 2. Result of variation on the simple FIND procedure


What happened here? If Customer 1 is the first Customer, and Customer 1 is in the USA, then why isn't it the first Customer in the USA? The AVM uses an index in the Country field to locate the first Customer in the USA, because that's the most efficient way to find it. That index, called the CountryPost index, has the PostalCode as its secondary field. If you rerun this procedure again and ask to see the PostalCode field instead of the Name field, you'll see why it came up first using that index, as shown in the following figure.

Figure 3. Result of the simple FIND procedure using PostalCode


The PostalCode is blank for this Customer, so it sorts first. Even if there is no other field in the index at all, that would only mean that the order of Customers within that index for a given country value would be undetermined. Only if the CustNum field is the next index component could you be sure that Customer 1 would come back as the first Customer in the USA.

These examples show that you must be careful when using any of the positional keywords (FIRST, NEXT, PREV, and LAST) in a FIND statement to make sure you know how the table is navigated.