Release 10.1A: OpenEdge Development:
Progress 4GL Handbook

> ## Data access without looping — the FIND statement

> In addition to all of these ways to retrieve and iterate through a set of related records, Progress 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:

>     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 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 Progress to use a particular index.

> If you include a `WHERE` clause, Progress 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 CustNum Name Country.

> Figure 6–15 shows the expected result.

> Figure 6–15: 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 Country = "USA".
>     DISPLAY CustNum Name Country.

> Figure 6–16 shows the not-so-expected result.

> Figure 6–16: 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? Progress 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 Figure 6–17.

> Figure 6–17: 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.

> ##### Index cursors

> To understand better how Progress navigates through a set of data, you need to understand the concept of index cursors. When you retrieve a record from the database using any of the statements you’ve seen in this chapter, Progress keeps track of the current record position using an index cursor—a pointer to the record, using the location in the database indexes of the key value used for retrieval.

> When you execute the statement `FIND FIRST Customer`, for example, Progress sets a pointer to the record for Customer 1 within the CustNum index. If you execute the statement `FIND FIRST Customer WHERE Country = “USA”`, Progress points to Customer 1025 through the CountryPost index.

> When you execute another `FIND` statement on the same table using one of the directional keywords, Progress can go off in any direction from the current index cursor location, depending on the nature of the statement. By default, it reverts to the primary index. Here’s an example that extends the previous one slightly:

>     FIND FIRST Customer WHERE Country = "USA".
>     DISPLAY CustNum NAME Country.
>     REPEAT:
>          FIND NEXT Customer NO-ERROR.
>          IF AVAILABLE Customer THEN
>               DISPLAY CustNum Name FORMAT "x(20)" Country PostalCode.
>          ELSE LEAVE.
>     END.

> ##### Using the FIND statement in a REPEAT block

> Notice the use of the `REPEAT` block to cycle through the remaining Customers. Within that block, you must write a `FIND` statement to get the next Customer because the `REPEAT` block itself, unlike the `FOR EACH` block, does not do the navigation for you. Also, the `REPEAT` block does not automatically terminate when the end of the Customers is reached, so you need to program the block with these three actions:

> 1. You must do the `FIND` with the `NO-ERROR` qualifier at the end of the statement. This suppresses the error message that you would ordinarily get when there is no next Customer.

> 2. You must use the `AVAILABLE` keyword to check for the presence of a Customer and display fields only if it evaluates to true.

> 3. You must write an `ELSE` statement to match the `IF-THEN` statement, to leave the block when there is no Customer available. Otherwise, your block goes into an infinite loop when it reaches the end of the Customer records. And notice that this truly is a separate statement. The `IF-THEN` statement ends with a period and the `ELSE` keyword begins a statement of its own.

> All of these are actions that the `FOR EACH` block does for you as it reads through the set of Customers. In the `REPEAT` block, though, where you’re doing your own navigation, you need to do these things yourself.

> Remember also that the `REPEAT` block scopes the statements inside the block to its own frame, unless you tell it otherwise. Therefore, you get one frame for the `FIRST` Customer and a new frame for all the Customer records retrieved within the `REPEAT` block.

> The keyword `AVAILABLE` is a Progress 4GL built-in function, so its one argument properly belongs in parentheses, as in `IF AVAILABLE (Customer)`. However, to promote the readability of the 4GL statement, the syntax also accepts the form as if it were a phrase without the parentheses, as in `IF AVAILABLE Customer`. This alternative is not generally available with other built-in functions.

> Finally, the `FORMAT “X(20)”` phrase reduces the display size of the Name field from its default (defined in the Data Dictionary) of 30 characters, to make room for the PostalCode field.

> ##### Switching indexes between FIND statements

> So what Customer do you expect to see as the next Customer after retrieving the first Customer using the CountryPost index (because of the `WHERE` clause)? If you remember that the default is always to revert to the primary index, then the result shown in Figure 6–18 should be clear.

> Figure 6–18: Result of using the primary index

> Looking at the sequence of records displayed in the frame for the `REPEAT` block, it’s clear that Progress is using the primary index (the CustNum index) to navigate through the records. This is unaffected by the fact that the initial `FIND` was done using the CountryPost index, because of its `WHERE` clause.

> What if you want to continue retrieving only Customers in the USA? In this case, you need to repeat the `WHERE` clause in the `FIND` statement in the `REPEAT` block:

>     FIND FIRST Customer WHERE Country = "USA".
>     DISPLAY CustNum NAME Country.
>     REPEAT:
>          FIND NEXT Customer WHERE Country = "USA" NO-ERROR.
>          IF AVAILABLE Customer THEN
>               DISPLAY CustNum NAME FORMAT "x(20)" Country PostalCode.
>          ELSE LEAVE.
>     END.

> Each `FIND` statement is independent of any other `FIND` statement, even if it refers to the same table, so the `WHERE` clause does not carry over automatically. If you do this, then Progress continues to use the CountryPost index for the retrieval, as the output in Figure 6–19 shows.

> Figure 6–19: Result of using the CountryPost index for record retrieval

> Because the PostalCode is the second field in the index used, the remaining records come out in PostalCode order.

> ##### Using a USE-INDEX phrase to force index selection

> You can also force a retrieval sequence with the `USE-INDEX` phrase. For instance, if you want to find the next set of Customers based on the Customer name, you can use the Name index, which contains just that one field:

>     FIND FIRST Customer WHERE Country = "USA".
>     DISPLAY CustNum NAME Country.
>     REPEAT:
>          FIND NEXT Customer WHERE Country = "USA" USE-INDEX NAME NO-ERROR.
>          IF AVAILABLE Customer THEN
>               DISPLAY CustNum NAME FORMAT "x(20)" Country PostalCode.
>          ELSE LEAVE.
>     END.

> The output shown in Figure 6–20 confirms that Progress is walking through the records in Name order, starting with the name of the first Customer in the USA.

> Figure 6–20: Result of forcing index selection

> This technique can be very valuable in expressing your business logic in your procedures. You might need to identify a record based on one characteristic and then retrieve all other records (or perhaps just one additional record) based on some other characteristic of the record you first retrieved. This is one of the most powerful ways in which Progress lets you define your business logic without the overhead and cumbersome syntax required to deal with all data access in terms of sets.

> ##### Doing a unique FIND to retrieve a single record

> Very often you just need to retrieve a single record using selection criteria that identify it uniquely. In this case, you can use a `FIND` statement with no directional qualifier. For example, you can identify a Customer by its Customer number. This is a unique value, so you can use the following `FIND` statement:

>     FIND Customer WHERE CustNum = 1025.
>     DISPLAY CustNum NAME Country.

> Figure 6–21 shows the result.

> Figure 6–21: Result of unique FIND

> You need to be sure when you do this that only one record satisfies the selection criteria. Otherwise, you get an error at run time.

> There’s also a shorthand for this `FIND` statement:

>     FIND Customer 1025.

> You can use this shorthand form if the primary index is a unique index (with no duplication of values), the primary index contains just a single field, and you want to retrieve a record using just that field. You can only use this form when all these conditions are true, so it’s not likely to be one you use frequently. Also, this shorthand form makes it harder to determine your criteria. It can break due to changes to the data definitions (for example, if someone went in and added another field to the CustNum index), so it’s better to be more specific and use a `WHERE` clause to identify the record.

> ##### Using the CAN-FIND function

> Often you need to verify the existence of a record without retrieving it for display or update. For example, your logic might need to identify each Customer that has at least one Order, but you might not care about retrieving any actual Orders. To do this, you can use an alternative to the `FIND` statement that is more efficient because it only checks index entries wherever possible to determine whether a record exists, without going to the extra work of retrieving the record itself. This alternative is the `CAN-FIND` built-in function. `CAN-FIND` takes a single parameter, which can be any record selection phrase. The `CAN-FIND` function returns true or false depending on whether the record selection phrase identifies exactly one record in the database.

> For example, imagine that you want to identify all Customers that placed Orders as early as 1997. You don’t need to retrieve or display the Orders themselves, you just need to know which Customers satisfy this selection criterion. Here’s a simple procedure that does this:

>     FOR EACH Customer WHERE Country = "USA":
>        IF CAN-FIND (FIRST Order OF Customer WHERE OrderDate < 1/1/98)
>             THEN DISPLAY CustNum Name.
>        ELSE DISPLAY CustNum "No 1997 Orders" @ Name.
>     END.

> This procedure uses a little display trick you haven’t seen before. If the Customer has any Orders for 1997, then the procedure displays the Customer name. Otherwise, it displays the text phrase No 1997 Orders. If you include that literal value in the `DISPLAY` statement, it displays in its own column as if it were a field or a variable. To display it in place of the Name field, use the at-sign symbol (@). Figure 6–22 shows the result.

> Figure 6–22: Result of CAN-FIND function procedure

> The `CAN-FIND` function takes the argument `FIRST Order OF Customer WHERE OrderData < 1/1/98`. Why is the `FIRST` keyword necessary? The `CAN-FIND` function returns true only if exactly one record satisfies the selection criteria. If there’s more than one match, then it returns false—without error—just as it would if there was no match at all. For example, if you remove the `FIRST` keyword from the example procedure and change the literal text to be No unique 1997 Order, and rerun it, then you see that most Customers have more than one Order placed in 1997:

>     FOR EACH Customer WHERE Country = "USA":
>        IF CAN-FIND (Order OF Customer WHERE OrderDate < 1/1/98)
>             THEN DISPLAY CustNum Name.
>        ELSE DISPLAY CustNum "No unique 1997 Order" @ Name.
>     END.

> After you page through the results, you see just a few records that don’t satisfy the criteria, as shown in Figure 6–23.

> Figure 6–23: Result of CAN-FIND function procedure without FIRST keyword

> Because you don’t get an error if there’s more than one match, it’s especially important to remember to define your selection criteria so that they identify exactly one record when you want the function to return true.

> The `CAN-FIND` function is more efficient than the `FIND` statement because it does not actually retrieve the database record. If the selection criteria can be satisfied just by looking at values in an index, then it doesn’t look at the field values in the database at all. However, this means that the record referenced in the `CAN-FIND` statement is not available to your procedure. For example, this variation on the example tries to display the OrderDate from the Order record as well as the Customer fields:

>     FOR EACH Customer WHERE Country = "USA":
>        IF CAN-FIND (FIRST Order OF Customer WHERE OrderDate < 1/1/98)
>             THEN DISPLAY CustNum Name OrderDate.
>        ELSE DISPLAY CustNum "No 1997 Orders" @ Name.
>     END.

> This results in the error shown in Figure 6–24, because the Order record is not available following the `CAN-FIND` reference to it.

> Figure 6–24: CAN-FIND error message

> If you need the Order record itself then you must use a form that returns it to you:

>     FOR EACH Customer WHERE Country = "USA":
>        FIND FIRST Order OF Customer WHERE OrderDate < 1/1/98 NO-ERROR.
>        IF AVAILABLE Order THEN
>             DISPLAY Customer.CustNum NAME OrderDate.
>        ELSE DISPLAY "No 1997 Orders" @ NAME.
>     END.

> When you run this code, you see the OrderDate as well as the Customer fields except in those cases where there is no Order from 1997, as shown in Figure 6–25.

> Figure 6–25: FIND FIRST Order result

> The samples so far have shown the `CAN-FIND` function in an `IF-THEN` statement. You can also use it anywhere where a logical (true/false) expression is valid in a `WHERE` clause, such as this:

>     FOR EACH Customer WHERE Country = "USA" AND
>          CAN-FIND (FIRST Order OF Customer WHERE OrderDate < 1/1/98):
>          DISPLAY Customer.CustNum NAME.
>     END.

> The next chapter continues the discussion on building complex procedures, with details on record buffers and record scope.

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095