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

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:

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