Skip to content

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

SELECT - columns

Short Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


... * 
  | { {col1|aggregate( [DISTINCT] col1 )} [AS a1]
      {col2|aggregate( [DISTINCT] col2 )} [AS a2]  ... }
  | (column_syntax) ... .

Alternatives

1. ... *
2. ... {col1|aggregate( [DISTINCT] col1 )} [AS a1]
      {col2|aggregate( [DISTINCT] col2 )} [AS a2] ...

3. ... (column_syntax)

Effect

The data in columns determines which columns are used to construct the results set.

Alternative 1

... *

Effect

If * is specified, the results set is constructed based on 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.


Note

If multiple database tables are specified after FROM, you cannot prevent multiple columns from inheriting the same name when you specify *.


Example

Reads all columns of multiple rows.

DATA wa TYPE spfli. 

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

Alternative 2

... {col1|aggregate( [DISTINCT] col1 )} [AS a1]
    {col2|aggregate( [DISTINCT] col2 )} [AS a2] ...

Effect

A list of column labels col1 col2 ... is specified in order to construct the results list from individual columns. An individual column can be specified directly or as an argument of an aggregate function aggregate. The order in which the column labels are specified is up to you and defines the order of the columns in the results list. Only if a column of the type LCHAR or LRAW is listed does the corresponding length field also have to be specified directly before it. An individual column can be specified more than once. Various specifications can be made after INTO, and their interaction is described by the column specified there.

The addition AS can be used to define an alternative column name a1 a2 ... (with a maximum of thirty characters) in the results set for every column label col1 col2 .... The system uses the alternative column name in the additions INTO|APPENDING CORRESPONDING FIELDS and ORDER BY.

Column Labels

The following column labels are possible:

  • If only a single database table or a single view is specified after FROM, the column labels in the database table (that is, the names of the components comp1 comp2...) can be specified directly for col1 col2 ... in the structure from ABAP Dictionary.

  • If the name of the component appears in multiple database tables of the addition FROM and the required database table or view dbtab is specified only once after FROM, the names dbtab~comp1 dbtab~comp2 ... must be specified for col1 col2 .... Here, comp1 comp2 ... are the names of the components in the structure from ABAP Dictionary and ~ is the column selector.

  • If the required database table or view occurs multiple times after FROM, the names tabalias~comp1 tabalias~comp2 ... have to be specified for col1 col2 .... tabalias is the alternative table name of the database table or view defined after FROM, and comp1 comp2 ... are the names of the components in the structure from ABAP Dictionary and ~ is the column selector.
  • The data type of a single column in the results list is the data type of the corresponding component in ABAP Dictionary. The corresponding data object after INTO or APPENDING has to be selected accordingly.


    Note

    If multiple database tables are specified after FROM, you can use alternative names when specifying single columns to avoid having multiple columns with the same name.


    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.

    DATA wa TYPE sbook. 
    
    SELECT fldate AVG( loccuram ) AS avg 
           INTO (wa-fldate, wa-loccuram) 
           FROM sbook 
           WHERE sbook~carrid = 'LH' AND 
                 sbook~connid = '0400' 
           GROUP BY fldate 
           ORDER BY avg DESCENDING. 
      WRITE: / wa-fldate, wa-loccuram. 
    ENDSELECT. 
    

    Alternative 3

    ... (column_syntax)

    Effect

    Instead of static data, a data object column_syntax in parentheses can be specified, which, when the command is executed, 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 specifying an internal table, you can distribute the syntax over multiple rows.

    If column_syntax is initial when the command is executed, columns is implicitly set to * and all columns are read.

    If columns are specified dynamically without the SINGLE addition, the results set is always regarded as having multiple rows.


    Notes

    • When pooled tables or cluster tables are accessed dynamically, the use of the DISTINCT addition 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 the creation of correct and secure dynamic column specifications.

    Example

    Produces all departure or destination cities of Lufthansa flights, depending on whether 'CITYFROM' or 'CITYTO' is specified.

    PARAMETERS comp LENGTH 20. 
    
    DATA: dref TYPE REF TO data, 
          long_name TYPE string, 
          ftab      TYPE TABLE OF string. 
    
    FIELD-SYMBOLS <fs>. 
    
    long_name = 'spfli-' && comp. 
    CREATE DATA dref TYPE (long_name). 
    ASSIGN dref->* TO <fs>. 
    
    APPEND comp TO ftab. 
    
    SELECT DISTINCT (ftab) 
           INTO <fs> 
           FROM spfli 
           WHERE carrid = 'LH'. 
      WRITE: / <fs>. 
    ENDSELECT.