SELECT - lines
Short Reference
ABAP Syntax ... { SINGLE [FOR UPDATE] }
| { [DISTINCT] { } } ... .
ABAP_ALTERNATIVES:
1 ... SINGLE [FOR UPDATE]
2 ... [DISTINCT] { }
What does it do? The information in lines specifies whether the
results set has one line or multiple lines.
ABAP_ALTERNATIVE_1 ... SINGLE [FOR UPDATE]
ABAP_ADDITION:
... FOR UPDATE
What does it do? If SINGLE is specified, the results set has a
single line. If the remaining additions of the statement SELECT
select more than one line from the database, the first line that is
found is placed in the results set. The data objects specified after
INTO cannot be internal tables, and the
APPENDING addition cannot be used. The
addition ORDER BY cannot be used
either.
Latest notes: When SINGLE is specified, the lines to be read
should be clearly specified in the WHERE condition
, for the sake of efficiency. When the data is read from a database
table, the system does this by specifying comparison values for the
primary key .
The addition SINGLE is not allowed in
subqueries .
If SINGLE is specified and
LOB handle s are created in the INTO
clause , all primary key fields joined by AND in logical
expressions must be checked for equivalence in the
WHERE condition . If this is not possible, the
UP TO 1 ROWS addition can be used instead
of SINGLE .
If the SINGLE addition is used, after the
creation of LOB handle s , all
reader streams which are created
when executing the SELECT statement, 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. We recommend that
you close all LOB handle s as soon as possible.
Example ABAP Coding Reads the line with the information on Lufthansa flight
0400 from database table SPFLI .
DATA wa TYPE spfli.
SELECT SINGLE *
FROM spfli
INTO CORRESPONDING FIELDS OF wa
WHERE carrid = 'LH' AND
connid = '0400'.
ABAP_ADDITION ... FOR UPDATE
What does it do? When reading an individual line using SINGLE , the
FOR UPDATE addition sets a write
lock for this line on the database. The SELECT statement is
then only executed if, in the WHERE condition ,
all primary key fields in logical expression which are joined using
AND are checked for equivalence. Otherwise the results set is empty
and sy-subrc is set to 8. If setting the lock produces a
deadlock , an exception is raised. When you
use the a FOR UPDATE addition, the SELECT statement
bypasses SAP buffering .
ABAP_ALTERNATIVE_2 ... [DISTINCT] { }
What does it do? If SINGLE is not specified and if
columns does not contain only
aggregate expressions , the
results set has multiple lines. All database lines that are selected by
the remaining additions of the statement SELECT are included in
the results set. If the ORDER BY
addition is not used, the order of the lines in the results set is not
defined and, if the same SELECT statement is executed multiple
times, the order may be different each time. A data object specified
after INTO can be an internal table and
the APPENDING addition can be used. If no
internal table is specified after INTO or APPENDING , the
SELECT statement triggers a loop that has to be closed using
ENDSELECT .
If multiple lines are read without SINGLE , the DISTINCT
addition can be used to exclude duplicate lines from the results set. If
DISTINCT is used, the SELECT statement bypasses SAP
buffering. The DISTINCT addition must not be used in the
following cases:
If a column specified in columns has the type STRING ,
RAWSTRING , LCHAR , or LRAW .
If the system tries to access pooled
tables or cluster tables and single
columns are specified in columns .
Latest notes: When specifying DISTINCT , note that this requires
the execution of sort operations in the database system, and the
SELECT statement therefore bypasses the
SAP buffer .
Example ABAP Coding Gets all destinations to which Lufthansa flies from
Frankfurt.
DATA destinations TYPE TABLE OF spfli-cityto WITH EMPTY KEY.
SELECT DISTINCT cityto
FROM spfli
INTO TABLE destinations
WHERE carrid = 'LH' AND
cityfrom = 'FRANKFURT'.
cl_demo_output=>display_data( destinations ).
Documentation extract taken from SAP system, � Copyright SAP AG. All rights reserved