Skip to content

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

SELECT - FROM

Short Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


... FROM { {data_source [AS tabalias]} 

         | join 

         | {(source_syntax) [AS tabalias]} } 

         [ {USING CLIENT clnt} 

        | {CLIENT SPECIFIED [entity1~clnt] [, entity2~clnt] ...} ] ...

Alternatives

1. ... source

2. ... join
3. ... (source_syntax)

Extras

1.... AS tabalias

2.... USING CLIENT

3.... CLIENT SPECIFIED [entity1~clnt] [, entity2~clnt] ...

Effect

The information specified after FROM specifies whether a database table, a classic view, a CDS view, or whether multiple database tables or views are accessed by a join expression. Optional additions define an alternative table name and regulate client handling.

Alternative 1

... source

Effect

Specify data_source an individual database table or view.


Note

If a database table or a view appears multiple times after FROM in a join expression, an alternative name tabalias must be specified after AS to avoid ambiguities.

Alternative 2

... join

Effect

Specifies a join expression that joins multiple database tables or views with one another.

Alternative 3

... (source_syntax)

Effect

Instead of specifying information statically, a data object source_syntax can be specified in parentheses. When the statement is executed, the data object must contain the syntax displayed for the statically specified information. The data object source_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 source_syntax is not case-sensitive (as is the case in ABAP Editor). When an internal table is specified, the syntax can be distributed across multiple rows.

The addition AS can be specified only if source_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 when specified statically.

When specifying the syntax in source_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.

Security Note

If used wrongly, dynamic programming techniques can present a serious security risk. Any dynamic content that is passed to a program from the outside must be checked thoroughly or escaped before being used in dynamic statements. This can be done using the system class CL_ABAP_DYN_PRG or the predefined function escape. See SQL Injections Using Dynamic Tokens.


Notes

  • If source_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 dynamically specified database tables when created correctly and securely.

  • The literals of the dynamically specified Open SQL statements can span multiple rows of a token specified dynamically as an internal table.

  • When specified dynamically, Open SQL statements can contain the comment characters * and " as follows:

  • In a dynamic token specified as a character-like data object, all content is ignored from the first comment character ".

  • In a dynamic token specified as an internal table, all rows are ignored that start with the comment character *. In the row, all content is ignored from the first comment character ".

Comment characters placed within literals are, however, part of the literal.

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 specified after SELECT is also dynamic. The values entered on the selection screen are specified dynamically using the name of the parameter in question. They are not chained directly. If they were, a special security check would be required for these parameters.

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, 
      source_syntax TYPE string. 

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

source_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 (source_syntax) 
       INTO CORRESPONDING FIELDS OF TABLE @itab. 

cl_demo_output=>display_data( itab ).

Example

See SELECT, Dynamically Specified Tokens

Addition 1

... AS tabalias

Effect

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 specified database table is addressed, and the actual name does not need to be used.

The alternative table name tabalias can have a maximum of 30 places and can contain letters, digits, the minus sign (-), and the underscore (_) in any order.


Notes

  • An alternative table name tabalias prevents the original name data_source from being used and this applies in principle to dynamic tokens too. This is not checked, however, until the introduction of the strict modes in the syntax check from Release 7.40, SP08 and may raise an exception.
  • If an alternative table name matches an addition of the SELECT statement exactly, it may be necessary to prefix it with the escape character !.

Addition 2

... USING CLIENT clnt

Effect

This addition modifies automatic client handling in Open SQL so that the client ID from clnt is used instead of the current client ID. The client specified in clnt is used in all places where, in implicit SELECTs, the current client is used in automatic client handling.

clnt expects a data object of the type c with length 3 and containing a client ID. If specified, the system field sy-mandt would be ignored and cannot be specified directly for clnt.

When a single database table or a single view is specified, the addition must be inserted directly after data_source in the join condition. When a join expression is specified, the addition must be inserted after the last addition ON of the join condition. This addition cannot be used with the addition CLIENT SPECIFIED.

The client handler can be switched precisely once for each SELECT statement and is applied to all client-specific database tables or views specified in the statement, for example in join expressions and in subqueries. If the database table or the view is specified statically, the addition can be specified only if at least one client-specific table or view is used; otherwise a syntax error occurs. The addition cannot be specified for a subquery in the WHERE condition.


Notes

  • If the addition USING CLIENT is used, the statement SELECT functions as if the current user were logged on with the client ID specified in clnt.
  • If clnt contains a client ID for a nonexistent client, the statement SELECT is executed with this ID and the results set is empty.
  • If the database table or view is specified statically, the addition USING CLIENT in SELECT can always be specified. There is no runtime error if no client-specific tables or views are used.
  • The addition USING CLIENT can also be used for writes.
  • The addition USING CLIENT is not allowed in the obsolete short form of SELECT.
  • When the addition USING CLIENT, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.
  • Since each client represents a complete unit, automatic client handling should never be switched in application programs.
  • If data has to be accessed in a different client, USING CLIENT should be used instead of the addition CLIENT SPECIFIED, since all necessary conditions are set implicitly and accessing client-specific CDS views is more simple.
  • See also the associated security note and the programming guideline.


Example

Reading all customers in client "800".

SELECT * 
       FROM scustom USING CLIENT '800' 
       INTO TABLE @DATA(customers).

Addition 3

... CLIENT SPECIFIED [entity1~clnt] [, entity2~clnt ] ...

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.

If one or more client-specific CDS views are specified after FROM, two cases are possible:

  • If the CDS view (cds_entity) is addressed as CDS entity, the client column is read from the database and added to the result set if CLIENT SPECIFIED is specified. This occurs even though the SELECT list of the CDS view does not have a client column. To address a client column of a client-specific CDS view in the SELECT statement, a name clnt must be declared for the column using entity~clnt. Here entity is the entity name of a client-specific CDS view listed after FROM and clnt is a freely definable name for its client column, which is valid throughout the current SELECT statement. This name can be used in the additions of the SELECT statement as a column name and is included in addition CORRESPONDING after INTO.
  • If the CDS view (CDS_DB_VIEW) is specified as a CDS database view, the client column from the view is read from the database (if CLIENT SPECIFIED is specified) and is added to the result set under the view's name (as is the case every time a classic view is accessed).


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.
  • When automatic client handling is deactivated for CDS entities, a suitable target area can be declared by using the addition CLIENT SPECIFIED of the statement TYPES. This is not necessary for CDS database views because their structure type has a client column for client-specific CDS views.
  • A name defined with entity~clnt has absolutely no dependency on the actual name of a client column in a data source of a CDS view.
  • A name defined with entity~clnt is used in a WHERE or ON condition, for example, to select specific clients of a CDS view.
  • Since each client represents a complete unit, automatic client handling should never be turned off in application programs.
  • If data has to be accessed in a different client, USING CLIENT should be used instead of the addition CLIENT SPECIFIED, since all necessary conditions are set implicitly and accessing client-specific CDS views is more simple.
  • See also the associated security note and the programming guideline.


Example

Like the example for USING CLIENT, this example reads all customers in client 800, but needs an explicit WHERE condition to do this.

SELECT * 
       FROM scustom CLIENT SPECIFIED 
       WHERE mandt = '800' 
       INTO TABLE @DATA(customers).

Continue

SELECT - data_source

SELECT - JOIN