Defining a Selection 

The SELECT clause defines the structure of the result set (selection) that you want to read from the database.

The selection can be flat (one line) or tabular (several lines). You can specify whether to accept or exclude duplicate entries. The SELECT clause also specifies the names of the columns to be read. You can replace the names of the database fields with alternative names. Aggregate functions can be applied to individual columns.

The SELECT clause can be divided into two parts for lines and columns:

SELECT lines cols ...

In lines you specifiy whether an individual line is read using SELECT SINGLE cols

or whether lines are read using SELECT [DISTINCT] cols.

If you do not use DISTINCT (lines is then empty), the system reads all of the lines that satisfy the WHERE condition. If you use DISTINCT, the system excludes duplicate entries.

cols defines the column selection.

Reading All Columns

To read all of the columns in the database table, use the following for cols:

SELECT lines *...

This reads all columns for the specified lines. Reading individual columns can be considerably more efficient than reading all of the columns in a table. You should therefore only read the columns that you need in your program.

Reading Single Columns

To read single columns from the database table, use the following for cols:

SELECT lines s₁ [AS a₁] s₂ [AS a₂] ...

·        where s₁ s₂ … are single columns. Using the ASaddition, an alternative column name a₁ a₂ … can be defined for each column s₁ s₂ …. The alias column name is used instead of the real name in the INTO and ORDER-BYclauses. This allows you, for example, to read the contents of a column s₁ into a component a₁ of a structure when you use the INTO CORRESPONDING FIELDS variant of the INTO condition.

Reading Aggregate Data for Columns

To read aggregate data for a column in the database, use the following for cols:

SELECT lines agg( [DISTINCT] s₁) [AS a₁]
             agg( [DISTINCT] s₂) [AS a₂] ...

where s₁ s₂ … are single columns. The expression agg represents one of the following aggregate functions:

·        MAX: supplies the maximum value of the column

·        MIN: supplies the minimum value of the column

·        AVG: supplies the average value of the column

·        SUM: supplies the total ov the column

·        COUNT: counts the values or lines as follows:

·         COUNT( DISTINCT s ) returns the number of different values in the column s.

·         COUNT( * ) or count(*) returns the total number of lines in the selection.

You can exclude duplicate values from the calculation using the DISTINCT option. In the ASaddition, you can define an alternative column name a₁ a₂ … for each aggregate expression.

Specifying Columns Dynamically

You can also specify cols  dynamically as follows:

SELECT lines (itab)...

The parentheses must include the name of an internal table itab  that is either empty or contains s₁ s₂ … ... to specify the columns or aggregate expressions to be read. If the internal table is empty, the system reads all columns.

Reading certain columns of a single line:

REPORT demo_select_single.

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.

The list output is:

SINGLE in the SELECTclause means that the statement reads a single entry from the database table SPFLI where the primary key fields CARRID and CONNID have the values specified in the WHERE clause. The columns specified in the SELECT clause are transferred to the identically-named components of the structure wa.

Reading particular columns of more than one line:

REPORT demo_select_some_columns.

DATA: itab TYPE STANDARD TABLE OF spfli,
      wa LIKE LINE OF itab.

SELECT   carrid connid cityfrom cityto
  INTO   CORRESPONDING FIELDS OF TABLE itab
  FROM   spfli
  WHERE  carrid EQ 'LH'.

IF sy-subrc EQ 0.
  LOOP AT itab INTO wa.
    WRITE: / wa-carrid, wa-connid, wa-cityfrom, wa-cityto.
  ENDLOOP.
ENDIF.

The list output is:

Since there are no lines specified in the SELECT clause, the statement reads all of the lines from the database table SPFLI that satisfy the condition in the WHEREclause. The columns specified in the SELECTclause are transferred to the identically-named components of the internal table itab.

Reading all columns of more than one line:

REPORT demo_select_all_columns.

DATA wa TYPE spfli.

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

  WRITE: / sy-dbcnt,
           wa-carrid, wa-connid, wa-cityfrom, wa-cityto.

ENDSELECT.

The list output is:

Since there are no lines specified in the SELECT clause, the statement reads all of the lines from the database table SPFLI that satisfy the condition in the WHEREclause. All of the columns in the table are transferred to the identically-named components of the flat structure WA. This is why you must conclude the SELECT loop with the ENDSELECTstatement.

Aggregate functions

Suppose a database table TEST, consisting of two columns and 10 lines:

COL_1	COL_2
1	3
2	1
3	5
4	7
5	2
6	3
7	1
8	9
9	4
10	3

The following coding demonstrates the aggregate functions:

DATA RESULT TYPE P DECIMALS 2.

SELECT   agg( [DISTINCT] COL_2)
  INTO   RESULT
  FROM   TEST.

WRITE RESULT.

The following table shows the results of this program extract according to different combinations of aggregate expressions agg and the DISTINCTaddition.

Aggregate Expression	DISTINCT	Result
MAX	nein	9.00
MAX	ja	9.00
MIN	nein	1.00
MIN	ja	1.00
AVG	nein	3.80
AVG	ja	4.43
SUM	nein	38.00
SUM	ja	31.00
COUNT	ja	7.00
COUNT( * )	---	10.00

Specifying Columns Dynamically

REPORT demo_select_dynamic_columns.

DATA: itab TYPE STANDARD TABLE OF spfli,
      wa LIKE LINE OF itab.

DATA: line(72) TYPE c,
      list LIKE TABLE OF line(72).

line = ' CITYFROM CITYTO '.
APPEND line TO list.

SELECT DISTINCT (list)
  INTO CORRESPONDING FIELDS OF TABLE itab
  FROM spfli.

IF sy-subrc EQ 0.
  LOOP AT itab INTO wa.
    WRITE: / wa-cityfrom, wa-cityto.
  ENDLOOP.
ENDIF.

The list output is:

The internal table itabcontains the columns of the database table SPFLI to be read. The DISTINCTaddition in the SELECT clause means that the statement only reads those lines that have different contents in both of these columns. The result is a list of possible routes.