ABAP Keyword Documentation → ABAP - Reference → Processing External Data → ABAP - Database Accesses → Open SQL → Open SQL - Read Accesses → SELECT → SELECT - result
SELECT - columns
Other versions:
7.31 | 7.40 | 7.54
Syntax
... *
| { {col1|aggregate( [DISTINCT] col1 )} [AS a1]
{col2|aggregate( [DISTINCT] col2 )} [AS a2] ... }
| (column_syntax) ... .
Alternatives
1. ...
*
2. ... {col1|aggregate( [DISTINCT] col1 )} [AS a1]
{col2|aggregate( [DISTINCT] col2 )} [AS a2] ...
3. ... (column_syntax)
Effect
The data in columns determines which columns are used to construct the results set.
Alternative 1
... *
Effect
If * is specified, the results set is constructed based on all columns in the
database tables or
views specified after
FROM, in the order given there. The columns in the results set inherit
the name and data type from the database tables or views. Only one data object can be specified after INTO.
Note
If multiple database tables are specified after FROM, you cannot prevent multiple columns from inheriting the same name when you specify *.
Example
Reads all columns of multiple rows.
DATA wa TYPE spfli.
SELECT *
FROM spfli
INTO CORRESPONDING FIELDS OF wa
WHERE carrid = 'LH'.
ENDSELECT.
Alternative 2
... {col1|aggregate( [DISTINCT] col1 )} [AS a1] {col2|aggregate( [DISTINCT] col2 )} [AS a2] ...
Effect
A list of column labels col1 col2 ... is specified in order to construct
the results list from individual columns. An individual column can be specified directly or as an argument
of an aggregate function aggregate.
The order in which the column labels are specified is up to you and defines the order of the columns
in the results list. Only if a column of the type LCHAR or LRAW is listed does the corresponding length
field also have to be specified directly before it. An individual column can be specified more than
once. Various specifications can be made after INTO, and their interaction is described by the column specified there.
The addition AS can be used to define an alternative column name a1
a2 ... (with a maximum of thirty characters) in the results set for every column label
col1 col2 .... The system uses the alternative column name in the additions
INTO|APPENDING CORRESPONDING FIELDS and ORDER BY.
Column Labels
The following column labels are possible:
FROM, the column labels in the database table (that is, the names of
the components comp1 comp2...) can be specified directly for col1 col2 ... in the structure from ABAP Dictionary. FROM
and the required database table or view dbtab is specified only once after
FROM, the names dbtab~comp1 dbtab~comp2 ... must
be specified for col1 col2 .... Here, comp1 comp2 ... are the names of the components in the structure from ABAP Dictionary and ~ is the
column selector. FROM,
the names tabalias~comp1 tabalias~comp2 ... have to be specified for
col1 col2 .... tabalias is the alternative table name of the database
table or view defined after FROM, and comp1 comp2 ... are the names of the components in the structure from ABAP Dictionary and ~ is the
column selector.
The data type of a single column in the results list is the data type of the corresponding component
in ABAP Dictionary. The corresponding data object after INTO or APPENDING has to be selected accordingly.
Note
If multiple database tables are specified after FROM, you can use alternative names when specifying single columns to avoid having multiple columns with the same name.
Example
Produces the flight date and the average booking rate of all customers of Lufthansa flights with the flight number 0400. The alternative name avg of the aggregate expression is required to
sort the results set.
DATA wa TYPE sbook.
SELECT fldate AVG( loccuram ) AS avg
INTO (wa-fldate, wa-loccuram)
FROM sbook
WHERE sbook~carrid = 'LH' AND
sbook~connid = '0400'
GROUP BY fldate
ORDER BY avg DESCENDING.
WRITE: / wa-fldate, wa-loccuram.
ENDSELECT.
Alternative 3
... (column_syntax)
Effect
Instead of static data, a data object column_syntax in parentheses can be
specified, which, when the command is executed, either contains the syntax shown with the static data, or is initial. The data object column_syntax can be a character-like data object or a
standard table without
secondary table keys
and with a character-like data object. The syntax in column_syntax, as ABAP Editor, is not case-sensitive. When specifying an internal table, you can distribute the syntax over multiple rows.
If column_syntax is initial when the command is executed, columns is implicitly set to * and all columns are read.
If columns are specified dynamically without the SINGLE addition, the results set is always regarded as having multiple rows.
Notes
-
When pooled tables or
cluster tables are accessed dynamically, the use of the
DISTINCTaddition raises a handleable exception. -
If
column_syntaxis an internal table with a header line, the table body is evaluated, and not the header line. -
The class CL_ABAP_DYN_PRG contains methods that support the creation of correct and secure dynamic column specifications.
Example
Produces all departure or destination cities of Lufthansa flights, depending on whether 'CITYFROM' or 'CITYTO' is specified.
PARAMETERS comp LENGTH 20.
DATA: dref TYPE REF TO data,
long_name TYPE string,
ftab TYPE TABLE OF string.
FIELD-SYMBOLS <fs>.
long_name = 'spfli-' && comp.
CREATE DATA dref TYPE (long_name).
ASSIGN dref->* TO <fs>.
APPEND comp TO ftab.
SELECT DISTINCT (ftab)
INTO <fs>
FROM spfli
WHERE carrid = 'LH'.
WRITE: / <fs>.
ENDSELECT.
Example