FROM Clause

FROM Clause

Variants:
1. ... FROM dbtab [AS alias]
2. ... FROM (dbtabname)
3. ... FROM tabref1 [INNER] JOIN tabref2 ON cond
4. ... FROM tabref1 LEFT [OUTER] JOIN tabref2 ON cond

Additions:
1. ... CLIENT SPECIFIED
2. ... BYPASSING BUFFER
3. ... UP TO n ROWS

Effect

Used in a SELECT statement to name the source ( database tables and views ) from which the system is to select data.

Effect

Automatic client handling is suspended. This enables you to search all clients in a client-specific table. The client field is treated as a perfectly normal table field, for which you can specify conditions in the WHERE clause.

The CLIENT SPECIFIED addition must come immediately after the name of the database table.

TABLES SCUSTOM. 
 
SELECT * FROM SCUSTOM CLIENT SPECIFIED 
		 WHERE MANDT = '003'. 
  WRITE: / SCUSTOM-ID, SCUSTOM-NAME. 
ENDSELECT.

Effect

The data is read direct from the database, bypassing any SAP buffer there may be.

Example

To output the address of the aircraft manufacturer Boeing:

TABLES SPPROD. 
 
SELECT * FROM SPPROD BYPASSING BUFFER 
		 WHERE PRODUCER = 'BOE'. 
  WRITE: / SPPROD-STREET, SPPROD-NUMB, SPPROD-POSTCODE, 
		 SPPROD-CITY, SPPROD-COUNTRY. 
ENDSELECT.

Effect

The set of results is restricted to a maximum of nrows.

Example

To output a list of the 3 business customers with the greatest discount:

TABLES SCUSTOM. 
 
SELECT * FROM SCUSTOM UP TO 3 ROWS
		 WHERE CUSTTYPE = 'B' 
		 ORDER BY DISCOUNT DESCENDING. 
  WRITE: / SCUSTOM-ID, SCUSTOM-NAME, SCUSTOM-DISCOUNT. 
ENDSELECT.

If you use an UP TO n ROWS addition in an ORDER-BY clause, the lines read are sorted into the correct order. The first n lines are then displayed. The system may need to read more than n lines from the database to be able to do this.

Effect

Reads data from the database table or view dbtab . You specify the name dbtab directly in the program. The name must be known to the ABAP Dictionary , and the program must contain an appropriate TABLES statement.

You can use an alternative table name alial to give column names a unique name in the SELECT, FROM, WHERE, GROUP-BY, or ORDER-BY clauses when you use more than one database table.

TABLES SCUSTOM. 
 
SELECT * FROM SCUSTOM. 
  WRITE: / SCUSTOM-ID, SCUSTOM-NAME. 
ENDSELECT.

Effect

Selects the data from a

database table or view . The name is specified at runtime as the contents of the field dbtabname. The database table must be recognized in the ABAP Dictionary.

DATA  TABNAME(10). 
DATA: BEGIN OF WA, 
		ID   LIKE SCUSTOM-ID, 
		NAME LIKE SCUSTOM-NAME, 
		REST(100), 
	END OF WA. 
 
TABNAME = 'SCUSTOM'. 
SELECT * INTO WA FROM (TABNAME). 
  WRITE: / WA-ID, WA-NAME. 
ENDSELECT.

You can only specify the database table name at runtime if you specify an INTO clause at the same time.

Effect

Selects data from the transparent database tables or views specified in tabref1 and tabref2. tabref1 and tabref2 either have the same form as in variant 1 or are themseleves joine expressions. The key word INNER can be omitted. The database tables or views specified in tabref1 and tabref2 must be recognized in the ABAP/4-Dictionary and declared in the program with an appropriate TABLES statement.

In a relational data structure, it is quite normal for data that belongs together to be split up across several tables to help standardization (see relational database). To regroup this information in a database query, you can link tables using a join command . This formulates conditions for the columns of the tables involved. An inner join contains all combinations of lines from database table tabref1 with lines from database table tabref2 that meet the condition specified in the logical condition ON cond.

Inner join between table 1 and table 2 where column D sets the join condition:

Table 1					Table 2 
|----|----|----|----|		|----|----|----|----|----| 
| A  | B  | C  | D  |		| D  | E  | F  | G  | H  | 
|----|----|----|----|		|----|----|----|----|----| 
| a1 | b1 | c1 | 1  |		| 1  | e1 | f1 | g1 | h1 | 
| a2 | b2 | c2 | 1  |		| 3  | e2 | f2 | g2 | h2 | 
| a3 | b3 | c3 | 2  |		| 4  | e3 | f3 | g3 | h3 | 
| a4 | b4 | c4 | 3  |		|----|----|----|----|----| 
|----|----|----|----| 
 
					\		/ 
					 \	/ 
					\	/ 
					 \  / 
						\/ 
	Inner Join 
	|----|----|----|----|----|----|----|----|----| 
	| A  | B  | C  | D  | D  | E  | F  | G  | H  | 
	|----|----|----|----|----|----|----|----|----| 
	| a1 | b1 | c1 | 1  | 1  | e1 | f1 | g1 | h1 | 
	| a2 | b2 | c2 | 1  | 1  | e1 | f1 | g1 | h1 | 
	| a4 | b4 | c4 | 3  | 3  | e2 | f2 | g2 | h2 | 
	|----|----|----|----|----|----|----|----|----|

Example

Output a list of all flights from Frankfurt to New York between 10th and 20th September, which are not sold out:

DATA: DATE   LIKE SFLIGHT-FLDATE, 
	CARRID LIKE SFLIGHT-CARRID, 
	CONNID LIKE SFLIGHT-CONNID. 
 
SELECT F~CARRID F~CONNID F~FLDATE 
	INTO (CARRID, CONNID, DATE) 
	FROM SFLIGHT AS F INNER JOIN SPFLI AS P 
		 ON F~CARRID = P~CARRID AND 
			F~CONNID = P~CONNID 
	WHERE P~CITYFROM = 'FRANKFURT' 
	AND P~CITYTO   = 'NEW YORK' 
	AND F~FLDATE BETWEEN '19970910' AND '19970920' 
	AND F~SEATSOCC < F~SEATSMAX. 
  WRITE: / DATE, CARRID, CONNID. 
ENDSELECT.

If there are columns in both tables with the same name, you must distinguish between them by prefixing the field descriptor with the table name, or using an alias.

Note

In order to determine the result of a SELECT statement where the FROM clause contains a join, the database system creates a temporary table containing the lines that meet the ON condition. The WHERE condition is then applied to the temporary table. It does not matter in an inner join whether the condition is in the ON or the WHERE clause. The following example returns the same solution as the previous one.

Example

Output a list of all flights from Frankfurt to New York between 10th and 20th September, which are not sold out:

Effect

Instead of the TABLES addition, you should use the USING or CHANGING addition. When you use the TABLES addition, only tables with the table type STANDARD are allowed. The internal tables specified are passed to the FORM together with their header lines. If you use a table without a header line as a TABLES parameter, the system automatically generates a header line for it. This is only valid within the FORM . For this reason, you should not use global commands such as HIDE in the header line. For details of how to specify a type for a TABLES parameter, see specifying a type. TABLES parameters are always passed by reference.

Example

DATA: BEGIN OF X OCCURS 0. 
		INCLUDE STRUCTURE SFLIGHT. 
DATA:   ADDITION(8) TYPE C, 
	END OF X. 
... 
PERFORM U TABLES X. 
... 
FORM U TABLES X STRUCTURE SFLIGHT. 
  WRITE: X-FLDATE. 
ENDFORM.

Effect

Defines the formal parameters p1,...pn, which are replaced by actual parameters when the subroutine is called.
You can assign a type to the formal parameters p1 ... pn (see assigning types). You can also specify the method for passing the parameter.

Note

Passing methods:

Example

TYPES: BEGIN OF FLIGHT_STRUC, 
		 FLCARRID LIKE SFLIGHT-CARRID, 
		 PRICE	LIKE  SFLIGHT-FLDATE, 
	 END   OF FLIGHT_STRUC. 
 
DATA: MY_FLIGHT TYPE FLIGHT_STRUC OCCURS 0, 
	IBOOK1	LIKE SBOOK		OCCURS 0, 
	IBOOK2	LIKE IBOOK1	 OCCURS 0, 
	STRUC	 LIKE SBOOK. 
 
PERFORM DISPLAY USING MY_FLIGHT IBOOK1 IBOOK2 STRUC. 
 
FORM DISPLAY USING  P_ITAB  LIKE	MY_FLIGHT[] 
					P_BOOK1 LIKE	IBOOK1[] 
					P_BOOK2 LIKE	IBOOK2[] 
					P_STRU  LIKE	STRUC. 
 
  DATA: L_FLIGHT  LIKE LINE OF P_ITAB, 
		L_CARRID  LIKE L_FLIGHT-FLCARRID. 
  ... 
  WRITE: / P_STRU-CARRID, P_STRU-CONNID. 
  ... 
  LOOP AT P_ITAB INTO L_FLIGHT WHERE FLCARRID = L_CARRID. 
	... 
  ENDLOOP. 
ENDFORM. Frankfurt to New York between 10th and 20th September 1997:
DATA: DATE   LIKE SFLIGHT-FLDATE, 
	CARRID LIKE SFLIGHT-CARRID, 
	CONNID LIKE SFLIGHT-CONNID. 
 
SELECT F~CARRID F~CONNID F~FLDATE 
	INTO (CARRID, CONNID, DATE) 
	FROM SFLIGHT AS F INNER JOIN SPFLI AS P 
		 ON F~CARRID = P~CARRID 
	WHERE F~CONNID = P~CONNID 
	AND P~CITYFROM = 'FRANKFURT' 
	AND P~CITYTO   = 'NEW YORK' 
	AND F~FLDATE BETWEEN '19970910' AND '19970920' 
	AND F~SEATSOCC < F~SEATSMAX. 
  WRITE: / DATE, CARRID, CONNID. 
ENDSELECT.

Note

Since not all of the database systems supported by SAP use the standard syntax for ON conditions, the syntax has been restricted. It only allows those joins which produce the same results on all of the supported database systems:

Variant 4

... FROM tabref1 LEFT [OUTER] JOIN tabref2 ON cond

Effect

Selects data from the transparent database tables or views specified in tabref1 and tabref2. tabref1 and tabref2 either have the same form as in variant 1 or are themseleves joine expressions. The key word OUTER can be omitted. The database tables or views specified in tabref1 and tabref2 must be recognized in the ABAP Dictionary and declared in the program with an appropriate TABLES statement.

In order to determine the result of a SELECT statement where the FROM clause contains a left outer join, the database system creates a temporary table containing the lines that meet the ON condition. The remaining fields from the left hand table (tabref1) are then added to this table, and their corresponding fields from the right hand table are filled with NULL values. The system then applies the WHERE condition to the table. t

Left outer join between table 1 and table 2 where column D sets the join condition.

Tabelle 1					Tabelle 2 
|----|----|----|----|		|----|----|----|----|----| 
| A  | B  | C  | D  |		| D  | E  | F  | G  | H  | 
|----|----|----|----|		|----|----|----|----|----| 
| a1 | b1 | c1 | 1  |		| 1  | e1 | f1 | g1 | h1 | 
| a2 | b2 | c2 | 1  |		| 3  | e2 | f2 | g2 | h2 | 
| a3 | b3 | c3 | 2  |		| 4  | e3 | f3 | g3 | h3 | 
| a4 | b4 | c4 | 3  |		|----|----|----|----|----| 
|----|----|----|----| 
 
					\		/ 
					 \	/ 
					\	/ 
					 \  / 
						\/ 
	Left Outer Join 
	|----|----|----|----|----|----|----|----|----| 
	| A  | B  | C  | D  | D  | E  | F  | G  | H  | 
	|----|----|----|----|----|----|----|----|----| 
	| a1 | b1 | c1 | 1  | 1  | e1 | f1 | g1 | h1 | 
	| a2 | b2 | c2 | 1  | 1  | e1 | f1 | g1 | h1 | 
	| a3 | b3 | c3 | 2  |NULL|NULL|NULL|NULL|NULL| 
	| a4 | b4 | c4 | 3  | 3  | e2 | f2 | g2 | h2 | 
	|----|----|----|----|----|----|----|----|----|

Example

Output a list of all customers with their bookings (if applicable) for 15th October 1997:

TABLES: SCUSTOM, SBOOK. 
 
SELECT SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY 
	 SBOOK~FLDATE SBOOK~CARRID SBOOK~CONNID SBOOK~BOOKID 
	 INTO (SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY, 
			 SBOOK-FLDATE, SBOOK-CARRID, SBOOK-CONNID, 
			 SBOOK-BOOKID) 
	 FROM SCUSTOM LEFT OUTER JOIN SBOOK 
		 ON SCUSTOM~ID = SBOOK~CUSTOMID AND 
			SBOOK~FLDATE = '19971015' 
	 ORDER BY SCUSTOM~NAME SBOOK~FLDATE. 
  WRITE: / SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY, 
		 SBOOK-FLDATE, SBOOK-CARRID, SBOOK-CONNID, 
		 SBOOK-BOOKID. 
ENDSELECT.

If there are columns in both tables with the same name, you must distinguish between them by prefixing the field descriptor with the table name, or using an alias.

Note

When you use a left outer join in the FROM clause of a SELECT command, it makes a crucial difference whether the logical condition is in the ON or the WHERE clause. Since not all of the database systems supported by SAP themselves support the standard syntax and semantics of the left outer join, the syntax has been restricted to those cases that return the same solution in all database systems:

Example

Example of a JOIN containing more than 2 tables: Select all flights from Frankfurt to New York between 10th and 20th September 1997 where there are free places, and display the name of the airline.

 
DATA: CARRID LIKE SFLIGHT-CARRID, 
	CONNID LIKE SFLIGHT-CONNID, 
	DATE   LIKE SFLIGHT-FLDATE, 
	NAME   LIKE SCARR-CARRNAME. 
 
SELECT F~CARRID F~CONNID F~FLDATE C~CARRNAME 
	INTO (CARRID, CONNID, DATE, NAME) 
	FROM ( SFLIGHT AS F INNER JOIN SPFLI AS P 
			 ON F~CARRID = P~CARRID AND 
				F~CONNID = P~CONNID ) 
		 INNER JOIN SCARR AS C 
			 ON F~CARRID = C~CARRID 
	WHERE P~CITYFROM = 'FRANKFURT' 
	AND P~CITYTO   = 'NEW YORK' 
	AND F~FLDATE BETWEEN '19970910' AND '19970920' 
	AND F~SEATSOCC < F~SEATSMAX. 
  WRITE: / NAME, DATE, CARRID, CONNID. 
ENDSELECT.