Saturday 15 February 2020

Using CDS views in ABAP code – Part 1 (data selection)

Introduction


In this blog post we will look at how and when to use CDS views in ABAP code to select data.

The standard SELECT statement in ABAP includes the ability to reference a CDS view. This addition is defined as follows in the SAP help:

… cds_entity[parameters][\path_expr]|[ WITH PRIVILEGED ACCESS]

Where

◉ [parameters] allows parameters to be sent to CDS views that require input parameters

◉ [\path_expr] allows one or more associations to be listed. Multiple associations form an association chain ending with the lowest association in the chain.

◉ [ WITH PRIVILEGED ACCESS] switches off CDS access control

This arms ABAPers with a new way to read data from a table that has a CDS view associated with it. We will consider the scenario where a simple set of related DB tables have basic CDS interface views on top of them. We will then evaluate the pros and cons of selecting from DB tables (DB approach) versus selecting from CDS views via the “cds_entity” clause above (CDS approach).

Our Data Scenario


VDM

The relevant part of the VDM is:

SAP ABAP Tutorial and Material, SAP ABAP Certifications, SAP ABAP Prep, SAP ABAP Exam Prep, SAP ABAP Guides

DB Tables

ZJTPHONE contains phones along with their colour code

SAP ABAP Tutorial and Material, SAP ABAP Certifications, SAP ABAP Prep, SAP ABAP Exam Prep, SAP ABAP Guides

JTCOLOR contains the colour dimension with attribute flagging whether the colour is dark.

SAP ABAP Tutorial and Material, SAP ABAP Certifications, SAP ABAP Prep, SAP ABAP Exam Prep, SAP ABAP Guides

ZJTCOLORTXT contains the colour texts per language

SAP ABAP Tutorial and Material, SAP ABAP Certifications, SAP ABAP Prep, SAP ABAP Exam Prep, SAP ABAP Guides

CDS views

AS per the VDM there is a basic interface view on top of each of these tables. The relevant parts of the CDS views are listed below.

@AbapCatalog.sqlViewName: 'ZIJTPHONE'
@VDM.viewType: #BASIC

define view Z_I_Phone
  as select from zjtphone
    association [0..1] to Z_I_COLOR as _Color 
                       on $projection.zcolor = _Color.zcolor
{
    @EndUserText.label: 'Phone'
key zphone,
    @EndUserText.label: 'Colour'
    @ObjectModel.foreignKey.association: '_Color'
    zcolor,

     _Color
}

@AbapCatalog.sqlViewName: 'ZIJTCOLOR'

define view Z_I_COLOR
  as select from zjtcolor
     association [0..*] to Z_I_COLORTXT as _Text 
              on $projection.zcolor = _Text.zcolor
{
      @ObjectModel.text.association: '_Text' 
      @EndUserText.label: 'Colour'
  key zcolor,

      _Text
}

@AbapCatalog.sqlViewName: 'ZIJTCOLORTXT'
@ObjectModel.dataCategory: #TEXT

define view Z_I_COLORTXT 
    as select from zjtcolortxt 
{
    @Semantics.language: true
key language,
    @ObjectModel.text.element: ['zcolortxt']
key zcolor,

    @Semantics.text: true
    zcolortxt
}

Requirement scenarios


Requirement 1 – data from last associated table only

Requirement: We want to produce a list of all dark colours that are used in phones.

Looking at the data in our tables we can see that this will be the colours blue and black. Brown is flagged as a dark colour but we do not have a brown phone.

Hence the list should look like:

SAP ABAP Tutorial and Material, SAP ABAP Certifications, SAP ABAP Prep, SAP ABAP Exam Prep, SAP ABAP Guides

DB approach

To get this list we use a select statement with inner joins across the 3 related tables as follows:

SELECT DISTINCT Text~zcolortxt
     INTO TABLE @DATA(lt_result)
           FROM zjtcolortxt AS Text
     INNER JOIN zjtcolor    AS Color ON Color~zcolor = Text~zcolor
     INNER JOIN zjtphone    AS Phone ON Phone~zcolor = Color~zcolor
          WHERE Color~is_dark = 'X'
            AND Text~language = 'E'.

LOOP AT lt_result ASSIGNING FIELD-SYMBOL(<ls_result>).
     WRITE: / <ls_result>-zcolortxt.
ENDLOOP.

CDS approach

We can produce the exact same result using the following select:

SELECT DISTINCT zcolortxt
           FROM z_i_phone
                \_Color[ is_dark = 'X' ]
                \_Text[ language = 'E' ]
             AS colortext
     INTO TABLE @DATA(lt_result).

Note that the association \_Text has been given the alias “colortext”. It is mandatory to provide an alias for the lowest level association and this emphasizes the fact that the select statement ONLY returns data from this last association. In other words, we can return fields from table zjtcolortxt but not tables zjtcolor or zjtphone as they are not pointed to by association _Text.

In our case this is not a problem as the only field we are after (zcolortxt) is from this lowest association (_Text). The next requirement we look at is a scenario where this is not the case.

Evaluation

The CDS view approach does not need to define the relationships between the tables as this is done externally (in the CDS views themselves). This results in a select statement that is very easy to understand as the filtering that takes place at each association level is contained in brackets directly after the association name.

Conversely, the DB table approach needs to define the relationships before adding the filtering via “where clauses” afterwards. This makes the code longer and harder to follow (although still very manageable in this simple example).

Requirement 2 – data from various tables in the association chain


Requirement: We want to produce a list of all phones that have dark colours along with their corresponding colour.

Looking at the data in our tables we can see there are 3 phones with dark colours.

Our list should look like:


SAP ABAP Tutorial and Material, SAP ABAP Certifications, SAP ABAP Prep, SAP ABAP Exam Prep, SAP ABAP Guides

DB approach

The only change to this approach is to add the newly required field(zphone) to the selection list.

SELECT  Phone~zphone,  Text~zcolortxt
INTO TABLE @DATA(lt_result)
      FROM zjtcolortxt AS Text
INNER JOIN zjtcolor    AS Color ON Color~zcolor = Text~zcolor
INNER JOIN zjtphone    AS Phone ON Phone~zcolor = Color~zcolor
     WHERE Color~is_dark = 'X'
       AND Text~language = 'E'.

LOOP AT lt_result ASSIGNING FIELD-SYMBOL(<ls_result>).
  WRITE: / <ls_result>-zphone, <ls_result>-zcolortxt.
ENDLOOP.

CDS approach/hybrid

As mentioned earlier, the CDS approach only exposes the target data of the lowest association i.e. _Text in our case. However, we can use this data to link back up to the zjtphone table via standard inner joins. This results in a hybrid of CDS and DB approaches.

SELECT DISTINCT iPhone~zphone, colortext~zcolor, colortext~zcolortxt
           FROM z_i_phone
                \_Color[ is_dark = 'X' ]
                \_Text[ language = 'E' ]
             AS colortext
 INNER JOIN zjtcolor    AS iColor ON iColor~zcolor = colortext~zcolor
 INNER JOIN zjtphone    AS iPhone ON iPhone~zcolor = iColor~zcolor
     INTO TABLE @DATA(lt_result).

Evaluation

While the second approach yields the exact same output as the first it has 2 major disadvantages:

1. The inner joins are traversing back up the table relationships we have already climbed down via associations

2. Instead of being easy to read this approach has resulted in a select statement that is harder to understand as it starts with CDS view z_i_phone and ends with the table (zjtphone) that sits underneath it.

Requirement 3 – authorisations


Requirement: List all colour codes a user is authorised for taking into consideration the data control language (DCL) file for CDS view Z_I_COLOR.

It is outside the scope of this blog to have a detailed discussion on how DCLs are used to restrict access to data. However, the requirement is listed as it is an important consideration thanks to the CDS approach having the “WITH PRIVILEGED ACCESS” clause available. As might be expected adding this clause switches CDS access control off.

Also note that this clause cannot be used together with path expressions (associations) in the select statement.

CDS approach

SELECT zcolor FROM z_i_color WITH PRIVILEGED ACCESS
               INTO TABLE @DATA(lt_result).

No comments:

Post a Comment