Release 10.1A: OpenEdge Development:
Programming Interfaces

> ## Results lists

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

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

> When you open a query, Progress 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 Progress builds the results list depends on the type of the query. As shown in Table 1–3, 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, Progress 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, Progress 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 Progress to build a preselected results list by specifying `PRESELECT` on the `OPEN` `QUERY`, `DO`, or `REPEAT` statement.

> Table 1–4 summarizes how the results list is built for each type of query.

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

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

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

> - When you have explicitly used the `PRESELECT` option. In this case, Progress 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, Progress 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. This forces Progress to build the entire results list immediately:

>     OPEN QUERY cust-query PRESELECT EACH customer WHERE credit-limit > 1500.

> If you had used `FOR` instead of `PRESELECT`, Progress 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.

> Progress 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:

>     OPEN QUERY cust-query FOR EACH customer BY city.

> Because there is no index for the city field, Progress 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, Progress cannot determine the first or last record for the query. If an index were defined on the city field, Progress 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 cust-num name.    /* Display first record */
>     PAUSE.
>     GET NEXT custqry.         /* Display second record */
>     DISPLAY cust-num name.
>     PAUSE.
>     GET LAST custqry.         /* Display last record */
>     DISPLAY cust-num name.
>     PAUSE.
>     GET PREV custqry.
>     DISPLAY cust-num 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.

> ##### Navigating a Results list

> As shown in Table 1–3, results lists are associated with the `OPEN` `QUERY` and `GET` statements. However, Progress only guarantees a results list if you first define the query with the `SCROLLING` option:

>     DEFINE QUERY custqry FOR customer SCROLLING.

> This option indicates to Progress that you want to use the results list for multi-directional navigation.

> You can use the `REPOSITION` statement to specify how many places forward or backward you want to move, so that you can skip over a given number records. It also allows you to move to a specific `ROWID`.

> The `REPOSITION` statement changes your location in the results list but does not actually fetch the record (unless the query is associated with a browse widget). To actually fetch records in a results list, you use the `GET` statement. The following example illustrates how the `REPOSITION` statement works:

>     DEFINE QUERY q FOR customer SCROLLING.
>     DEFINE VARIABLE rid AS ROWID.       /* to save the ROWID of cust 4 */
>     OPEN QUERY q FOR EACH cust.
>     GET NEXT q.                 /* gets cust no. 1 */
>     GET NEXT q.                 /* gets cust no. 2 */
>                                 /* query is positioned ON cust 2 */
>     GET PREV q.                 /* gets cust no. 1 */
>     REPOSITION q FORWARD 0.     /* query is positioned BETWEEN cust 1 and 2 */
>     GET NEXT q.                 /* gets cust no. 2 */
>                                 /* query is positioned ON cust 2 */
>     REPOSITION q FORWARD 1.     /* query is positioned BETWEEN cust 3 and 4 */
>     GET NEXT q.                 /* gets cust no. 4 */
>     rid = ROWID(cust).          /* query is positioned ON cust 4 */
>     REPOSITION q BACKWARD 2.    /* query is positioned BETWEEN cust 2 and 3 */
>     GET PREV q.                 /* gets cust no. 2 */
>     REPOSITION q TO ROWID(rid). /* query is positioned BETWEEN cust 3 and 4 */ GET
>     NEXT q.                     /* gets cust no. 4 */

> After a record is fetched (with a `GET` statement), the results list position is on the `ROWID`, so that `GET` `NEXT` gets the next record, and `GET` `PREV` gets the previous record. After a `REPOSITION`, the position is always between two records. Thus, `REPOSITION` `FORWARD 0` repositions the results list immediately after the current record. `GET` `NEXT` fetches the next record; `GET` `PREV` fetches the previous record. `REPOSITION` `FORWARD 1` repositions the results list between the next record and the record after it.

> To find the total number of rows in a results list, you can use the `NUM–RESULTS` function. To find the current position within a results list, you can use the `CURRENT–RESULT–ROW` function.

> As Table 1–3 shows, Progress also creates results lists for `FOR` `EACH` statements and for `DO` and `REPEAT` statements with the `PRESELECT` phrase. However, you cannot navigate freely through a results list created for the `FOR` `EACH` statement. If the results list was created for the `FOR` `EACH` statement, then Progress automatically steps through the results list in sorted order:

>     FOR EACH customer BY name:
>        DISPLAY name city state.
>     END.

> Within a `PRESELECT` block, you can use the `FIND` statement to move backwards and forwards through the results list:

>     /* This code fragment displays all customers in descending order */
>     DO PRESELECT EACH customer:
>       FIND LAST customer.    /* last position in list */
>       DISPLAY cust-num name WITH FRAME a DOWN.
>       REPEAT:
>         FIND PREV customer.  /* move backward through list */
>         DOWN WITH FRAME a.
>         DISPLAY cust-num name WITH FRAME a.
>       END.
>     END.

> Note: A powerful way of navigating a record set is with the browse widget. For more information, see OpenEdge Development: Progress 4GL Handbook .

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