CDS Associations and Path Expressions – ABAP on HANA

This Article demonstrates concept of CDS associations and coding path expression in SQL statements and in CDS view. I will also explain how we can specify Inner join instead of left outer join which is default for CDS associations.

SAP wants us to use associations instead of joins because they are closer to “conceptual Thinking”. Association is basically not a join as such, it is just metadata about possible join connection. It’s Join on Demand. Actual join is created when the association is used in a path expression.

Example for simple CDS association which looks no different from left outer join:

@AbapCatalog.sqlViewName: 'ZCDS_ASSOC11'
define view zcds_assoc1 as select from scarr as sca
   association [0..1] to spfli as _spfli 
   on sca.carrid = _spfli.carrid   
   { * }

Examples for Exposed CDS associations:

@AbapCatalog.sqlViewName: 'ZCDS_ASSOC41'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view zcds_assoc4 as select from sairport as sair
    association [1..*] to spfli as _spfli on
        $projection.airportfrom = _spfli.airpfrom
    {     
       sair.id as airportfrom,
       sair.name,
       sair.time_zone,
      -- exposing association
      _spfli
    }

In the below example, you can see association with SPFLI table to SFLIGHT and SAIRPORT tables. All fields of SFLIGHT and SAIRPORT are exposed in the projection list using alias _sfli and _sair. When path expression used to invoke association, join condition will be created on spfli.carrid = sflight.carrid and spfli.connid = sflight.connid and on spfli.airport = sairport.id based on selection fields.

@AbapCatalog.sqlViewName: 'ZCDS_ASSOC21'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view zcds_assoc2 as select from spfli 
   association to sflight as _sfli on 
        spfli.carrid = _sfli.carrid and
        spfli.connid = _sfli.connid
    association [1..1] to sairport as _sair on
        $projection.airportfrom = _sair.id
    {
      spfli.carrid,
      spfli.connid,
      spfli.airpfrom as airportfrom,
      -- exposing association
      _sfli,
      _sair    
    }

In the below example, association defined using association above which is ZCDS_ASSOC2 CDS entity

@AbapCatalog.sqlViewName: 'ZCDS_ASSOC31'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view zcds_assoc3 as select from scarr
        association [1..1] to zcds_assoc2  as _cdsassoc on
         $projection.carrierid = _cdsassoc.carrid
      {
         scarr.carrid as carrierid,
     -- Exposing Association    
         _cdsassoc
      } 

Path Expression in SQL statement: To call CDS association in SQL statement we need to use path expression shown below. In the above CDS association, entire projection list of ZCDS_ASSOC2 is exposed using _cdsassoc.

DATA(w_dbfeature) = cl_abap_dbfeatures=>use_features( requested_features = VALUE #( ( cl_abap_dbfeatures=>views_with_parameters ) ) ).
IF w_dbfeature IS NOT INITIAL.
  SELECT  carrierid ,
          \_cdsassoc\_sfli-fldate AS flightdate,
          \_cdsassoc\_sair-name AS flightname
        FROM zcds_assoc3
        WHERE carrierid = @carrid
        INTO TABLE @DATA(t_data1).
ENDIF.

Path expression in CDS view:

@AbapCatalog.sqlViewName: 'ZCDS_ON_ASSOC1'
define view zcds_on_assoc with parameters airport: S_FROMAIRP  as select from zcds_assoc2 as cds2
    {
      cds2.carrid,
      cds2.connid,
      cds2.airportfrom,    
      cds2._sair.name, -- use inner join...by default association uses left outer join
      cds2._sfli.planetype
    }
 where cds2.airportfrom = :airport 

As I have mentioned, CDS associations creates left outer join when invoked using path expression.

In SPFLI table data, there is no flights from airport RTM

Result of below CDS view will be an entry for RTM airport without carrid when we enter parameter value for airport = RTM.

@AbapCatalog.sqlViewName: 'ZCDS_ON_ASSOC41'
define view zcds_on_assoc4 with parameters airport: S_FROMAIRP as select from zcds_assoc4 as cds_assoc
    {
      cds_assoc.airportfrom,
      cds_assoc.name,
      cds_assoc.time_zone,    
      cds_assoc._spfli.carrid -- use inner join...by default association uses left outer join
    }
 where cds_assoc.airportfrom = :airport 

Executed above CDS view:

Resulted in data preview:

To change left outer join which is default to inner join in CDS view calling CDS association zcds_assoc4 using path expression, we need to use [inner] as shown below:

@AbapCatalog.sqlViewName: 'ZCDS_ON_ASSOC41'
define view zcds_on_assoc4 with parameters airport: S_FROMAIRP as select from zcds_assoc4 as cds_assoc
    {
      cds_assoc.airportfrom,
      cds_assoc.name,
      cds_assoc.time_zone,    
      cds_assoc._spfli[inner].carrid -- use inner join...by default association uses left outer join
    }
 where cds_assoc.airportfrom = :airport 

If we executed this, will result in empty data for parameter value: RTM

Right outer join is not available for CDS association yet.

Filters on CDS associations shown in below example:

@AbapCatalog.sqlViewName: 'ZCDS_ASSOC_FILT'
define view ZCDS_ASSOC_FILTER  as select from zcds_assoc2 as cds2
    {
      cds2._sair[ id = 'TYO' ].name,
      cds2._sfli.planetype
    }

Output for above CDS view:

Hope this article helpful.