Let’s make ourselves familiar with some important keywords before we jump in to understand Association with examples:
Association
Association is different than join in that sense that association defines how two entities are connected to each other. It is sometimes understood as “Conceptual Thinking” also because its nature of linking entities rather than joining them directly. The join may be the next step once the association is defined.
Cardinality
In database design, the cardinality or fundamental principle of one data aspect with respect to another is a critical feature. The relationship of one to the other must be precise and exact between each other in order to explain how each aspect links together.
In the relational model, tables can be related as any of “one-to-many”, “many-to-many” “one-to-zero-or-one”, etc.. This is said to be the cardinality of a given table in relation to another.
In terms of SAP:
- A [1..1] B means that for every row of A, there is a unique row in B and vice versa
- A[0..1] B or just A [1] B means that B may have a record for which there no source information in A.
- A [0..*] B means that B may have many records for which there no source information in A.
Path Expressions
Path expressions identify an object by describing how to navigate to it to objects via steps/route. For example, the path expression Object.Employee.Address.ZipCode can refer to Zip Code of particular Employee’s address. Of course, this relationship should be established beforehand using association/cardinality to use this kind of Path to access particular information.
To understand Association better, let’s take help of some CDS views and an ABAP program to consume CDS.
Case 1:
In Case 1, we create 3 CDS views and a report for concept demonstration. We create first CDS view to link Business Partner with ADR6 table. In Second CDS we link first CDS and ADRC table. In Third CDS, we link first and second CDS so that it can be consumed by ABAP, oData service, and other CDS. In the last step of Case 1, we create an ABAP program and consume third CDS view.
All the code for Case 1 can be found on GitHub.
CDS- ZADR6_STOB:
@AbapCatalog.sqlViewName: ‘Zadr6_view’
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘BP + ADR6’
define view Zadr6_stob
as select from but020 as b20
association [1..1] to adr6 as _adr6 on b20.addrnumber = _adr6.addrnumber
and _adr6.flgdefault = ‘X’
{
// Business partner
key b20.partner as partner,
// Address #
key b20.addrnumber as addrnumber,
b20.addr_valid_from,
b20.addr_valid_to,
_adr6
}
where
// Address should be valid on the current date!
b20.addr_valid_from <= tstmp_current_utctimestamp()
and b20.addr_valid_to >= tstmp_current_utctimestamp()
Above CDS links Business Partner with the ADR6 table.
CDS- ZADRC_STOB:
@AbapCatalog.sqlViewName: ‘Zadrc_view‘
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘BP + ADR6 + ADRC’
define view Zadrc_stob
as select from adrc
association [1..1] to Zadr6_stob
as _adrc on $projection.addrnumber = _adrc.addrnumber
{
// Business partner
key _adrc.partner as partner,
// Address #
key adrc.addrnumber as addrnumber,
// Name
adrc.name1 as name,
_adrc
}
CDS links ZADR6_STOB with ADRC.
CDS- ZGET_FULL_ADDRESS:
@AbapCatalog.sqlViewName: ‘zget_full_addr‘
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Full Address’
define view zget_full_address
as select from Zadrc_stob as _address
{
// See how information is being fetched using Path expression
// Root Path : _address > fields
_address.partner,
_address.name,
_address.addrnumber,
// Path : _address > _adrc > Fields
_address._adrc.addr_valid_from,
_address._adrc.addr_valid_to,
// Path : address > _adrc > _adr6 > email
_address._adrc._adr6.smtp_addr
}
CDS links ZADRC_STOB with ZADR6_STOB and hence provide full address for a Business Partner. CDS can be readily consumed in an ABAP program as below:
Report: ZCDS_TEST
*&———————————————————————*
*& Report ZCDS_TEST
*&———————————————————————*
*&
*&———————————————————————*
REPORT zcds_test.
SELECT *
FROM zget_full_address
INTO TABLE @DATA(lt_data)
WHERE partner = ‘0010100001’.
IF sy–subrc IS INITIAL.
cl_demo_output=>display( lt_data ).
ENDIF.
This program passes a business partner to the CDS ZGET_FULL_ADDRESS and in return, CDS returns full address.
Output:
Case 2
In this case, we create a CDS to demonstrate how multiple tables can be linked using association and then information can be fetched using path expression.
All the code for Case 2 can be found on GitHub.
CDS:
CDS is written to join Business Partner, ADR6, and ADRC tables. This is also an example of CDS view with Parameters. We are going to cover that in next section!
@AbapCatalog.sqlViewName: ‘Ztest_cds_view‘
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘ZTEST_CDS’
define view Ztest_CDS_2
with parameters
i_partner :bu_partner
as select from but020 as b20
association to adr6 as a6 on
b20.addrnumber = a6.addrnumber
and a6.flgdefault = ‘X’
association [1..1] to adrc as _address
on $projection.addrnumber = _address.addrnumber
{
// Business partner
key b20.partner as partner,
// Address #
key b20.addrnumber as addrnumber,
a6[1: left outer].smtp_addr as email, // Email ID
// Exposed association is used to future reusability
// Association is on demand join,
// so if no field of ADRC is selected, join will never execute!
_address
}
where
b20.partner = :i_partner
and(
// Address should be valid on current date
b20.addr_valid_from <= tstmp_current_utctimestamp()
and b20.addr_valid_to >= tstmp_current_utctimestamp() )
CDS Output:
Program:
Program consumes CDS and since CDS is an exposed association, logic fetches exposed columns.
*&———————————————————————*
*&
Report ZCDS_TEST
*&———————————————————————*
*&
*&———————————————————————*
REPORT zcds_test.
DATA : i_bp TYPE bu_partner VALUE ‘0123456789’.
SELECT
FROMztest_cds_2( i_partner = @i_bp )
FIELDS
” Path expression used to get Telephone #
\_address–tel_number AS telephone, ” Telephone
email AS email ” Email
INTO TABLE @DATA(lt_address).
IF sy–subrc IS INITIAL.
cl_demo_output=>display( lt_address ).
ENDIF.
Notice the way Path expression is used to access Telephone number!
Program output:
You may also like Performance Comparison: New Open SQL vs CDS vs AMDP vs CTE(Common Table Expression)
Thanks to my father who told me concerning this
blog, this weblog is actually remarkable.
Every weekend i used to pay a visit this web page, because i wish for enjoyment, as this this web site conations in fact good funny data too.
Greetings I am so delighted I found your blog page, I really found you by
error, while I was searching on Bing for something else, Nonetheless I
am here now and would just like to say thanks for a incredible post and
a all round enjoyable blog (I also love the theme/design), I don’t have
time to go through it all at the moment but I have bookmarked it
and also added in your RSS feeds, so when I have
time I will be back to read a great deal more, Please do keep
up the superb job.
Its like you read my thoսghts! Yоu appear to
understand a lot approximately thіs, like you wrote the book in it or something.
I think that you simply can do with ѕome percent to fоrϲe
the message home a bit, hߋwever instead of that, that is fantastic
bⅼοg. A great read. I will сeｒtainly be Ьaϲk. https://janerinedesiderioiressitable.blogspot.com/
Hi everybody, here every person is sharing these experience,
thus it’s pleasant to read this webpage, and I used to visit this web site
all the time.