SELECT clause

Variants

1. SELECT [SINGLE [FOR UPDATE] | DISTINCT] *
2. SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn
3. SELECT [SINGLE [FOR UPDATE] | DISTINCT] (itab)

Effect

The result of a SELECT statement is itself a table . The SELECT clause describes which columns this table is supposed to have.

In addition, you can use the optional addition SINGLE or DISTINCT if you want only certain lines of the solution set to be visible for the calling program:
SINGLE The result of the selection is a single record . If this record cannot be uniquely identified, the first line of the solution set is selected. The addition FOR UPDATE protects the selected record against parallel changes by other transactions until the next database commit occurs (see LUW and Database locking ). If the database system detects a deadlock, the result is a runtime error.
DISTINCT Any lines which occur more than once are automatically removed from the selected dataset.

Note

To ensure that a record is uniquely determined, you can fully qualify all fields of the primary key by linking them together with AND in the WHERE condition.

Note

Performance
The additions SINGLE FOR UPDATE and DISTINCT exclude the use of SAP buffering .
The addition DISTINCT requires sorting on the database server and should therefore only be specified if duplicates are likely to occur.

Variant 1

SELECT [SINGLE [FOR UPDATE] | DISTINCT] *

Effect

In the result set, the columns correspond exactly in terms of order, ABAP/4 Dictionary type and length to the fields of the database table (or view ) specified in the FROM clause .

Example

Output all flight connections from Frankfurt to New York:


TABLES SPFLI.

SELECT * FROM SPFLI
         WHERE
           CITYFROM = 'FRANKFURT' AND
           CITYTO   = 'NEW YORK'.
  WRITE: / SPFLI-CARRID, SPFLI-CONNID.
ENDSELECT.

Example

Output all free seats on the Lufthansa flight 0400 on 28.02.1995:


TABLES SFLIGHT.
DATA   SEATSFREE TYPE I.

SELECT SINGLE * FROM SFLIGHT
                WHERE
                  CARRID   = 'LH '      AND
                  CONNID   = '0400'     AND
                  FLDATE   = '19950228'.
SEATSFREE = SFLIGHT-SEATSMAX - SFLIGHT-SEATSOCC.
WRITE: / SFLIGHT-CARRID, SFLIGHT-CONNID,
         SFLIGHT-FLDATE, SEATSFREE.

Variant 2

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

Effect

The order, ABAP/4 Dictionary type and length of the columns of the result set are explicitly defined by the list s1 ... sn . Each si has the form
ai or ai AS bi .
Here, ai stands either for

a field f of the database table or

a aggregate print.

bi is an alternative name for the i-th column of the result set.
When using INTO CORRESPONDING FIELDS OF wa in the INTO clause , you can specify an alternative column name to assign a column of the result set uniquely to a column of the target area.
An aggregate print uses an aggregate function to group together data from one or all columns of the database table. Aggregate prints consist of three or four components:
An aggregate function immediately followed by an opening parenthesis DISTINCT (optional) The database field f A closing parenthesis
All components of a print must be separated by at least one blank.

The following aggregate functions are available:
MAX Returns the greatest value in the column determined by the database field f for the selected lines. Specifying DISTINCT does not change the result. NULL values are ignored unless all values in a column are NULL values. In this case, the result is NULL .
MIN Returns the smallest value in the column determined by the database field f for the selected lines. Specifying DISTINCT does not change the result. NULL values are ignored unless all values in a column are NULL values. In this case, the result is NULL .
AVG Returns the average value in the column determined by the database field f for the selected lines. AVG can only apply to a numeric field. NULL values are ignored unless all values in a column are NULL values. In this case, the result is NULL .
SUM Returns the sum of all values in the column determined by the database field f for the selected lines. SUM can only apply to a numeric field. NULL values are ignored unless all values in a column are NULL values. In this case, the result is NULL .
COUNT Returns the number of different values in the column determined by the database field f for the selected lines. Specifying DISTINCT is obligatory here. NULL values are ignored unless all values in a column are NULL values. In this case, the result is 0
COUNT( * ) Returns the number of selected lines. If the SELECT command contains a GROUP BY clause , it returns the number of lines for each group. The form COUNT(*) is also allowed.
If ai is a field f , MAX( f ) , MIN( f ) or SUM( f ) , the corresponding column of the result set has the same ABAP/4 Dictionary format as f . With COUNT( f ) or COUNT( * ) , the column has the type INT4 , with AVG( f ) the type FLTP .
If you specify aggregate functions together with one or more database fields in a SELECT clause, all database fields not used in one of the aggregate functions must be listed in the GROUP-BY clause . Here, the result of the selection is a table.
If only aggregate functions occur in the SELECT clause, the result of the selection is a single record. Here, the SELECT command is not followed later by an ENDSELECT .

Notes

This variant is not available for pooled tables and cluster tables .
If the SELECT clause contains a database field of type LCHAR or LRAW , you must specify the appropriate length field immediately before.

Notes

Performance
Specifying aggregate functions excludes the use of SAP buffering .
Since many database systems do not manage the number of table lines and therefore have to retrieve this at some cost, the function COUNT( * ) is not suitable for checking whether a table contains a line or not. To do this, it is best to use SELECT SINGLE f ... for any table field f .
If you only want to select certain columns of a database table, you are recommended to specify a list of fields in the SELECT clause or to use a View .

Examples

Output all flight destinations for Lufthansa flights from Frankfurt:


TABLES SPFLI.
DATA   TARGET LIKE SPFLI-CITYTO.

SELECT DISTINCT CITYTO
       INTO TARGET FROM SPFLI
       WHERE
         CARRID   = 'LH '       AND
         CITYFROM = 'FRANKFURT'.
  WRITE: / TARGET.
ENDSELECT.

Output the number of airline carriers which fly to New York:


TABLES SPFLI.
DATA   COUNT TYPE I.

SELECT COUNT( DISTINCT CARRID )
       INTO COUNT FROM SPFLI
       WHERE
         CITYTO = 'NEW YORK'.
WRITE: / COUNT.

Output the number of passengers, the total weight and the average weight of luggage for all Lufthansa flights on 28.02.1995:


TABLES SBOOK.
DATA:  COUNT TYPE I, SUM TYPE P DECIMALS 2, AVG TYPE F.
DATA:  CONNID LIKE SBOOK-CONNID.

SELECT CONNID COUNT( * ) SUM( LUGGWEIGHT ) AVG( LUGGWEIGHT )
       INTO (CONNID, COUNT, SUM, AVG)
       FROM SBOOK
       WHERE
         CARRID   = 'LH '      AND
         FLDATE   = '19950228'
       GROUP BY CONNID.
  WRITE: / CONNID, COUNT, SUM, AVG.
ENDSELECT.

Variant 3

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

Effect

Works like SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn if the internal table itab contains the list s1 ... sn as ABAP/4 source code, and like SELECT [SINGLE [FOR UPDATE] | DISTINCT] * , if itab is empty. The internal table itab can only have one field which must be of type C and cannot be more than 72 characters long. itab must appear in parentheses and there should be no blanks between the parentheses and the table name.

Note

With this variant, the same restrictions apply as for SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn .

Example

Output all Lufthansa flight routes:


TABLES: SPFLI.
DATA:   FTAB(72) OCCURS 5 WITH HEADER LINE.

REFRESH FTAB.
FTAB = 'CITYFROM'. APPEND FTAB.
FTAB = 'CITYTO'.   APPEND FTAB.
SELECT DISTINCT (FTAB)
       INTO CORRESPONDING FIELDS OF SPFLI
       FROM SPFLI
       WHERE
         CARRID   = 'LH'.
  WRITE: / SPFLI-CITYFROM, SPFLI-CITYTO.
ENDSELECT.