SELECT Clause

SELECT clause

Variants:

1. SELECT [SINGLE [FOR UPDATE] | DISTINCT] *
2. SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn
3. SELECT [SINGLE [FOR UPDATE] | DISTINCT] (itab)

Effect

The result of a SELECT statement is itself a table. The SELECT clause describes which columns this table is supposed to have.

In addition, you can use the optional addition SINGLE or DISTINCT if you want only certain lines of the solution set to be visible for the calling program:

SINGLE The result of the selection is a single record. If this record cannot be uniquely identified, the first line of the solution set is selected. The addition FOR UPDATE protects the selected record against parallel changes by other transactions until the next database commit occurs (see Logical Unit of Work (LUW) and Database Locking). If the database system detects a deadlock, the result is a runtime error.

DISTINCT Any lines which occur more than once are automatically removed from the selected dataset.

Note

To ensure that a record is uniquely determined, you can fully qualify all fields of the primary key by linking them together with AND in the WHERE condition.

Effect

In the result set, the columns correspond exactly in terms of order, ABAP/4 Dictionary type and length to the fields of the database table (or view) specified in the FROM clause .

Example

Output all flight connections from Frankfurt to New York:

TABLES SPFLI. 
 
SELECT * FROM SPFLI 
		 WHERE 
		 CITYFROM = 'FRANKFURT' AND 
		 CITYTO   = 'NEW YORK'. 
  WRITE: / SPFLI-CARRID, SPFLI-CONNID. 
ENDSELECT.


Example

Output all free seats on the Lufthansa flight 0400 on 28.02.1995:

TABLES SFLIGHT. 
DATA   SEATSFREE TYPE I. 
 
SELECT SINGLE * FROM SFLIGHT 
				WHERE 
				CARRID   = 'LH '	AND 
				CONNID   = '0400'	 AND 
				FLDATE   = '19950228'. 
SEATSFREE = SFLIGHT-SEATSMAX - SFLIGHT-SEATSOCC. 
WRITE: / SFLIGHT-CARRID, SFLIGHT-CONNID, 
		 SFLIGHT-FLDATE, SEATSFREE.

Variant 2

SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn

Effect

The order, ABAP/4 Dictionary type and length of the columns of the result set are explicitly defined by the list s1 ... sn . Each si has the form

ai or ai AS bi.

Here, ai stands either for

bi is an alternative name for the i-th column of the result set.

When using INTO CORRESPONDING FIELDS OF wa in the INTO clause, you can specify an alternative column name to assign a column of the result set uniquely to a column of the target area.

An aggregate expression uses an aggregate function to group together data from one or all columns of the database table. Aggregate expressions consist of three or four components:

All components of an expression must be separated by at least one blank.

The following aggregate functions are available:

MAX Returns the greatest value in the column determined by the database field f for the selected lines. Specifying DISTINCT does not change the result. NULL values are ignored unless all values in a column are NULL values. In this case, the result is NULL .

MIN Returns the smallest value in the column determined by the database field f for the selected lines. Specifying DISTINCT does not change the result. NULL values are ignored unless all values in a column are NULL values. In this case, the result is NULL .

AVG Returns the average value in the column determined by the database field f for the selected lines. AVG can only apply to a numeric field. NULL values are ignored unless all values in a column are NULL values. In this case, the result is NULL.

SUM Returns the sum of all values in the column determined by the database field f for the selected lines. SUM can only apply to a numeric field. NULL values are ignored unless all values in a column are NULL values. In this case, the result is NULL.

COUNT Returns the number of different values in the column determined by the database field f for the selected lines. Specifying DISTINCT is obligatory here. NULL values are ignored unless all values in a column are NULL values. In this case, the result is 0

COUNT( * ) Returns the number of selected lines. If the SELECT command contains a GROUP BY clause , it returns the number of lines for each group. You can also write COUNT(*) instead of COUNT( * ).

If ai is a field f, MAX( f ), MIN( f ) or SUM( f ), the corresponding column of the result set has the same ABAP/4 Dictionary format as f. With COUNT( f ) or COUNT( * ), the column has the type INT4, with AVG( f ) the type FLTP.

If you specify aggregate functions together with one or more database fields in a SELECT clause, all database fields not used in one of the aggregate functions must be listed in the GROUP-BY clause . Here, the result of the selection is a table.

If only aggregate functions occur in the SELECT clause, the result of the selection is a single record. Here, the SELECT command is not followed later by an ENDSELECT.

This variant is available for pooled tables and cluster tables only if there are no aggregate functions in the SELECT clause.

Examples

Output all flight destinations for Lufthansa flights from Frankfurt:

TABLES SPFLI. 
DATA   TARGET LIKE SPFLI-CITYTO. 
 
SELECT DISTINCT CITYTO 
	 INTO TARGET FROM SPFLI 
	 WHERE 
		 CARRID   = 'LH '	 AND 
		 CITYFROM = 'FRANKFURT'. 
  WRITE: / TARGET. 
ENDSELECT.

Output the number of airline carriers which fly to New York:

TABLES SPFLI. 
DATA   COUNT TYPE I. 
 
SELECT COUNT( DISTINCT CARRID ) 
	 INTO COUNT FROM SPFLI 
	 WHERE 
		 CITYTO = 'NEW YORK'. 
WRITE: / COUNT.

Output the number of passengers, the total weight and the average weight of luggage for all Lufthansa flights on 28.02.1995:

TABLES SBOOK. 
DATA:  COUNT TYPE I, SUM TYPE P DECIMALS 2, AVG TYPE F. 
DATA:  CONNID LIKE SBOOK-CONNID. 
 
SELECT CONNID COUNT( * ) SUM( LUGGWEIGHT ) AVG( LUGGWEIGHT ) 
	 INTO (CONNID, COUNT, SUM, AVG) 
	 FROM SBOOK 
	 WHERE 
		 CARRID   = 'LH '	AND 
		 FLDATE   = '19950228' 
	 GROUP BY CONNID. 
  WRITE: / CONNID, COUNT, SUM, AVG. 
ENDSELECT.

Effect

Works like SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn if the internal table itab contains the list s1 ... sn as ABAP/4 source code, and like SELECT [SINGLE [FOR UPDATE] | DISTINCT] *, if itab is empty. The internal table itab can only have one field which must be of type C and cannot be more than 72 characters long. itab must appear in parentheses and there should be no blanks between the parentheses and the table name.

Note

With this variant, the same restrictions apply as for SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn.

TABLES: SPFLI. 
DATA:   FTAB(72) OCCURS 5 WITH HEADER LINE. 
 
REFRESH FTAB. 
FTAB = 'CITYFROM'. APPEND FTAB. 
FTAB = 'CITYTO'.   APPEND FTAB. 
SELECT DISTINCT (FTAB) 
	 INTO CORRESPONDING FIELDS OF SPFLI 
	 FROM SPFLI 
	 WHERE 
		 CARRID   = 'LH'. 
  WRITE: / SPFLI-CITYFROM, SPFLI-CITYTO. 
ENDSELECT.