Skip to content

ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Reads →  SELECT clauses →  SELECT - FROM 

SELECT - FROM data_source

Quick Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


...  dbtab |view  [WITH PRIVILEGED ACCESS] 

   | cds_entity[
sql_para][
sql_path]|[ WITH PRIVILEGED ACCESS] 

   | +cte[sql_path] 

   | hierarchy_data 

   | @itab

Alternatives

1. ... dbtab |view  [WITH PRIVILEGED ACCESS]

2. ... cds_entity[sql_para][sql_path]|[ WITH PRIVILEGED ACCESS]

3. ... +cte[sql_path]

4. ... hierarchy_data
5. ... @itab

Effect

Specifies an individual database table dbtab, classic view view, non-abstract CDS entity cds_entity, a common table expression +cte, hierarchy data, or an internal table @itab in the FROM clause of a query. Only views whose key fields are located together at the beginning of the view can be accessed.

Alternative 1

... dbtab |view  [WITH PRIVILEGED ACCESS]

Addition

... WITH PRIVILEGED ACCESS

Effect

Specifies an ABAP Dictionary database table dbtab of a classic view or an external view view. . Classic views on which reads can be performed using SELECT are database views and projection views.


Notes

  • If a CDS view is defined as a replacement object for a database table or database view specified as a data source, the SELECT statement accesses the CDS view and not the database table or the database view. In this case, it is recommended that the CDS view is used as a data source if possible.
  • The database table or view must be specified using the exact name defined for it in ABAP Dictionary. More specifically, it cannot be prefixed with the name for the database schema. An ABAP SQL statement always accesses the database schema assigned to the current database user name. In cases where the standard connection or a service connection is used, this is the ABAP database schema. In cases where a secondary connection is used, this is the database schema assigned to the database user defined in the secondary connection. Here, each access assumes that the database table or view exists under this exact name in the current AS ABAP ABAP Dictionary, regardless of the connection.


Example

Gets the data of the database view DEMO_SCARR_SPFLI.

SELECT * 
       FROM demo_scarr_spfli 
       ORDER BY id, carrier, flight, departure, destination 
       INTO TABLE @DATA(result).

Addition

... WITH PRIVILEGED ACCESS

Effect

The addition WITH PRIVILEGED ACCESS is currently ignored by database tables and classic views .


Notes

  • If CDS access control is implemented for database tables and classic views in a future release, the addition WITH PRIVILEGED ACCESS will turn it off.
  • If the addition WITH PRIVILEGED ACCESS is specified, the syntax check is made in strict mode from Release 7.52.

Alternative 2

... cds_entity[sql_para][sql_path]|[ WITH PRIVILEGED ACCESS]

Extras

1. ... sql_path
2. ... WITH PRIVILEGED ACCESS

Effect

Specifies a non-abstract CDS entity cds_entity created with the CDS DDL in ABAP CDS. Possible CDS entities are:

The CDS entity is specified using its name cds_entity defined after DEFINE VIEW, DEFINE TABLE FUNCTION, or DEFINE HIERARCHY.

  • If the CDS entity has input parameters, actual parameters must be assigned to these in a parenthesized list sql_para.
  • A path expression sql_path can be specified for CDS views or CDS hierarchies with CDS associations. This expression defines its target data source as a data source of the SELECT statement.

CDS table functions and CDS hierarchies are database extensions that are not supported by all database systems. The CDS DDL in ABAP CDS, however, enables CDS entities to be created and used as data sources regardless of the database system. In ABAP programs, CDS entities like this can also be specified as a data source of a SELECT statement regardless of the database system. The following applies here:

  • A handleable exception of the class CX_SY_SQL_UNSUPPORTED_FEATURE is raised if the statement detects that the current database system does not support the feature. The same applies when CDS entities are accessed that contain CDS table functions or CDS hierarchies as data sources.
  • When used, CDS entities like this produce syntax warnings from the extended program check. These warnings can be hidden using the pragma
  • ##db_feature_mode[amdp_table_function]
.

CDS hierarchies are handled like hierarchies and special rules apply.


Notes


Example

Gets the data of the CDS view DEMO_CDS_SCARR_SPFLI.

SELECT * 
       FROM demo_cds_scarr_spfli 
       ORDER BY id, carrier, flight, departure, destination 
       INTO TABLE @DATA(result).

Addition 1

... sql_path

Effect

Specifies a path expression sql_path after a CDS entity. In this case, the entity must be a CDS view or CDS hierarchy that publishes the first CDS association of the path expression in its SELECT list or element list. A CDS view must be specified using its name cds_entity defined after DEFINE VIEW. All CDS associations of the path expression must be published in the SELECT lists of the CDS entities in question for use from outside.

The target data source of the last CDS association of the path expression is the data source of the current SELECT statement. The data is read in accordance with the join conditions of the CDS associations and the other conditions of the CDS views in question.

The path expression publishes only the elements of its target data source in the SELECT statement. If an element of this type is used as a column specified in other clauses of the SELECT statement and the column is assigned to the data source using the column selector ~, an alias name must be defined and used here using AS. A path expression in the data source of the FROM clause must have an alias name defined with AS. This is checked in strict mode as of release 7.52.


Notes

  • A CDS entity cds_entity with a path expression can be used in join expressions, like any other data source.
  • In the SELECT statement, it is not possible to access elements of the entities of the path expression that are in front of the target data source. This applies more specifically to the elements of the specified entity cds_entity. Regular access to these elements is possible only if they are specified again as data sources of explicit join expressions in the statement.
  • The obsolete addition CLIENT SPECIFIED cannot be used if a data source is specified using a path expression.
  • As the path expression is specified directly after cds_entity, source cannot and must not be specified here.
  • A path expression cannot contain CDS associations of an abstract CDS entity.


Example

Accesses the CDS view DEMO_CDS_ASSOC_SAIRPORT_TZ with parameter passing and a path expression with the CDS associations _spfli and _scarr in the FROM clause.

SELECT FROM demo_cds_assoc_sairport_tz( tz = 'UTC+1' ) 
            \_spfli 
            \_scarr[ currcode = 'EUR' ] 
            AS scarr 
       FIELDS carrname 
       ORDER BY carrname 
       INTO TABLE @DATA(result).

Executable Example

Path Expressions, Use in the FROM Clause

Addition 2

... WITH PRIVILEGED ACCESS

Effect

The addition WITH PRIVILEGED ACCESS switches CDS access control off.

When a CDS entity is accessed that is associated with a CDS role, its access conditions are not evaluated.

The addition WITH PRIVILEGED ACCESS cannot be used together with a path expression sql_path. It is applied only to that CDS entity for which it is specified. It is not applied to the CDS entities published using CDS associations of the specified CDS entity.


Notes

  • The addition WITH PRIVILEGED ACCESS overrides all delivered and self-defined roles.
  • CDS access control can also be switched off using the annotation @AccessControl.authorizationCheck:#NOT_ALLOWED when a non-abstract CDS entity is defined.
  • In updates, the addition WITH PRIVILEGED ACCESS must be specified when accessing all CDS entities for which CDS access control is not disabled using the annotation @AccessControl.authorizationCheck:#NOT_ALLOWED.
  • If the addition WITH PRIVILEGED ACCESS is specified, the syntax check is made in strict mode from Release 7.52.


Example

Uses the addition WITH PRIVILEGED ACCESS when the CDS view DEMO_CDS_AUTH_LITERAL is accessed, which is associated with the following CDS role:

@MappingRole: true
define role demo_cds_role_literal {
  grant select on demo_cds_auth_literal
  where carrid = 'LH'; }

Unlike the program DEMO_CDS_AUTH_LITERAL, the following read reads all data of the view.

SELECT * 
       FROM demo_cds_auth_literal WITH PRIVILEGED ACCESS 
       ORDER BY carrid 
       INTO TABLE @DATA(result). 

cl_demo_output=>display( result ).

Alternative 3

... +cte[sql_path]

Addition

... sql_path

Effect

Specifies a common table expression cte in a subquery or the closing main query of a WITH statement.

The SELECT statement accesses the results set of the common table expression. All common table expressions can be used that were defined in the same WITH statement in front of the current SELECT statement.


Note

If a common table expression is used as the data source, a temporary database table is accessed, which is available during the WITH statement.


Example

Accesses the results set of the common table expression +carriers in a join expression of the FROM clause of the main query of the statement WITH.

WITH +carriers AS ( 
  SELECT carrid, carrname 
         FROM scarr ) 
  SELECT FROM +carriers 
           INNER JOIN spfli 
             ON +carriers~carrid = spfli~carrid 
         FIELDS +carriers~carrname, 
                spfli~connid, 
                spfli~cityfrom, 
                spfli~cityto 
         WHERE spfli~carrid = 'LH' 
         INTO TABLE @DATA(result).

Addition

... sql_path

Effect

Specifies a path expression sql_path after a common table expression +cte that is specified as the data source of a query in a WITH statement. The common table expression must publish the first association of the path expression with the addition WITH ASSOCIATIONS. The association can be a CDS association or a CTE association.

The same rules apply as when specifying a path expression after a CDS entity cds_entity that is specified as a data source. In particular, the path expression must have an alias name defined with AS.


Example

The target data source of the path expression _scarr published by the common table expression +cte is used as the data source of the main query of a WITH statement.

WITH 
  +cte AS ( SELECT * 
                   FROM demo_cds_assoc_spfli_scarr ) 
             WITH ASSOCIATIONS ( \_scarr ) 
  SELECT carrid, carrname 
         FROM +cte\_scarr AS scarr 
         INTO TABLE @DATA(result).

Alternative 4

... hierarchy_data

Effect

Specifies the following hierarchy data:


Example

Accesses a hierarchy as a data source created by the hierarchy generator HIERARCHY.

SELECT hierarchy~*, hierarchy_level 
       FROM HIERARCHY( SOURCE demo_cds_parent_child_source 
                       CHILD TO PARENT ASSOCIATION _relat 
                       START WHERE id = 'A' 
                       MULTIPLE PARENTS ALLOWED ) AS hierarchy 
       INTO TABLE @DATA(result).

Alternative 5

... @itab

Effect

Specifies an internal table @itab as a host variable, whose name itab must be prefixed with the @ character. More information can be found under SELECT - FROM @itab.


Example

Uses SELECT to access an internal table using as an alternative to the statement READ TABLE. Unlike READ TABLE, the statement SELECT offers a (dynamic) WHERE condition and evaluates the field list for the inline declaration. The statement is executed on the AS ABAP and the data in the internal table is not transported to the database.

DATA itab TYPE TABLE OF scarr WITH EMPTY KEY. 

... 

DATA cond TYPE string. 

... 

SELECT SINGLE 
       FROM @itab AS carriers 
       FIELDS carrid, carrname 
       WHERE (cond) 
       INTO @DATA(wa).

Continue

SELECT - FROM hierarchy_data

SELECT - FROM @itab