Defining Selections (SAP Library - ABAP Programming (BC-ABA))

Defining Selections

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> ...

<lines> specifies whether you want to read one or more lines. <cols> defines the column selection.

Reading a Single Line

To read a single entry from the database, use the following:

SELECT SINGLE <cols> ... WHERE ...

To ensure that the line can be uniquely identified, you must specify values for all of the fields of the primary key of the table in the WHERE clause. If the WHERE clause does not contain all of the key fields, the syntax check produces a warning, and the SELECT statement reads the first entry that it finds that matches the key fields that you have specified.

The result of the selection is either an elementary field or a flat structure, depending on the number of columns you specified in <cols>. The target area in the INTO clause must be appropriately convertible.

If the system finds a line with the corresponding key, SY-SUBRC is set to 0, otherwise to 4.

Reading Several Lines

To read a several entries from the database, use the following:

SELECT [DISTINCT] <cols> ... WHERE ...

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.

The result of the selection is a table. The target area of the INTO clause can be an internal table with a line type appropriate for <cols>. If the target area is not an internal table, but a flat structure, you must include an ENDSELECT statement after the SELECT statement:

SELECT [DISTINCT] <cols> ... WHERE ...
...
ENDSELECT.

The lines are read in a loop one by one into the target area specified in the INTO clause You can work with the target area within the loop.

If at least one line is read, SY-SUBRC is set to 0 after the statement (or loop) has been processed. If no lines are read, SY-SUBRC is set to 4. The number of lines read is placed in the system field SY-DBCNT. Within the loop, SY-DBCNT already contains the number of lines that have already been passed to the target area.

Technically, it is possible to nest SELECT loops. However, for performance reasons, you should avoid doing so. If you want to read interdependent data from more than one database table, you can use a join in the FROM clause or a subquery in the WHERE clause.

Reading the Whole Line

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

SELECT <lines> * ...

This reads all columns for the specified lines. The data type of the selected lines is a structure with exactly the same data type as the database table in the ABAP Dictionary. The target area of the INTO clause should be compatible with, or at least convertible into this data type. In the other clauses, you can only address the columns under their names in the database table.

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:

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

where <s _i > are single columns. There are different ways of addressing the columns, depending on the form of your FROM clause:

<s _i > specifies the component <c _i > of the database table. This is only possible if the corresponding name is unique. This is always the case if you only specify a single database table in the FROM clause.

<s _i > specifies the full name of the required component in the form <dbtab>~<c _i >, where <dbtab> is the name of the database table. This is always necessary if the component name appears in more than one database table in the FROM clause, and is only possible if the database table <dbtab> only appears once in the FROM clause.

<s _i > specifies the full name of the required component in the form <tabalias>~<c _i >, where <tabalias> is an alias name for the database table. This is always necessary if the table from which you want to read occurs more than once in the FROM clause. You must define the alias name <tabalias> in the FROM clause.

In the SELECT clause, you can use the AS addition to specify an alias name <a _i > for each column <s _i >. The alias column name is used instead of the real name in the INTO and ORDER BY clauses. This allows you for example, to read the contents of a column <s _i > into a component <a _i > of a structure when you use the INTO CORRESPONDING FIELDS OF addition.

The data type of a column is the Dictionary type of the corresponding underlying domain in the ABAP Dictionary. You must choose the data types of the target fields in the INTO clause so that the Dictionary types can easily be converted. For a table of Dictionary types and their corresponding ABAP data types, refer to Data Types in the ABAP Dictionary.

Reading Aggregate Data for Columns

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

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

where <s _i > are the same field labels as above. The expression <agg> represents one of the following aggregate functions:

MAX: returns the maximum value of the column <s _i >

MIN: returns the minimum value of the column <s _i >

AVG: returns the average value of the column <s _i >

SUM: returns the sum value of the column <s _i >

COUNT: counts values or lines as follows:

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

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

You can exclude duplicate values from the calculation using the DISTINCT option. The spaces between the parentheses and the arguments of the aggregate expressions must not be left out. The arithmetic operators AVG and SUM only work with numeric fields.

The data type of aggregate functions using MAX, MIN, or SUM is the Dictionary type of the corresponding column. Aggregate expressions with the function AVG have the Dictionary type FLTP, and those with COUNT have the Dictionary type INT4. The target field should have the corresponding type. When you calculate average values, it is a good idea to use the ABAP type F. However, remember that the database system may use different approximations to ABAP. When you calculate a sum, ensure that the target field is large enough.

Unlike ABAP, database systems recognize null values in database fields. A null value means that a field has no contents, and it is not included in calculations. The result of the calculation is only null if all of the lines in the selection contain the null value in the corresponding field. In ABAP, the null value is interpreted as zero (depending on the data type of the field).

In the AS addition, you can define an alternative column name <a _i > for each aggregate expression. The alias column name is used instead of the real name in the INTO and ORDER BY clauses. This is the only way of sorting by an aggregate expression in the ORDER BY clause.

If the list in the SELECT clause (excepting aggregate expressions) contains one or more field names, the field names must also be listed in the GROUP BY clause. The aggregate functions do not then apply to all of the selected lines, but to the individual groups of lines.

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. For this purpose, the line type of <itab> must be a type C field with a maximum length of 72. If the internal table is empty, the system reads all columns.

Examples

Example

Reading certain columns of a single line:

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 output is:

This graphic is explained in the accompanying text

SINGLE in the SELECT clause 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:

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 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 WHERE clause. The columns specified in the SELECT clause are transferred to the identically-named components of the internal table ITAB.

Example

Reading all columns of more than one line:

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 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 WHERE clause. 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 ENDSELECT statement.

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 DISTINCT option.

Aggregate expression	DISTINCT	Result
MAX	no	9.00
MAX	yes	9.00
MIN	no	1.00
MIN	yes	1.00
AVG	no	3.80
AVG	yes	4.43
SUM	no	38.00
SUM	yes	31.00
COUNT	yes	7.00
*COUNT( )**	---	10.00

Example

Specifying Columns Dynamically

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 output is:

This graphic is explained in the accompanying text

The internal table ITAB contains the columns of the database table SPFLI to be read. The DISTINCT addition 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.