SELECT - ABAP Keyword Documentation

ABAP - Keyword Documentation → ABAP - Programming Language → Processing External Data → ABAP Database Access → ABAP SQL → ABAP SQL - Read Access →

Mail Feedback

SELECT

Short Reference

Syntax

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

Effect

Use of the ABAP SQL statement SELECT as a standalone statement. This statement reads data from one or more DDIC database tables, DDIC views, or non-abstract CDS entities, uses this data to create a multirow or a single row result set, and assigns this result set to suitable ABAP data objects.

The additions mainquery_clauses define which data is read from the database in which form. The set operators UNION, INTERSECT, and EXCEPT can be used to combine the result sets of multiple queries. In this case, special rules query_clauses apply when 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 result set is assigned by row or by package.
Restricting the result set
The additions UP TO and OFFSET determine the number of rows to be read.
ABAP-specific additions
Optional additions abap_options define whether table buffering is to be 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, that is, 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 after SELECT
- The columns of the result set are specified statically in the SELECT list, they contain only aggregate functions, and the additions GROUP BY, UNION, INTERSECT, and EXCEPT are not specified.
If an assignment is made to a table-like target range, that is, 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 package of rows to the data objects specified in target. If the last row has been assigned or the result 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 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. If the entire result set is passed to the data object in one step, no loop is opened and the statement ENDSELECT cannot be specified.

A SELECT loop can be exited with one of the following statements:

EXIT that exits a complete loop and resumes the program behind the closing statement of the loop.
A statement for exiting the current processing block
A statement for existing the current program

In order to exit the current loop pass and to continue with the next loop pass, the statements CONTINUE and CHECK can be used.

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 was passed in the loop.
4	The statement SELECT sets sy-subrc to 4 if the result set is empty. This usually means that no data was found on the database. Special rules apply when only aggregate expressions specified as columns are used in the SELECT list of the SELECT clause.
6	The statement SELECT sets sy-subrc to 6 if no lock can be set for the addition FOR UPDATE NOWAIT.
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 result set is empty, sy-dbcnt is set to 0. As with sy-subrc, special rules apply when only aggregate expressions specified in columns are used in the SELECT list of the SELECT clause.

Hints

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 packages from the database and 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.
Within a SELECT loop, no statements can be used that produce a database commit or a database rollback, causing the corresponding database cursor to be closed as a result.
If write accesses are performed on the data sources read by a SELECT loop within the loop, the behavior is database-dependent and undefined. Such accesses should therefore be avoided.
The statement ENDSELECT closes all the reader streams linked to the SELECT loop.
The current isolation level determines whether a SELECT statement accesses only data released by a database commit or whether it also accesses unreleased data in a different database LUW.
Outside of classes, an obsolete short form can be used for which the target area does not need to be specified using INTO or APPENDING. The prerequisites here are as follows: all columns are read with *, a single DDIC 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 DDIC database table or DDIC view. In this case, the system adds the addition INTO source to the SELECT implicitly.
As well as explicit ABAP SQL reads using SELECT loops and OPEN CURSOR, the ABAP SQL interface also opens database cursors implicitly in certain situations, 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.
The INTO clause as last clause of the SELECT statement leads to the strict mode as of ABAP release 7.40, SP08.
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 ABAP release 7.50. The additions UP TO, OFFSET, and abap_options can then also be placed in front of or after the FROM clause.

Example

The example shows two SELECT statements that differ only in the arrangement of their SELECT and FROM clauses. The result of both statements, which access two DDIC 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 @FINAL(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 @FINAL(result2)
       UP TO 10 ROWS.

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

Continue
SELECT, mainquery_clauses
ENDSELECT
Example

SELECT, Dynamic Token Specification