REPOSITION statement

Repositions the cursor associated with a specific query. The query must be associated with a browse widget or defined with the SCROLLING option. The next record to be retrieved is the record following the cursor position.

Syntax

REPOSITION query 
  {     TO ROWID rowid1[ , rowid2]... 
          [ FOR TENANT tenant-expression][ NO-ERROR ] 
     |  TO RECID recid[ NO-ERROR ] 
     |  TO ROW n 
     |  FORWARDS n 
     |  BACKWARDS n 
  }

query

The name of the query to reposition. The query must be open.

TO ROWID rowid1[ , rowid2]... [FOR TENANT tenant-expression] [NO-ERROR]

Repositions the join levels of a query to the corresponding ROWID expressions (rowid1, rowid2, and so on) that you specify, where rowid1 represents the ROWID for the top level of the join, rowid2 represents the ROWID for the next level of the join, and so on. You can specify any number of ROWID expressions up to the number of join levels. If you specify fewer ROWID expressions than the number of join levels, the AVM repositions the join levels of the query to the corresponding ROWID expressions you specify, but positions the remaining join levels for the unspecified ROWID expressions arbitrarily.

The FOR TENANT option is useful only for a multi-tenant database, and primarily one with a connection identity that has super tenant access. If the user has a super-tenant connection identity and you do not specify this option, the query repositions to data owned by the effective tenant. If you do specify this option, the query repositions to data owned by the regular tenant identified by tenant-expression.

If the user has a regular-tenant connection identity, and you specify this option, tenant-expression must match the tenancy of the connection identity. Otherwise, the statement raises ERROR.

If tenant-expression evaluates to an integer, the value must be a valid tenant ID for a regular tenant or zero (0) for the default tenant. If tenant-expression evaluates to a character string, the value must be a valid tenant name for a regular or "Default" for the default tenant. Otherwise, the statement raises ERROR.

TO RECID recid[ NO-ERROR ]

Similar to the TO ROWID option, except that the value recid is an expression that evaluates to a RECID value, and you can specify only one recid. Supported only for backward compatibility.

NO-ERROR suppresses any error messages that result from specifying an illegal value or a value that does not identify any records returned by the query. See the NO-ERROR entry below for more information.

NO-ERROR

The NO-ERROR option is used to prevent the statement from raising ERROR and displaying error messages.

TO ROW n

Repositions the cursor to before the specified row in the result list of the query. The value n must be an integer expression that identifies a row in the result list. You cannot use this option with a query opened with the INDEXED-REPOSITION option.

FORWARDS n

Moves the cursor from its current position in the result list to a new position n records forward, where n represents an integer expression.

REPOSITION FORWARDS always places the cursor between two rows. For example:

If the cursor is on a row—say, row 5—REPOSITION FORWARDS 1 moves the cursor to row 6, then to half way between rows 6 and 7. From this position, GET PREVIOUS moves the cursor to row 6, while GET-NEXT moves the cursor to row 7.
If the cursor is already between two rows—say, between rows 5 and 6— REPOSITION FORWARDS 1 moves the cursor to half way between rows 6 and 7. From this position, GET PREVIOUS moves the cursor to row 6, while GET-NEXT moves the cursor to row 7.

BACKWARDS n

Moves the cursor from its current position in the result list to a new position n records back, where n represents an integer expression.

REPOSITION BACKWARDS always places the cursor between two rows. For example:

If the cursor is on a row—say, row 5—REPOSITION BACKWARDS 1 moves the cursor to row 4, then to half way between rows 4 and 5. From this position, GET PREVIOUS moves the cursor to row 4, while GET-NEXT moves the cursor to row 5.
If the cursor is already between two rows—say, between rows 5 and 6— REPOSITION BACKWARDS 1 moves the cursor to half way between rows 4 and 5. From this position, GET PREVIOUS moves the cursor to row 4, while GET-NEXT moves the cursor to row 5.

Example

The following example uses the REPOSITION statement to move forward or backward within a query:

r-repos.p

DEFINE VARIABLE num AS INTEGER NO-UNDO INITIAL 1.

DEFINE QUERY q-order FOR Customer, Order SCROLLING.

DEFINE BUTTON b_quit LABEL "Quit".
DEFINE BUTTON b_frwd LABEL "FORWARD".
DEFINE BUTTON b_back LABEL "BACKWARD".

FORM b_frwd b_back b_quit
  WITH FRAME butt-frame ROW 1.

ON CHOOSE OF b_back, b_frwd DO:
  PROMPT-FOR num LABEL "Records To Skip" 
    WITH FRAME pos-info CENTERED ROW 5 overlay.
  HIDE FRAME pos-info NO-PAUSE.
  IF SELF:LABEL = "BACKWARD" THEN
    REPOSITION q-order BACKWARDS INPUT num + 1.
  ELSE
    REPOSITION q-order FORWARDS INPUT num - 1.
  RUN getone.
END.

OPEN QUERY q-order FOR EACH Customer NO-LOCK,
  EACH Order OF Customer NO-LOCK.
RUN getone.

ENABLE b_back b_frwd b_quit WITH FRAME butt-frame.
WAIT-FOR CHOOSE OF b_quit OR WINDOW-CLOSE OF CURRENT-WINDOW.

PROCEDURE getone:
  GET NEXT q-order.
  IF NOT AVAILABLE Customer THEN DO:
    REPOSITION q-order BACKWARDS 1.
    GET NEXT q-order.
  END.
  DISPLAY Customer.CustNum Customer.Name SKIP
    Order.OrderNum Order.OrderDate
    WITH FRAME order-info CENTERED ROW 5 SIDE-LABELS OVERLAY.
END PROCEDURE.

Notes

The REPOSITION statement does not fetch a record, except when the query is associated with a browse. The REPOSITION statement positions the cursor for the query so that a subsequent GET NEXT statement fetches the specified record, and GET PREV fetches the record before it.
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.
If you reposition a query associated with a browse widget, the browse widget data is refreshed with the record after the new position at the top.
If you try to position the cursor outside the list of records that satisfy the query, the AVM does not raise the ERROR condition. If you try to position the cursor before the first record, the AVM positions the query to just before the first record. If you try to position the cursor beyond the last record, the AVM positions it just beyond the last record.
The REPOSITION statement might be slow if the record you position to has not yet been fetched.
The REPOSITION TO ROWID statement might be especially slow. If the record has not yet been fetched, the AVM performs a series of GET NEXT operations until the record is found. You can optimize the performance of a REPOSITION TO ROWID statement by opening the query using the INDEXED-REPOSITION option of the OPEN QUERY statement.
The INDEXED-REPOSITION option of the OPEN QUERY statement, followed by REPOSITION TO ROWID or GET LAST, causes the query results list to change dramatically. Subsequent use of the CURRENT-RESULT-ROW or NUM-RESULTS functions might produce unknown or unexpected results.
The order of the records in the query is determined by the options specified in the OPEN QUERY statement.
For SpeedScript, the on-endkey-phrase and the on-quit-phrase do not apply.
When specifying the FOR TENANT option, the AVM looks up tenant-expression in the database with a share lock. The AVM waits 60 seconds to get the share lock and raises ERROR if it fails to obtain the share lock in that amount of time. The AVM releases the share lock immediately after successfully fetching the row. This share lock is released even if the statement is called while in the scope of a transaction.

REPOSITION statement

Syntax

Example

Notes

See also