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

Quick Reference

Syntax

SELECT [SINGLE [FOR UPDATE]]
{ select_clause
FROM source }
|{ FROM source
FIELDS select_clause }
[[FOR ALL ENTRIES IN itab] WHEREsql_cond]
[GROUP BYgroup] [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 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 ifaggregate 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.

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 thedatabase interface for the programming interface of the database system and is then passed to the database system. The data is read inpackets from the database and is transported from the database server to the currentapplication server.On the application server, the data is passed to the data objects of the ABAP program in accordancewith 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-subrcMeaning
0The 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.
4The 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 the SELECT list of the SELECT clause.
8The 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 SELECTsets sy-dbcnt to the number of rows passed. If an overflow occurs becausethe 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 onlyaggregate expressionsspecified 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 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

SELECT - ABAP Keyword Documentation (2024)

FAQs

How do you write a SELECT query in SAP ABAP? ›

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.

How can you access the documentation for an unfamiliar keyword in an ABAP program? ›

The ABAP Keyword Documentation is what appears when you hit F1 on ABAP statements in the ABAP Editor in SAP GUI (SE80, SE38, SE24, ...) or in ADT. Hitting F1 triggers a search.

What is the use of SELECT-options in SAP ABAP? ›

The statement SELECT-OPTIONS has the following effect: The statement declares a selection table in the program with the name selcrit. A selection table is an internal standard table with header line and standard key. In selection tables, multiple selection conditions can be saved.

When to use SELECT single in SAP ABAP? ›

Select single has to be used with a where condition that has all the key fields: It will always return a unique record(If a match is found). Select upto 1 rows would get your the first record if multiple matches are found. and select up to has to be end with endselect statements.

What is the example of SELECT query? ›

Example 1: Select all columns and rows from the EMPLOYEE table. Example 2: Select the project name (PROJNAME), start date (PRSTDATE), and end date (PRENDATE) from the PROJECT table. Order the result table by the end date with the most recent dates appearing first.

How do you create a SELECT query? ›

On the Create tab, in the Queries group, click Query Wizard. In the New Query dialog box, click Simple Query Wizard, and then click OK. Next, you add fields. You can add up to 255 fields from as many as 32 tables or queries.

How to read ABAP code in SAP? ›

Basic : Reading an ABAP source code

To get source code of this, we can use “READ REPORT” command as below : READ REPORT command fills string internal table “gt_source” with source code of the report "ZTEST". By doing this, we only have source code as a pure string table. There is no interpretation about code.

How to use type ref to data in ABAP? ›

The addition TYPE REF TO is used by the statement CREATE DATA to create a reference variable. The static type of the reference variable can be specified either directly as type or dynamically in name. When type is specified, the same rules apply as to the definition of reference types using the statement TYPES.

What is the SELECT keyword used for? ›

The SQL SELECT statement is used to query and retrieve data from a database. SELECT is one of the most commonly used SQL statements, allowing you to select specific columns and rows of data from database object(s).

What is the difference between SELECT options and ranges in ABAP? ›

They both are used for the range selection from selection screen. The main diff. between them is, while we use SELECT-OPTIONS system implicitly creates the select options internal table which contains the fields of SIGN,OPTION,LOW & HIGH. But in case of RANGES, this internal table should be defined explicitly.

Can we loop SELECT options in SAP ABAP? ›

You can apply loop on select-optoins table as follows: loop at so_budat. if so_budat-(low/High/sign/option) = <value>.

What are the different types of SELECT statements in SAP ABAP? ›

There are many different variations of the SELECT statement in ABAP. You have SELECT/ENDSELECT, SELECT Single, SELECT into TAble, inner joins, outter joins, etc.

What is the difference between SELECT up to 1 row and SELECT single in SAP? ›

Here is the difference: SELECT SINGLE returns the first matching row for the given condition and it may not be unique, if there are more matching rows for the given condition. SELECT ... UP TO 1 ROWS retrieves all the matching records and applies aggregation and ordering and returns the first record.

What is SELECT endselect in SAP ABAP? ›

select endselect is used when there are multiple records are to be processed and then kept internal table. Use internal table without header line and move correponding records in it.

How do you write a SELECT query for a column? ›

SELECT statements

The syntax is: SELECT column1, column2 FROM table1, table2 WHERE column2='value'; In the above SQL statement: The SELECT clause specifies one or more columns to be retrieved; to specify multiple columns, use a comma and a space between column names.

How to create a query in SAP step by step? ›

To create a new query, enter a name for it, and choose the Create button. A new dialog box appears, showing all available InfoSets. Choose the InfoSet that you require for your query and confirm the dialog. On the Create Query screen, enter a title and a description for your query.

Can we write SELECT on internal table in SAP ABAP? ›

You cannot use SELECT statement on internal table. If you want to select some rows from internal table you can LOOP the table or you can READ the table.

References

Top Articles
Latest Posts
Recommended Articles
Article information

Author: Twana Towne Ret

Last Updated:

Views: 6113

Rating: 4.3 / 5 (44 voted)

Reviews: 91% of readers found this page helpful

Author information

Name: Twana Towne Ret

Birthday: 1994-03-19

Address: Apt. 990 97439 Corwin Motorway, Port Eliseoburgh, NM 99144-2618

Phone: +5958753152963

Job: National Specialist

Hobby: Kayaking, Photography, Skydiving, Embroidery, Leather crafting, Orienteering, Cooking

Introduction: My name is Twana Towne Ret, I am a famous, talented, joyous, perfect, powerful, inquisitive, lovely person who loves writing and wants to share my knowledge and understanding with you.