Skip to Content

In this short blog I will use the most primitive example to show you the way from joins in ABAP Open SQL to associations in ABAP CDS.

The aim of the blog is not to show you something you should do but to gain a basic understanding of associations in CDS views.

 

Step 1, Join in Open SQL

I will start with the following very simple INNER JOIN between database tables SPFLI and SCARR from the good ol’ flight model in Open SQL in the ABAP Editor (either WB or ADT in Eclipse):

SELECT FROM spfli
               INNER JOIN scarr ON
                  spfli~carrid = scarr~carrid
       FIELDS scarr~carrname  AS carrier,
              spfli~connid    AS flight,
              spfli~cityfrom  AS departure,
              spfli~cityto    AS arrival
       ORDER BY carrier, flight
       INTO TABLE @DATA(result_open_sql_join).

Nothing special about that and the result shown with CL_DEMO_OUTPUT looks as follows:

/wp-content/uploads/2016/08/inner_join_1014535.jpg

 

Step 2, Join in ABAP CDS

Now let’s transform the above ABAP code into the DDL of an ABAP CDS view in the DDL source code editor of ADT in Eclipse:

@AbapCatalog.sqlViewName: 'DEMO_CDS_JN1'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_join1
  as select from spfli
    inner join   scarr on
      spfli.carrid = scarr.carrid
  {
    scarr.carrname  as carrier,
    spfli.connid    as flight,
    spfli.cityfrom  as departure,
    spfli.cityto    as arrival
  }

 

This can almost be done by copy and paste. Hey, it’s (almost standard) SQL for both.

After activating this view you can use the data preview of ADT (F8) or acces it with Open SQL:

SELECT FROM demo_cds_join1
       FIELDS *
       ORDER BY carrier, flight
       INTO TABLE @DATA(result_cds_join).

It is not too surprising that result_cds_join and result_open_sql_join contain exactly the same data.

 

Step 3, Association in ABAP CDS

Finally, I will use the advanced modelling capabilty of ABAP CDS and transform the explicit join into an association of another view:

@AbapCatalog.sqlViewName: 'DEMO_CDS_JN2'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_join2
  as select from spfli
  association to scarr as _scarr on
    spfli.carrid = _scarr.carrid
  {
    _scarr[inner].carrname as carrier,
    spfli.connid           as flight,
    spfli.cityfrom         as departure,
    spfli.cityto           as arrival
  }

 

The association _scarr is declared once behind the keyword association and can be used at several places inside the view in path expressions. You can also publish it for usage in other views or in Open SQL, but I have not done that here.

For our simple example, I use the path expression _scarr[inner].carrname as the first element of the select list. When using a path expression, the associations listed there are internally transformed to joins. In the select list those joins are left outer joins by default. Therefore, I override the default with [inner] in order to enforce an inner join. You can check the result by displaying the SQL DDL (shown for HANA here) that is generated from the ABAP CDS DDL in ADT (Context menu Show SQL CREATE statement):

CREATE VIEW "DEMO_CDS_JN2" AS SELECT
  "SPFLI"."MANDT" AS "MANDT",
  "=A0"."CARRNAME" AS "CARRIER",
  "SPFLI"."CONNID" AS "FLIGHT",
  "SPFLI"."CITYFROM" AS "DEPARTURE",
  "SPFLI"."CITYTO" AS "ARRIVAL"
FROM "SPFLI" "SPFLI" INNER JOIN "SCARR" "=A0" ON (
  "SPFLI"."MANDT" = "=A0"."MANDT" AND
  "SPFLI"."CARRID" = "=A0"."CARRID" )

You see, we end up with something well known.

And of course, the data preview of ADT (F8) or the following Open SQL retrieve again the same data as before:

SELECT FROM demo_cds_join2
       FIELDS *
       ORDER BY carrier, flight
       INTO TABLE @DATA(result_cds_assoc).

With other words, no exceptions from

ASSERT result_cds_join  = result_open_sql_join.
ASSERT result_cds_assoc = result_cds_join.

 Conclusion

The aim of this simple example is to show you that CDS associations are nothing but specifications of joins in a central position. These joins are instantiated in native SQL when using associations in path expressions.

The benefits of using associations are not shown in the simple example here. The advanced modelling capabilities stem from the reuse of the associations (meaning their joins) in different positions. Of course,  path expressions can contain more than one association, relieving you from the task of coding complex join expressions.  Such path expressions can be used in the same or other CDS views and even in Open SQL (if published by the defining view).

To report this post you need to login first.

16 Comments

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

    1. Horst Keller Post author

      like why would anyone want to do this

      SAP’s next generation ABAP applications as S/4 HANA will be built on 10000s of CDS views interconnected by associations. Sometime in the future you will have to deal with those in custom code and then it might be good to know that it is all about joins.

      (0) 
      1. Sandra Rossi

        My dream: that would be so good if CDS views with associations could be provided by SAP in the classic non-S/4 HANA systems! (they could be used by customers as a technical data model, it would be much easier to find the relationships between tables, much better than the old foreign keys and EDM)

        (0) 
        1. Horst Keller Post author

          Yep, that’s the idea. While semantical properties of database tables as foreign key relationships  never had any technical meaning in ABAP (only in dynpros), the modelling capabilities of CDS do have real effects.

          (1) 
  1. Amisha parikh

    I have question.  I created one view and included for e.g.  10 fields in projection list (ZCDS1).  Now I want to create other cds view(ZCDS2)   using  ZCDS1 and sap table1 using join or association  .   I want to use all the fields of ZCDS1 + new table two fields.

    Every time I have to add all 10 fields in projection list  from zcds1 manually .   Is there any way to select all the fields without typing all the fields of ZCDS1  field list ?

    Because if I add new filed in ZCDS1  I have to again change in ZCDs2.

    (0) 
  2. Alexander K

    Hello, Horst.

    Can you give an example, in which one association relieving you from the task of coding complex join expressions.

    Why association  is more convenient then join? 

    (0) 
    1. Horst Keller Post author

      Associations model relations between entities. Once modelled, the user can use path expression to naturally follow such relations, e.g. Open SQL:

          SELECT DISTINCT carrname
                 FROM demo_cds_assoc_sairport_utc1\_spfli\_scarr AS scarr
                 ORDER BY carrname
                 INTO TABLE @DATA(result).

      Here, two associations of two views are used in one path expression.

      Example taken from docu

      (1) 
  3. Ceyhun Alp

    Hello Horst and everyone,

    Thank you for this great blog, at first. Very useful and helpful .

    I got stuck somewhere and trying to find the answer of the questions that makes my mind busy for a while. I guess, association doesn’t have all the features join have or it has some other use cases than complex joins.

    When I try to make 2 or more associations and use the fields in the first associated view to join the new associations, things start to get mixed. Better to give an example;

    @AbapCatalog.sqlViewName: 'ZdemoCdsJn2 '
    @EndUserText.label: 'CDS Demo Join'
    
    define view Zdemo_Cds_Jn2 
      as select from spfli
      association to scarr as _scarr on
        spfli.carrid = _scarr.carrid
      association to scarplan as _scplan on
        $projection.carrid= _scplan.carrid
        
      {
        key spfli.carrid           as id,
        key spfli.connid           as flight,
            spfli.cityfrom         as departure,
            spfli.cityto           as arrival,
            _scarr.carrid,
            _scplan.planetype 
      } 

    The complier gives the following error
    “Association _scplan cannot be used locally within the view”

    Actually, my goal is also associating SPLANE table on the field _scplan-planetype. But it doesn’t let to use this field.

    What does the “Association _scplan cannot be used locally within the view” error mean? So, that means we can use association only for spesific scenarios? For navigating with Odata etc? But for complex scenarios, it looks like join has more features.

    Thank you very much for all your cooperation,

    Ceyhun

    (0) 
    1. Horst Keller Post author

       

      You have a kind of recursion. You use $projection that refers to the SELECT list and use the association in the SELECT list itself. The following is error free:

      define view ...
        as select from
          spfli
          association to scarr    as _scarr  on
            spfli.carrid = _scarr.carrid
          association to scarplan as _scplan on
            spfli.carrid = _scplan.carrid
          {
            key spfli.carrid           as id,
            key spfli.connid           as flight,
                spfli.cityfrom         as departure,
                spfli.cityto           as arrival,
                _scarr.carrid,
                _scplan.planetype
          }

       

      (1) 
      1. Ceyhun Alp

        Thank you.

        As I see when we use the base datasource table (here spfli) it works ok. Actually what i need is to use an associated view (let’s say _scarr) in another association and use the fields of the final associated table in my cds view.

        When i use traditional join, it looks ok. But my main goal is to create an analytical model with associations. To do this, i guess i need to understand the association concept first.

        Thank you,

        Ceyhun

        (0) 
      2. Raveesh Saurabh

        Hi Horst,

        I was facing the same issue, but field required for the on condition(BSEG-LIFNR) for the second association is not available in the initial data source BKPF for me.

        I understand from your reply is that we can not use fields from the selection list in the ON condition which themselves point to another association if we want the fields from the association to be included in the selection list for the current view.

        But I have no other option here as LIFNR is not available here in BKPF, else I would have used it from there.

        I was able to resolve this issue by changing the tables, and making BSEG as my first table.Wanted to check if we can activate the CDS view without changing the order of the tables?

        If I remove the path expression _lfa1.name and replace it with _lfa1 so that it is exposed to consumption by other views, then the error goes but we get two warnings.

        Could you please comment on the warnings as well. Thanks for your help!

        Warm Regards,

        Raveesh

         

         

        (0) 

Leave a Reply