ABAP - Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP Database Accesses →  Open SQL →  Open SQL - Reads →  SELECT clauses → 

SELECT - UP TO, OFFSET

Quick Reference

Syntax

... [UP TO n ROWS]
    [OFFSET o] ...

Extras:

1.... UP TO n ROWS

2.... OFFSET o

Effect

These optional additions of a query of a SELECT statement or WITH statement restrict the results set using an offset and the maximum number of rows read. The syntax varies as follows for main queries and subqueries:

If the INTO clause is specified as last clause of the SELECT statement, the additions must follow after the INTO clause. Otherwise, they can also be specified after the SELECT clause or after the FROM clause. The order of the two additions is fixed. The addition OFFSET can be used only if there is an ORDER BY clause.
The addition UP TO can only be specified after an ORDER BY clause and the addition OFFSET can only be specified after UP TO.

Note

These additions are applied to the results set defined by the preceding clauses.

Addition 1

... UP TO n ROWS

Effect

The addition UP TO limits the number of rows in the results set of a SELECT statement to n. n expects a host variable prefixed by an escape character @, a host expression or literal of type i that can represent all non-negative numbers from the value range of i except its maximum value +2,147,483,647. In the strict mode from Release 7.51, n must have the types b, s, i, or int8. Furthermore, a literal or constant specified for n cannot have the value 0 in this strict mode. The content of n must match the data type i in accordance with the rules for a lossless assignment. A host variable should be prefixed by the escape character @.

The addition UP TO cannot be used with addition SINGLE and cannot be used with UNION.

Notes

It is advisable to use the addition UP TO 1 ROWS to read at most one row from a set of selected rows. The addition SINGLE, on the other hand. should generally be used to read a row specified in full.

Example

Reads the three business customers with the highest discount rates:

SELECT *
       FROM scustom
       WHERE custtype = 'B'
       ORDER BY discount DESCENDING
       INTO TABLE @DATA(result)
       UP TO 3 ROWS.

Addition 2

... OFFSET o

Effect

The addition OFFSET is used to return only the rows after the row with the count o from the results set. If OFFSET is specified, the results set must be sorted using ORDER BY. o expects a host variable prefixed by an escape character @, a host expression, or a literal of the type b, s, i or int8 that can represent all non-negative numbers in the value range of i except its maximum value +2,147,483,647. A literal or constant specified for n cannot have the value 0. A host variable must be prefixed by the escape character @.

The addition OFFSET cannot be used together with the additions SINGLE and FOR ALL ENTRIES, not when UNION is used and not when pooled tables/ cluster tables or projection views are accessed.

Note

When the addition OFFSET is used, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.

Example

Reads the data of all flights of a connection (except for the ten flights with the fewest seats taken).

SELECT fldate
       FROM sflight
       WHERE carrid = 'LH' AND connid = '400'
       ORDER BY seatsocc ASCENDING, fldate
       INTO TABLE @DATA(result)
       OFFSET 10.

Executable Example

Restricting the Results Set



Continue
Example SELECT, Restriction of the Rows in the Results Set