Skip to content

ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Reads →  SELECT clauses →  SELECT - select_clause 

SELECT - select_list

Quick Reference

Other versions: 7.31 | 7.40 | 7.54

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.

Alternative 1

... *

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

  • If multiple database tables are specified after FROM, it is not possible to prevent multiple columns from inheriting the same name when * is specified.
  • For performance reasons, only specify those columns that are actually needed. Specify * only if the content of all columns is actually needed.
  • If the addition INTO CORRESPONDING is used, * can be transformed implicitly to a list of columns for which there is a name match.
  • If UNION is used, * cannot be specified as a SELECT list.
  • When an internal table @itab with elementary row type is accessed in the FROM clause of a common table expression after WITH, * cannot be specified as a SELECT list.


Example

Reads all columns of multiple rows.

SELECT * 
       FROM spfli 
       WHERE carrid = 'LH' 
       INTO TABLE @DATA(wa).

Alternative 2

..., data_source~*, ..., col_spec [AS alias], ...

Variants

1. ... data_source~* ...

2. ... col_spec [AS alias] ...

Effect

Comma-separated list with

  • data_source~* specifying all columns of a data source data_source or
  • the definition of individual columns col_spec of the result set.

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.

Variant 1

... 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.

Note the following special conditions:


Notes

  • For performance reasons, only specify those columns that are actually needed. data_source~* should, therefore, only be specified if the content of all columns is actually needed.
  • The definition of the results set can also be produced by specifying a single data source data_source~*. This defines the same results set as specifying *, but can be different if information is specified after INTO and due to the resulting behavior.
  • When data_source~* is used, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.


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 ).

Variant 2

... col_spec [AS alias] ...

Addition

... AS alias

Effect

Definition of individual columns of the result set via specified columns col_spec, which can be defined using any 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 as col, does the corresponding length field also have to be specified directly before it. Various information can be specified after INTO, and the interaction of this information is described by the column specified there.


Notes

  • The obsolete short form without an explicitly specified target area cannot be used when specifying individual columns. The only exception here is when the aggregation function count( * ) is used to statically specify “nothing”, if no alias name and no GROUP BY are specified.
  • Instead of using commas, blanks can be used to separate columns specified in an obsolete form. Commas must be specified, however, in the strict modes of the syntax check from Release 7.40, SP05.


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).

Addition

... AS alias

Effect

The addition AS can be used to define an alias name alias (with a maximum of thirty characters) in the results set for every specified column col_spec. The alias name alias must follow the naming conventions for internal program names and the name table_line cannot be used. This is checked in strict mode of the syntax check from Release 7.54.

An alias name cannot be assigned more than once. The name of a column that does not have any alias names assigned to it should also not be used. The alias name is used implicitly in the addition INTO|APPENDING CORRESPONDING FIELDS OF. An alias 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

  • If multiple database tables are specified after FROM, alternative names can be used when specifying single columns to avoid having multiple columns with the same name.
  • In particular, an alias name cannot be used as the operand of an SQL expression.
  • If an alias name matches an addition of the SELECT statement exactly, it may be necessary to prefix it with the escape character !.
  • Outside of the strict syntax check mode from Release 7.54, it is also possible for an alias name to contain the minus sign (-), which permits assignments to be made to component of substructures with the addition CORRESPONDING FIELDS OF in the INTO clause.


Example

Displays the flight date and average fare of all customers on Lufthansa flights with flight number 0400. The alternative name avg of the aggregate expression is required for the ORDER BY clause and the inline declaration using @DATA(...) in the INTO clauses.

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 ).

Alternative 3

... (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 predefined function escape. See SQL Injections Using Dynamic Tokens.


Notes

  • If column_syntax is 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 dynamically specified columns.
  • In dynamically specified columns, static attributes or constants of a class cannot be accessed from outside in cases where the class has a static constructor and the constructor has not yet been executed.

  • The literals of the dynamically specified ABAP SQL statements can span multiple rows of a token specified dynamically as an internal table.

  • When specified dynamically, ABAP SQL statements can contain the comment characters * and " as follows:

  • In a dynamic token specified as a character-like data object, all content is ignored from the first comment character ".

  • In a dynamic token specified as an internal table, all rows are ignored that start with the comment character *. In the row, all content is ignored from the first comment character ".

Comment characters placed within literals are, however, part of the literal.

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

Continue

SELECT - col_spec