Skip to content

ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP Database Accesses →  Open SQL →  Open SQL - Read Accesses →  SELECT →  SELECT - result 

SELECT - lines

Short Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


... { SINGLE [FOR UPDATE] } 
  | { [DISTINCT] { }      }  ...

Alternatives

1. ... SINGLE [FOR UPDATE]

2. ... [DISTINCT] { }

Effect

The information in lines specifies whether the results set has one row or multiple rows.

Alternative 1

... SINGLE [FOR UPDATE]

Addition

... FOR UPDATE

Effect

If SINGLE is specified, the results set has a single row. If the remaining additions of the statement SELECT select more than one row from the database, the first row that is found is placed in the results set. The data objects specified after INTO cannot be internal tables, and the addition APPENDING cannot be used. The addition ORDER BY cannot be used either.


Notes

  • When SINGLE is specified, the rows 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, this is done by specifying comparison values for the primary key.
  • The addition SINGLE is not allowed in subqueries.
  • 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 equivalence 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

Reads the row with the information about Lufthansa flight 0400 from the database table SPFLI.

DATA wa TYPE spfli. 

SELECT SINGLE * 
       FROM spfli 
       WHERE carrid = 'LH' AND 
             connid = '0400' 
       INTO CORRESPONDING FIELDS OF @wa.

Addition

... FOR UPDATE

Effect

When reading an individual row using SINGLE, the addition FOR UPDATE sets an exclusive lock for this row on the database. The statement SELECT 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. If the addition FOR UPDATE is used, the statement SELECT bypasses SAP buffering.

Alternative 2

... [DISTINCT] { }

Effect

If SINGLE is not specified and the SELECT list select_list does not contain only aggregate expressions, the results set has multiple rows. All database rows that are selected by the remaining additions of the statement SELECT are included in the results set. If the addition ORDER BY is not used, the order of the rows in the results set is not defined and, if the same statement SELECT is executed multiple times, the order may be different each time. A data object specified after INTO can be an internal table and the addition APPENDING can be used. If no internal table is specified after INTO or APPENDING, the statement SELECT triggers a loop that has to be closed using ENDSELECT.

If multiple rows are read without SINGLE, the addition DISTINCT can be used to exclude duplicate rows from the results set. If DISTINCT is used, the statement SELECT bypasses SAP buffering. The addition DISTINCT must not be used in the following cases:

  • If a column specified in the SELECT list select_list has the type STRING, RAWSTRING, LCHR or LRAW,
  • If the system tries to access pooled tables or cluster tables and single columns are specified in the SELECT list select_list.


Notes

When specifying DISTINCT, note that this requires the execution of sort operations in the database system, and the statement SELECT therefore bypasses the SAP buffer.


Example

Gets all destinations to which Lufthansa flies from Frankfurt.

DATA destinations TYPE TABLE OF spfli-cityto WITH EMPTY KEY. 

SELECT DISTINCT cityto 
       FROM spfli 
       WHERE carrid = 'LH' AND 
             cityfrom = 'FRANKFURT' 
       INTO TABLE @destinations. 

cl_demo_output=>display_data( destinations ).