Skip to Content
Technical Articles

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.

1 Comment
You must be Logged on to comment or reply to a post.
  • Hi,

     

    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

    <<<UPDATE>>>>>

    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'
    {
      //acdoca
      key rldnr,
      key rbukrs,
      key gjahr,
      key belnr,
      key docln,
      _Compcode.land1 as land1,
      _Compcode,
      _Country
    }  

     

     

    Thanks

    Pavan Bhamidipati