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'
{
  acdoca.rbukrs,
  acdoca.rclnt,
  _Compcode.land1 as land1,
  _Compcode.mandt as mandt,
  _Compcode,
  _Country.landx,  -- Country Description
  _Country
}                                        

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'
{
  a.rbukrs,
  a.rclnt,
  T.land1,
  C.landx  -- Country Description
 }  

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