SELECT clause ( SAP ABAP Keyword)

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.

Related 10 ABAP Syntax Tutorials

• WRITE - Output as symbol (ABAP keyword)
• FIELD-GROUPS (ABAP Keyword)
• DESCRIBE (ABAP Keyword)
• ENDSELECT (ABAP Keyword)
• REFRESH (ABAP Keyword)
• CREATE (ABAP Keyword)
• REJECT (ABAP Keyword)
• ENDDO (ABAP Keyword)
• COLLECT ( ABAP keyword)
• WRITE - Output as icon ( SAP ABAP Keyword)

Most readed SAP Tutorials

• List of SAP HR TABLES and Infotypes tables
  Detailed full list of tables and infotypes used in SAP HR module.
• WRITE - Output to a list ( SAP ABAP Keyword)
  WRITE ( Output to a list ) is a keyword used in SAP ABAP programming.This tutorial covers its introduction & syntax details.
• List of SAP MM Transaction codes
  This documentation covers the details of SAP MM Transaction codes
• SELECT-OPTIONS ( SAP ABAP Keyword)
  SELECT-OPTIONS is a keyword used in SAP ABAP programming.This tutorial covers its introduction & syntax details.
• SAP MM Process Flow
  The typical procurement cycle for a service or material consists of the following phases:
• FI Accounts Receivable and Accounts Payable | SAP FI PDF manual
  The following topics are an introduction to the Accounts Receivable and Accounts Payable application components.
• SELECT clause ( SAP ABAP Keyword)
  SELECT clause is a keyword used in SAP ABAP programming.This tutorial covers its introduction & syntax details.

Latest Added SAP documents

• 9KE9 Transaction code
  Details about SAP transaction code 9KE9
• 9KE8 Transaction code
  Details about SAP transaction code 9KE8
• 9KE7 Transaction code
  Details about SAP transaction code 9KE7
• 9KE6 Transaction code
  Details about SAP transaction code 9KE6
• 9KE5 Transaction code
  Details about SAP transaction code 9KE5
• 9KE4 Transaction code
  Details about SAP transaction code 9KE4
• 9KE3 Transaction code
  Details about SAP transaction code 9KE3
• 9KE2 Transaction code
  Details about SAP transaction code 9KE2
• 9KE1 Transaction code
  Details about SAP transaction code 9KE1
• 9KE0 Transaction code
  Details about SAP transaction code 9KE0