Skip to Content

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.

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Wesley Yin

    Hi Santhosh,

    Thank you very much for knowledge sharing.

    I am very fresh about association.

    Could you kindly explain more about the difference between association[0..*], association[0..1], association[1.1] and association[1..*]?

    how to determine to use which of these associations?

    Wesley

    (0) 
    1. Rohit Gupta

      It defines the cardinality of the target source, [ min..max ] with possible values 0,1 and * . 

      For an example, let’s say we have sales order and sales order item table,  for 1 sales order there must be 1 sales order item and and maximum could be any number, in such case our cardinality will be [ 1..* ] . It says that Sales order should have minimum 1 sales order item and maximum could be any number..

      Let’s extend this example further, let’s say 1 sales item can not have multiple material, so the cardinality of association in between sales order item and item material would be [ 1..1 ] .

      Hope I made it clear!!

      (0) 
  2. Isaac Samuel Arancon

    Hi Santosh,

     

    I am trying to extend CPURORDITEMENH. I created a new view Z*DISC to get the item discount. When i applied it in the extension, i encountered the error CPURORDITEMENH is inconsistent with the active version.

    I attached the image for the associations.

    Do you have any ideas on how to fix the inconsistency error?

     

    Thanks,

    Sam

    (0) 

Leave a Reply