SELECT ORDERED statement (select ordered statement: positioned) (SAP Library

SELECT ORDERED statement (select ordered statement: positioned)

The SELECT ORDERED statement ( select ordered statement: positioned ) selects the first or last row, or, in relation to a certain position, the next or previous row in an ordered table. The order is defined by a key or by an index. The position is defined by a cursor position.

Syntax

<select ordered format1: positioned> ::=
SELECT <FIRST | LAST> <select column>,... INTO <parameter spec>,...
FROM <table name> [INDEX <column name> | INDEXNAME <index name>]
WHERE CURRENT OF <result table name> [<lock option>]
| SELECT <FIRST | LAST> <select column>,... INTO <parameter spec>,...
FROM <table name> [index pos spec]
WHERE CURRENT OF <result table name> [<lock option>]

<select ordered format2: positioned> ::=
SELECT <NEXT | PREV> <select column>,...INTO <parameter spec>,...
FROM <table name> [<index pos spec>]
WHERE CURRENT OF <result table name> [<lock option>]

select column, parameter spec, table name, column name, index name, result table name, lock option, index pos spec

Explanation

The select ordered statement is used to access the first or last row of an order defined by the key or a secondary key, or to access the previous or next row starting at a specified position.

The result table must have been specified with FOR UPDATE.

The user must have the SELECT privilege for the selected columns or for the entire table.

The table name in the SELECT ORDERED statement must be identical to that in the FROM clause of the QUERY statement that generated the result table.

If no index name is specified with INDEX <column name> or INDEXNAME <index name> and no index position is specified with ( index pos spec ), the order is defined by the key.

If an index name is specified with INDEX <column name> or INDEXNAME <index name> or an index position is specified with, the order is defined by the secondary key and the key. The ascending key order then is the second sort criterion.

The position within the table is defined by the optional index position specification and by a key value, whereby the key value is determined by the cursor position.

FIRST | LAST

FIRST (LAST) produces a search for the first (last) row which, in relation to the order, is greater (less) than or equal to the position.

NEXT | PREV

NEXT (PREV) produces a search in ascending (descending) order for the next row, starting at the specified position.

Single-column index

If an index name is specified with INDEX <column name> or INDEXNAME <index name> or an index position is specified, and the associated index only contains one column, the rows with NULL values in the index column (see data type) are not taken into account for the SELECT ORDERED statement. In such a case, the result of the select ordered statement can never be a row with a NULL value in the index column.

Further information

INTO <parameter spec> is not necessary in interactive mode.

The column name must denote an indexed column.

The LOCK option determines which lock is set for the read row.

A LONG column can only be specified in a selected column in the uppermost sequence of selected columns in this SELECT ORDERED statement.

Result

If the cursor is positioned on a row of the result table and a row was found which satisfies the specified conditions, then the corresponding column values are assigned to the parameters. The FETCH statement rules apply for assigning the values to the parameters.

If the cursor is not positioned on a row in the result table, an error message is issued and no values are assigned to the parameters specified in the parameter specifications.