SELECT - ABAP Keyword Documentation (2024)

SAP NetWeaver AS ABAP Release 750, ©Copyright 2016 SAP AG. All rights reserved.

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

SELECT

Syntax

Effect

SELECT is the Open SQL statement for reading data from one or moredatabase 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 ifa*ggregate expressionsare not exclusively specified in the SELECTlist of the SELECT clause select_clause, the results set is multirow or tabular by default. All database rows thatare selected by the remaining additions of the statement SELECT are includedin the results set.If the addition ORDERBY is not used, the order of the rows in the results set is not defined and, if the samestatement SELECT is executed multiple times, the order may be different eachtime. A data object specified after INTOcan be an internal table and the addition APPENDINGcan 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 SINGLEdefines 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 targetobject, and the addition SINGLE cannot be combined with all additions of the SELECT statement.

• The SELECT clause select_clausedefines the structure of the results set of the SELECT statement. It consistsmainly 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 mergesmultiple 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 sortsthe results set. If ORDER BY is not specified, the order of the rows in the results set is undefined.
• Optional additions additional_options specify whetherSAP 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 whichthe results set is assigned by row or by package, are specified in target.
  

The SELECT clause select_clausecan be specified before or after the FROMclause. 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 SELECTstatement. In this case, the optional additions additional_options must be located after the INTO clause. Forcompatibility reasons, the INTO clause can be placed before or after theFROM clause. The additionsadditional_options can then be placed before or after the FROM clause. In thestrict 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 onlyaggregate functions,and the additions GROUPBY 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 ofrows to the data objects specified in target. If the last row has been assigned or the results set is empty, SELECT jumps to ENDSELECT. Adatabase cursor isopened 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 theOpen 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 sectionExiting Loops. If the total results set is passed tothe data object in a single step, a loop is not opened and the statement ENDSELECT cannot be specified.

Notes

• Although the WHERE condition is optional, for performance reasons, it should always be specified, and the results set should not be restricted in theapplication 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 loadingbuffered 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 adatabase commit ordatabase rollback can be used, causing the correspondingdatabase cursor to be closed as a result.
• If change accesses are performed on the data sourcesread 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 aCDS entity associated with aCDS role and for whichCDS access control is not disabled using the value#NOT_ALLOWED for the annotation @AccessControl.authorizationCheck, only that data is read implicitly that matches theaccess condition definedin 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 theCDS database view of a CDS view is accessed, no access control takes place.
• The arrangement of the FROM clausebefore 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 thestrict mode as of Release 7.40, SP08.

• A SELECT clause startedwith FIELDS after the FROM clause leads to thestrict mode as of Release 7.50.

• The current isolation level is responsible for determining whether a SELECT statement accesses only data released by adatabase commit or whether it also accesses unreleased data in a differentdatabase 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 singleclassic view is specified statically after FROM, and atable 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 source to the SELECT implicitly.

Example

The example shows two SELECT statements that differ only in the arrangementof 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