Skip to content

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

SELECT

Short Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


SELECT result 

      FROM source 

      [[FOR ALL ENTRIES IN itab] WHERE sql_cond] 

       [GROUP BY
group] [HAVING group_cond] 

       [ORDER BY sort_key] 

       INTO|APPENDING target 

       [additions]. 

  ... 
[ENDSELECT].

Effect

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

The select statement reads a results set (whose structure is determined in result) from the database tables specified in source, and assigns the data from the results set to the data objects specified in target. 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 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 additions specify whether SAP buffering is bypassed, specify the maximum number of rows to be read and define the database connection.

The INTO clause introduced using INTO|APPENDING should be specified as the final clause of the SELECT statement. In this case, the optional additions additions must be located after the INTO clause. For compatibility reasons, the INTO clause can be placed before or after the FROM clause. The additions additions can then be placed before or after the FROM clause.

The data objects specified in target must match the results set result. This means that the results set is either assigned to the data objects in one step, or by rows or packets of rows. The latter is the case:

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

If these approaches are used, the statement SELECT opens a loop, which must be closed with ENDSELECT. 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. The loop can be exited using the statements listed in the Exiting Loops section. 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

sy-subrc Meaning
0 The statement SELECT sets sy-subrc to 0 forevery value passed to an ABAP data object. The SELECT statement also setssy-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 onlyaggregate expressionsspecified as columns are used in result.
8 The statement SELECT sets sy-subrc to 8 if the addition FOR UPDATE is used in result, and theprimary 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 result.


Notes

  • 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 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 view. In this case, the system implicitly extends the SELECT statement with the addition INTO source.
  • 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.
  • 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 database tables 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 the INTO clause is specified as the final clause of the SELECT statement, the syntax check is performed in a strict mode. This handles the statement more strictly than the regular syntax check.
  • If SELECT is used to access a CDS view using the CDS entity and the view is associated with a CDS role, all data is filtered out of the results set automatically for which the current user does not have a CDS authorization. If the user does not have an assigned role, no data is read. The ABAP program cannot distinguish whether data is not read because it does not exist or because an authorization is missing.
  • 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.

Continue

SELECT - result

SELECT - FROM

SELECT - WHERE

SELECT - GROUP BY

SELECT - HAVING

SELECT - ORDER BY

SELECT - INTO

SELECT - additions

ENDSELECT

SELECT - Examples