AS ABAP Release 753, ©Copyright 2019 SAP AG. All rights reserved.
ABAP Keyword Documentation → ABAP − Reference → Processing External Data → ABAP Database Access → ABAP SQL → ABAP SQL - Reads → SELECT clauses → SELECT - select_clause →
SELECT - select_list
Syntax
... *
| {...,
data_source~*, ...,
col_spec [AS alias], ...}
| (column_syntax) ...
Alternatives:
1. ... *
2. ..., data_source~*, ..., col_spec [AS alias], ...
3. ... (column_syntax)
Effect
SELECT list of the SELECT clause of a query statement. This information specifies which columns are used to construct the results set of the SELECT statement and how their values are obtained. There are two static variants and one dynamic variant.
The SELECT list actually passed to the database is determined by the addition
INTO CORRESPONDING. If one
or more names match, all the columns for which there are no name matches are removed from the
SELECT list implicitly and therefore from the results set too. If there are no name matches, none of the columns are removed from the results set.
... *
Effect
Defines all columns of the results set using *. The results set is constructed from all columns in the data sources specified after FROM, in the order given there. The columns of the results set take their names and data types from the data sources. Only a single data object can be specified after INTO and lists of data objects cannot be specified.
Notes
Example
Reads all columns of multiple rows.
SELECT *
FROM spfli
WHERE carrid = 'LH'
INTO TABLE @DATA(wa).
..., data_source~*, ..., col_spec [AS alias], ...
Variants:
1. ... data_source~* ...
2. ... col_spec [AS alias] ...
Effect
Comma-separated list with
This information can be specified in any combination, with the exception that data_source~* cannot be used together with aggregate expressions.
Note
When a comma-separated list is used, the syntax check is performed in a
strict mode, which handles the statement more strictly than the regular syntax check. Specifying individual blank-separated columns is
obsolete.
... data_source~* ...
Effect
Defines multiple columns of the results set using data_source~*, where
data_source stands for an individual database table
dbtab, a classic view view,
a non-abstract CDS entity cds_entity, or an
internal table. The results set contains all columns
of the specified database source data_source in the relevant location and
in the order of the columns. The names of the database sources or their alias names can be specified
for data_source that are also specified as
data_source after FROM.
A data source can also be specified more than once. If data_source~* is used,
only a structure can be specified after INTO, no elementary data objects or lists of data objects.
Notes
Example
Reads the columns of two database tables in a join into an internal table. Two columns are read from SCARR and all columns are read from SPFLI.
TYPES BEGIN OF structure.
TYPES carrname TYPE scarr-carrname.
INCLUDE TYPE spfli AS spfli.
TYPES url TYPE scarr-url.
TYPES END OF structure.
DATA itab TYPE STANDARD TABLE OF structure WITH EMPTY KEY.
SELECT scarr~carrname, spfli~*, scarr~url
FROM scarr INNER JOIN spfli ON scarr~carrid = spfli~carrid
INTO TABLE @itab.
cl_demo_output=>display( itab ).
... col_spec [AS alias] ...
Addition:
Effect
Defines individual columns of the results set using specified columns col_spec, the names of columns, aggregate functions, or SQL expressions. The order in which the columns are specified is free and defines the order of the columns in the results set. Only if a column of the type LCHR or LRAW is specified explicitly does the corresponding length field also have to be specified directly before it. An individual column can be specified more than once. Various information can be specified after INTO, and the interaction of this information is described by the column specified there.
Notes
Example
Reads two columns from a cell in the database table SCARR
SELECT SINGLE
FROM scarr
FIELDS carrname, url
WHERE carrid = 'UA'
INTO @DATA(result).
... AS alias
Effect
The addition AS can be used to define an alternative column name alias (with a maximum of thirty characters) in the results set for every specified column col_spec. The alternative column name alias can contain letters, digits, the minus sign (-), and the underscore (_) in any order.
An alternative column name cannot be assigned more than once. The name of a column that does not have any alternative names assigned to it should also not be used. The alternative column name is used implicitly in the addition INTO|APPENDING CORRESPONDING FIELDS. An alternative column name can only be specified after ORDER BY. A non-unique column name used after ORDER BY causes a syntax error or an exception.
Notes
Example
Displays 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 for the ORDER BY clause and the inline declaration in the INTO clause.
SELECT fldate, AVG( loccuram as DEC( 31,2 ) ) AS avg
FROM sbook
WHERE sbook~carrid = 'LH' AND
sbook~connid = '0400'
GROUP BY fldate
ORDER BY avg DESCENDING
INTO TABLE @DATA(itab).
cl_demo_output=>display( itab ).
... (column_syntax)
Effect
Instead of the previous two static specifications, a data object column_syntax in parentheses can be specified. When the statement is executed, this data object either contains the syntax shown in static cases (with the exception of host expressions) or is initial.
The data object column_syntax can be a character-like data object or a standard table with a character-like row type. The syntax in column_syntax is not case-sensitive (as in the static syntax). When an internal table is specified, the syntax can be distributed across multiple rows.
If column_syntax is initial when the statement is executed, select_list is set implicitly to * and all columns are read.
If columns are specified dynamically without the addition SINGLE, the results set is always regarded as having multiple rows.
Invalid syntax raises a handleable exception from the class CX_SY_DYNAMIC_OSQL_ERROR.
Security Note
If used wrongly, dynamic programming techniques can present a serious security risk. Any dynamic content that is passed to a program from the outside must be checked thoroughly or escaped before being used in dynamic statements. This can be done using the system class CL_ABAP_DYN_PRG or the built-in function escape. See SQL Injections Using Dynamic Tokens.
Notes
Example
Produces all departure or destination cities of Lufthansa flights, depending on whether 'CITYFROM' or 'CITYTO' is specified. A method of the class CL_ABAP_DYN_PRG is used to check whether the input values are valid.
DATA column TYPE c LENGTH 16 VALUE 'cityfrom'.
cl_demo_input=>request( CHANGING field = column ).
TRY.
column =
cl_abap_dyn_prg=>check_whitelist_tab(
val = to_upper( column )
whitelist = VALUE string_hashed_table( ( `CITYFROM` )
( `CITYTO` ) ) ).
CATCH cx_abap_not_in_whitelist.
cl_demo_output=>display( 'Not allowed' ).
LEAVE PROGRAM.
ENDTRY.
DATA(name) = `SPFLI-` && column.
DATA dref TYPE REF TO data.
CREATE DATA dref TYPE (name).
ASSIGN dref->* TO FIELD-SYMBOL(<fs>).
DATA output TYPE TABLE OF string WITH EMPTY KEY.
SELECT DISTINCT (column)
FROM spfli
WHERE carrid = 'LH'
INTO @<fs>.
output = VALUE #( BASE output ( |{ <fs> }| ) ).
ENDSELECT.
cl_demo_output=>display( output ).
Example
Comment character in a dynamically specified token. The lines introduced using * and the content from the character " are ignored.
DATA: itab TYPE TABLE OF scarr,
columns TYPE TABLE OF string.
columns = VALUE #( ( `* Comment` )
( `carrid, "comment` )
( `* Comment` )
( `carrname "comment` ) ).
SELECT (columns)
FROM scarr
INTO CORRESPONDING FIELDS OF TABLE @itab.
cl_demo_output=>display( itab ).
Executable Example
SELECT, Dynamic Token Specification