SELECT clause is a keyword used in SAP ABAP programming.This tutorial covers its introduction & syntax details.
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.