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

[ssurampally]

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.