CDS View with Association and Path Expressions

Index

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 sysubrc 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
FROM
ztest_cds_2( i_partner = @i_bp )

    FIELDS

” Path expression used to get Telephone #

    \_addresstel_number AS telephone” Telephone

    email AS email ” Email

    INTO TABLE @DATA(lt_address).

IF sysubrc IS INITIAL.

    cl_demo_output=>display( lt_address ).

ENDIF.

Notice the way Path expression is used to access Telephone number!

Program output:

Index

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s