The value of NUM-RESULTS does not always increment as you execute GET NEXT statements and operate on each row, however. There are various factors that force the AVM to retrieve all the results in advance of presenting you with any data.
One of these is under your direct control: the PRESELECT option on the OPEN QUERY statement. When you use a PRESELECT EACH rather than a FOR EACH statement to define the data selection, you are telling the AVM to retrieve all the records that satisfy the query in advance and to save off their record identifiers in temporary storage. Then the AVM again retrieves the records using their identifiers as you need them. As discussed in OPENand CLOSE QUERY statements, you typically use the PRESELECT option to make sure that the set of records is not disturbed by changes that you make as you work your way through the list, such as changing a key value in such a way as to change a record's position in the list.
To see visible evidence of the effect of the PRESELECT keyword in your OPEN QUERY statement:
1. Change the OPEN QUERY statement in the sample procedure:
OPEN QUERY CustQuery PRESELECT EACH Customer WHERE State = "LA".
2. Run the procedure again to see the different value of NUM-RESULTS:
All the records are pre-retrieved. Therefore, the value of NUM-RESULTS is the same no matter what record you are positioned to. This means that you could use the PRESELECT option to display, or otherwise make use of, the total number of records in the results list before displaying or processing all the data.
Another factor that can force the AVM to pre-retrieve all the data is a sort that cannot be satisfied using an index.
To see an example of pre-retrieved data:
1. Change the OPEN QUERY statement back to use a FOR EACH block and then try sorting the data in the query by the Customer Name:
OPEN QUERY CustQuery FOR EACH Customer
WHERE Customer.State = "LA" BY Customer.Name.
2. Run the query:
The Name field is indexed, so the AVM can satisfy the BY phrase and present the data in the sort order you want by using the index to traverse the database and retrieve the records.
3. By contrast, try sorting on the City field:
OPEN QUERY CustQuery FOR EACH Customer
WHERE Customer.State = "LA" BY Customer.City.
4. Add the City field to the DISPLAY list and rerun the procedure to see the result:
There is no index on the City field, so the AVM has to retrieve all 13 of the records for Customers in Louisiana in advance to sort them by the City field before presenting them to your procedure. Therefore, NUM-RESULTS is equal to the total number of records from the beginning, as soon as the query is opened.
