Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
ssurampally
Active Contributor
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.
1 Comment
Labels in this area