Skip to Content
Author's profile photo Former Member

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.

Assigned Tags

      13 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi Santhosh,

      Thank you very much for the explanation!!!

       

      Can you please explain association [1..1] briefly?

       

      Author's profile photo Wesley Yin
      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

      Author's profile photo Rohit Gupta
      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!!

      Author's profile photo Former Member
      Former Member

      Hi Rohit,

      Let's assume the material number is not a key field so there may be “1” or “o” or “n” entries in the item table. In that case, which cardinality would be used?

       

      Thanks,
      Thulasi

      Author's profile photo Isaac Samuel Arancon
      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

      Author's profile photo sudarshan raju
      sudarshan raju

      Hi Isaac,

      I am actually starting the standard cds view extension. when i am adding fields to it using a database table in fact any table, it is giving the below error

      Thanks,

      Sudarshan

      Author's profile photo Pavel Astashonok
      Pavel Astashonok

      C_OUTGOINGCHECK doesn't look like CDS view

      Author's profile photo Venkatesh Veera
      Venkatesh Veera

      don’t use qmel.erdat instead use erdat  try to see if it works.

      Thank you.

      Author's profile photo sudarshan raju
      sudarshan raju

      Hi Santhosh,

      I was actually searching for a similar information. thanks for the blog.

      I am starting on extending the standard cds view and actually facing issues. It will be really helpful if you give any info .

       

      Thanks

      Sudarshan

      Author's profile photo Giri Babu Darapaneni
      Giri Babu Darapaneni

      I am trying to extend the MSEG table with one custom field via append structure YYNAME1 and for the same need to extend the proxy object of NSDM_E_MSEG getting below error. inside { yy_name }

      Author's profile photo Pavel Astashonok
      Pavel Astashonok

      NSDM_E_MSEG needs to be CDS view, not proxy object

      Author's profile photo Venkatesh Veera
      Venkatesh Veera

      don't use mseg.YY_NAME instead use YY_NAME  try to see if it works.

      Thank you.

       

      V

      Author's profile photo kyo choi
      kyo choi

      Nice tutorial.  One comment, the ABAP CDS views works on other DB as well and it’s not just for HDB.  Currently I am using Netweaver 7.51 SP2 on SYBASE.