Results lists

A results list is a list of ROWIDs that satisfy a query. The results list allows you to quickly access the records in the record set you define, and allows ABL to make the records available one at a time, as needed.

When more than one row satisfies a query, the AVM doesn't lock all of the records and hold them until you release them. Instead, when a specific record is needed, the AVM uses the record's ID to go directly to the record, locking only that record. By going directly to the record, the AVM also ensures that you have the latest copy of the record.

When you open a query, the AVM does not normally build the entire results list. Instead it initializes the results list and adds to it as needed. The NUM–RESULTS function returns the number of records currently in the results list. This is not necessarily the total number of records that satisfy the query.

Whether and when the AVM builds the results list depends on the type of the query. As shown in Table 4, the DO or REPEAT PRESELECT statements always use a results list, while the FOR EACH and OPEN QUERY statements sometimes use a results list.

Queries have the following characteristics:

Scrolling versus non-scrolling — A query is scrolling if you specify SCROLLING in the DEFINE QUERY statement or if you define a browse for the query. You can use the REPOSITION statement to change your current position within the results list. For a non-scrolling query, you can only move sequentially through the rows by using the FIRST, LAST, NEXT, and PREV options of the GET statement. Scrolling queries must use a results list (often initially empty); non-scrolling queries might not.

Index-sorted — If the order of the rows in the query can be determined by using a single index, the query is index-sorted. For an index-sorted query, The AVM can use the index to order records without a results list. However, if the query requires additional sorting it must also be presorted.

Presorted — If the query requires any sorting without an index or with multiple indexes, it must be presorted. For a presorted query, the AVM must read all the records, sort them, and build the complete results list before any records are fetched.

Preselected versus non-preselected — You can force the AVM to build a preselected results list by specifying PRESELECT on the OPEN QUERY, DO, or REPEAT statement.

The following table summarizes how the results list is built for each type of query.

Table 5. Results lists for specific query types
Query type	Results list
Non-scrolling, index-sorted, no preselection	None.
Scrolling, no sorting, no preselection	Empty list¹ established when query is opened. Records are added to the results list as needed.
Presorted or preselected	Complete list built when query is opened.

¹ If a browse is defined for the query, the results list initially contains one row.

There are two cases where the AVM has to build the entire results list when you first open the query:

When you have explicitly used the PRESELECT option. In this case, the AVM performs a preselection phase in which it reads each record of the query. You can specify the lock type to use during the preselection phase. (For an OPEN QUERY, the lock type you specify in the OPEN QUERY statement is used for the preselection phase.) These locks are released immediately unless the preselection occurs within a transaction.

When you have not used the PRESELECT option, but have specified sort criteria that cannot be performed using an index. In this case, the AVM performs a presort phase in which it reads each record of the query with NO–LOCK and builds the results list.

For example, the following statement explicitly uses the PRESELECT option and forces the AVM to build the entire results list immediately:

If you had used FOR instead of PRESELECT, the AVM would not have had to build the entire results list because it uses the primary index to fetch the records. It could use this index to find the first or last record for the query; it only needs to search forward or backward through the index until it finds a record that satisfies the WHERE clause.

You can use the PRESELECT option of the OPEN QUERY statement when you need to know immediately how many records satisfy the query or you can use it to immediately lock all the records that satisfy the query.

The AVM also builds a complete results list when you open a query with a sort condition that cannot be resolved using a single index. Suppose you open a query on the Customer table as follows:

Because there is no index for the city field, the AVM must retrieve all the records that satisfy the query (in this case, all the Customer records), perform the sort, and build the entire results list before any records can be fetched. Until it performs this sort, the AVM cannot determine the first or last record for the query. If an index were defined on the city field, the AVM could use that index to fetch the records in sorted order (forwards or backwards) and would not need to build the results list in advance.

If the sort conditions for a query can be resolved using a single index, you can use the GET statement with the FIRST, LAST, NEXT, and PREV options on that query. For example, the following query is sorted using the primary index:

OPEN QUERY custqry FOR EACH Customer.
GET FIRST custqry.
DISPLAY Customer.CustNum Customer.Name.  /* Display first record */
PAUSE.
GET NEXT custqry.
DISPLAY Customer.CustNum Customer.Name.  /* Display second record */
PAUSE.
GET LAST custqry.
DISPLAY Customer.CustNum Customer.Name.  /* Display last record */
PAUSE.
GET PREV custqry.
DISPLAY Customer.CustNum Customer.Name.  /* Display second-to-last record */

Because the sorting is done with a single index, you can move freely forwards and backwards within the query.

Note: If you want to use the REPOSITION statement on a query, you must make the query scrolling by specifying the SCROLLING option in a DEFINE QUERY statement.