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(). WRITE: / -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(). WRITE: / -zphone, -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).