AS ABAP Release 758, ©Copyright 2024 SAP SE. All rights reserved.
ABAP - Keyword Documentation → ABAP - Programming Language → Processing External Data → ABAP Database Access → ABAP SQL → ABAP SQL - Read Access → SELECT, clauses → SELECT, abap_options →
SELECT, UP TO, OFFSET
Syntax
... [UP TO n ROWS]
[OFFSET o] ...
1.... UP TO n ROWS
2.... OFFSET o
Effect
These optional additions of a query of a SELECT statement or WITH statement restrict the result set using an offset and the maximum number of rows to be 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 be after the INTO clause. Otherwise, they can also be after the SELECT clause or after the FROM clause. The order of the two additions is not 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.
Hint
These additions affect the result set defined by the preceding clauses.
... UP TO n ROWS
Effect
The addition UP TO limits the number of rows in the result set of a SELECT statement to n. For n, a host variable, a host expression, or a literal of type i is expected, which can represent all non-negative numbers from the value range of i except its maximum value +2,147,483,647. Only the types b, s, i, or int8 can be specified for n. Furthermore, a literal or constant specified for n cannot have the value 0. This is checked in strict mode from ABAP release 7.51. If n is an untyped literal or a host variable, its content must match the data type i in accordance with the rules for a lossless assignment.
The addition UP TO cannot be used with addition SINGLE and cannot be used with UNION, INTERSECT, or EXCEPT.
Hints
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 completely specified row.
Example
Reading of the three business customers with the highest discount rates.
SELECT *
FROM scustom
WHERE custtype = 'B'
ORDER BY discount DESCENDING
INTO TABLE @FINAL(result)
UP TO 3 ROWS.
... OFFSET o
Effect
The addition OFFSET is used to return only the rows after the row with the count o from the result set. If OFFSET is specified, the result set must be sorted using ORDER BY. o expects a host variable, a host expression, or a literal of the type b, s, i, or int8, which 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.
The addition OFFSET cannot be used together with the additions
SINGLE and FOR
ALL ENTRIES, and not when UNION,
INTERSECT, or EXCEPT is used, and not when
DDIC projection views are accessed.
Hints
Example
Reading of 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 @FINAL(result)
OFFSET 10.
Executable Example
SELECT, Restriction of the Rows in the Result Set