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