cardinality in a CDS view: different behaviour in 7.52SP04 and HANA1909

[abo]

Trying out the examples in an online tutorial on CDS there is one part I do not fully understand: it is expected that the cardinality specification in the views changes the result of the association, which makes sense, at least intuitively when coming from classical ABAP.

So I have a short report to query the two views:

• one with 1..1 cardinality
• another with 1..*

Thing is, they have the same output (EDIT: in 7.52). Other than a warning during activation, to the effect that the cardinality might affect results, I'm a bit lost.Assuming it is not because of the accent, why should the result be different?

EDIT: I'm adding this part after seeing the answer by osman.s

TLDR in HANA1909 the results are indeed different as explained by the instructor whereas in AS ABAP 7.52P04 they are the same, as I originally wrote above.

The execution plan in HANA for "DEMO1" (1..1):

(Here I am getting the SUM aggregation replicated on all lines)

The plan for "DEMOST" (1..*) also in HANA:

Here you can see that the join is being performed.

The execution plans in 7.52SP04 are basically the same, here I am getting the SUM aggregation replicated on all lines:

SELECT "SOID" ,SUM( "NETAMOUNT" ) "NETAMOUNT" FROM /* Entity name: ZABO_CDS_CARDIN_DEMO1 */ "ZABO_CARD_DEMO1" "ZABO_CDS_CARDIN_DEMO1" WHERE "CLIENT" = ? GROUP BY "SOID"  /* R3:ZABO_ASSOCIATION:26 T:ZABO_CARD_DEMO1 M:001 */ 

SELECT "SOID" ,SUM( "NETAMOUNT" ) "NETAMOUNT" FROM /* Entity name: ZABO_CDS_CARDIN_DEMO_STAR */ "ZABO_CARD_DEMOST" "ZABO_CDS_CARDIN_DEMO_STAR" WHERE "CLIENT" = ? GROUP BY "SOID"  /* R3:ZABO_ASSOCIATION:43 T:ZABO_CARD_DEMOST M:001 */ 

Wrapping it up and getting to the updated question, it seems HANA takes into account the cardinality whereas the older version does not.

Question: is this to be expected? I figure sandra.rossi horst.keller might know this one.

[former_member1702]

Cardinality is defined only for the Target (associated) Table/View.

It is mainly used to document the semantics of the data model. It is not validated at the runtime but can produce syntax check warnings.

I have seen your code. In this, you have taken two table SNWD_SO and SNWD_SO_I and both have same primary key of NODE_KEY. Since both of them have same Primary key, the output result is same whether you use 1:1 Cardinality or 1:*.

I have used two tables SPFLI and SFLIGHT. And they have different primary keys. There is one extra Primary key in target table SFLIGHT. So the relation between SPFLI and SFLIGHT is 1:many. If cardinality 1.1 is used in association (means – associated table SFLIGHT can have 1 entry for source table SPFLI), JOIN to table SFLIGHT does not happen and table SFLIGHT is not accessed internally. Hence internally works in the same way as LEFT OUTER TO ONE JOIN.

When cardinality of associated table SFLIGHT is set as 1:* then JOIN happens in the result set of the SELECT statement. So, Cardinality 1:* internally works in the same way as LEFT OUTER JOIN. Hence the output result is different. I would encourage you to accept this answer if it is helpful. In this way, other members can see that the answer is correct.

Thanks,

Osman Syed.

[abo]

While useful, the answer isn't correct because (drumroll) the question itself is not.

I'm in the process of collecting new data and will update it shortly.

[Sandra_Rossi]

Sorry, I don't know at all.