Release 10.1A: OpenEdge Development:
Progress 4GL Handbook

> ## Identifying the current row in the query

> As you move through the results list, Progress keeps track of the current row number, that is, the sequence of the row in the results list. You can retrieve this value using the `CURRENT-RESULT-ROW` function:

>     CURRENT-RESULT-ROW ( query-name )

> The function returns an `INTEGER` value with the sequence of the current row. The `query-name` is an expression, either a quoted query name or a variable reference.

> For `CURRENT-RESULT-ROW` to work properly, you must define the query to be `SCROLLING`. If you don’t define the query as `SCROLLING`, the `CURRENT-RESULT-ROW` function returns a value, but that value is not reliable.

> | To use `CURRENT-RESULT-ROW`, make these changes to your sample procedure:

>     DEFINE QUERY CustQuery FOR Customer SCROLLING.

>     OPEN QUERY CustQuery FOR EACH Customer WHERE State = "LA".
>     GET FIRST CustQuery.
>     DO WHILE NOT QUERY-OFF-END("CustQuery"):
>         DISPLAY Customer.CustNum Customer.NAME
>                 NUM-RESULTS("CustQuery") LABEL "Rows"
>                 CURRENT-RESULT-ROW("CustQuery") LABEL "Row#"
>             WITH FRAME CustFrame 15 DOWN.
>         GET NEXT CustQuery.
>         DOWN WITH FRAME CustFrame.
>     END.

> When you run the procedure, you see that the value of `CURRENT-RESULT-ROW` keeps pace with `NUM-RESULTS`, as shown in Figure 10–4.

> Figure 10–4: Result of CURRENT-RESULT-ROW example

> This is not always the case, of course. If you use the `PRESELECT` option or a nonindexed sort to retrieve the data, then `NUM-RESULTS` is always 13, as you have seen. But the value of `CURRENT-RESULT-ROW` changes from 1 to 13 just as it does above.

> You can use the `CURRENT-RESULT-ROW` function to save off a pointer to reposition to a specific row. See the "Using a RowID to identify a record" section for information on how to identify a record.

> Here are a few special cases for `CURRENT-RESULT-ROW`:

> - If the query is empty, the function returns the Unknown value (`?`).

> - If the query is explicitly positioned before the first row, for example by executing a `GET FIRST` followed by a `GET PREV`, then the function returns the value 1.

> - If the query is explicitly positioned after the last row, for example by executing a `GET LAST` followed by a `GET NEXT`, then the function returns the value one more than the number of rows in the results list.

> ##### Using INDEXED-REPOSITION to improve query performance

> If you anticipate jumping around in the result set using statements such as `GET LAST`, you should add another option to the end of your `OPEN QUERY` statement: the `INDEXED-REPOSITION` keyword. If you do this, your `DEFINE QUERY` statement must also specify the `SCROLLING` keyword.

> If you don’t open the query with `INDEXED-REPOSITION`, then Progress retrieves all records in sequence in order to satisfy a request such as `GET LAST`. This can be very costly. If you do use `INDEXED-REPOSITION`, Progress uses indexes, if possible, to jump directly to a requested row, greatly improving performance in some cases. There are side effects to doing this, however, in terms of the integrity of the results list, as discussed next.

> ##### INDEXED-REPOSITION and field lists

> When you define a query with the `FIELDS` phrase, make sure that you include (in the list of fields) the index that the query will use when you open the query with the `INDEXED-REPOSITION` keyword. Otherwise, you’ll get an error when the query is re-opened and it attempts to reposition on a field that it cannot find.

> ##### Factors that invalidate CURRENT-RESULT-ROW and NUM-RESULTS

> Under some circumstances, when you open your query with the `INDEXED-REPOSITION` keyword, the value of `CURRENT-RESULT-ROW` or `NUM-RESULTS` becomes invalid. As explained earlier, the results list holds the row identifiers for those rows that satisfy the query and that have already been retrieved.

> Thirteen rows satisfy the query for Customers in Louisiana, so the value of these two functions goes as high as 13 for that query. When you do a `PRESELECT` or a nonindexed sort, all the rows have already been retrieved before any data is presented to you, so `NUM-RESULTS` is 13 at the beginning of the `DISPLAY` loop. Normally, Progress adds the identifiers for all the rows it retrieves to the results list, but there are circumstances where this is not the case. If you execute a `GET LAST` statement on a query, and your `OPEN QUERY` statement does not use a `PRESELECT` or a sort that forces records to be pre-retrieved, Progress jumps directly to the last record using a database index, without cycling through all the records in between. In this case, it has no way of knowing how many records would have been retrieved between the first one and the last one, and it cannot maintain a contiguous results list of all rows that satisfy the query. For this reason, Progress flushes and reinitializes the results list when you jump forward or backward in the query. So after a `GET LAST` statement, `NUM-RESULTS` returns 1 (because the `GET LAST` statement has retrieved one row) and `CURRENT-RESULT-ROW` is unknown (because there is no way to know where that row would fit into the full results list).

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