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