SAP SELECT CLAUSE COL SPEC ABAP Statements
Get Example source ABAP code based on a different SAP table
SELECT>, col_spec> Short Reference >
ABAP_SYNTAX
... sql_exp> ...>
ABAP_ALTERNATIVES:
1 ... col>
2 ... literal $| @dobj $| @( expr )>
3 ... sql_func $| sql_arith $| sql_cast $| sql_string $| sql_case >
4 ... sql_agg>
5 ... sql_win>
What does it do?
Specifies columns in the SELECT> list> of the SELECT> clause> of a query>. The specified columns are used to construct the result set of a query> from individual columns. Each column is specified using an SQL expression> sql_exp>>. Any SQL expressions and parentheses> can be used.
Each SQL expression uses its arguments to determine a value with a data type that depends on the expression and places this value in the column of the result set. The corresponding data host variable after INTO > or APPENDING> must be a suitable> choice.
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.
Latest notes:
Certain SQL expressions> can be calculated in the table buffer>. If any other SQL expressions are used, table buffering> is bypassed.
NON_V5_HINTS
ABAP_HINT_END
ABAP Alternative 1 ... col>
What does it do?
Direct specification col>> of an individual column of a data source> as an elementary SQL expression>. If the name of a data source is specified directly, its content is placed directly in the associated column of the result set. The data type of a column in the result set is the data type of the corresponding component in the ABAP Dictionary.
The following special conditions apply:
If required, the specified column can contain a path expression> for CDS associations> or CTE associations>. If the addition CORRESPONDING> or an inline declaration @DATA(...)> or @FINAL(...)> is used in the INTO>> clause, any columns specified using a path expression must have an alias name alias> defined using AS>>.
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>.
BEGIN_SECTION SAP_INTERNAL_HINT
Reading a field of type LRAW> or LCHR> into a target field of type x> or c> without reading the length field gives undefined results. Reading into a target field of type xstring or string works fine. Since release ABAP_740 there is a syntax warning and a runtime error if a LRAW > or LCHR> is read without reading the length field for all target types. Before release ABAP_740 , there was only the syntax warning for all target types.
END_SECTION SAP_INTERNAL_HINT
Latest notes:
A column can be specified more than once.
NON_V5_HINTS
Directly specified columns can still 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 ABAP_STRICT_740_SP05 strict mode / of the syntax check, which is otherwise caused by the SQL expressions.
ABAP_HINT_END
ABAP_EXAMPLE_VX5
Direct specification of the columns CARRID>, CARRNAME>, and URL> of the DDIC database table SCARR>.
ABEXA 00588
ABAP_EXAMPLE_END
ABAP Alternative 2 ... literal $| @dobj $| @( expr )>
What does it do?
Specification of a literal literal >>, host variable @dobj>>, 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.
Latest notes:
The restrictions on elementary SQL expressions> apply with respect to the data types that can be used. More specifically, 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 of a SELECT> list to assign a defined value to a column of a result set that is not read from the database.
Specifying a constant or a literal as the only element of a SELECT > list is one way of defining whether data in a selection exists without having to read data from the database.
NON_V5_HINTS
Literals, host variables, and host expressions are handled as real SQL expressions in the SELECT> list, which result in a ABAP_STRICT_740_SP05 strict mode / of the syntax check and cannot be used in combination with FOR ALL ENTRIES>>.
ABAP_HINT_END
ABAP_EXAMPLE_VX5
Specification of the constant abap_true> from the type pool ABAP> to determine whether a specific row exists.
ABEXA 00589
ABAP_EXAMPLE_END
ABAP Alternative 3 ... sql_func $| sql_arith $| sql_cast $| sql_string $| sql_case>
What does it do?
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 sql_cast>>, string expression sql_string>>, or case distinction sql_case>>. 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.
Latest notes:
NON_V5_HINTS
The non-elementary SQL expressions result in a ABAP_STRICT_740_SP05 strict mode / of the syntax check and cannot be used in combination with FOR ALL ENTRIES>>. No LOBs> can be modified in the expressions.
ABAP_HINT_END
ABAP_EXAMPLE_VX5
Use of a non-elementary SQL expression as a column specification.
ABEXA 00590
ABAP_EXAMPLE_END
ABAP Alternative 4 ... sql_agg>
What does it do?
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 aggregate function>. 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 result set.
The following rules apply when aggregate expressions are used in the SELECT> list:
If aggregate expressions are used, all column names that are not specified within an aggregate expression must be specified after the addition GROUP BY>>. The aggregate functions evaluate the content of the groups defined by GROUP BY> in the database system and pass the result to the merged rows of the result set.
If the addition FOR ALL ENTRIES> is 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 SELECT > list, the result set has just one row and the addition GROUP BY > is not required. If a non-table-like target area is specified after INTO> for aggregated-only columns and without the addition GROUP BY> after a single SELECT> statement, that is, not in the case of multiple statements joined using UNION >>, INTERSECT>>, or EXCEPT >>, the statement ENDSELECT> cannot 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 INTO> regardless of the addition GROUP BY> and the first row of this table filled.
In the case of exclusively aggregated columns without GROUP BY>, the result 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 INTO> and, unless COUNT( * )> is used only statically and an alias name is not specified, sy-subrc> is set to 0 and sy-dbcnt> is set to 1.
If COUNT( * )> is used as the only static column specified and an alias name is not specified using AS>> and GROUP BY> is not specified at the same time, the addition INTO > can 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-dbcnt> contains the value -1. If no data is found on the database, sy-dbcnt> is set to 0 and sy-subrc> is set to 4.
Latest notes:
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.
NON_V5_HINTS
It was possible to specify individual aggregate expressions directly before the introduction of SQL expressions, so this does not lead to the ABAP_STRICT_740_SP05 strict mode / of the syntax check, which is otherwise brought about by SQL expressions.
ABAP_HINT_END
ABAP_EXAMPLE_VX5
Determination of the number of rows in the DDIC database table SCARR >.
ABEXA 00591
ABAP_EXAMPLE_END
ABAP Alternative 5 ... sql_win>
What does it do?
Specifies a window expression sql_win>> in which a window function> is applied to a window> of the result set. Window expressions can only be specified in the SELECT> list.
Return to menu