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:
DB Tables
ZJTPHONE contains phones along with their colour code
JTCOLOR contains the colour dimension with attribute flagging whether the colour is dark.
ZJTCOLORTXT contains the colour texts per language
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:
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:
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