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

Short 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. The information specifies which columns are used to construct the results set and how their values are obtained. There are two static variants and one dynamic variant.

The results set that is defined using the entries displayed here is influenced 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 results set. 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 database tables or views specified after FROM, in the order given there. The columns in the results set inherit the name and data type from the database tables or views. Only one data object can be specified after INTO and no lists of data objects.


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.


Example

Reads all columns of multiple rows.

DATA wa TYPE spfli. 

SELECT * 
       FROM spfli 
       WHERE  carrid = 'LH' 
       INTO CORRESPONDING FIELDS OF @wa. 
ENDSELECT.

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
  • specifying individual columns col_spec.

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 individual database tables data, classic views view, or CDS views cds_view. The results set contains all columns of the specified database table or view data_source in the relevant location and in the order of the columns. The names of the database tables or views or their alias names can be specified for data_source that are also specified after as data_source after FROM. A database table or view 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

  • 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 arise from specifying a single database table or view data~*. This defines the same results set as specifying *, but can be different if information is specified after INTO and due to the resulting behavior.
  • If specified, data_source~* cannot be combined with aggregate expressions.
  • 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] ...

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.

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

  • 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.
  • 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 specify "nothing", if no alternative column name and no GROUP BY have been 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.
  • If an alternative columnn name matches an addition of the SELECT statement exactly, it may be necessary to prefix it with the escape character !.


Example

Produces 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 to sort the results set and assign it to the columns of the target table using CORRESPONDING FIELDS.

TYPES: BEGIN OF wa, 
         fldate  TYPE sbook-fldate, 
         avg     TYPE sbook-loccuram, 
       END OF wa. 
DATA   itab TYPE TABLE OF wa WITH EMPTY KEY. 

SELECT fldate, AVG( loccuram ) AS avg 
       FROM sbook 
       WHERE sbook~carrid = 'LH' AND 
             sbook~connid = '0400' 
       GROUP BY fldate 
       ORDER BY avg DESCENDING 
       INTO CORRESPONDING FIELDS OF TABLE @itab. 

cl_demo_output=>display( itab ).

Alternative 3

... (column_syntax)

Effect

Instead of these two static data entries, a data object column_syntax in parentheses can be specified. When the statement is executed, this data object either contains the syntax shown with the static data, or is initial. The data object column_syntax can be a character-like data object or a standard table without secondary table keys and with a character-like data object. The syntax in column_syntax, as ABAP Editor, is not case-sensitive. 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.

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

  • When pooled tables or cluster tables are accessed dynamically, the use of the addition DISTINCT raises a handleable exception.
  • 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 dynamically specified columns when created correctly and securely.

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

  • When specified dynamically, Open 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: comp      TYPE c LENGTH 16, 
      dref  TYPE REF TO data. 

cl_demo_input=>request( CHANGING field = comp ). 
TRY. 
    comp = 
      cl_abap_dyn_prg=>check_whitelist_tab( 
        val = to_upper( comp ) 
        whitelist = VALUE string_hashed_table( ( `CITYFROM` ) 
                                              ( `CITYTO` ) ) ). 
  CATCH cx_abap_not_in_whitelist. 
    cl_demo_output=>display( 'Not allowed' ). 
    LEAVE PROGRAM. 
ENDTRY. 

DATA(long_name) = `SPFLI-` && comp. 
CREATE DATA dref TYPE (long_name). 
ASSIGN dref->* TO FIELD-SYMBOL(<fs>). 

DATA output TYPE TABLE OF string WITH EMPTY KEY. 
SELECT DISTINCT (comp) 
       FROM spfli 
       WHERE  carrid = 'LH' 
       INTO @<fs>. 
  APPEND |{ <fs> }| TO output. 
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 ).

Example

See SELECT, Dynamically Specified Tokens

Continue

SELECT - col_spec