SAP SELECT UP TO OFFSET ABAP Statements
Get Example source ABAP code based on a different SAP table
• UP TO ROWS SELECT
• OFFSET SELECT
SELECT, UP TO, OFFSET Short Reference
ABAP_SYNTAX
... $[UP TO n ROWS$]
$[OFFSET o$] ...
ABAP Addition
1 ... UP TO n ROWS
2 ... OFFSET o
What does it do?
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:
Main query 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.
Subquery The addition UP TO can only be specified after an ORDER BY clause and the addition OFFSET can only be specified after UP TO.
Latest notes:
These additions affect the result set defined by the preceding clauses.
NON_V5_HINTS
ABAP_HINT_END
ABAP Addition
What does it do?
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.
BEGIN_SECTION VERSION 5 OUT This is checked in ABAP_STRICT_763 strict mode from ABAP_RELEASE ABAP_763 / .
END_SECTION VERSION 5 OUT 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.
If n contains the value 0, a maximum of 2,147,483,647 rows are placed in the result set.
A positive number in n indicates the maximum number of rows in the result set.
If n contains a negative number or +2,147,483,647, a syntax error is produced, or an uncatchable exception is raised.
The addition UP TO cannot be used with addition SINGLE and cannot be used with UNION, INTERSECT, or EXCEPT.
Latest notes:
The addition UP TO n ROWS is preferable to a SELECT loop that is terminated after importing n rows. In the latter case, the last package passed from the database to the AS ABAP usually contains superfluous rows.
Without the addition ORDER BY, the addition UP TO 1 ROWS provides the same result as the addition SINGLE and there are no major differences in performance.
If SINGLE is used, data can be read into a non-table-like work area without opening a loop closed using ENDSELECT.
If UP TO 1 ROWS is used, the addition ORDER BY can be specified to determine the first row in a multirow set of hits. 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.
If the addition ORDER BY is also specified, the rows of the hit list are sorted on the database server and only the number of sorted rows specified in n are passed to the result set. If the addition ORDER BY is not specified, n arbitrary rows that meet the WHERE condition are passed to the result set. If the ORDER BY clause does not sort the result set uniquely, it is not possible to define which rows are in the result set.
If the addition FOR ALL ENTRIES is also specified, all selected rows are initially read into an internal table and the addition UP TO n ROWS only takes effect during the passing from the system table to the actual target area. This can produce unexpected memory bottlenecks.
The addition UP TO 1 ROWS is often used to determine whether a data source contains any rows that meet a certain condition. To avoid unnecessary transports of data, a SELECT list can also be used that contains nothing but a single constant.
NON_V5_HINTS For the use of a constant value in the SELECT list, see the executable example.
Host variables without the escape character @ are obsolete. The escape character @ must be specified in the strict modes of the syntax check from ABAP_RELEASE ABAP_740_SP05 .
ABAP_HINT_END
ABAP_EXAMPLE_VX5
Reading of the three business customers with the highest discount rates.
ABEXA 00643
ABAP_EXAMPLE_END
ABAP Addition
What does it do?
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.
If o contains the value 0, all rows from the first row are respected.
If o contains a positive number, only the rows after the row indicated by o are respected.
If o contains a negative number or +2,147,483,647, a syntax error is produced, or an uncatchable exception is raised.
The addition OFFSET cannot be used together with the additions SINGLE and FOR ALL ENTRIES, and not when UNION, INTERSECT, or EXCEPT is used
BEGIN_SECTION VERSION 5 OUT , and not when DDIC projection views are accessed
END_SECTION VERSION 5 OUT .
Latest notes:
It only makes sense to specify the addition OFFSET if the order of the rows in the result set is not undefined. Therefore, ORDER BY must be specified as well, followed by appropriate columns.
NON_V5_HINTS
When the addition OFFSET is used, the syntax check is performed in a ABAP_STRICT_763 strict mode / , which handles the statement more strictly than the regular syntax check.
ABAP_HINT_END
ABAP_EXAMPLE_VX5
Reading of the data of all flights of a connection, except for the ten flights with the fewest seats taken.
ABEXA 00644
ABAP_EXAMPLE_END
ABAP_EXAMPLE_ABEXA
Restricting the result set
ABAP_EXAMPLE_END
Return to menu
