[UP TO ...] [OFFSET ...]
Uses the ABAP SQL statement
SELECT as a standalone statement. This statement reads data from one or more
database tables ,
classic views, or non-abstract
CDS entities, uses this data to create a multiple row or a single row results set, and assigns this results set to suitable ABAP data objects.
define which data can be read from the database in which form. The language element
UNION can be used to combine the results sets of multiple queries. In
this case, special rules
query_clauses apply for specifying clauses. Finally, the following properties are defined:
ABAP target objects
INTOclause after INTO or
targetdata objects are specified, to which the results set is assigned by row or by package.
Restricting the Results Set
OFFSETdetermine the number of rows to read.
abap_optionsdefine whether table buffering is bypassed and define the database connection.
In the following cases, the statement
SELECT opens a loop that must be closed using
If an assignment is made to a non-table-like target range (meaning a
SELECTstatement without the addition
INTO|APPENDING ... TABLE), a loop closed by
ENDSELECTalways occurs, except in the following instances:
- The addition
SINGLEfor reading a single row is specified behind
- The columns of the results set are specified statically in the
SELECTlist, they contain only aggregate functions, and the additions GROUP BY and
UNIONare not specified.
If an assignment is made to a table-like target range (meaning a
SELECTstatement with the addition
INTO|APPENDING ... TABLE), a loop closed by
ENDSELECToccurs whenever the addition
PACKAGE SIZEis used.
In each loop iteration, the
SELECT statement assigns a row or a packet of
rows to the data objects specified in
target. If the last row has been assigned or the results set is empty,
SELECT jumps to
database cursor is
opened implicitly to process a
SELECT loop, and is closed again when the loop has ended. In a single program, a maximum of 17 database cursors can be open simultaneously across the
ABAP SQL interface.
If more than 17 database cursors are opened, the runtime error DBSQL_TOO_MANY_OPEN_CURSOR occurs. A
SELECT loop can be exited using the statements in the section
Exiting Loops. If the total results set is passed to
the data object in a single step, a loop is not opened and the statement
ENDSELECT cannot be specified.
INTO clause introduced using
INTO|APPENDING must be specified as the final clause of the
statement and the optional additions UP
abap_options must be specified after the
|0||In each value passing to an ABAP data object, the statement
After each value that is passed to an ABAP data object, the statement
sy-dbcnt to the number of rows passed. If an overflow occurs because
the number or rows is greater than 2,147,483,647,
sy-dbcnt is set to -1.
If the results set is empty,
sy-dbcnt is set to 0. As with
sy-subrc, special rules apply if only
specified in columns are used in the
SELECT list of the
The query formulated in the
SELECTstatement is implemented in the database interface for the programming interface of the database system and is passed to this system. The data is read in packets from the database and is transported from the database server to the current AS ABAP. On AS ABAP, the data is passed to the data objects of the ABAP program in accordance with the settings specified in the INTO and
SELECTloops can be nested. For performance reasons, it may be more efficient to use a join or a subquery.
As well as explicit ABAP SQL reads using
OPEN CURSOR, the ABAP SQL interface also opens database cursors implicitly, such as when loading buffered tables. The runtime error DBSQL_TOO_MANY_OPEN_CURSOR can be avoided by not using explicit reads to exploit the maximum number of open database cursors.
SELECTloop, no statements that produce a database commit or database rollback can be used, causing the corresponding database cursor to be closed as a result.
If change accesses are performed on the data sources
read by a
SELECTloop within the loop, the behavior is database-specific and undefined. Avoid this kind of access if possible.
ENDSELECTcloses all the reader streams which are associated with the
The current isolation level is responsible for determining whether a
SELECTstatement accesses only data released by a database commit or whether it also accesses unreleased data in a different database LUW.
For compatibility reasons, the
INTOclause can also be specified in front of or after the
FROMclause outside the syntax check strict mode from Release 7.50. The additions
abap_optionscan then be placed in front of or after the
INTOclause as last clause of the
SELECTstatement leads to the strict mode as of Release 7.40, SP08.
An obsolete short form can be used (not in classes),
for which the target area does not need to be specified using
INTOor APPENDING. The preconditions here are as follows: all columns are read with
*, a single database table or a single classic view is specified statically after
FROM, and a table work area data_source is declared using the statement
TABLESfor the corresponding database table or classic view . In this case, the system adds the addition
INTO sourceto the
The example shows two
SELECT statements that differ only in the arrangement
FROM clauses. The result of the two statements, which access two database tables via an
INNER JOIN, is identical.
DATA cityfrom TYPE spfli-cityfrom VALUE 'NEW YORK'. SELECT c~carrname, p~connid, p~cityfrom, p~cityto FROM scarr AS c INNER JOIN spfli AS p ON c~carrid = p~carrid WHERE p~cityfrom = @cityfrom ORDER BY c~carrname, p~connid, p~cityfrom, p~cityto INTO TABLE @DATA(result1) UP TO 10 ROWS. SELECT FROM scarr AS c INNER JOIN spfli AS p ON c~carrid = p~carrid FIELDS c~carrname, p~connid, p~cityfrom, p~cityto WHERE p~cityfrom = @cityfrom ORDER BY c~carrname, p~connid, p~cityfrom, p~cityto INTO TABLE @DATA(result2) UP TO 10 ROWS. ASSERT result2 = result1. cl_demo_output=>display( result1 ).