You use a form of the GET statement to change the current position within the record set that the OPEN QUERY statement defines. This is the syntax for the GET statement:

Syntax

GET [ FIRST | NEXT | PREV | LAST | CURRENT ]query-name.

The query must be open before you can use a GET statement. If the query involves a join, the AVM populates all the buffers used in the query on each GET statement. As noted earlier, a record can remain current through multiple GET statements if a second table in the query has multiple records matching the record for the first table. This would be the case for a Customer and its Orders, for example. A series of GET NEXT statements leaves the same Customer record in its buffer as long as there is another matching Order record to read into the Order buffer.

When you first open a query, it is positioned in effect before the first record in the result set. Either a GET FIRST or a GET NEXT statement positions to the first record in the result set.

If you execute a GET NEXT statement when the query is already positioned on the last record, then the query is positioned effectively beyond the end of the result set. A GET PREV statement then repositions to the last record in the result set. Likewise, a GET PREV statement executed when already on the first record results in the query being positioned before the first record, and a GET FIRST or GET NEXT statement repositions to the first record. When the query is repositioned off the beginning or off the end of the result set, no error results. You can use the AVAILABLE function that you are already familiar with to check whether you have positioned beyond the result set. For example, this code opens a query and cycles through all the records in its result set, simply counting them as it goes:

DEFINE VARIABLE iCount AS INTEGER INITIAL 0 NO-UNDO.

DEFINE QUERY CustOrd FOR Customer, Order.

OPEN QUERY CustOrd FOR EACH Customer, EACH Order OF Customer.
GET FIRST CustOrd.

DO WHILE AVAILABLE Customer:
  iCount = iCount + 1.
  GET NEXT CustOrd.
END.
DISPLAY iCount.

The following figure shows the result.

Figure 1. Result of GET statement example

The GET FIRST statement is needed to make a record available before the IF AVAILABLE statement is first encountered. Otherwise, the AVAILABLE test would fail before the code ever entered the loop.

Note also that the AVAILABLE function must take a buffer name as its argument, not the name of the query. If the query involves an outer join, then you should be careful about which buffer you use in the AVAILABLE function. If you name a buffer that could be empty because of an outer join (such as an empty Order buffer for a Customer with no Orders), then your loop could terminate prematurely. On the other hand, you might want your application logic to test specifically for the presence of one buffer or another in order to take special action when one of the buffers has no record.