ABAP Keyword Documentation → ABAP − Reference → Processing External Data → ABAP Database Access → ABAP SQL → ABAP SQL - Operands and Expressions → ABAP SQL - SQL path expressions sql_path
Path Expressions, Use in the FROM Clause
This example demonstrates path expressions in the FROM clause in ABAP SQL.
Other versions:
7.31 | 7.40 | 7.54
Source Code
DATA:
tz TYPE s_tzone VALUE 'UTC+1',
currc TYPE s_currcode VALUE 'EUR',
fltime TYPE s_fltime VALUE 0.
cl_demo_input=>new(
)->add_field( CHANGING field = tz
)->add_field( CHANGING field = currc
)->add_field( CHANGING field = fltime )->request( ).
"Path expression in Open SQL
SELECT DISTINCT carrname
FROM demo_cds_assoc_sairport_tz( tz = @( to_upper( tz ) ) )
\_spfli[ fltime > @fltime ]
\_scarr[ currcode = @( CONV s_currcode(
to_upper( currc ) ) ) ]
AS scarr
ORDER BY carrname
INTO TABLE @DATA(result_path).
"Joins in Open SQL
SELECT DISTINCT scarr~carrname
FROM demo_cds_assoc_sairport_tz( tz = @( to_upper( tz ) ) )
AS airports
INNER JOIN demo_cds_assoc_spfli_scarr AS flights
ON flights~airpfrom = airports~id AND
flights~fltime > @fltime
INNER JOIN scarr
ON scarr~carrid = flights~carrid AND
scarr~currcode = @( to_upper( currc ) )
ORDER BY scarr~carrname
INTO TABLE @DATA(result_join).
ASSERT result_path = result_join.
cl_demo_output=>display( result_path ).
Description
The first SELECT statement accesses the CDS view demo_cds_assoc_sairport_tz:
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_assoc_sairport_tz
with parameters
tz : s_tzone
as select from sairport
association to demo_cds_assoc_spfli_scarr as _spfli on
sairport.id = _spfli.airpfrom
{
_spfli,
id
}
where
time_zone = :tz
This view publishes its CDS association _spfli in its SELECT list. The CDS association _spfli uses the view demo_cds_assoc_spfli_scarr as a target data source:
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_assoc_spfli_scarr
as select from
spfli
association to scarr as _scarr on
spfli.carrid = _scarr.carrid
{
_scarr,
carrid,
airpfrom,
fltime
}
This view publishes its CDS association _scarr in its SELECT list, making it possible to specify it in path expressions after _spfli. This CDS association uses a database table as a data source and always closes a path expression.
In the FROM clause, the first
SELECT statement uses a path expression with the CDS associations _spfli
and _scarr after the name of the CDS view. The names of all carriers are read that depart from airports in a specific time zone. The time zone is a
parameter of CDS view demo_cds_assoc_sairport_tz,
and a value is passed to it. Further restrictions are made on the local currency of the airline in a
filter condition for the CDS association _scarr and on the flight time in a filter condition for the CDS association _spfli
The second SELECT statement demonstrates which joins and conditions need to be created in ABAP SQL to achieve the same result. This is guaranteed by an assertion.