SELECT - col_spec
7.31 | 7.40 | 7.54
... sql_exp ...
Specifies columns in the
list of the
SELECT clause of a
query. The specified columns are used to construct the results set of a
query from individual columns. Each column is specified using an
sql_exp. Any SQL expressions and
parentheses can be used
Each SQL expression uses its arguments to determine a value (with a data type dependent on the expression)
and places this value in the column of the results set. The corresponding data host variable after
APPENDING be a
To be able to evaluate the result of SQL expressions except for individual column specifications in
INTO|APPENDING CORRESPONDING FIELDS,
INTO ...@DATA(...), and ORDER
BY, an alias name must be defined using
AS, which is then used by these additions.
The following sections discuss the special characteristics of the various SQL expressions.
col of an individual column of a
data source as an
elementary SQL expression. If the name of a data source
is specified directly, the relevant content is provided in the corresponding column of the result set. The data type of a column in the results set is the data type of the corresponding component in ABAP Dictionary.
Note the following special conditions
If required, the specified column can contain a path expression for
CDS associations or
CTE associations. If
CORRESPONDINGor an inline declaration
@DATA(...)is used in the
INTOclause, any columns specified using a path expression must have an alias name
If the value of a column of type LRAW or LCHR is read, the associated length field of type INT2 or INT4 must also be read and specified in the list in front of the column of type LRAW or LCHR.
- A column can be specified more than once.
- Directly specified columns can only be listed in the obsolete form of space-separated lists.
It was possible to specify individual columns directly before the introduction of SQL expressions, so this does not lead to the
strict mode of the syntax check, which is otherwise brought about by the SQL expressions.
Specifies the columns CARRID, CARRNAME, and URL of the database table SCARR directly.
SELECT FROM scarr FIELDS carrid, carrname , url INTO TABLE @DATA(itab).
... literal | @dobj | @( expr )
Specification of a literal
or host expression
@( expr ) as an
elementary SQL expression. These are values of the ABAP
program that are determined before the query is executed in ABAP and are transferred to the database
system, where they are inserted into the result set as a column. A column defined in this way contains the same value in all rows of the result set.
- The restrictions on elementary SQL expressions apply with respect to the data types that can be used. More specifically, no strings can be specified and any host variables specified as field symbols or formal parameters cannot have generic data types.
It can be a good idea to specify a literal, a host variable, or a host expression as an elementary expression
SELECTlist whenever a defined value is assigned to a column of a results set that is not read from the database.
Specifying a constant or a literal as the only element of a
SELECTlist is one way of defining whether data in a selection exists without having to read data from the database.
Literals, host variables, and host expressions are handled as real SQL expressions in the
SELECTlist, which result in a strict mode of the syntax check and cannot be used in combination with
FOR ALL ENTRIES.
Specification of the constant
abap_true from the type group ABAP to determine whether a specific row exists.
SELECT SINGLE FROM scarr FIELDS @abap_true WHERE carrid = '...' INTO @DATA(flag). IF flag IS INITIAL. RETURN. ENDIF.
... sql_func | sql_arith | sql_cast | sql_string | sql_case
Specification of a non elementary SQL expression in the form of a built-in function
sql_func, of an arithmetic expression
sql_arith, cast expression
or case distinction
The values of these expressions are calculated in the database. The rules and restrictions described
for these apply. The values of the data source columns specified as operands are the selected values of the current row.
Using a non elementary SQL expression as a column specification.
SELECT CONCAT_WITH_SPACE( carrname && ' (' && carrid && '),', url, 1 ) FROM scarr INTO TABLE @DATA(itab).
Specifies an aggregate expression
sql_agg, in which a column of a
data source or an
SQL expression is specified as an argument of an
An aggregate function uses the values from multiple rows of the specified column or SQL expression to
calculate a single value (with a data type that depends on the aggregate function), and then places this value in the column of the results set.
The following rules apply when aggregate expressions are used in the
If aggregate expressions are used, any column names that are not specified within an aggregate expression
must be specified after the addition GROUP
BY. The aggregate functions evaluate the contents of the groups defined by
GROUP BYin the database system and pass the result to the merged rows of the results set.
If the addition
FOR ALL ENTRIESis used before
WHERE, no aggregate expressions can be used except COUNT( * ). In cases like these, the aggregate expression is not evaluated in the database, but is emulated on the AS ABAP.
If columns are only used within aggregate expressions in the
SELECTlist, the results set has just one row and the addition
GROUP BYis not required. If a non-table-like target area is specified after
INTOfor aggregated-only columns and without the addition
GROUP BYafter a single
SELECTstatement (which means not in the case of multiple statements joined using UNION), the statement
ENDSELECTcannot be specified (as is also the case when using the addition
SINGLE). If the aggregate expression COUNT( * ) is not used, an internal table can be specified after
INTOregardless of the addition
GROUP BYand the first row of this table filled.
In the case of aggregated-only columns without
GROUP BY, the results set also contains a row if no data is found in the database. If COUNT( * ) is used, the column in question contains the value 0. The columns of the other aggregate functions contain initial values. This row is assigned to the data object specified after
INTOand, unless COUNT( * ) is used only statically and an alias name is not specified,
sy-subrcis set to 0 and
sy-dbcntis set to 1.
COUNT( * )is used as the only static column specified and an alias name is not specified using
GROUP BYis not specified at the same time, the addition
INTOcan be omitted. The number of selected rows can then be taken from the system field
sy-dbcnt. If more than 2,147,483,647 rows are selected,
sy-dbcntcontains the value -1. If no data is found on the database,
sy-dbcntis set to 0 and
sy-subrcis set to 4.
Aggregate expressions cannot be specified together with
data_source~*in the comma-separated list.
- If aggregate expressions are used as operands of SQL expressions, the same rules apply as if they were specified directly.
It was possible to specify individual aggregate expressions directly before the introduction of SQL expressions, so this does not lead to the
strict mode of the syntax check, which is otherwise brought about by SQL expressions.
Gets the number of rows in the database table SCARR.
SELECT FROM scarr FIELDS COUNT( * ) INTO @DATA(wa).