So if you execute a REPOSITION statement that repositions TO ROW 5 or FORWARDS 10 or TO ROWID rRow, then your procedure is positioned to that row so that you can display it or otherwise use it, right? Well, not exactly. When you use the REPOSITION statement, the AVM positions the query in between records, so that you then have to execute a GET NEXT or GET PREV statement to position on a row so that you can actually use it. Here are some of the specifics:
When you execute a REPOSITION statement that uses the TO ROWID or TO ROW phrase, the query is positioned before the record requested. You then need to execute a GET NEXT statement to make the row you want available.
When you execute a REPOSITIONquery-nameBACKWARDS statement, the query is positioned between records, and you need to execute a GET NEXT statement to make the row you intend available. For example, if the query is positioned to row 6 of a query results list and you execute a REPOSITIONquery-nameBACKWARDS 2, then the query is positioned effectively between results list rows 3 and 4, and a GET NEXT statement makes row 4 available. A GET PREV statement makes row 3 available.
When you execute a REPOSITION statement with the FORWARDS phrase, you are actually positioned beyond the record you might expect. For example, if you are on row 6 and issue a statement with FORWARDS 2, then the query is positioned between row 8 and row 9, and you need to then execute a GET PREV statement to make row 8 available or a GET NEXT statement to make row 9 available.
After executing a REPOSITON statement that involves a multi-table join, the bottom-most buffer will not be available, as is the case for a query built on a single table. You then need to execute a GET NEXT statement to make the row you want available. The availability of non-bottom level buffers following the REPOSITION, however, is undetermined. That is, non-bottom level buffers may or may not be available.
Note as well that there is only one way for the AVM to know which row is five rows ahead of the current row, or five rows behind it, or the fifth row of the result set, and that is to walk through the query row by row until it gets to the one you want. If the row in question has already been retrieved and is in the results list, then the AVM can reposition to the row you want very quickly using the results list. Therefore, the REPOSITION statement with any of the options TO ROW, FORWARDS, or BACKWARDS maintains the integrity of the results list and the functions that use it. The REPOSITION TO ROWID statement also maintains the integrity of the list if the row you want has already been retrieved and the results list has not been flushed since you retrieved it, because the AVM can scan the results list to locate the RowID.
When you execute a REPOSITION statement that uses the TO ROWID or TO ROW phrase, the query is positioned before the record requested. You then need to execute a GET NEXT statement to make the row you want available.