SELECT - columns

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 input in columns determines which columns are used to build the resulting set.

Alternative 1

... *

Effect

If * is specified, the resulting set is built based on all columns in the database tables or views specified after FROM, in the order given there. The columns in the resulting set take on 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 getting the same name when you specify *.

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 build the resulting 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 resulting 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 multiple times.

The addition AS can be used to define an alternative column name a1 a2 ... with a maximum of fourteen digits in the resulting 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 of the ABAP Dictionary.
If the name of the component occurs in multiple database tables of the FROM addition but the desired database table or the view dbtab is only specified once after FROM, the names dbtab~comp1 dbtab~comp2 ... have to be specified for col1 col2 .... comp1 comp2 ... are the names of the components in the structure of the ABAP Dictionary and ~ is the column selector.
If the desired database table or view occurs multiple times after FROM, the names tabalias~comp1 tabalias~comp2 ... have tobe 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 of the ABAP Dictionary and ~ is the column selector.

The data type of a single column in the resulting list is the data type of the corresponding component in the 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

Read specific columns of a single row.

DATA wa TYPE spfli.
SELECT SINGLE carrid connid cityfrom cityto
       INTO CORRESPONDING FIELDS OF wa
       FROM spfli
       WHERE carrid EQ 'LH' AND connid EQ '0400'.
IF sy-subrc EQ 0.
  WRITE: / wa-carrid, wa-connid, wa-cityfrom, wa-cityto.
ENDIF.

Alternative 3

... (column_syntax)

Effect

Instead of static data, a data object column_syntax in brackets 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-type data object or an internal table with a character-type data type. The syntax in column_syntax, like in the 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 resulting set is always regarded as having multiple rows.

Notes

Before Release 6.10, you could only specify an internal table with a flat character-type row type for column_syntax with a maximum of 72 characters. Also, before Release 6.10, if you used the DISTINCT addition for dynamic access to pool tables or cluster tables, this was ignored, but since release 6.10, this causes a known exception.
If column_syntax is an internal table with header line, the table body and not the header line is evaluated.

Example

Read out how many flights go to and from a city. The SELECT command is implemented only once in a sub-program. The column data, including aggregate function and the data after GROUP BY, is dynamic. Instead of adding the column data to an internal l_columns table, you could just as easily concatenate it in a character-type l_columns field.

PERFORM my_select USING `CITYFROM`.
ULINE.
PERFORM my_select USING `CITYTO`.
FORM my_select USING l_group TYPE string.
  DATA: l_columns   TYPE TABLE OF string,
        l_container TYPE string,
        l_count     TYPE i.
  APPEND l_group TO l_columns.
  APPEND `count( * )` TO l_columns.
  SELECT (l_columns)
         FROM spfli
         INTO (l_container, l_count)
         GROUP BY (l_group).
    WRITE: / l_count, l_container.
  ENDSELECT.
ENDFORM.