SAP SELECT SINGLE ABAP Statements

Get Example source ABAP code based on a different SAP table

• SINGLE SELECT

SELECT, SINGLE
Short Reference

ABAP_SYNTAX
... SINGLE $[FOR UPDATE$] ...
... SINGLE ... $[FOR UPDATE NOWAIT$].

ABAP Addition
$[... FOR UPDATE ... $| ... FOR UPDATE NOWAIT.$]

What does it do?
Using the ABAP-specific addition SINGLE, the result set of a query contains a single row set. The addition is possible with a standalone SELECT statement or with the main query of a standalone WITH statement. If SINGLE is specified, a SELECT statement does not open a loop that must be closed using ENDSELECT or ENDWITH when reading into a non-table-like target area. No internal tables can be specified as a target area .

If the selection of the SELECT statement covers exactly one row, this row is included in the result set.

If the selection of the SELECT statement covers more than one row, one of these rows is included in the result set.
The following restrictions apply:

The additions FOR ALL ENTRIES, ORDER BY, UP TO, and OFFSET cannot be used together with SINGLE.

The addition SINGLE cannot be used in the main query of the statement OPEN CURSOR or in subqueries.

Latest notes:

A SELECT statement with the addition SINGLE is generally faster for completely specified rows than for incompletely specified rows.

The result set of SELECT statements with the addition SINGLE matches the set from the addition UP TO 1 ROWS without using the addition ORDER BY. The difference between the statements is:

If the addition SINGLE is used, ABAP SQL handles the result set as a single row. The target area is a structure or a list of elementary fields, but the statement ENDSELECT or ENDWITH is not necessary. The row cannot be read into an internal table. Not all additions of the SELECT statement, however, can be used.

If the addition UP TO 1 ROWS is used, ABAP SQL handles the result set as tabular. The statement ENDSELECT or ENDWITH must be specified or the row must be read into an internal table. The addition ORDER BY can, however, be specified. A SELECT statement with the addition SINGLE is optimized for reading a single row. This means that a SELECT statement with the addition SINGLE is generally slightly faster than with the addition UP TO 1 ROWS, since no loop has to be opened. In practice, however, this difference can usually be ignored. The following is recommended:

Use of the addition SINGLE to read exactly one completely specified row.

Use of the addition UP TO 1 ROWS to read a maximum of one row from a set of selected rows.

The addition ORDER BY cannot be specified together with SINGLE, which means that it is not possible to define which row is read from a non-unique selection. Instead, the addition UP TO 1 ROWS can be specified with the addition ORDER BY to define which row is read from a non-unique selection.

If SINGLE is specified and LOB handles are created in the INTO clause , all primary key fields joined by AND in logical expressions must be checked for equality in the WHERE condition. If this is not possible, the addition UP TO 1 ROWS can be used instead of SINGLE.
BEGIN_SECTION SAP_INTERNAL_HINT
There are technical reason for demanding full key specification with LOB-Handles. A SELECT SINGLE, where the key is not fully specified, is switched internally to a SELECT UP TO 1 ROWS and reader streams would be closed implicitly in contradiction to the following hint.
END_SECTION SAP_INTERNAL_HINT

If the addition SINGLE is used and LOB handles are created, all reader streams that are created when the SELECT statement is executed as well as locators continue to exist until they are closed, either explicitly with one of their methods, or implicitly at the end of the current database LUW. During this time, the associated database operation is not completed. It is recommended that all LOB handles are closed explicitly as soon as possible.

The addition SINGLE is designed to pass exactly one row to a flat structure as a work area without opening a loop that must be closed using ENDSELECT or ENDWITH.

Usually, the row must be identified exactly, and it must be specified uniquely in the WHERE condition. In a data source, this is usually done by specifying comparison values for the primary key.

If a row is not uniquely determined, the addition SINGLE can also be used to detect whether a corresponding row exists. In this case, the warning from the extended program check must be hidden using a pragma. To avoid unnecessary transports of data, a SELECT list can also be used that contains nothing but a single constant.
NON_V5_HINTS

For the latter see the executable example.

The extended program check produces a warning if no exact row is determined.
ABAP_HINT_END

ABAP_EXAMPLE_VX5
Reads the row with the information about Lufthansa flight 0400 from the DDIC database table SPFLI.
ABEXA 00641
ABAP_EXAMPLE_END

ABAP_EXAMPLE_VX5
The class CL_DEMO_SELECT_SINGLE_VS_UP_TO compares the performance of SELECT statements with the addition SINGLE with equivalent statements with the addition UP TO 1 ROWS.
ABAP_EXAMPLE_END
• FOR UPDATE SELECT SINGLE
• NOWAIT SELECT SINGLE

ABAP Addition
BEGIN_SECTION ID SELECT-FOR-UPDATE

What does it do?
When reading an individual row using SINGLE, the addition FOR UPDATE sets a database lock as an exclusive lock for this row on the database. With this addition, the SELECT statement is only executed if, in the WHERE condition, all primary key fields in logical expression that are joined using AND are checked for equivalence. Otherwise the result set is empty and sy-subrc is set to 8.
The addition FOR UPDATE can only be used when accessing data sources where writes are allowed. This addition cannot be used when accessing views where only reads are allowed.
The addition FOR UPDATE NOWAIT works in the same way as the addition FOR UPDATE and must be specified at the end of a SELECT SINGLE statement. Using the addition NOWAIT, an attempt is made to set the lock without waiting. If no lock can be set, sy-subrc is set to 6. In this case, no data is read.

Latest notes:

If set incorrectly, the lock can produce a deadlock.

If the addition FOR UPDATE is used, a standalone SELECT statement bypasses table buffering.
NON_V5_HINTS
ABAP_HINT_END

ABAP_EXAMPLES
In the following example, the exclusive lock set by the statement DELETE is released using a database commit. Then, an exclusive lock is already set by the SELECT statement and not only by the UPDATE statement.
ABEXA 00642
ABAP_EXAMPLE_END
END_SECTION ID SELECT-FOR-UPDATE

Return to menu