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 1 [AS a 1 ] s 2 [AS a 2 ] ...
- where s 1 s 2 … are single columns. Using the ASaddition, an alternative column name a 1 a 2 … can be defined for each column s 1 s 2 …. 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 1 into a component a 1 of a structure when you use the INTO CORRESPONDING FIELDS variant of the INTOcondition.
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 1 ) [AS a 1 ] agg( [DISTINCT] s 2 ) [AS a 2 ] ...
where s 1 s 2 … 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 1 a 2 … 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 1 s 2 … ... 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.