Skip to Content

From Open SQL Joins to CDS Associations

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):

               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:



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):

  "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.


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).

You must be Logged on to comment or reply to a post.
    • 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.

      • 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)

        • 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.

  • 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.

  • 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? 

    • 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

  • 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,

    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,



      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
          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,


      • 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,


      • 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 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,




  • Hello,

    I'm desperately searching for a possibility, to build and fill a table mesh straight away from my CDS view incorporating the associations.

    In the the whole Structure looks like:

    -[Tasks Lists of Workcenters]
    --[Materials of Task Lists]
    ---[Consumption of Materials] (Transposed from columns to rows)
    ----[Workdays per Month] (Transposed form columns to rows)

    With of course some additional calculation in between

    As now based on this path structure, I'd like to create an ABAP report

    Unfortunately (as of now) I can't find anything?

    Any help would be appreciated

    • "I’m desperately searching for a possibility, to build and fill a table mesh straight away from my CDS view incorporating the associations."

      Me too ...

  • Then let's hope and pray 😉

    But now I have another issue with Associations compared to Joins...

    I have a view (C_MAT_CONS) an association (on WERKS and MATNR) from material (B_MAT) to material consumption (B_CONS)...
    In this view I have a SUM on the consumption grouped by WERKS and MATNR.

    Sure when going to the preview, it calculates based on the WHOLE consumption...

    Now when I select the data via ABAP and add a where condition which "filters" the consumption data, this restriction is not considered by the SUM function on the view!

    But when I write a simple JOIN between B_MAT and B_CONS again only joined via WERKS and MATNR grouped and summarized, the SUM is based on my where condition on B_CONS

    Same if i select from the VIEW and calculate the SUM within my select query, again it calculates properly!

    So only the VIEW itself is not considering my where restriction to the association!?

  • Hi Horst Keller,

    I created a association view like below.

    @AbapCatalog.sqlViewName: 'ZS4HCDS_ASS04'
    @AbapCatalog.compiler.compareFilter: true
    @AccessControl.authorizationCheck: #CHECK
    @EndUserText.label: 'Filtered Associations: cardinality “to-N”'

    define view ZS4HCDS_ASS_04
    as select from scustom as customer
    association [0..*] to sbook as _bookings on = _bookings.customid

    //Exposed Association with filter
    _bookings[class='C'] as _business_flights,

    //Exposed Association without filter


    and i used this association view in my program like below.

    SPAN {
    font-family: "Consolas";
    font-size: 10pt;
    color: #000000;
    background: #FFFFFF;
    .L0S31 {
    font-style: italic;
    color: #808080;
    .L0S52 {
    color: #0000FF;
    .L0S55 {
    color: #800080;
    .L0S70 {
    color: #808080;
    *& Report ZS4HCDS_ASS04_CONSUME
    REPORT zs4hcds_ass04_consume.

    **-- Association without Filter.
    SELECT  FROM zs4hcds_ass04
    FIELDS  \_bookings-mandt AS mandt \_bookings-carrid AS carrid \_bookings-connid AS connid
    INTO TABLE @DATA(lt_2).

    but i'm getting the sytax error like  "The association "_BOOKINGS" could not be resolved."




    • I experienced the same error message when I try to use new Open SQL syntax for fields in SELECT list from associations

      Horst Keller, is it possible for you to check where we are making a mistake


      For example my test view as follows

      @AbapCatalog.sqlViewName: 'ZEY_ORDER_CDS'
      @AbapCatalog.compiler.compareFilter: true
      @AccessControl.authorizationCheck: #CHECK
      @EndUserText.label: 'Test CDS View'
      define view ZEY_Order
      select from vbap as I
      association [0..1] to SEPM_I_Currency as _Currency
          on $projection.waerk = _Currency.Currency


      And the ABAP code to consume it as follows


      FROM zey_order_cds
      INTO TABLE @DATA(lt_vbap)
        UP TO 10 ROWS.

      And the error message I got is

      The association "_CURRENCY" could not be resolved.

  • Hello Horst Keller,,

    How does this fit into the VDM? The resulting CDS view does not really describe a single entity, so it is not a basic interface view. However, composite interface views shouldn't access database tables directly.

    My current understanding of CDS is that every CDS view should fit into the VDM. What does this mean for your examples? Is this a CDS outside of the VDM that's just used for code push-down? Would that be good practice or bad practice?

    I'd appreciate any help, thanks.

  • /