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 line or multiple lines.

Alternative 1

... SINGLE [FOR UPDATE]

Addition

... FOR UPDATE

Effect

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.


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 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 UP TO 1 ROWS addition can be used instead of SINGLE.
  • If the SINGLE addition is used, after the creation of LOB handles, 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 handles as soon as possible.


Example

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

Addition

... FOR UPDATE

Effect

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.

Alternative 2

... [DISTINCT] { }

Effect

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.


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

Gets all destinations to which Lufthansa flies from Frankfurt.

DATA target TYPE spfli-cityto. 

SELECT DISTINCT cityto 
       FROM spfli 
       INTO target 
       WHERE carrid = 'LH' AND 
             cityfrom = 'FRANKFURT'. 
  WRITE: / target. 
ENDSELECT.