FROM Clause

Variants:

1. ... FROM dbtab [AS alias]

2. ... FROM tabref1 [INNER] JOIN       tabref2 ON cond

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

4. ... FROM (source_text) [AS alias]

Extras:

1. ... CLIENT SPECIFIED

2. ... BYPASSING BUFFER

3. ... UP TO n ROWS

4. ... CONNECTION con

Effect

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

In some cases, the syntax rules that apply to Unicode programs are different than those for non-Unicode programs.See Open SQL and Unicode.

Addition 1

... CLIENT SPECIFIED

Effect

Automatic client handling is suspended. This enables
you, for client-dependent tables, to search for data in all clients. The client field is treated as a standard table field for which you can specify suitable conditions in the WHERE clause .

If you are only reading from a single table, the CLIENT SPECIFIED addition must come directly after the name of the table; in the case of JOINs, the addition must come after the ON condition.

Example

Output of a list of all customers in client 3:

DATA: WA_SCUSTOM TYPE SCUSTOM.

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

Addition 2

... BYPASSING BUFFER

Effect

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

Example

To output the address of the aircraft manufacturer Boeing:

DATA: WA_SPPROD TYPE SPPROD.

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

Addition 3

... UP TO n ROWS

Effect

The set of results is restricted to n lines
maximuum.

Example

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

DATA: WA_SCUSTOM TYPE SCUSTOM.

SELECT * FROM SCUSTOM INTO WA_SCUSTOM UP TO 3 ROWS

         WHERE CUSTTYPE = 'B'
         ORDER BY DISCOUNT DESCENDING.
  WRITE: / WA_SCUSTOM-ID, WA_SCUSTOM-NAME,
           WA_SCUSTOM-DISCOUNT.
ENDSELECT.

Notes

1. If you combine UP TO n ROWS with an ORDER-BY clause , the lines read are sorted in the order specified and the first n are displayed. The system may need to read more than n lines from the database to be able to do this.
   
2. If n = 0, all the selected lines are displayed.
   
3. n < 0 causes a runtime error.
   
   
   

Addition 4

... CONNECTION con

Effect

The Open SQL command is not executed on the
standard database but on the Secondary Database Connection specified using con. con is the name of the database connection as it was specified in the table DBCON in the column CON_NAME. The database connection con can als be specified dynamically in the form (source_text) - the field source_text contains the name of the database connection and must be type C oder STRING.

Variant 1

... FROM dbtab [AS alias]

Effect

The data is to be selected from the database table or the view dbtab. The name dbtab is specified directly in the program. dbtab must be recognized by the ABAP Dictionary.

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

Example

Outputs a list of all customers:

DATA: WA_SCUSTOM TYPE SCUSTOM.

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

Variant 2

... FROM tabref1 [INNER] JOIN tabref2 ON cond

Effect

The data is to be selected from transparent database tables and/or views determined by tabref1 and tabref2. tabref1 and tabref2 each have the same form as in variant 1 or are themselves Join expressions. The keyword INNER does not have to be specified. The database tables or views determined by tabref1 and tabref2 must be recognized by the ABAP Dictionary.

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

Inner join between table 1 and table 2, where column D in both tables in the join condition is set the same:

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 September 10th and 20th, 2001 that 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 '20010910' AND '20010920'
      AND F~SEATSOCC < F~SEATSMAX.
  WRITE: / DATE, CARRID, CONNID.
ENDSELECT.

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

Note

In order to determine the result of a SELECT command where the FROM clause contains a join, the database system first 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 WHEREclause. The following example returns the same solution as the previous one.

Example

Output of a list of all flights from Frankfurt to New York between September 10th and 20th, 2001 that 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
    WHERE F~CONNID = P~CONNID
      AND P~CITYFROM = 'FRANKFURT'
      AND P~CITYTO   = 'NEW YORK'
      AND F~FLDATE BETWEEN '20010910' AND '20010920'
      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 that produce the same results on all of the supported database systems:

1. Only a table or view may appear to the right of the JOIN operator, not another join expression.
   
2. Only AND is possible in the ON condition as a logical operator.
   
3. Each comparison in the ON condition must contain a field from the right-hand table.
   
4. If an outer join occurs in the FROM clause, all the ON conditions must contain at least one "real" JOIN condition (a condition that contains a field from tabref1 amd a field from tabref2.
   
   
   

Note

In some cases, '*' may be specified in the SELECT clause, and an internal table or work area is entered into the INTO clause (instead of a list of fields). If so, the fields are written to the target area from left to right in the order in which the tables appear in the FROM clause, according to the structure of each table work area. There can then be gaps between table work areas if you use an Alignment Request. For this reason, you should define the target work area with reference to the types of the database tables, not simply by counting the total number of fields. For an example, see below:

Variant 3

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

Effect

Selects the data from the transparent database tables and/or views specified in tabref1 and tabref2. tabref1 und tabref2 both have either the same form as in variant 1 or are themselves join expressions. The keyword OUTER can be omitted. The database tables or views specified in tabref1 and tabref2 must be recognized by the ABAP-Dictionary.

In order to determine the result of a SELECT command 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 ZERO values. The system then applies the WHERE condition to the table.

Left outer join between table 1 and table 2 where column D in both tables set 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  |        |----|----|----|----|----|
|----|----|----|----|

                    \        /
                     \      /
                      \    /
                       \  /
                        \/
    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 custimers with their bookings for October 15th, 2001:

DATA: CUSTOMER TYPE SCUSTOM,
      BOOKING  TYPE SBOOK.

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

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

Note

For the resulting set of a SELECT command with a left outer join in the FROM clause, it is generally of crucial importance whether a logical condition is in the ON or WHERE condition. 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:

1. Only a table or view may come after the JOIN operator, not another join statement.
   
2. The only logical operator allowed in the ON condition is AND.
   
3. Each comparison in the ON condition must contain a field from the right-hand table.
   
4. Comparisons in the WHERE condition must not contain a field from the right-hand table.
   
5. The ON condition must contain at least one "real" JOIN condition (a condition in which a field from tabref1 as well as from tabref2 occurs).
   
   
   

Note

In some cases, '*' may be specivied as the field list in the SELECT clause, and an internal table or work area is entered in the INTO clause (instead of a list of fields). If so, the fields are written to the target area from left to right in the order in which the tables appear in the llen in der FROM clause, according to the structure of each table work area. There can be gaps between the table work areas if you use an Alignment Request. For this reason, you should define the target work area with reference to the types of the database tables, as in the following example (not simply by counting the total number of fields).

Example

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

DATA: BEGIN OF WA,
        FLIGHT TYPE SFLIGHT,
        PFLI   TYPE SPFLI,
        CARR   TYPE SCARR,
      END OF WA.

SELECT * INTO WA
    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 '20010910' AND '20010920'
      AND F~SEATSOCC < F~SEATSMAX.
  WRITE: / WA-CARR-CARRNAME, WA-FLIGHT-FLDATE, WA-FLIGHT-CARRID,
           WA-FLIGHT-CONNID.
ENDSELECT.

Variant 4

... FROM (source_text) [AS alias]

Effect

Works like variants 1-3, provided the source_text varialb contains the table name or a join expression as ABAP source text.

Examples

Output of a list of all customers:

DATA  tabname(10).
DATA: BEGIN OF wa,
        id   TYPE scustom-id,
        name TYPE scustom-name,
      END OF wa.

tabname = 'SCUSTOM'.
SELECT id name INTO CORRESPONDING FIELDS OF wa FROM (tabname).
  WRITE: / wa-id, wa-name.
ENDSELECT.

Output of all flight connections with the airline name and the flight number where a dynamic join is set up at runtime.

CONSTANTS: flight_tab_name(30) VALUE 'SPFLI'.

DATA: from_clause TYPE STRING.
DATA: BEGIN OF wa,
        name(20) TYPE C,
        connid   TYPE spfli-connid,
      END OF wa.

CONCATENATE flight_tab_name ' AS t1'
            ' JOIN scarr AS t2 ON t1~carrid = t2~carrid'
   INTO from_clause.

SELECT t1~connid t2~carrname AS name
    FROM (from_clause)
    INTO CORRESPONDING FIELDS OF wa.
  WRITE: / wa-name, wa-connid.
ENDSELECT.

Notes

1. You can only specify a FROM clause at runtime if you also specify an INTO clause.
   
   
2. If ON conditions are specified in source_text, you have the following limitations:
   
   - Only literals, not variables, can be used as values.
   
   - The IN operator cann be used in the form f1 IN itab1.
   
   
3. The variable source_text must not be empty.
   
   
4. The AS alias addition may only be used in the form ... FROM (source_text) AS alias if the source_text has neither a join expression nor a table alias.
   
   
5. If you have a statement in the form SELECT * FROM (source_text) INTO wa , no join expression with a table containing strings must be specified in the source_text. However, if you specify a single table, this may contain strings.
   
   
   

When you perform a grammatical analysis of the source text in source_text, the same exceptions can occur as in a dynamic logical condition. In addition, the following exceptions can occur:

Exceptions

Catchable Exceptions

CX_SY_DYNAMIC_OSQL_SYNTAX:

• Cause: The variable source_text is empty.
  Runtime Error: SAPSQL_EMPTY_TABNAME
  
• Cause: An unknown join operator was found.
  Runtime Error: SAPSQL_ILLEGAL_JOIN
  
• Cause: The right operand of a join operator is a join expression.
  Runtime Error: SAPSQL_JOIN_LEFT_DEEP
  
• Cause: In INSERT, UPDATE, MODIFY, or DELETE, a join expression was specified instead of a dynamic table name.
  Runtime Error: SAPSQL_JOIN_NO_SELECT
  
• Cause: There was an attempt to link the table on the right side of a LEFT JOIN with several different tables through ON conditions.
  Runtime Error: SAPSQL_JOIN_OJ_ON_ONCE
  
• Cause: In an ON condition, two database fields are to be comparied. These fields are fields of tables that are on the same side as the join operator that belongs to this ON condition.
  Runtime Error: SAPSQL_JOIN_OJ_PROPER_STAR
  
• Cause: In ON conditions, fields that belong to tables that are both on the right side of a LEFT JOIN are compared.
  Runtime Error: SAPSQL_JOIN_OJ_1SIDED
  
• Cause: An ON condition does not contain any column of the right join operand.
  Runtime Error: SAPSQL_JOIN_ON_CMP
  
• Cause: An ON condition is empty.
  Runtime Error: SAPSQL_JOIN_ON_EMPTY
  
• Cause: An incorrect compare operator BETWEEN or [NOT] LIKE was used.
  Runtime Error: SAPSQL_JOIN_ON_OJ_CMP
  
• Cause: In a join, the ON condition is missing.
  Runtime Error: SAPSQL_MISSING_ON_CONDITION
  
• Cause: In UPDATE or DELETE, there was an attempt to access the table of a main query in a subquery.
  Runtime Error: SAPSQL_MODIFYING_SELFSQ
  
• Cause: A RIGHT JOIN was used.
  Runtime Error: SAPSQL_NO_RIGHT_JOIN
  
• Cause: In an ON condition, the operator NOT was used.
  Runtime Error: SAPSQL_JOIN_ON_ILLEGAL_NOT
  
• Cause: In an ON condition, the operator OR was used.
  Runtime Error: SAPSQL_JOIN_ON_ILLEGAL_OR
  
• Cause: In a join, too many tables are linked.
  Runtime Error: SAPSQL_TOO_MANY_TABLES
  
• Cause: One of the database tables that is in a dynamically specified join contains a string column. SELECT * is used to select from this join and the resulting set is to be stored in a work area using INTO wa . This is not supported in Open SQL.
  Runtime Error: SAPSQL_DYN_JOIN_WITH_STRING
  

CX_SY_DYNAMIC_SQL_SEMANTICS:

• Cause: A table in a subquery is not transparent.
  Runtime Error: SAPSQL_SUBQUERY_ILL_TABLE_TYPE
  
• Cause: A table in a join is not transparent or it is a projecti on view.
  Runtime Error: SAPSQL_JOIN_ILLEGAL_TABLE_TYPE
  
• Cause: Grouping is to be done according to a field in a non-transparent table.
  Runtime Error: SAPSQL_GROUP_BY_ILL_TABLE_TYPE
  
• Cause: An aggregate function of a column in a non-transparent table is to be created.
  Runtime Error: SAPSQL_AGGREGATE_ILL_TABTYPE
  

Additional help

Specify Database Tables.