SELECT Clause

Variants:

1. SELECT [SINGLE [FOR UPDATE] | DISTINCT] *

2. SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn

3. SELECT [SINGLE [FOR UPDATE] | DISTINCT] (source_text)

Effect

The result of a SELECT statement is itself a table. The SELECT clause, along with the database tables and/or viewsin the FROM clause, specifies the sequence, name, database type, and length of the columns of the result table.

You can also use the optional additions SINGLE or DISTINCT to indicate that only certain lines in the result set should be visible to the program:

SINGLE: The result of the selection should be a single entry. If it is not possible to identify a unique entry, the system uses the first line of the selection. If you use the FOR UPDATE addition, the selected entry is protected against parallel updates from other transactions until the next database commit (see LUW and database lock mechanism). If the database system identifies a deadlock, a runtime error occurs.

DISTINCT: Duplicate entries in the result set are automatically deleted.

Note

To ensure that an entry can be uniquely identified, you can specify all of the fields in the primary key using AND expressions in the WHERE condition.

Note

Performance:

The additions SINGLE FOR UPDATE and DISTINCT bypass the SAP buffering.
The addition DISTINCT forces a sort on the database server. You should therefore only use it if you are really expecting duplicates in the result set.

Variant 1

SELECT [SINGLE [FOR UPDATE] | DISTINCT] *

Effect

The columns of the result set will have exactly the same sequence, names, database type, and length as the fields of the database table or view specified in the FROM clause.

Examples

Example to display all flights from Frankfurt to New York:

DATA wa TYPE spfli.

SELECT * FROM spfli INTO wa
         WHERE
           cityfrom = 'FRANKFURT' AND
           cityto   = 'NEW YORK'.
  WRITE: / wa-carrid, wa-connid.
ENDSELECT.

Example to display the free seats on Lufthansa flight 0400 on 02/28/2001:

DATA wa        TYPE sflight.
DATA seatsfree TYPE I.

SELECT SINGLE * FROM sflight INTO wa
       WHERE
         carrid   = 'LH '      AND
         connid   = '0400'     AND
         fldate   = '20010228'.
         FLDATE   = '19950228'.
seatsfree = wa-seatsmax - wa-seatsocc.

WRITE: / wa-carrid, wa-connid, wa-fldate, seatsfree.

Note

If you specify more than one table in the FROM clause and the INTO clause contains an internal table or work area instead of a field list, the fields are placed into the target area from left to right in the order in which they occur in the tables in the FROM clause. Gaps may occur between the table work areas for the sake of alignment. For this reason, you should define the target work area by referring to the types of database tables instead of simply listing the fields. For an example, refer to the documentation of the FROM clause.
If the database table or the join specified in the FROM clause contain columns of the type STRING or RAWSTRING, you must not use the addition DISTINCT.

Variant 2

SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn

Effect

The columns of the result table will have the same format as the column references s1 ... sn.

If si stands for a field f, MAX( f ), MIN( f ), or SUM( f ), the corresponding column in the result set will have the same ABAP Dictionary format as f. For COUNT( DISTINCT f ) or COUNT( * ) the column has the type INT4. For AVG( f ) it has the type FLTP.

If you use aggregate functions with one or more database fields in the SELECT clause, you must include all of the database fields that are not used in the aggregate function in the GROUP BY clause. The result of the selection in this case is a table.

If the SELECT clause only contains aggregate functions, the result of the selection will be a single entry. In this case, SELECT does not have a corresponding ENDSELECT statement.

Examples

Example to display all destinations served by Lufthansa from Frankfurt:

DATA: target TYPE spfli-cityto.

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

Example to display the number of airlines that fly to New York:

DATA   count TYPE I.

SELECT COUNT( DISTINCT carrid )
       INTO count
       FROM spfli
       WHERE
         cityto = 'NEW YORK'.
WRITE: / count.

Example to find the number of passengers, the total luggage weight, and the average weight of the luggage for all Lufthansa flights on 02/28/2001:

DATA:  count TYPE I, sum TYPE P DECIMALS 2, avg TYPE F,
       connid LIKE sbook-connid.

SELECT connid COUNT( * ) SUM( luggweight ) AVG( luggweight )
       INTO (connid, count, sum, avg)
       FROM sbook
       WHERE
         carrid   = 'LH '      AND
         fldate   = '20010228'
       GROUP BY connid.
  WRITE: / connid, count, sum, avg.
ENDSELECT.

Notes

This variant is only available for pool and cluster tables if the SELECT clause does not contain any other aggregate functions except COUNT( * ).
The addition FOR ALL ENTRIES excludes all aggregate functions in the SELECT clause. One exception is if COUNT( * ) is the only element of the SELECT clause.
If a database field of the type LCHAR or LRAW is specified in the SELECT clause, you must specify the associated length field directly before it in the SELECT clause.
If one of the database columns f1, ..., fn has the type STRING or RAWSTRING, you must not use the addition DISTINCT.

Notes

Performance:

If you use aggregate functions, the SAP buffering is bypassed.
Since certain database systems do not manage the number of lines in a table in their catalog and therefore must retrieve this number, the function COUNT( * ) is not suited to check whether a table contains lines at all. You should better use SELECT f ... FROM tab UP TO 1 ROWS for any table field f.
If you only want to select specific columns of a database table, you should specify a list of fields in the SELECT clause or use a view.

Variant 3

SELECT [SINGLE [FOR UPDATE] | DISTINCT] (source_text)

Effect

Works like SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn if the variable source_text contains the list s1 ... sn as source code (see Dynamic Source Code in Open SQL). If source_text is empty, the statement works like SELECT [SINGLE [FOR UPDATE] | DISTINCT] *.

Example

Example to display all Lufthansa routes:

DATA: wa   TYPE spfli,
      ftab TYPE TABLE OF STRING.

APPEND 'CITYFROM' TO ftab.
APPEND 'CITYTO'   TO ftab.

SELECT DISTINCT (ftab)
       FROM spfli
       INTO CORRESPONDING FIELDS OF wa
       WHERE
         carrid   = 'LH'.
  WRITE: / wa-cityfrom, wa-cityto.
ENDSELECT.

Note

This variant is subject to the same restrictions as SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn.

Additional help

Defining Selections