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:
Main query
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.
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.
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. 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 as of release 7.51. 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 @.
If n contains the value 0, a maximum of 2,147,483,647 rows are passed to the results set.
A positive number in n indicates the maximum number of rows in the results set.
If n contains a negative number or +2,147,483,647, a syntax error is produced or a non-handleable exception is raised.
The addition UP TO cannot be used with addition SINGLE and cannot be used with UNION.
Notes
The addition UP TO n ROWS should be used in preference to a SELECT loop that is canceled 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 row specified in full.
The addition UP TO 1 ROWS is often used to confirm whether a
data source contains any rows that meet a certain
condition at all. To avoid unnecessary transports of data, a SELECT list can also be used that contains nothing but a single constant (see the
executable example).
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 results set. If the addition
ORDER BY is not specified, n arbitrary rows that
meet the WHERE condition are passed
to the results set. If the ORDER BY clause does not sort the results set in a unique way, it is not possible to define which rows are in the results set.
If the addition FOR ALL ENTRIES
is also specified, all selected rows are initially read into a system 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.
Host variables without the escape character @ are
obsolete. The escape character @ must be specified in the
strict modes of the syntax check from Release 7.40, SP05.
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 @.
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 a non-handleable exception is raised.
It only makes sense to specify the addition OFFSET if the order of the rows
in the results set is undefined. Therefore, ORDER BY should be specified as well, followed by appropriate columns.
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.