SELECT - ABAP Keyword Documentation

ABAP - Keyword Documentation → ABAP - Reference → Processing External Data → ABAP Database Accesses → Open SQL → Open SQL - Read Accesses →

SELECT

Quick Reference

Syntax

SELECT [SINGLE [FOR UPDATE]]
       { select_clause
         FROM source }
      |{ FROM source
         FIELDS select_clause }
       [[FOR ALL ENTRIES IN itab] WHERE sql_cond]
     [GROUP BY group] [HAVING group_cond]
       [UNION [ALL|DISTINCT] select]
       [ORDER BY sort_key]
       INTO|APPENDING target
       [additional_options].
  ...
[ENDSELECT].

Effect

SELECT is the Open SQL statement for reading data from one or more database tables, classic views, or CDS entities into data objects.

The statement SELECT creates either a multirow or single-row results set that is assigned to suitable ABAP data objects.

If SINGLE is not specified, and if aggregate expressions are not exclusively specified in the SELECT list of the SELECT clause select_clause, the results set is multirow or tabular by default. All database rows that are selected by the remaining additions of the statement SELECT are included in the results set.If the addition ORDER BY is not used, the order of the rows in the results set is not defined and, if the same statement SELECT is executed multiple times, the order may be different each time. A data object specified after INTO can be an internal table and the addition APPENDING can be used. If no internal table is specified after INTO or APPENDING, the statement SELECT in multirow results sets triggers a loop that must be closed using ENDSELECT.

The optional addition SINGLE defines a single-row results set that can be protected against parallel changes by another program. When using SINGLE, it is not possible to specify an internal table as target object, and the addition SINGLE cannot be combined with all additions of the SELECT statement.

The SELECT clause select_clause defines the structure of the results set of the SELECT statement. It consists mainly of a SELECT list that defines the columns of the results set. Duplicate rows can be excluded (this is optional).
After FROM, the data sources, from which the database is read, are specified in source. All data sources must be defined in the ABAP Dictionary.
The results set can be restricted using the addition WHERE.
The addition GROUP BY merges multiple database rows into one row of the results set. The addition HAVING restricts the merged rows.
The addition UNION creates the union of the rows in the results sets of two SELECT statements.
The addition ORDER BY sorts the results set. If ORDER BY is not specified, the order of the rows in the results set is undefined.
Optional additions additional_options specify whether SAP buffering is bypassed, specify the maximum number of rows to be read and define the database connection.
After INTO or APPENDING, the data objects to which the results set is assigned by row or by package, are specified in target.

The SELECT clause select_clause can be specified before or after the FROM clause. After the FROM clause, the SELECT clause must be prefixed with the addition FIELDS.

The INTO clause introduced using INTO|APPENDING should be specified as the final clause of the SELECT statement. In this case, the optional additions additional_options must be located after the INTO clause. For compatibility reasons, the INTO clause can be placed before or after the FROM clause. The additions additional_options can then be placed before or after the FROM clause. In the strict mode of the syntax check from Release 7.50, the INTO clause must be the last clause.

The data objects specified in target must match the results set result. The results set of the can be assigned to the data objects either

as a full set in a single set or
row by row or in packages of rows in a loop.

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 Open 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.

With the exception of the additions INTO and APPENDING, the information in the statement SELECT specifies which data should be read by the database and in what form. This requirement is implemented in the database interface for the programming interface of the database system and is then passed to the database system. The data is read in packets from the database and is transported from the database server to the current application server. On the application server, the data is passed to the data objects of the ABAP program in accordance with the settings specified in the additions INTO and APPENDING.

System Fields

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

sy-subrc	Meaning
0	The statement SELECT sets sy-subrc to 0 for every value passed to an ABAP data object. The SELECT statement also sets sy-subrc to 0 before it exits a SELECT loop with ENDSELECT if at least one row was passed.
4	The statement SELECT sets sy-subrc to 4 if the results set is empty, that is, if no data was found in the database. 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

Although the WHERE condition is optional, for performance reasons, it should always be specified, and the results set should not be restricted in the application layer.
SELECT loops can be nested. For performance reasons, it may be more efficient to use a join or a subquery.
As well as explicit Open SQL reads using SELECT loops and OPEN CURSOR, the Open 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.
If SELECT is used to access a CDS entity associated with a CDS role and for which CDS access control is not disabled using the value #NOT_ALLOWED for the annotation @AccessControl.authorizationCheck, only that data is read implicitly that matches the access condition defined in the CDS role. If data cannot be read, ABAP programs cannot distinguish whether this is due to the conditions of the SELECT statement, the conditions of the CDS entity, or an associated CDS role. If the CDS database view of a CDS view is accessed, no access control takes place.
The arrangement of the FROM clause before a SELECT clause with FIELDS supports tools such as code completion in the ABAP Editor.
The following syntax produces a strict mode of syntax check, in which the statement is handled more strictly than the standard syntax check:

The INTO clause as last clause of the SELECT statement leads to the strict mode as of Release 7.40, SP08.

A SELECT clause started with FIELDS after the FROM clause leads to the strict mode as of Release 7.50.

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.
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
       INTO TABLE @DATA(result1).

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
       INTO TABLE @DATA(result2).

ASSERT result2 = result1.
cl_demo_output=>display( result1 ).

Continue
SELECT - SINGLE
SELECT - select_clause
SELECT - FROM
SELECT - WHERE
SELECT - GROUP BY
SELECT - HAVING
SELECT - UNION
SELECT - ORDER BY
SELECT - INTO
SELECT - additional_options
ENDSELECT
SELECT - Examples