Skip to content

ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP - Database Accesses →  Open SQL →  Open SQL - Read Accesses →  SELECT 

SELECT - source

Short Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


... FROM { {dbtab [AS tabalias]} 
         | join 

         | {(dbtab_syntax) [AS tabalias]} } 

         [CLIENT SPECIFIED] 
         [UP TO n ROWS] 

         [BYPASSING BUFFER] 

        [CONNECTION {con|(con_syntax)}] ... .

Alternatives

1. ... dbtab [AS tabalias]

2. ... join

3. ... (dbtab_syntax) [AS tabalias]

Extras

1.... CLIENT SPECIFIED

2.... UP TO n ROWS
3.... BYPASSING BUFFER

Effect

Entries in source specify whether a database table, a view, or multiple database tables or views are accessed by a join expression. Optional additions perform client handling, specify whether SAP buffering is avoided, and determine the maximum number of rows to be read.

Alternative 1

... dbtab [AS tabalias]

Effect

A database table or view defined in ABAP Dictionary can be specified for dbtab. An alternative table name tabalias can be assigned to the database table or the view using the addition AS. This name is valid during the SELECT statement only, and in all other positions where this specification of the database table is addressed, and the actual name does not need to be used.


Note

If a database table or a view appears multiple times after FROM in a join expression, you must use the alternative name to avoid ambiguities.


Example

Reading from the database table spfli and assigning the alternative name s. In this case, the specification of the prefix s~ after ORDER BY can also be omitted, because only one database table is read and the column name carrid is unique. The prefix spfli~ can no longer be used when assigning the alternative name.

DATA wa TYPE spfli. 

SELECT * 
       FROM spfli AS s 
       INTO wa 
       ORDER BY s~carrid. 
  WRITE: / wa-carrid, wa-connid. 
ENDSELECT.

Alternative 2

... join

Effect

Specification of a Join expression that joins multiple database tables or views with one another.

Alternative 3

... (dbtab_syntax) [AS tabalias]

Effect

Instead of static specifications, a data object dbtab_syntax can be specified in brackets. When executing the statement, it must contain the syntax displayed during the static specification. The data object dbtab_syntax can be a character-like data object or a standard table without secondary table keys and with a character-like data object. The syntax in dbtab_syntax is, as in ABAP Editor, not case-sensitve. When specifying an internal table, you can distribute the syntax over multiple rows.

The addition AS can be specified only if dbtab_syntax contains only the name of a single database table or a view. The addition has the same meaning for this database table or view as in a static specification.

When specifying the syntax in dbtab_syntax, the following restrictions apply:

  • Only a list of fields can be specified in a join condition after the language element IN, not a selection table.
  • No database table containing columns of the type RAWSTRING, SSTRING, or STRING can be used in a join expression.


Notes

  • If dbtab_syntax is an internal table with a header line, the header line and not the table body is evaluated.
  • The class CL_ABAP_DYN_PRG contains methods that support the creation of correct and secure dynamic database table specifications.


Example

Displaying the flight connections (flight date, airline name, and flight number) for the user input of a departure city and a destination city. The inner joins are constructed dynamically at runtime. The column specification after SELECT is also dynamic.

PARAMETERS: p_cityfr TYPE spfli-cityfrom, 
            p_cityto TYPE spfli-cityto. 

DATA: BEGIN OF wa, 
         fldate TYPE sflight-fldate, 
         carrname TYPE scarr-carrname, 
         connid   TYPE spfli-connid, 
       END OF wa. 

DATA itab LIKE SORTED TABLE OF wa 
              WITH UNIQUE KEY fldate carrname connid. 
DATA: column_syntax TYPE string, 
      dbtab_syntax TYPE string. 

column_syntax = `c~carrname p~connid f~fldate`. 

dbtab_syntax = `( ( scarr AS c ` 
  & ` INNER JOIN spfli AS p ON p~carrid  = c~carrid` 
  & ` AND p~cityfrom = p_cityfr` 
  & ` AND p~cityto   = p_cityto )` 
  & ` INNER JOIN sflight AS f ON f~carrid = p~carrid ` 
  & ` AND f~connid = p~connid )`. 

SELECT (column_syntax) 
       FROM (dbtab_syntax) 
       INTO CORRESPONDING FIELDS OF TABLE itab. 

LOOP AT itab INTO wa. 
  WRITE: / wa-fldate, wa-carrname, wa-connid. 
ENDLOOP.

Example

See SELECT, dynamic token specification

Addition 1

... CLIENT SPECIFIED

Effect

This addition deactivates automatic client handling in Open SQL. When specifying a single database table or a single view, the addition must be inserted directly after dbtab in the join condition. When specifying a join expression, it must be inserted after the last addition ON of the join condition.

When using the addition CLIENT SPECIFIED, the first column of the client-specific database tables can be specified in the WHERE condition to determine the client identifier. In the addition ORDER BY, the column can be sorted explicitly according to client identifier.


Notes

  • If the addition CLIENT SPECIFIED is specified, the client column is handled like any other column in the table. If the client ID is not specified in the WHERE condition, the selection is made across all clients.
  • If the addition CLIENT SPECIFIED is specified, but the client ID in the WHERE condition is not, the SELECT statement bypasses SAP buffering.
  • Since each client represents a complete unit, automatic client handling should never be turned off in application programs. This is checked by the ABAP runtime environment in multitenancy systems.


Example

Reading all customers in client "800".

DATA wa_scustom TYPE scustom. 

SELECT * 
       FROM scustom CLIENT SPECIFIED 
       INTO wa_scustom 
       WHERE mandt = '800'. 
ENDSELECT.

Addition 2

... UP TO n ROWS

Effect

This addition restricts the number of rows in the result set. n expects a data object of type i. A positive number in n indicates the maximum number of rows in the result set. If n contains the value 0, all selected rows are passed to the result set. If n contains a negative number, an exception that cannot be handled is raised.


Notes

  • The addition UP TO n ROWS should used in preference to a SELECT loop that is canceled after importing n rows. In the latter case, the last package passed from the database to the application server usually contains superfluous rows.
  • If the addition ORDER BY is also specified, the rows of the hit list are sorted on the database server and only the number of sorted rows specified in n are passed to the result set. If the addition ORDER BY is not specified, n arbitrary rows that meet the WHERE condition are passed to the result set.
  • If the addition FOR ALL ENTRIES is also specified, all selected rows are initially read into a system table and the addition UP TO n ROWS only takes effect during the passing from the system table to the actual target area. This can result in unexpected memory bottlenecks.


Example

Reading the three business customers with the highest discount rates:

DATA: wa_scustom TYPE scustom. 

SELECT * 
       FROM scustom UP TO 3 ROWS 
       INTO wa_scustom 
       WHERE custtype = 'B' 
       ORDER BY discount DESCENDING. 
ENDSELECT.

Addition 3

... BYPASSING BUFFER

Effect

This addition causes the SELECT statement to avoid SAP buffering and to read directly from the database and not from the buffer on the application server

Continue

SELECT - join

SELECT - source, connection

Joins