SELECT - ABAP Keyword Documentation

ABAP Keyword Documentation → ABAP − Reference → Processing External Data → ABAP Database Access → ABAP SQL → ABAP SQL - Reads →

SELECT

Quick Reference

Syntax

SELECT mainquery_clauses
  [UNION ...]
  INTO|APPENDING target
  [UP TO ...] [OFFSET ...]
  [abap_options].
  ...
[ENDSELECT].

Effect

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.

The additions mainquery_clauses 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

In the INTO clause after INTO or APPENDING, the target data objects are specified, to which the results set is assigned by row or by package.

Restricting the Results Set

The results UP TO, OFFSET determine the number of rows to read.

ABAP-specific additions

Optional additions abap_options define 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 ENDSELECT.

If an assignment is made to a non-table-like target range (meaning a SELECT statement without the addition INTO|APPENDING ... TABLE), a loop closed by ENDSELECT always occurs, except in the following instances:

The addition SINGLE for reading a single row is specified behind SELECT

The columns of the results set are specified statically in the SELECT list, they contain only aggregate functions, and the additions GROUP BY and UNION are not specified.

If an assignment is made to a table-like target range (meaning a SELECT statement with the addition INTO|APPENDING ... TABLE), a loop closed by ENDSELECT occurs whenever the addition PACKAGE SIZE is 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 ENDSELECT. A 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.

The INTO clause introduced using INTO|APPENDING must be specified as the final clause of the SELECT statement and the optional additions UP TO, OFFSET, and abap_options must be specified after the INTO clause.

System Fields

The statement SELECT sets the values of the system fields sy-subrc and sy-dbcnt.

sy-subrc	Meaning
0	In each value passing to an ABAP data object, the statement SELECT sets sy-subrc to 0. In addition, SELECT sets sy-subrc to 0 before a SELECT loop is exited using ENDSELECT, if at least one row is passed in the loop.
4	The statement SELECT sets sy-subrc to 4 if the results set is empty. This means that no data is found on the database in most cases. Special rules apply when only aggregate expressions specified as columns are used in the SELECT list of the SELECT clause.
8	The statement SELECT sets sy-subrc to 8 if the addition FOR UPDATE is used in result, and the primary key is not fully specified after WHERE.

After each value that is passed to an ABAP data object, the statement SELECT sets 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 aggregate expressions specified in columns are used in the SELECT list of the SELECT clause.

Notes

The query formulated in the SELECT statement 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 APPENDING additions.
SELECT loops 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 SELECT loops and 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.
Within a SELECT loop, 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 SELECT loop within the loop, the behavior is database-specific and undefined. Avoid this kind of access if possible.
The statement ENDSELECT closes all the reader streams which are associated with the SELECT loop.
The current isolation level is responsible for determining whether a SELECT statement accesses only data released by a database commit or whether it also accesses unreleased data in a different database LUW.
For compatibility reasons, the INTO clause can also be specified in front of or after the FROM clause outside the syntax check strict mode from Release 7.50. The additions UP TO, OFFSET, andabap_options can then be placed in front of or after the FROM clause.
The INTO clause as last clause of the SELECT statement 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 INTO or 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 TABLES for the corresponding database table or classic view . In this case, the system adds the addition INTO source to the SELECT implicitly.

Example

The example shows two SELECT statements that differ only in the arrangement of their SELECT and 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 ).

Continue
SELECT - mainquery_clauses
ENDSELECT
Example

SELECT, Dynamic Token Specification