Documentation View

We are hiring and constantly growing! Have a look through our vacancies to find the right role for you!

ABAPSELECT_SINGLE - SELECT SINGLE

ABAPSELECT_SINGLE - SELECT SINGLE

SUBST_MERGE_LIST - merge external lists to one complete list with #if... logic for R3up   SUBST_MERGE_LIST - merge external lists to one complete list with #if... logic for R3up  
This documentation is copyright by SAP AG.
SAP E-Book

SELECT, SINGLE

Short Reference



... SINGLE $[FOR UPDATE$] ...

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

Addition:

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

Effect

The ABAP-specific addition SINGLE makes the result set of a query 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, these statements does not open a loop closed using ENDSELECT or ENDWITH during imports to a non-table-like target area, and 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 at random.

The following restrictions apply:

Notes

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

  • 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 (see the executable example).

In the case of completely specified rows, a SELECT statement with the addition SINGLE is generally faster than specifying only part of the row.
  • 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.
  • If the addition SINGLE is used, it is not necessary to use the statements ENDSELECT, ENDWITH or to read the row into an internal table. Not all additions of the SELECT statement, however, can be used.

  • If the addition UP TO 1 ROWS is used, 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 can be optimized for reading a single row, which means is generally somewhat faster than when using the addition UP TO 1 ROWS. In practice, however, this difference can usually be ignored. In light of this, 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.
  • If the addition SINGLE is used, after the creation of LOB handles, all reader streams which are created when executing the statement SELECT, 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. The associated database operation is not completed during this time. It is best to close all LOB handles as soon as possible.

Example

Reading of the row with the information about Lufthansa flight 0400 from the DDIC database table SPFLI.

Example

The program 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.

Addition

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

Effect

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 NOWAIT can be added after FOR UPDATE only at the end of a SELECT SINGLE statement. If NOWAIT is used and a lock cannot be set, the value of sy-subrc is set to 6. In this case, no data is read.

Notes

  • If set incorrectly, the lock can produce a deadlock.
  • If the statement SELECT SINGLE FOR UPDATE ... is used, the addition NOWAIT is not possible. It is only possible when FOR UPDATE is added at the end of the SELECT SINGLE statement: SELECT SINGLE ... FOR UPDATE NOWAIT.

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.






BAL_S_LOG - Application Log: Log header data   Addresses (Business Address Services)  
This documentation is copyright by SAP AG.

Length: 11568 Date: 20230206 Time: 094845     sap01-206 ( 241 ms )