Skip to Content
Technical Articles
Author's profile photo Sreekanth Surampally

Transitive Join Limitation in ABAP CDS View associations – S/4 HANA

When I tried to create a CDS view on 3 tables, I couldn’t establish the join relationship based on association target 1 and association target 2 fields, and I get the below error message when association target 2 column is used as path expression in the same CDS view.

In order to fix this error, I had got 2 options.  1.using open SQL joins instead of associations. Or  2. Create another CDS view on top of it, to use the field in the select query.In this technical blog, I will explain the scenario briefly

What is a transitive join, if rows of table A are joined to rows of table B, and those rows of table B are joined to rows of table C, then the rows of A could also be joined to rows of C and it can be shown in below picture.

I created a cds view with association to establish the relationship ACDOCA — > T001 — > T005T as shown in above example to get the Country description(T005T-landx) for all rows in ADOCA table data. Below is the code,

@AbapCatalog.sqlViewName: 'ZCDSLIMIT1'
@EndUserText.label: 'CDS Limitations 1'

define view zcds_assoc_limitation1
 as select from acdoca
 association[0..1] to t001 as _Compcode on acdoca.rbukrs = _Compcode.bukrs
                                        and acdoca.rclnt = _Compcode.mandt
 association[0..1] to t005T as _Country on $projection.land1 = _Country.land1
                                      and $projection.mandt = _Country.mandt
                                      and _Country.spras = 'E'
  _Compcode.land1 as land1,
  _Compcode.mandt as mandt,
  _Country.landx,  -- Country Description

I get the above error on using _Country.landx column as path expression in this CDS view, because using association target on join condition with a column in $projection is going to result in inconsistent join behavior.

Then I switched the logic to use joins instead of association and able to get the Country name column with in this CDS view.

@AbapCatalog.sqlViewName: 'ZCDSLIMIT2'
@EndUserText.label: 'CDS Limitations 1'

define view zcds_assoc_limit_joins
 as select from acdoca a
 left outer join t001 as T on a.rbukrs = T.bukrs
                          and a.rclnt = T.mandt
 left outer join  t005t as C on T.land1 = C.land1
                           and T.mandt = C.mandt
                           and C.spras = 'E'
  C.landx  -- Country Description

So I believe in this transitive join scenario use case, using Joins instead of associations will get the required result.

Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Pavan Chand Bhamidipati
      Pavan Chand Bhamidipati



      Thanks for explaining the error and resolution.However,I tried to execute your first CDS definition and could not understand the result. When I follow Association of company I get to see multiple entries. off-course I deleted the landx field with in the definition. I was assuming it should display only one record from t001 table


      After including the entire key I was able to see the way I was expecting.

      @AbapCatalog.sqlViewName: 'ZXXX_BPADR'
      @AbapCatalog.compiler.compareFilter: true
      @AbapCatalog.preserveKey: true
      @AccessControl.authorizationCheck: #CHECK
      @EndUserText.label: 'Business Partner Address'
      @VDM.viewType:  #BASIC
      define view ZXXX_BP_ADR
       as select from acdoca 
       association[1] to t001 as _Compcode on acdoca.rbukrs = _Compcode.bukrs
       association[1] to t005t as _Country on _Compcode.land1 = _Country.land1
                                             and _Country.spras = 'E'
        key rldnr,
        key rbukrs,
        key gjahr,
        key belnr,
        key docln,
        _Compcode.land1 as land1,




      Pavan Bhamidipati