Entering content frame

Defining a Selection Locate the document in its SAP Library structure

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

This graphic is explained in the accompanying text

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 s1 [AS a1] s2 [AS a2] ...

        where s1 s2 … are single columns. Using the ASaddition, an alternative column name a1 a2 … can be defined for each column s1 s2 …. 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 s1 into a component a1 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] s1) [AS a1]
             agg( [DISTINCT] s2) [AS a2]
...

where s1 s2 … 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 a1 a2 … 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 s1 s2 … ... to specify the columns or aggregate expressions to be read. If the internal table is empty, the system reads all columns.

Example

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:

This graphic is explained in the accompanying text

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.

Example

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:

This graphic is explained in the accompanying text

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.

Example

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:

This graphic is explained in the accompanying text

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.

Example

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

Example

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:

This graphic is explained in the accompanying text

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.

 

 

 

Leaving content frame