CDS view association[1..1] - INNER JOIN

[former_member620346]

Hello experts.

I'm practicing with new views CDS.

I have a question.

A "association [1..1] " is the same as a" INNER JOIN "??

* association [1..1]  -> The association has one target instance

I have this CDS view with INNER JOIN:

@AbapCatalog.sqlViewName: 'ZCDS_AS_001'
define view Zcds_Assoc_001 as 

 select from kna1 as k 
    inner join adr6 as a 
    on k.adrnr = a.addrnumber 
    {
       key k.kunnr,
       k.name1,
       k.adrnr,
       a.addrnumber,
       a.smtp_addr

     } 

This CDS view returns only records that have results in both tables.

But when I use "association [1..1] " instead of INNER JOIN, the CDS view behaves like LEFT OUTER JOIN!!!

@AbapCatalog.sqlViewName: 'ZCDS_AS_001'
define view Zcds_Assoc_001 as 

 select from kna1 as k 
    association[1..1] to adr6 as a 
    on k.adrnr = a.addrnumber 
    {
       key k.kunnr,
       k.name1,
       k.adrnr,
       a.addrnumber,
       a.smtp_addr

     } 

How can I program  CDS  with associations for act  like a INNER JOIN??

Thanks

[former_member205436]

Hi David,

Please see the CDS language documentation: ABAP Keyword Documentation

When you use path expressions (associations) in the select list, then this always leads to a LEFT OUTER JOIN. If you want to influence this behavior, then you can specify the JOIN type when you use the association. In your case you would implement the following:

a[ 1: INNER ].addrnumber,

a[ 1: INNER ].smtp_addr

Currently you can only specify INNER or LEFT OUTER in the square brackets. RIGHT OUTER is not yet supported.

Similarly, if you use an association in the FROM clause, e.g. "FROM entity.assoc" then this always leads to an INNER JOIN. This default is different than to when you use the association in the select list as above. The default JOIN type is dependent on where you use the association.

Kind regards

Chris

[former_member620346]

thank you very much for the explanation!!!

@AbapCatalog.sqlViewName: 'ZCDS_AS_001'
define view Zcds_Assoc_001 as 

 select from kna1 as k
    association[1..1] to adr6 as a
    on k.adrnr = a.addrnumber 
    {
       key k.kunnr,
       k.name1,
       k.adrnr,
       a[1:inner].addrnumber,
       a[1:inner].smtp_addr

     } 

Now it behaves like an INNER JOIN!!!

Kind regards

David

[former_member11830]

Hi Christiaan,

I was going through the discussion and to check the same I implemented the below simple cds view and it behaves as the inner join . Please let me know anything I am missing here,

@AbapCatalog.sqlViewName: 'ZCDS_ASSOC'

@AbapCatalog.compiler.CompareFilter: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'CDS with Associations'

define view Zcds_Associations as select from zbut000 as a

association [1..1] to zcrm_jds1 as b

    on  a.partner_guid  = b.objnr

    {

b.mandt,                          

a.partner_guid,

b.stat ,

b.udate,

b.utime,

b.inact

}

Table Contents: zbut000

Contents ZCRM_JDS1

Out put

[former_member205436]

Hi,

the easiest way to see exactly how the associations are translated and how the data sources are joined is to take a look at the generated SQL CREATE Statement. If you are running SAP NetWeaver AS ABAP 7.50 or later, you can do this in ADT. Open the DDL source > right-click > "Show SQL CREATE Statement". If you are using SAP NetWeaver AS ABAP 7.4, then navigate to the SQL view from Eclipse (F3 on the SQL view name in the annotation @AbapCatalog.sqlViewName) and in the menu in the SE11 select "Extras" > "CREATE Statement".

The rest is just understanding how LEFT OUTER JOINs and INNER JOINs behave.

In your case the cardinality [1..1] doesn't seem to be correct. This doesn't seem to be a "to one" relationship.

Also note, that in ABAP CDS [1..1] does not mean "one to one". The specified cardinality only applies to the target. The syntax is [min .. max]. So you have specified with [1..1], that the target is minimum 1 and maximum 1.

Regards

Chris

[ShakulJugran]

Hello David,

thanks for posting your query, this clarified the concept for me too

- Thanks for your answers

Best Regards,

Shakul.