Skip to Content
Technical Articles
Author's profile photo Pruthvi Renukarya

Filtering data in association with [n..*] cardinality

Introduction

One of the limitation with association in ABAP CDS view is we cannot use association field in where condition if the maximum cardinality of the association is n ( association[n..*] ).

 

Scenario

In the below example, a basic view on TFK003 (Document types) master data table is being created with association to its text table TFK003T.  As the cardinality of the association is defined as [1..*], use of where condition on language field spras to restrict to session language results in below error

“To many” associations (cardinality [n..*]) are not allowed here

Alternative way is creating a new basic view for text table TFK003T with filter on language and using that new basic view for association which leads to extra effort.

To overcome this, we can use path expression on association name to filter out data as shown below:

Output:

 

Conclusion

The above approach is straight forward and will cut down the effort.

Note:

Similar approach can be used with any pair of tables with 1:n relationship to filter out data but care should be taken as the association with filter path expression can modify the cardinality of the results set.

 

Assigned tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Misaq T
      Misaq T

      Thanks Pruthvi. Helpful piece of information.

      Author's profile photo Luiz Martin
      Luiz Martin

      Hi, Pruthvi! Nice way for filtering, but why not filter directly on the declaration of the association?

      @AbapCatalog.sqlViewName: 'TEST_FK'
      @AbapCatalog.compiler.compareFilter: true
      @AbapCatalog.preserveKey: true
      @AccessControl.authorizationCheck: #CHECK
      @EndUserText.label: 'test'
      define view test_tfk003 as select from tfk003 A
      
      association[1..*] to tfk003t as _Text on $projection.blart = _Text.blart
          and _Text.spras = $session.system_language
      {
          key A.applk,
          key A.blart,
          _Text.ltext as text
      }
      

      Would you be able to tell if there's any advantage/disadvantage?

      Thanks!

      Author's profile photo Pruthvi Renukarya
      Pruthvi Renukarya
      Blog Post Author

      Hello Luiz,

      That's a good point. we can use directly with association as well.

      But the question would be similar to using filter on Join vs filter with Where clause in SQL.

       

      Filtering with ON clause for may result in unexpected result while using LEFT, RIGHT or FULL OUTER JOIN. below link explains with example under heading Filtering data

      https://dataschool.com/how-to-teach-people-sql/difference-between-where-and-on-in-sql/

       

      We can use any approach as long as we vet the output to be correct.