2

I have a CDS view ZCDS_XXX :

define view ZCDS_XXX
  as select distinct from XXX 
association [1..*] to ZCDS_YYY as _YYY ...
{
  key a,
  _YYY
} ...

I have a DCL ZDCL_XXX :

define role ZDCL_XXX{
  grant
    select
      on
        ZCDS_XXX
          where
            ( _YYY.ProfitCenter ) = aspect pfcg_auth( K_PCAR_REP, PRCTR );
}

The goal is to return the entries of the CDS ZCDS_XXX
for which the user has at least one authorization from a list of associated profit centers .

Okay everything is working fine ...

BUT, when the DCL is active, The CDS view ZCDS_XXX view returns me duplicate keys
despite the DISTINCT (removes duplicates from the results list).
As if the DCL does the processing after the selection and changed the cardinality of my result >(

I would like to know if there is a solution directly in the DCL or the CDS view to avoid having duplicates and avoid doing a SELECT DISTINCT in oSQL or delete duplicates for example if I use the CDS view in a search help.

And also if you have a link that describes its behavior or the SQL syntax in the DCL, I can't find much in the docs...

Thanks you a lot for your help !

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
depth1
  • 135
  • 10
  • So putting a DCL which checks fields from the association results in the association always getting expanded even if none of its fields are requested? That's an interesting gotcha. – Philipp Nov 14 '22 at 15:26
  • Mhm it's weird actually it seems to be more complicated. I redid the example with a simple association and the association with an area to be controlled by authorization that includes different values ​​and the association is not consumed by the DCL and everything is fine. So it may come from my association which has several joints etc. I'll try to find the moment when everything goes crazy – depth1 Nov 14 '22 at 16:45
  • 2
    IIRC this case can only occur when the cardinality in the CDS entity missmatches the data, i.e. when the association states [1..1] but there are actually multiple entries – Jonas Wilms Nov 15 '22 at 23:26
  • 1
    The DCL gets mixed into the SQL query sent to the database, thus when the DCL makes incorrect assumptions about the underlying datamodel, so does the SQL query, producing potentially unexpected results – Jonas Wilms Nov 15 '22 at 23:30
  • 1
    Documentation can be found in https://help.sap.com/doc/abapdocu_cp_index_htm/CLOUD/en-US/index.htm?file=abencds_f1_cond_pfcg_mapping.htm – Jonas Wilms Nov 16 '22 at 00:02
  • what is the primary key of `ZCDS_YYY`? does it match with the key of `ZCDS_XXX`? you don't give the full view definition so we don't know on which field you associate them, and by which field you make a conclusion of *key duplication* – Suncatcher Nov 25 '22 at 01:48
  • also Jonas assumption `CDS entity missmatches the data` makes sense, as we don't see dataset samples which gets duplicated – Suncatcher Nov 25 '22 at 01:52

1 Answers1

0

Probably you need a left outer to one join in the CDS definition, please give it a try. At SQL run time of a CDS selection, cardinality plays a important role if using a field from associated entity. Please read this blog for the general explanation https://blogs.sap.com/2018/08/20/cardinality-of-association-in-cds-view/

wanderlandderek
  • 376
  • 4
  • 5