Skip to content

ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP Database Accesses →  Open SQL →  Open SQL - Read Accesses →  SELECT →  SELECT - result →  SELECT - select_list 

SELECT - col_spec

Short Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


... col 
  | aggregate 

  | sql_exp

Alternatives

1. ... col
2. ... aggregate

3. ... sql_exp

Effect

When building the results set from individual columns, the columns can be specified either by directly entering the name of column col, by using an aggregate expression, or by using an SQL expression sql_exp.

When specifying column names col (directly, in aggregate expressions or in SQL expressions), the following applies:

  • If only a single database table or a single view is specified after FROM, the column label in the database table (that is, the component name comp) can be specified for col directly in the ABAP Dictionary structure.

  • If the name of the component occurs in multiple database tables of the FROM addition and the required database table dbtab or classic view view or CDS view cds_view is, however, only specified once after FROM, the name dbtab~comp, view~comp, or cds_view~comp must be specified for col. comp is the name of the component in the ABAP Dictionary structure and ~ is the column selector.

  • If the required database table or view occurs multiple times after FROM, the name tabalias~comp have to be specified for col. tabalias is the alternative table name of the database table or view defined after FROM, and comp is the name of the component in the ABAP Dictionary structure and ~ is the column selector.


    • Note

    If SQL expressions are specified together with aggregate expressions, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.

    Alternative 1

    ... col

    Effect

    Specifies a database table column or view column directly. If the name of a column of a database table or view is specified directly, its content is passed directly to the associated column of the results set. The data type of a column in the results set is the data type of the corresponding component in ABAP Dictionary. The corresponding data host variable after INTO or APPENDING be a suitable choice.

    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.


    Notes

    Alternative 2

    ... aggregate

    Effect

    Specifies an aggregate expression aggregate, in which a database table column or a view column is listed as an argument of an aggregate function. An aggregate function uses the values from multiple rows of the specified column 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 corresponding data host variable after INTO or APPENDING be a suitable choice.

    If aggregate expressions are used, any column identifiers that are not included as arguments of an aggregate function must be included after the addition GROUP BY. The aggregate functions evaluate the contents of the groups defined by GROUP BY in the database system and pass the result to the merged rows of the results set.

    The following rules apply when aggregate expressions are used in the SELECT list:

    • If the addition FOR ALL ENTRIES is used before WHERE, or if cluster tables or pooled tables are included after FROM, no aggregate expressions apart from COUNT( * ) can be used. In cases like these, the aggregate expression is not evaluated in the database, but is emulated on the application server.
    • Columns of type STRING, RAWSTRING, LCHR, or LRAW cannot be handled with aggregate functions.
    • When aggregate expressions are used, the SELECT statement bypasses SAP buffering.
    • Null values are not included in the calculation for the aggregate functions. The result is a null value only if all the rows in the column in question contain a null value.
    • If aggregate expressions only are used after SELECT, the results set has just one row and the addition GROUP BY is not required. If a non-table-like target area is specified after INTO, the statement ENDSELECT cannot be specified as is the case when the addition SINGLE is used. If the aggregate expression COUNT( * ) is not used, an internal table can be specified after INTO, and the first row of this table is filled.
    • If aggregate functions only are used 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 for the other aggregate functions contain initial values. This row is assigned to the data object specified after INTO and, unless only COUNT( * ) is used, sy-subrc is set to 0 and sy-dbcnt is set to 1.
    • If only COUNT( * ) is used as the only specified column 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 2147483647 rows are selected, sy-dbcnt contains the value -1. If no data is found on the database, sy-subrc is set to 4 and sy-dbcnt is set to 0.
    • To be able to evaluate the result of aggregate functions in INTO|APPENDING CORRESPONDING FIELDS and ORDER BY, an alternative column name must be specified using AS. This is then used by these additions.


    Notes

    • Aggregate expressions cannot be specified together with data_source~* in the comma-separated list.
    • Aggregate expressions can only be listed in the obsolete form of space-separated lists.

    Alternative 3

    ... sql_exp

    Effect

    Specifies an SQL expression sql_exp, which columns of database tables, views, literals or host variables, or expressions, can have as operands. An SQL expression uses the operands 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 INTO or APPENDING be a suitable choice. The values of the database table columns or view columns specified as operands are the selected values of the current row.

    SQL expressions cannot currently be used together with the addition FOR ALL ENTRIES. The data types usable in SQL expressions are also restricted. LOBs in particular cannot be edited in expressions.

    SQL expressions are not possible in obsolete non-Unicode programs and not if the program attribute fixed point arithmetic is switched off. They dictate that the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check. In particular, host variables must be prefixed with the escape character @ and lists must be specified as comma-separated lists.


    Note

    SQL expressions are calculated in the database system.

    Continue

    SELECT - aggregate

    SELECT - sql_exp