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 SELECT List
This example demonstrates path expressions in the SELECT list in ABAP SQL.
Other versions:
7.31 | 7.40 | 7.54
Source Code
DATA carrid TYPE scarr-carrid VALUE 'AA'.
cl_demo_input=>request( CHANGING field = carrid ).
"Path expressions in Open SQL
SELECT scarr~carrname,
\_spfli-connid AS connid,
\_spfli\_sflight-fldate AS fldate,
\_spfli\_sairport-name AS name
FROM demo_cds_assoc_scarr AS scarr
WHERE scarr~carrid = @carrid
ORDER BY carrname, connid, fldate
INTO TABLE @DATA(result1).
"Joins in Open SQL
SELECT demo_cds_assoc_scarr~carrname,
demo_cds_assoc_spfli~connid,
sflight~fldate AS fldate,
sairport~name AS name
FROM demo_cds_assoc_scarr
LEFT OUTER JOIN demo_cds_assoc_spfli
ON demo_cds_assoc_spfli~carrid =
demo_cds_assoc_scarr~carrid
LEFT OUTER JOIN sflight
ON sflight~carrid = demo_cds_assoc_spfli~carrid AND
sflight~connid = demo_cds_assoc_spfli~connid
LEFT OUTER JOIN sairport
ON sairport~id = demo_cds_assoc_spfli~airpfrom
WHERE demo_cds_assoc_scarr~carrid = @carrid
ORDER BY demo_cds_assoc_scarr~carrname,
demo_cds_assoc_spfli~connid,
fldate
INTO TABLE @DATA(result2).
ASSERT result1 = result2.
"Path expressions in CDS
SELECT *
FROM demo_cds_use_assocs( p_carrid = @carrid )
ORDER BY carrname, connid, fldate
INTO TABLE @DATA(result3).
ASSERT result1 = result3.
"Joins in CDS
SELECT *
FROM demo_cds_outer_joins( p_carrid = @carrid )
ORDER BY carrname, connid, fldate
INTO TABLE @DATA(result4).
ASSERT result1 = result4.
cl_demo_output=>display( result1 ).
Description
The first SELECT statement accesses the CDS view demo_cds_assoc_scarr:
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_assoc_scarr
as select from scarr
association to demo_cds_assoc_spfli as _spfli on
scarr.carrid = _spfli.carrid
{
_spfli,
carrid,
carrname
}
This view publishes its CDS association _spfli in its SELECT list. The CDS association _spfli uses the view demo_cds_assoc_spfli as a target data source:
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_assoc_spfli
as select from
spfli
association to sflight as _sflight on
spfli.carrid = _sflight.carrid
and spfli.connid = _sflight.connid
association [1..1] to sairport as _sairport on
spfli.airpfrom = _sairport.id
{
_sflight,
_sairport,
carrid,
connid,
airpfrom
}
This view publishes its CDS associations _sflight and _sairport in its SELECT list, making it possible to specify them in path expressions after _spfli. These CDS associations use database tables as data sources and always close a path expression.
The SELECT statement contains three path expressions in its SELECT
list alongside a column specified as an elementary column. The first association contains only the CDS
association _spfli of the CDS view demo_cds_assoc_scarr
specified after FROM. The CDS associations of the data source of the root element are added to the other two path expressions.
The second SELECT statement demonstrates which joins need to be created in ABAP SQL to achieve the same result. This is guaranteed by an assertion.
The third SELECT statement accesses the CDS view demo_cds_use_assocs:
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_use_assocs
with parameters
p_carrid :s_carrid
as select from
demo_cds_assoc_scarr as scarr
{
scarr.carrname,
scarr._spfli.connid,
scarr._spfli._sflight.fldate,
scarr._spfli._sairport.name
}
where
scarr.carrid = :p_carrid
This view shows how the same paths specified in the SELECT statement of the view in CDS DDL syntax and also achieves the same result.
Finally, a fourth SELECT statement accesses the CDS view demo_cds_outer_joins:
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_outer_joins
with parameters
p_carrid :s_carrid
as select from
demo_cds_assoc_scarr
left outer join demo_cds_assoc_spfli on
demo_cds_assoc_spfli.carrid = demo_cds_assoc_scarr.carrid
left outer join sflight on
sflight.carrid = demo_cds_assoc_spfli.carrid
and sflight.connid = demo_cds_assoc_spfli.connid
left outer join sairport on
sairport.id = demo_cds_assoc_spfli.airpfrom
{
demo_cds_assoc_scarr.carrname as carrname,
demo_cds_assoc_spfli.connid as connid,
sflight.fldate as fldate,
sairport.name as name
}
where
demo_cds_assoc_scarr.carrid = :p_carrid
In this view, the same result is again achieved using joins.