Skip to content

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:

@AbapCatalog.sqlViewName: 'DEMO_CDS_ASCSRTZ'
@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:

@AbapCatalog.sqlViewName: 'DEMO_CDS_ASCSPSC'
@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.