6.2ABAP Core Data Services

This section introduces the new ABAP CDS features in detail and uses examples to show you how to use these functions for your developments. For this purpose, we use the already known data model SFLIGHT.

As explained in Section 6.1, ABAP CDS currently only implements a part of the CDS specification, that is, the requirements arising within today’s scope of ABAP application development. Because the data from SAP solutions used in enterprises are already stored in the database system, it makes sense that the main focus of ABAP CDS is on creating views of already existing business data and not on defining new database tables. The resulting CDS views lay the foundation for the acceleration of existing ABAP programs and for the development of new, innovative applications. In SAP S/4HANA, for example, business data are provided by well-defined CDS views on SAP Business Suite database tables. These views allow new client applications to access clear and consistent interfaces and benefit from the code pushdown of these views. This is an important characteristic of ABAP CDS. You can also directly reuse your existing data models and DDIC objects (tables, views, data elements) in ABAP CDS. Therefore, the ABAP CDS specification is an extension of the DDIC and the SQL objects defined in the DDIC.

Another important characteristic of ABAP CDS is its database-independent implementation. Similar to Open SQL, CDS views can run on all SAP-supported database systems on AS ABAP 7.4. This ensures that the database models you’ve modeled with ABAP CDS show a uniform functional behavior irrespective of your application case and system landscape. If you execute your CDS views on the SAP HANA database, you additionally benefit from the main memory data processing performance advantages.

The following sections introduce the CDS functions that have been provided since AS ABAP 7.4 SP 5. However, the further development of the CDS specification and implementation in AS ABAP is ongoing, so you can expect new and useful features in the future.

6.2.1Core Data Services Views

In general, views are queries that are stored in the database. The structure and content of the data, which are returned as the query result, are based on other database tables or views. The results aren’t stored; that is, each time a view is called, the respective query is executed again. You can use views for read accesses in ABAP everywhere where database tables can be used.

In contrast to classic DDIC views, queries for CDS views are defined using the DDL of the CDS specification. This definition is written to a DDL source. When the DDL source is activated in ABAP, the query is stored as an SQL view in the database. In addition, the CDS metadata, for example, annotations and associations, are stored in the ABAP repository, and an entry is created for the view definition in the ABAP repository buffer. An entry is created in table TADIR for each DDL source. The respective transport object is called R3TR DDLS <DDL source name>.

In addition to DDL sources and CDS views, you should know the following concepts in the ABAP CDS context:

Creating Core Data Services Views

To demonstrate how to create a CDS view, we’ll define the SFLIGHTS view from the DDIC, which we already used in Chapter 3, Section 3.2.3, as a CDS view. You can define CDS views using the ABAP Development Tools for SAP NetWeaver (introduced in Chapter 2, Section 2.4.2) in the ABAP perspective. You can’t define CDS views using the ABAP Workbench.

To define a CDS view, you have to create a DDL source. For this purpose, open the context menu of a package in the Project Explorer, and select New • Other ABAP Repository Object. In the dialog window that opens, select Dictionary • DDL Source. The creation wizard shown in Figure 6.1 opens. Enter a name and description for the DDL source.

Because it’s currently not possible to define more than one CDS view in a DDL source in ABAP, it has become common practice to give the DDL source the same name as the CDS view that is defined in the source. This is very useful because only the name of the DDL source—not the name of the CDS view—is displayed in the Project Explorer. For our example, we use the name "ZA4H_06_SFLIGHTS".

Creating a DDL Source

Figure 6.1Creating a DDL Source

[»]DDL Sources in AS ABAP 7.5

As of AS ABAP 7.5, you create DDL sources in the Project Explorer under Core Data Services • Data Definitions instead of under Dictionary • ABAP DDL Sources.

Perform all other steps in the wizard. In the last step, you can choose between different creation templates. SAP provides these templates for all CDS objects, which you can define in DDL sources. They form the basic structure of your definition and are useful if you’ve just learned how to use CDS syntax. Because they are delivered as code templates in Eclipse, you can modify them or define your own templates. You can manage the templates by choosing Window • Preferences • ABAP Development • Editors • Source Code Editors • DDL Templates. For our example, select the Define View with Join template as shown in Figure 6.2.

After selecting the template, open the newly created DDL source in the DDL Editor in Eclipse. If no template is selected, the DDL source is empty. In this case, you can assign a template retroactively by using the code completion of the editor. To do so, press (Ctrl)+(Space), and select Define View with Join.

Selecting a Template for Creating a CDS View

Figure 6.2Selecting a Template for Creating a CDS View

The template only contains placeholders at first, which you have to fill (see Figure 6.3):

You can use the (Tab) key to navigate from one placeholder to the next and then edit them. Listing 6.1 shows the structure of the Za4h_06_Sflights CDS view.

@AbapCatalog.sqlViewName: 'ZA4H06SFLIGHTS'
define view Za4h_06_Sflights as
select from scarr
inner join spfli
on scarr.carrid = spfli.carrid
inner join sflight
on spfli.carrid = sflight.carrid
and spfli.connid = sflight.connid {
scarr.carrid,
scarr.carrname,
spfli.connid,
spfli.countryfr,
spfli.cityfrom,
spfli.airpfrom,
spfli.countryto,
spfli.cityto,
spfli.airpto,
sflight.fldate,
sflight.seatsmax,
sflight.seatsocc
}

Listing 6.1Za4h_06_Sflights CDS View

The view query is made via a SELECT statement. Database table SCARR is called as the first data source. Database tables SPFLI and SFLIGHT are used as additional data sources. The data sources are linked via JOIN operators taking into account the defined ON conditions. The fields selected from the database tables are specified in the SELECT list between curly brackets. The fields that are separated from each other by commas define the structure of the returned data. In CDS, these fields are also called elements. As you’ll see later, these elements can also be associations.

Now, check (via (Ctrl)+(F2)) and activate ((Ctrl)+(F3)) the DDL source. You can now test the automatically created view in the data preview in Eclipse via Data Preview by pressing the (F8) key. Figure 6.4 shows the result set that our CDS view returns.

Result Set of Za4h_06_Sflights in the Data Preview

Figure 6.4Result Set of Za4h_06_Sflights in the Data Preview

[»]Formatting DDL Sources

Currently, the DDL editor doesn’t provide a Pretty Printer that you can call to format your DDL sources. However, when you save your DDL sources, the DDL editor automatically ensures that all names are formatted consistently. The names of all CDS entities and their elements are formatted according to their conventions in the definition (case preservation). All other names and CDS keywords are written in lowercase letters.

Joins

You might have noticed that we changed the left outer join, as provided by the code template, to an inner join in Figure 6.3. This was necessary to ensure that the process of querying the CDS view is identical to the process of querying the SFLIGHTS view. Compared to classic database views that are defined in the DDIC, ABAP CDS provides two new join options: left outer join and right outer join. The join type for linking two data sources highly influences the result set of the query:

Because we used an inner join, our view, Za4h_06_Sflights, only returns airlines that actually offer flights. To output all airlines, we should have used the LEFT OUTER JOIN operator. In this case, you should always consider the implicit link sequence. Joins in CDS view definitions are executed from left to right by default. You can determine the join sequence explicitly by sorting ON conditions and compounding JOIN clauses appropriately.

[»]Join Sequence

The join sequence can be defined solely via the sequence of the ON conditions. You don’t have to compound JOIN operations using round brackets. However, you should always compound objects to enhance the readability of your definition and clarify the query’s intention.

Client Handling

Another important difference between ABAP CDS and SAP HANA CDS is the automatic client handling feature in ABAP. Although the client field of the underlying data source hasn’t been handled explicitly in the CDS view definition in Listing 6.1, the defined view is client-dependent. In the CREATE statement for the SQL view, the ABAP AS automatically considers client handling if the data sources are client-dependent.

Let’s look at the SQL view definition that was created in the database for the CDS view from Listing 6.1. For this purpose, you can directly navigate from the DDL source in the ABAP Development Tools to Transaction SE11. To do so, position the cursor in the DDL source on the ZA4H06SFLIGHTS SQL view name in the @AbapCatalog.sqlViewName annotation, and press (F3). The system then takes you to Transaction SE11 where you can call the CREATE statement of the SQL view in the menu via Extras • CREATE Statement. The statement is shown in Listing 6.2.

CREATE VIEW "ZA4H06SFLIGHTS" AS SELECT
"SCARR"."MANDT" AS "MANDT",
"SCARR"."CARRID",
"SCARR"."CARRNAME",
"SPFLI"."CONNID",
"SPFLI"."COUNTRYFR",
"SPFLI"."CITYFROM",
"SPFLI"."AIRPFROM",
"SPFLI"."COUNTRYTO",
"SPFLI"."CITYTO",
"SPFLI"."AIRPTO",
"SFLIGHT"."FLDATE",
"SFLIGHT"."SEATSMAX",
"SFLIGHT"."SEATSOCC"
FROM (
"SCARR" "SCARR" INNER JOIN "SPFLI" "SPFLI" ON (
"SCARR"."MANDT" = "SPFLI"."MANDT" AND
"SCARR"."CARRID" = "SPFLI"."CARRID"
)
) INNER JOIN "SFLIGHT" "SFLIGHT" ON (
"SPFLI"."CARRID" = "SFLIGHT"."CARRID" AND
"SPFLI"."CONNID" = "SFLIGHT"."CONNID" AND
"SCARR"."MANDT" = "SFLIGHT"."MANDT"
)

Listing 6.2CREATE Statement for SQL View ZA4H06SFLIGHTS

Compared to the CDS view definition, in the SQL view definition, the client field of the first data source in the FROM clause was automatically added to the SELECT list, and the ON conditions of the JOIN clauses were also extended, respectively. As a result, the CDS view is client-dependent. The automatically added client field, however, isn’t visible in the CDS view; that is, you can’t address the MANDT field in Open SQL or in another CDS view definition if you use the CDS view as a client-dependent data source.

[»]Explicit Client Handling

You shouldn’t add the client field explicitly to the SELECT list of your view definition or handle the client fields in the join conditions. Client handling for CDS views with client-dependent data sources is implemented automatically in AS ABAP.

You can deactivate the automatic client handling function in CDS views as follows:

Listing 6.3 shows how you can use the CLIENT SPECIFIED additions in ABAP to read the flights of the '001' client via the Za4h_06_Sflights CDS view. We then output the flights using a WRITE statement.

TYPES: ty_result TYPE za4h_06_sflights
CLIENT SPECIFIED clnt.
DATA: lt_results TYPE STANDARD TABLE OF ty_result
WITH EMPTY KEY.

SELECT * FROM za4h_06_sflights
CLIENT SPECIFIED za4h_06_sflights~clnt
WHERE clnt = '001'
INTO TABLE @lt_results.

LOOP AT lt_results ASSIGNING FIELD-SYMBOL(<flight>).
WRITE: / <flight>-clnt,
<flight>-carrid,
<flight>-connid,
<flight>-fldate.
ENDLOOP.

Listing 6.3Using the CLIENT SPECIFIED Addition for CDS Views

Associations

In the SFLIGHT data model, the relationships between database tables SCARR, SPFLI, and SFLIGHT are defined by foreign keys in the DDIC. We used these relationships to determine the data sources for our CDS view and to define the join conditions. Although the data model is simple, the foreign key relationships are still rather technical. They don’t necessarily follow our natural way of thinking when working with this data model. We would presumably define an issue that we want to solve with the SFLIGHTS DDIC view as follows: “I want to view all flights of the airlines, including information on flight plans and names of the airlines.” Of course, the requested information still needs to be detailed, but we would never say: “I want an INNER JOIN of SCARR and SPFLI for which the CARRIDs are identical and….” CDS and the associations in particular help you make the data model more comprehensible for users and experts.

Defining Associations

In this section, we again define SFLIGHTS as a CDS view, but we also use associations this time. For this purpose, we first define CDS views on database tables SPFLI and SFLIGHT in which we model the relationships between the data sources as associations. We then use these views as data sources for the Za4h_06_Sflights_Using_Assocs CDS view in which the associations are used. Table 6.1 provides an overview of the views to be defined and their associations.

CDS View

Data Source (Table)

Defined Association

Relationship Modeled by the Association

Za4h_06_Flightplan

SPFLI

Airline

Za4h_06_Flightplan (SPFLI) to SCARR

Za4h_06_Flight

SFLIGHT

Flightplan

Za4h_06_Flight (SFLIGHT) to Za4h_06_Flightplan (SPFLI)

Table 6.1CDS Views and Associations for Database Tables SPFLI and SFLIGHT

First, we define the Za4h_06_Flightplan CDS view as shown in Listing 6.4.

@AbapCatalog.sqlViewName: 'ZA4H06FLIGHTPLAN'
define view Za4h_06_Flightplan as select from spfli
association [1..1] to scarr as Airline
on spfli.carrid = Airline.carrid
{
carrid,
connid,
countryfr,
cityfrom,
airpfrom,
countryto,
cityto,
airpto,
fltime,
deptime,
arrtime,
distance,
distid,
fltype,
period,
//Display association for external usage
Airline
}

Listing 6.4CDS View Za4h_06_Flightplan with Definition of the AIRLINE Association

The Airline association is defined through the ASSOCIATION keyword. It models a link between the data source in the FROM clause (table SPFLI) and the data source after the TO keyword (table SCARR). The data source after TO is referred to as the target data source. The condition for the link is defined in the ON clause. All fields of the data sources that are specified in the FROM clause and used in the ON condition must also be added to the SELECT list. The name of the association is also used as an alias for the target data source in the association definition. This alias must be used in the ON clause. You don’t have to assign an association name. If you don’t assign a name, the association is given the name of the target data source.

At first glance, the association definition looks similar to the join definition in Listing 6.1. Actually, you can generate a join in the database from this association definition; however, this is only implemented when the association is used. When the DDL source is activated, AS ABAP converts the association into an SQL join before the SQL view is created in the database.

Associations can be used locally in the CDS view in which they are defined and externally by other CDS views that leverage the view as a data source. For this purpose, you must make the association visible by adding it to the SELECT list. Otherwise, it remains hidden from other CDS views.

Finally, association definitions also include the cardinality of the target data source, which is specified in square brackets ([<min>..<max>]). This information is supposed to help users understand the relationship between the data sources and can also be used for the generation of SQL joins for optimizations in the SAP HANA database. To express that a flight plan belongs to exactly one airline, for example, the cardinality is defined with [1..1]. If a flight plan could belong to no airline or one airline, the definition is [0..1] or, abbreviated, [1] (if the minimum value is omitted, the default value 0 is used). In this case, you don’t have to specify the target cardinality; however, you should always specify it if you know it. The default value that is used if the target cardinality is omitted is [1].

[»]Runtime Check of the Target Cardinality

The cardinality of the target data source isn’t checked at runtime. Nevertheless, you should ensure that the specification is correct because as of AS ABAP 7.5, it’s used to optimize SQL joins in SAP HANA. The cardinality is also used for syntax checks within the scope of the association’s use. For example, only associations with cardinality [1] can be used in WHERE clauses.

Next, we define a Za4h_06_Flight CDS view for table SFLIGHT (see Listing 6.5). The Flightplan association uses the Za4h_06_Flightplan CDS view as the target data source. In the association definition, you can see the $PROJECTION keyword. It allows you to refer to fields of the SELECT list. If you’ve assigned aliases to your fields to improve readability, you can also use these aliases in your association definition via $PROJECTION.

@AbapCatalog.sqlViewName: 'ZA4H06FLIGHT'
define view Za4h_06_Flight as select from sflight
association [1..1] to Za4h_06_Flightplan
as Flightplan on
$projection.carrid = Flightplan.carrid and
$projection.connid = Flightplan.connid
{
carrid,
connid,
fldate,
price,
currency,
planetype,
seatsmax,
seatsocc,
paymentsum,
seatsmax_b,
seatsocc_b,
seatsmax_f,
seatsocc_f,
//Display association for external usage
Flightplan
}

Listing 6.5CDS View Za4h_06_Flight with Definition of the FLIGHTPLAN Association

[+]Modeling Associations

When modeling associations, you should make the same considerations as when defining foreign key relationships in SQL. Associations are usually modeled from the transaction data level to the master data level. This means that associations are defined in the entity of the transaction data, and the entity of the master data is specified as the target data source.

Nevertheless, it’s possible and often makes sense to describe relationships between entities using associations that don’t meet the integrity condition. Similar to joins, associations aren’t bound by referential integrity. Associations are supposed to provide easy and semantically reproducible access to the data model and to the relationships of the entities the data model contains.

However, you should make sure that the association definitions don’t lead to cyclical dependencies between CDS views. At least in AS ABAP 7.4, these definitions can result in errors during the activation of the involved CDS sources.

Use of Associations

The Za4h_06_Flightplan and Za4h_06_Flight CDS views now form the interface for reading the flight data. Direct read accesses to the database tables of the flight data model are no longer necessary. You’ll understand the benefits of using CDS views as interfaces to business data when we solve our initial problem with these views, that is, calling all flights of the airlines, including information on flight plans and names of the airlines. Listing 6.6 shows how we implemented this query using a CDS view and the Flightplan and Airline associations.

@AbapCatalog.sqlViewName: 'ZA4H06SFLIGHTSUA'
define view Za4h_06_Sflights_Using_Assocs
as select from Za4h_06_Flight as Flight {
Flight.carrid,
Flight.Flightplan.Airline.carrname,
Flight.connid,
Flight.Flightplan.countryfr,
Flight.Flightplan.cityfrom,
Flight.Flightplan.airpfrom,
Flight.Flightplan.countryto,
Flight.Flightplan.cityto,
Flight.Flightplan.airpto,
Flight.fldate,
Flight.seatsmax,
Flight.seatsocc
}

Listing 6.6CDS View Za4h_06_Sflights_Using_Assocs with Path Expressions

Compared to Listing 6.1, the view definition is definitely more intuitive. Because we use associations in path expressions, we don’t need to know or specify the technical join conditions of the data sources used. We only have to determine the data source link in the association definition once so that the ABAP AS can interpret it by each time the association is used. This means that we can focus on the semantic relationships between the entities during the modeling process: “A flight has a flight plan, and a flight plan belongs to an airline.” This way, our query better reflects the requirements of the users as shown in Table 6.2. To make the path expressions even more transparent, we assigned the Flight alias to the Za4h_06_Flight data source and used this alias in the SELECT list. This alias isn’t mandatory and can also be omitted in the path expressions.

Requirement

Statement/Path Expression

Explanation

"I want to view all flights of the airlines …,

select from Za4h_06_Flight

Read all flights.

… including information on their flight plans …

Flight.Flightplan.<detail>

Read the information from the flight plan of the flight. For this purpose, follow the path from the flight via the flight plan (association) to the information. Every piece of information is a field from the target data source of the Flightplan association.

… and including the names of the airlines.”

Flight.Flightplan.Airline. carrname

Read the name of the airline from the flight plan. For this purpose, follow the path from the flight via the flight plan and airline (associations) to the name.

Table 6.2Power of Expression of CDS, Demonstrated Using CDS View Za4h_Sflights_Using_Assocs

[ ! ]Prefixes in Association Names

The association name is a semantic alias for the target data source. In the Flight.Flightplan path, Flightplan, for example, represents the Za4h_06_Flightplan CDS view. The path is generated via the dot operator. The dot operator allows access to the elements in the Za4h_06_Flightplan view (including the associations defined there).

Consequently, it becomes superfluous to express in the association name that it’s an association or a path (e.g., Flight.toFlightPlan.toAirline.carrname). Prefixes, such as to, impair the readability and are considered bad form. If you want to use prefixes to avoid naming conflicts with other fields of the SELECT list when defining associations, for example, you can use an underscore (e.g., Flight._FlightPlan._Airline.carrname). Underscores are used as prefixes in nearly all CDS data models provided by SAP to avoid conflicts and ensure a consistent format.

Let’s take a look at the corresponding SQL view definition in Listing 6.7, which was created in the database for the CDS view.

CREATE VIEW "ZA4H06SFLIGHTSUA" AS SELECT
"FLIGHT"."MANDT" AS "MANDT",
"FLIGHT"."CARRID",
"=A1"."CARRNAME",
"FLIGHT"."CONNID",
"=A0"."COUNTRYFR",
"=A0"."CITYFROM",
"=A0"."AIRPFROM",
"=A0"."COUNTRYTO",
"=A0"."CITYTO",
"=A0"."AIRPTO",
"FLIGHT"."FLDATE",
"FLIGHT"."SEATSMAX",
"FLIGHT"."SEATSOCC"
FROM (
"ZA4H06FLIGHT" "FLIGHT"
LEFT OUTER JOIN "ZA4H06FLIGHTPLAN" "=A0" ON (
"FLIGHT"."CARRID" = "=A0"."CARRID" AND
"FLIGHT"."CONNID" = "=A0"."CONNID" AND
"FLIGHT"."MANDT" = "=A0"."MANDT"
)
) LEFT OUTER JOIN "SCARR" "=A1" ON (
"FLIGHT"."MANDT" = "=A1"."MANDT" AND
"=A0"."CARRID" = "=A1"."CARRID"
)

Listing 6.7SQL View Definition for CDS View Za4h_06_Sflights_Using_Assocs

Because the database management systems don’t provide native support for ABAP CDS entities, the entity names are replaced by their corresponding SQL view name and the ABAP AS converts the used associations into SQL joins. In the generated SQL statement, aliases (=A0 and =A1) were automatically defined for the ZA4H06FLIGHTPLAN and SCARR data sources, and the system generated left outer joins from the associations.

Which join type is generated for the associations depends on where the associations are used. Associations can be used in the SELECT list and in the FROM, WHERE, and GROUP BY clauses. If they are used in the FROM clause, an inner join is generated by default. In all other cases, left outer joins are generated. You can define the join type yourself by specifying the join operator and the target cardinality that is to be expected after the association name in the path expression, for example, Flight.Flightplan[1: inner].countryfrom.

Here, as well, you don’t have to specify the target cardinality, but it does make sense to do so. If you use associations, you can only indicate the maximum target cardinality.

If you use associations, you should pay particular attention to the sequence of the generated joins. The ABAP AS translates the occurrences of associations from left to right. For more complex CDS views, you should take a look at the generated CREATE statement.

[»]Supported Join Types for Associations

When assigning the join type, you can only use the INNER and LEFT OUTER operators. You can’t specify RIGHT OUTER. If you require right outer joins, you first have to—if this is possible and makes sense from the technical modeling perspective—either reverse the direction of your association in the definition (source and target) and use a left outer join or define the links of the data sources via classic join definitions as shown earlier in Listing 6.1.

You can also filter the data that are linked via associations. Association filters can be specified within square brackets and then affect the result set. For example, the following usage of an association allows you to view only the flights with Germany as the target country:

Flightplan[1: inner where countryto = 'DE'].countryfrom

If you define filter conditions together with a join type, the filter conditions must begin with the WHERE keyword. You can’t use association filters in view definitions that form aggregations. The ABAP AS inserts the conditions of the association filters into the generated ON condition of the SQL join.

The system generates a join for each occurrence of an association. If no filter is specified, the ABAP AS aggregates similar joins in one join. If you use association filters, this is no longer done automatically. However, you can enforce this optimization by using the @AbapCatalog.compiler.CompareFilter: true annotation. This annotation must precede the DEFINE VIEW statement. The filters of the associations are then compared, and joins with the same filter are also aggregated. We recommend that you generally use this annotation for your CDS view definitions.

Views with Parameters

Using literals in view definitions, for example, in comparison expressions, is usually inflexible. If you want to transfer values to a CDS view when it’s called, you can define parameters for this view. In Listing 6.8, we use parameters to transfer the target country to which the result list of the flights is supposed to be restricted only when the view query is executed.

@AbapCatalog.sqlViewName: 'ZA4H06SFLIGHTSUP'
@AbapCatalog.compiler.compareFilter: true
define view Za4h_06_Sflights_Using_Params
with parameters p_countryto : abap.char(3)
as select from Za4h_06_Flight as Flight {
Flight.carrid,
Flight.Flightplan.Airline.carrname,
Flight.connid,
Flight.Flightplan.countryfr,
Flight.Flightplan.cityfrom,
Flight.Flightplan.airpfrom,
Flight.Flightplan.countryto,
Flight.Flightplan.cityto,
Flight.Flightplan.airpto,
Flight.fldate,
Flight.seatsmax,
Flight.seatsocc
}
where Flight.Flightplan.countryto =
$parameters.p_countryto
and Flight.Flightplan.countryto is not null

Listing 6.8CDS View Za4h_06_Sflights_Using_Params with Parameters

View parameters are defined before the SELECT statement using the WITH PARAMETERS keyword. The parameter definition includes the specification of the parameter name and type. The type needs to be scalar. You can define selected DDIC types and data elements from the DDIC as parameter types.

In CDS views, DDIC types are prefixed with abap, for example, abap.int4. Parameter names commonly start with p_ to avoid naming conflicts. Although the $PARAMETERS keyword or scope operator (:) need to be used for parameters, the ABAP CDS Compiler reports a syntax error if parameters and elements of the SELECT list use the same names.

You can define several parameters in a CDS view that are separated by a comma. In general, you can use parameters everywhere in the view definition where literals can be used, in particular on the right side of comparison operators.

CDS views with parameters can be used as data sources in other CDS views and in Open SQL statements. In AS ABAP 7.4, SAP HANA and many other database systems support CDS views with parameters. If CDS views with parameters aren’t available, the execution of the view results in the CX_SY_SQL_UNSUPPORTED_FEATURE exception. As of AS ABAP 7.5, this feature is available for all database systems.

If you use CDS views with parameters in Open SQL statements, AS ABAP 7.4 outputs a syntax warning indicating that these views aren’t supported by all database versions. You can prevent this warning from being issued with the ##DB_FEATURE_MODE pragma. You can also use the CL_ABAP_DBFEATURES class to check at runtime whether CDS views with parameters are supported. If required, you can implement the function as an alternative.

Listing 6.9 shows you how to use CDS views with parameters in Open SQL. Note that the more strict Open SQL syntax must be applied here. You need to prefix the lv_countryto and lt_flights ABAP variables with the escape character (@). For more information on the syntax, refer to Section 6.4.

DATA:
lv_countryto TYPE za4h_06_flightplan-countryto,
lt_flights TYPE STANDARD TABLE OF
za4h_06_sflights_using_params
WITH EMPTY KEY.
lv_countryto = 'US'.
IF abap_true =
cl_abap_dbfeatures=>use_features(
requested_features =
VALUE #( (
cl_abap_dbfeatures=>views_with_parameters ) )
).
SELECT *
FROM za4h_06_sflights_using_params(
p_countryto = @lv_countryto )
INTO TABLE @lt_flights
##DB_FEATURE_MODE[VIEWS_WITH_PARAMETERS].
ELSE.
* Alternative implementation ...
ENDIF.

Listing 6.9Using CDS Views with Parameters in Open SQL

You can provide parameters with literals or other parameters. If a CDS view with parameters is used as the target data source of an association, the parameter must be provided with a value if the association is used. However, in this case, you can also use a field of the data sources as the parameter value.

[»]Support of Session Variables as of SAP NetWeaver AS ABAP 7.5

As of AS ABAP 7.5, you can also use session variables in CDS view definitions. These variables don’t have to be transferred as parameters but can be addressed as global variables. The values for session variables are automatically set when using Open SQL. For example, for the $session.user session variable, the value from the sy-uname system field is set. If Open SQL isn’t used, and the view isn’t executed on SAP HANA, the values of the session variable aren’t defined when the view is executed. Always keep this in mind when using session variables.

6.2.2Code Pushdown

The previous sections introduced CDS and data modeling using ABAP CDS. However, ABAP CDS provides even more functions that enable you to implement calculations and application logic in your CDS views to support code pushdown. If you are familiar with the SQL-92 and SQL:1999 standards, you’ll probably already know some of them.

This section provides you with an overview of these new functions and explains their usage by using examples. After reading this section, you should easily be able to determine which processing logic you can implement in ABAP CDS views. You can find a complete description of the individual functions in the ABAP help ((F1) help).

Table 6.3 through Table 6.8 provide you with an overview of the SELECT clauses, operators, and functions that ABAP CDS supports as of AS ABAP 7.4 SP 8. In addition, you can use CASE statements (simple case and searched case) in the SELECT list to define condition expressions. You can imagine simple CASE statements as SWITCH statements, whereas searched CASE statements behave like IF ... ELSE ... IF statements.

Clause/Operator

Usage/Remark

WHERE

Specifies filters for the result set. The filters are applied after all data sources have been linked. Subselects aren’t supported.

GROUP BY

Specifies fields with which aggregations (AVG, SUM, MIN, MAX, COUNT) are supposed to be formed. Rows with the same values in the specified fields define a group and are summarized in one row in the result set.

AS

Specifies an alternative name (alias).

UNION

Merges the result sets of two SELECT statements in the same view definition. Duplicates in the merged result set are deleted.

HAVING

Specifies filters for the result set. In contrast to the WHERE clause, aggregation functions can be used in conditions here. If aggregation functions are used in conditions, the filters are applied after the filters in the WHERE clause have been applied.

UNION ALL

Like UNION, but keeps results that occur several times in the merged result set. UNION ALL is more efficient than UNION and should be used if you already know that there are no duplicates.

Table 6.3SELECT Clauses

Function

Usage/Remark

AVG( [DISTINCT] field )

Average value of field for each group. If DISTINCT is indicated, duplicates are ignored during the aggregation process.

MIN( field )

Minimum value of field for each group.

MAX( field )

Maximum value of field for each group.

SUM( [DISTINCT] field )

Sum of the values of field for each group. If DISTINCT is indicated, duplicates are ignored during the aggregation process.

COUNT( DISTINCT field )

Number of rows in the group. Duplicates aren’t counted.

COUNT( * )

Number of rows in the group.

Table 6.4Aggregation Functions

Function

Usage/Remark

CEIL( expr )

Smallest integer that is greater than or equal to the expr numeric expression.

MOD( expr1, expr2 )

Divides numeric expression expr1 by expr2 and returns the remainder.

ABS( expr )

Absolute value of the expr numeric expression.

DIV( expr1, expr2 )

Integer-based division of numeric expression expr1 by expr2.

DIVISION( expr1, expr2, dec )

Decimal-based division of numeric expression expr1 by expr2. The result is rounded to dec decimal places.

FLOOR( expr )

Greatest integer that is smaller than or equal to the expr numeric expression.

ROUND( expr, pos )

Rounded value of expr. pos numeric expression indicates the position of the rounding in relation to the decimal separator.

Table 6.5Numeric Functions

Function

Usage/Remark

SUBSTRING( expr, pos, len )

Returns a part of the character string from the expr string expression. The part is determined by the position (pos) and length (len) in the character string.

LPAD( expr, len, literal )

Populates the right-aligned character string from the expr string expression with the characters from literal up to the length len from the left.

CONCAT( expr1, expr2 )

Concatenates the character strings of the expr1 and expr2 string expressions.

REPLACE( expr1, expr2, expr3 )

Replaces all occurrences of the expr2 character string in the expr1 character string with the content of expr3. All specified character strings can be string expressions.

Table 6.6String Functions in ABAP CDS 7.4

Function

Usage/Remark

CAST( expr AS type )

Converts the result type of the expr expression into the defined type DDIC type. AS ABAP 7.4 supports only selected DDIC types. They are indicated with the abap prefix, for example,

abap.int4 or abap.char(3).

COALESCE( expr1,
expr2 )

Returns the value of the expr1 expression if this isn’t equal to NULL. Otherwise, the value of the expr2 expression is returned.

CURRENCY_CONVERSION( ... )

Performs a currency conversion.

UNIT_CONVERSION( ... )

Performs a unit conversion.

DECIMAL_SHIFT( ... )

Sets the decimal separator of a value according to the currency specified.

Table 6.7Additional Functions in ABAP CDS 7.4

Operator Type

Operators

Usage/Remark

Boolean operators

NOT, AND, OR

AND and OR link logical expressions. NOT reverses the result of a logical expression.

Comparison operators

BETWEEN, =, <>, <, >, <=, >=, LIKE,
IS [NOT] NULL

IS [NOT] NULL can only be used in WHERE clauses.

Arithmetic operators

+, -, *, /

The / operator is used for float-based divisions. It’s generally not suitable for financial applications.

Table 6.8Operators

To demonstrate the usage of these functions with an example, we implement a variant of the Open SQL query with aggregate functions from Listing 3.6 in Chapter 3, Section 3.2.2, as a CDS view. We want to find out if there are more bookings for the booking class of a flight (based on the entries in table SBOOK) than occupied seats (table SFLIGHT). The desired class is specified using a view parameter.

Listing 6.10 shows the CDS view. We only consider the bookings that haven’t been canceled and restrict the bookings to the requested class in the WHERE clause. The actual check, whether there are more bookings than occupied seats, is performed in the HAVING clause. We implement the check separately for each class. In addition, we marked the key attributes of the view with the key keyword. In SAP NetWeaver 7.4, the specification of the key is mainly to help users understand the data model and isn’t relevant for the execution of the view query in Open SQL. However, because there are ABAP frameworks that use this metadata, for example, SAP List Viewer with Integrated Data Access (ALV with IDA), you should always define the key attributes in your views.

@AbapCatalog.sqlViewName: 'ZA4H06BOOKCHKP'
@AbapCatalog.compiler.compareFilter: true
define view Za4h_06_Booking_Cchk_Params
with parameters p_Booking_Class : abap.char( 1 )
as select from sbook as booking
inner join sflight as flight
on booking.carrid = flight.carrid
and booking.connid = flight.connid
and booking.fldate = flight.fldate
{
key booking.carrid,
key booking.connid,
key booking.fldate,
case $parameters.p_Booking_Class
when 'Y' //Economy Class
then flight.seatsocc
when 'C' //Business Class
then flight.seatsocc_b
else //First Class
flight.seatsocc_f
end as count_Occupied_Seats,
count( * ) as count_Bookings
}
where booking.cancelled <> 'X'
and booking.class = $parameters.p_Booking_Class
group by
booking.carrid,
booking.connid,
booking.fldate,
booking.class,
flight.seatsocc,
flight.seatsocc_b,
flight.seatsocc_f
having
//Occupied seats < number of bookings
( booking.class = 'Y' and
flight.seatsocc < count( * ) )
or ( booking.class = 'C' and
flight.seatsocc_b < count( * ) )
or ( booking.class = 'F' and
flight.seatsocc_f < count( * ) )

Listing 6.10Consistency Check of Flight Bookings

Finally, we design a CDS view that checks all classes for inconsistencies and returns these inconsistencies in a result set. For each inconsistency, it should be transparent to which booking class it refers, and the difference between the number of bookings and the number of occupied seats should be determined. For this purpose, we first define the UNION ALL CDS view as shown in Listing 6.11.

@AbapCatalog.sqlViewName: 'ZA4H06BOOCKCHKA'
@AbapCatalog.compiler.compareFilter: true
define view Za4h_06_Booking_Check_All as
select from
Za4h_06_Booking_Cchk_Params( p_Booking_Class: 'Y' )
{
key carrid,
key connid,
key fldate,
cast('ECONOMY' as abap.char( 8 )) as class,
count_Bookings,
count_Occupied_Seats,
abs(count_Bookings - count_Occupied_Seats)
as difference
}
union all
select from Za4h_06_Booking_Cchk_Params( p_Booking_Class: 'C' )
{
carrid,
connid,
fldate,
'BUSINESS' as class,
count_Bookings,
count_Occupied_Seats,
abs(count_Bookings - count_Occupied_Seats)
as difference
}
union all
select from Za4h_06_Booking_Cchk_Params( p_Booking_Class: 'F' )
{
carrid,
connid,
fldate,
'FIRST' as class,
count_Bookings,
count_Occupied_Seats,
abs(count_Bookings - count_Occupied_Seats)
as difference
}

Listing 6.11Consistency Check for the Flight Bookings of All Classes

We define a separate query for each booking class. We merge the results using UNION ALL because there will hardly be any duplicates in the merged result set. We define a class field for each result, which maps the booked class. We use a literal to define the value of this field. It’s important that each query has the same number of fields and that the corresponding fields are type-compatible in the individual queries. When merging the results, the first query always has the leading role; that is, the element names and element types as well as the annotations defined for the elements of the overall result are used from the first SELECT list. Consequently, we convert the class field of the first query to define an appropriate type for all literals.

[»]Further New Functions with AS ABAP 7.5

The following sections introduce additional code pushdown examples in CDS views, for example, the usage of the CURRENCY_CONVERSION function in Listing 12.5 from Chapter 12, Section 12.3.2. There are many more new developments to come in the future. As of AS ABAP 7.5, you can also use the following functions:

6.2.3View Extensions

The CDS specification also describes how you can extend CDS views free of modifications. These extensions enable you to include additional fields from data sources used in the view. You can also add calculated fields. You define view extensions in ABAP CDS in the same way you specify view definitions in a separate DDL source. For this purpose, the creation wizard provides the Extend View template. Listing 6.12 shows an extension of the Za4h_06_Sflights CDS view from Listing 6.1.

@AbapCatalog.sqlViewAppendName: 'ZA4H06SFLIEXT'
@EndUserText.label: 'ZA4H_06_SFLIGHTS_EXTENSION'
extend view Za4h_06_Sflights with Za4h_06_Sflights_Extension {
sflight.planetype,
replace( scarr.url, 'http:', '' ) as protocol_relative_url
}

Listing 6.12Extension of CDS View Za4h_06_Sflights

The EXTEND VIEW keyword extends the CDS view. The name of the CDS extension follows the WITH keyword. Technically, the extensions are implemented through the default extension mechanisms of the ABAP system. For each extension, an append view is generated and appended to the SQL view. The name of the append view is defined in the @AbapCatalog.sqlViewAppendName annotation.

CDS extensions are always assigned to exactly one CDS view, but you can create more than one extension for a CDS view.

Within the curly brackets, you can specify more fields that are supposed to be added to the CDS view. The name of the respective data source must precede the name of these fields to identify the fields clearly. If naming conflicts arise, you can define aliases for the individual fields. In Listing 6.12, we added two fields to the Za4h_06_Sflights CDS view: planetype and protocol_relative_url.

You can also use the fields of the data sources in expressions and calculations. The scarr.url field, for example, is used to convert the absolute URL of the airline into a relative URL (without “http:”).

You can’t use parameters, associations, or aggregation functions in CDS extensions; you can’t define new join conditions.; and you can’t extend CDS views with GROUP BY or UNION clauses. Some of the restrictions were removed in AS ABAP 7.5.

As shown in Figure 6.5, the symbol at the left side of the editor indicates whether a CDS view has been extended. Click on the symbol to view a list of the CDS extensions. The hyperlinks navigate you to the individual extensions. The Element Info displays all elements of the selected CDS views, including extensions. To call the Element Info, position the cursor on the entity name in the DDL source, and press (F2).

Extension Symbol at the Left Side of the DDL Editor

Figure 6.5Extension Symbol at the Left Side of the DDL Editor

Figure 6.6 shows information on the Za4h_06_Sflights view. The Element Info is available for all data sources and elements (including associations) that are used in the DDL source.

Element Info on an Extended CDS View

Figure 6.6Element Info on an Extended CDS View

6.2.4Annotations

In our examples of view definitions in this chapter, you already learned about four critical annotations with which you can add metadata on CDS objects: AbapCatalog.sqlViewName, AbapCatalog.sqlViewAppendName, AbapCatalog.compiler.compareFilter, and ClientDependent. In DDL sources, the @ symbol precedes these annotations. Furthermore, a value needs to be specified for the annotation if the annotation doesn’t have a default value. Depending on the type of the annotation, truth values (true or false), literals (numeric or string literals), or enumeration values can be specified.

Annotations have a specific scope in which they can be used. In AS ABAP 7.4, annotations can be specified for views (scope: View), view extensions (scope: Extend View), or elements (scope: Element). Annotations with the View or Extend View scope must always precede the DEFINE VIEW or EXTEND VIEW statement. Annotations with the Element scope precede the element.

[»]Post-Annotations

You can also write element annotations directly after the element. In this case, they are prefixed with the @< symbol to define the annotations as post-annotations. However, this format is rather uncommon. We recommend first writing the annotation and then the element.

The specification of annotations in CDS is a generic concept to enhance the metadata of a data model. In AS ABAP 7.4, annotations are used to integrate ABAP CDS objects optimally into the existing infrastructure of the ABAP AS. The annotations map the functionality, which you already know from classic DDIC views and database tables. For your CDS views, you can define texts, buffer settings, and reference fields for fields with quantities (QUAN data type) or currency amounts (CURR data type).

Table 6.9 through Table 6.11 list the annotations supported in AS ABAP 7.4.

Annotation

Valid Values

Default Values for Usage

Effect

AbapCatalog.
sqlViewName

String literal with a maximum of 16 characters

–

Specifies the SQL view name

AbapCatalog.
compiler.
compareFilter

true, false

true

Compares the annotation filters and optimization of SQL joins

ClientDependent

true, false

true

Defines a view as client-dependent

AbapCatalog.
buffering.
status

#ACTIVE, #SWITCHED_OFF, #NOT_ALLOWED

#SWITCHED_OFF

Determines whether the view is or may be buffered

AbapCatalog.
buffering.type

#SINGLE, #GENERIC, #FULL, #NONE

#NONE

Determines the buffering type

AbapCatalog.
buffering.
numberOfKeys

Integer value between 0 and the number of key attributes minus 1

0

Defines the number of key attributes for generic buffering

EndUserText.
label

String literal with a maximum of 60 characters

–

Specifies a translatable short text for the CDS view

Table 6.9View Annotations (Scope "View")

Annotation

Valid Values

Default Values for Usage

Effect

EndUserText.
label

String literal with a maximum of 60 characters

–

Specifies a translatable short text for the element

EndUserText.
quickInfo

String literal with a maximum of 100 characters

–

Specifies a translatable tool tip for the element

Semantics.
currencyCode

true, false

true

Defines the field as a currency key

Semantics.
amount.
currencyCode

Field name of a currency key

–

Defines the field as the currency field and assigns a currency key to it

Semantics.
unitOfMeasure

true, false

true

Defines the field as a unit key

Semantics.
quantity.
unitOfMeasure

Field name of a unit key

–

Defines the field as a quantity field and assigns a unit key to it

Table 6.10Element Annotations (Scope "Element")

Annotation

Valid Values

Default Value for Usage

Effect

AbapCatalog.
sqlViewAppendName

String literal with a maximum of 16 characters

–

Specifies the append view name

Table 6.11Annotations for View Extensions (Scope "Extend View")

In Listing 6.13, we used annotations to activate the CDS view buffering and assign the PRICE currency field a currency key. You can see the effect that the buffering annotations have on the SQL view in Figure 6.7. By default, in ABAP CDS, short texts for view fields are derived from the data element from the DDIC. You should use the @EndUserText.label annotation for view fields if the data element texts are supposed to be overwritten, the fields have been calculated, or the fields have been converted into integrated DDIC types.

@AbapCatalog.sqlViewName: 'ZA4H06FLIPRICE'
@EndUserText.label: 'Flight prices'
@AbapCatalog.buffering.status: #ACTIVE
@AbapCatalog.buffering.type: #FULL
define view Za4h_06_Flight_Price as
select from sflight {

key carrid,

@EndUserText.label: 'Flight Number'
key connid,

@EndUserText.quickInfo: 'Flight departure date'
key fldate,

@EndUserText.label: 'Airfare Currency'
@Semantics.currencyCode: true
currency,

@EndUserText.quickInfo: 'Airfare Economy Class'
@Semantics.amount.currencyCode: 'currency'
price
}

Listing 6.13Using Annotations in CDS Views

Effect of the Buffering Annotations on the SQL View (Transaction ST05)

Figure 6.7Effect of the Buffering Annotations on the SQL View (Transaction ST05)

[»]New Annotations as of SAP NetWeaver AS ABAP 7.5

As of AS ABAP 7.5, there are a lot of new annotations that enable you, for example, to define analytical queries, OData services, and models for SAP NetWeaver Enterprise Search.

6.2.5Using Core Data Services Views in ABAP and in SAP List Viewer with Integrated Data Access

The CDS view name represents a structured data type in the DDIC that contains the fields of the CDS view as structural components. You can use the data type to enter variables and parameters in your ABAP programs. In general, you should always use the entity name (CDS view name) instead of the SQL view name in your ABAP programs. By using the CDS view name, AS ABAP can access CDS-specific metadata and perform many additional checks to ensure the quality of your developments. This is not possible if you use the SQL view name.

CDS entities can have the following functions:

They can’t be used as data types for the definition of classic DDIC objects, such as table types.

Many existing ABAP frameworks support CDS entities. The new SAP List Viewer with Integrated Data Access (ALV with IDA) also supports CDS views. ALV with IDA enables you to process large data amounts and output the data on the UI. The ALV with IDA functions allow you to execute operations, such as sorting, grouping, paging (scrolling), aggregating, and filtering, in the database without having to load the data into internal tables on AS ABAP first.

You can use ALV with IDA via both SAP HANA and other SAP-supported databases. The static method DB_CAPABILITIES of the CL_SALV_GUI_TABLE class enables you to check which function you can use with your database. Using the statements from Listing 6.14, we have the system display the Za4h_06_Flight_Price CDS view from Listing 6.13 as an ALV list with IDA. Figure 6.8 shows the output.

ALV Output of the Flight Data from CDS View Za4h_06_Flight_Price

Figure 6.8ALV Output of the Flight Data from CDS View Za4h_06_Flight_Price

The tool tip that we defined with the @EndUserText.quickInfo annotation is displayed here when the user moves the cursor over the Airfare column.

DATA(lo_alv_display) =
cl_salv_gui_table_ida=>create_for_cds_view(
CONV #( 'ZA4H_06_FLIGHT_PRICE' ) ).

lo_alv_display->fullscreen( )->display( ).

Listing 6.14Displaying a CDS View with Parameters with ALV with IDA

ALV with IDA also supports CDS views with parameters. The IF_SALV_GUI_TABLE_IDA~SET_VIEW_PARAMETERS interface method allows you to set the values of the parameters. For more information on ALV with IDA, go to the SAP Help Portal (http://help.sap.com), and search for “ALV with IDA”.

6.2.6Tips for Using ABAP Core Data Services Views

To conclude this section on ABAP CDS, we’ll provide you with some recommendations for their usage in real-life scenarios.

View-on-View Schema

Although ABAP CDS already provides an entire gamut of code pushdown options, some application scenarios still aren’t supported. In many cases, the view-on-view schema proves to be useful. With this modeling schema, you implement subtasks in your own CDS views, which you then can use as data sources in other CDS views. For example, you can reproduce SELECT statements with subselects by implementing the subselect in your CDS view and linking this view to the data sources of the main query using INNER JOIN.

[ ! ]Moderate Modularization

The view-on-view schema enables you to modularize your data model into smaller modules. This way, you can reuse parts of the model more easily to define new queries. However, you should always follow the principle, “Modularize, don’t atomize”; that is, you should always ensure that the individual modules are semantically useful and contain sufficient logic.

Activation Log

In the case of activation errors, you should view the activation log of the CDS view. It contains important information that can help you solve the problem. You can directly navigate from the display of the DDL source to the activation log via Navigate • Open Activation Log. In the ABAP log, call the detailed log by pressing the (Ctrl) key and clicking on the hyperlink. Use the search function to find errors by activating the search toolbar via Show Search Toolbar and searching for “error”. Previously, you should ensure that all columns are displayed in the tool (Figure 6.9) by choosing View Menu • Show all Columns.

Using the ABAP Log Tool

Figure 6.9Using the ABAP Log Tool

Additional Information

You can find more information on ABAP CDS tools in the ABAP Development Tools under Help • Help Contents • SAP – Core Data Services for ABAP. For a detailed description of the ABAP CDS language features, you can call the (F1) help by positioning the cursor on a CDS keyword and pressing (F1).