AS ABAP Release 753, ©Copyright 2019 SAP AG. All rights reserved.
ABAP Keyword Documentation → ABAP − Reference → Processing External Data → ABAP Database Access → ABAP SQL → ABAP SQL - Reads → SELECT clauses → SELECT - FROM →
SELECT - FROM data_source
Syntax
... dbtab |view [WITH PRIVILEGED ACCESS]
| cds_entity[
parameters][
path_expr]|[ WITH PRIVILEGED ACCESS]
| +cte[path_expr]
| table_function
| @itab
Alternatives:
1. ... dbtab |view [WITH PRIVILEGED ACCESS]
2. ... cds_entity[parameters][path_expr]|[ WITH PRIVILEGED ACCESS]
3. ... +cte[path_expr]
4. ... table_function
5. ... @itab
Effect
Specifies an individual database table dbtab,
classic view view, non-abstract
CDS entity cds_entity, a common
table expression +cte, a
table function table_function, 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.
... dbtab |view [WITH PRIVILEGED ACCESS]
Addition:
Effect
Specifies a database table database table dbtab of a classic view or of an external view view. Classic views on which reads can be performed using SELECT are database views and projection views.
Notes
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).
... WITH PRIVILEGED ACCESS
Effect
The addition WITH PRIVILEGED ACCESS is currently ignored by database tables and classic views .
Notes
... cds_entity[parameters][path_expr]|[ WITH PRIVILEGED ACCESS]
Extras:
1. ... path_expr
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 or DEFINE TABLE FUNCTION.
CDS table functions are database extensions that are not supported by all database systems. The ABAP CDS CDS DDL, 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:
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).
... path_expr
Effect
Specifies a path expression path_expr after a CDS entity. In this case, the entity must be a CDS view that publishes the first association of the path expression in its SELECT list. The CDS view must be specified using its name cds_entity defined after DEFINE VIEW. All associations of the path expression must be published in the SELECT lists of the CDS views in question for use from outside.
The target data source of the last 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 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 alternative table name must be defined and used here using AS. A path expression in the data source of the FROM clause must have an alternative table name defined with AS. This is checked in strict mode as of release 7.52.
Notes
Example
Accesses the CDS view DEMO_CDS_ASSOC_SAIRPORT_TZ with parameter passing and a path expression with the 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
... 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 path_expr. It is applied only to that CDS entity for which it is specified. It is not applied to the CDS entities published using associations of the specified CDS entity.
Notes
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:
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 ).
... +cte[path_expr]
Addition:
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).
... path_expr
Effect
Specifies a path expression path_expr 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 same rules apply as when specifying a path expression after a CDS view cds_entity that is specified as a data source. In particular, the path expression must have an alternative table 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).
... table_function
Effect
Specifies a table function table_function. On the database, a table function is transformed to an access that creates a tabular return value, which is itself accessed in the query.
For documentation about the potential functions, see SELECT - FROM table_function.
Example
Accesses the hierarchy function HIERARCHY.
SELECT hier~*, hierarchy_level
FROM HIERARCHY( SOURCE demo_cds_parent_child_source
CHILD TO PARENT ASSOCIATION _relat
START WHERE id = 'A'
MULTIPLE PARENTS ALLOWED ) AS hier
INTO TABLE @DATA(result).
... @itab
Effect
Specifies an internal table @itab 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).