Tell us what you think

We want to create the best possible experience for our learners. Your feedback helps us to improve the experience for everyone.

Using Associations in Path Expressions

Objectives

After completing this lesson, you will be able to:

Use path expressions in CDS views.
Use filter conditions in path expressions.
Use path expressions in ABAP SQL.

Path Expressions in CDS

When a CDS view entity reads from an entity with exposed associations, there are two possibilities:

Propagating the association: By adding the association name to the element list, the association becomes also visible to the consumers of this view. In the example, association _Department is defined and exposed in view entity R_Employee. View entity C_Employee reads from R_Employee and propagates association _Department.
Using the association: By adding a period sign (.) and an element name after the association name, the association is used to read data from the association target. This kind of use is called a Path Expression. In the example, the target of association _Department is CDS view entity C_Department. CDS view entity C_Employee uses a path expression to read field DepmentDesignation from the association target and adds it to its own element list.

On database level, a path expression gets translated into a join of the association source and the association target. This becomes visible in the Show SQL Create Statement function of the editor.

In the example, path expression _Department.DepmentDesignation leads to a join of SQL views R_EMPLOYEE and R_DEPARTMENT on the database. The ON-condition for the join is derived from the ON-condition of the association.

Note

The join type LEFT OUTER MANY TO ONE JOIN is a specialty of the SAP HANA database. There is also a join type LEFT OUTER MANY TO MANY JOIN that is used when the association cardinality has a maximum higher than 1.

It is important to emphasize that the join on database level is only built when the association is used in a path expression. Defining, exposing, and propagating an association does not lead to a join. This feature of associations is sometimes referred to as Join on Demand.

Let us look at an example again: even though CDS view entity R_Employee defines and exposes association _Department and CDS view entity C_Employee propagates this association, the corresponding SQL view C_EMPLOYEE reads only from SQL view R_EMPLOYEE.

Up till now we discussed path expressions with one association, only. But you can also built longer path expressions by chaining several associations.

Here is an example: CDS view entity R_Employee exposes association _Department, which has CDS view entity R_Department as target. This target exposes another association _Head, pointing back to R_Employee but reading the employee that is head of the respective department. Path expression _Department._Head.LastName reads the last name of the head of the department to which an employee is assigned. The long and complicated join of three data sources is generated automatically.

Most of the time, when a CDS view entity defines an association, it exposes it to its consumers. However, it can also use the association, directly, either instead of or in addition to exposing it.

We call this an ad-hoc usage of associations. In the example, CDS view entity R_Employee defines association _Department. Instead of exposing the association, it uses it directly in a path expression to retrieve the designation of the related department.

In the case of an ad-hoc usage, the join is built immediately in the SQL view for this CDS view entity.

How to Write Path Expressions in CDS

Play the video to see how to write path expressions in CDS.

The Influence of the Cardinality on Path Expressions

When you use an association in a path expression, the cardinality can have an influence on the syntax check. The editor displays warnings and even errors for the wrong use of "to many" associations, that is, associations for which the cardinality has a maximum larger than 1.

In path expressions in the WHERE-clause of the view definition, the use of "to many" associations is not allowed at all, and leads to a syntax error.

In the element list, "to many" associations are allowed, but the editor displays a warning, indicating that this path expression can modify the number of result sets.

What does this warning mean? Imagine your tables contain only 1 department with 5 employees. Without the path expression, CDS view C_Department returns exactly 1 data set. Then you add the path expression with the "to many" association _Employee to the element list. After that, the same view returns 5 data sets, one for each employee. This can be misleading for the consumers of view C_Department because they expect one data set for each department.

Hint

If you are interested in a combination of employee data and department data, you should start from view R_Employee and use the "to one" association _Department to add the department details.

One way to use a "to many" association correctly, is to place the path expression inside an aggregation. The GROUP-BY-clause, which is mandatory for aggregations, ensures that the number of data sets stays the same. In the example, CDS view entity C_Department still returns one data set per department and not one data set per employee.

Note

Another way to use a "to many" association correctly is the addition of a filter condition. We will discuss filters in the next section.

Try It Out: Wrong and Correct Use of 'to many' associations

Create a new CDS data definition with /DMO/I_Connection_R as referenced data object.
Remove the element list, including the surrounding curly brackets.
Copy the following code snippet and insert it after as select from /DMO/I_Connection_R:
Code Snippet
Copy code
Switch to dark mode
```
123456789101112131415161718192021

{
key AirlineID,
key ConnectionID,

// _Airline.CurrencyCode,
// _Flight.PlaneType,

DepartureAirport,
DestinationAirport

}
where
AirlineID = 'LH'          // Only one connection
and ConnectionID = '0400' // fulfills this filter


// and _Airline.CurrencyCode = 'EUR'
// and _Flight.PlaneType = '747-400'
```
Activate the data definition and display the data preview. Because the WHERE clause fully qualifies the primary key of flight connections you only see one data set.
Remove the comment markers from the two path expressions in the element list. Notice the syntax warning because _Flight has cardinality 0..* and therefore is a "to many" association.
Activate the data definition despite the warning and display the data preview. Now you see several data sets.
Comment out the additional conditions in the WHERE clause. Notice the syntax error for the path expression that includes association _Flight.
Remove the element list and WHERE-clause and replace it with the following syntax:
Code Snippet
Copy code
Switch to dark mode
```
1234567891011121314151617

{
    key AirlineID,
    key ConnectionID,

        _Flight.OccupiedSeats
        //        sum(_Flight.OccupiedSeats) as TotalOccupiedSeats

  }
where
      AirlineID    = 'LH'   // Only one connection
  and ConnectionID = '0400' // fulfills this condition

//  group by
//    AirlineID,
//    ConnectionID
```
Notice the syntax warning for the path expression including association _Flight. Activate and display the data preview.
Comment out the path expression and remove the coment markers from the aggregate function sum( ) and the GROUP BY clause. Notice that the syntax warning has gone and that data preview displays only one data set.

Filters in Path Expressions

When you use an association in a path expression, you can add filters to restrict the data sets of the association target. The filter condition is placed in a pair of square brackets ([ ]) immediately after the association name. Like in a WHERE-clause, you can use relational operators and boolean operators AND, OR, NOT. The left-hand side of a condition has to be a field of the association target. For the right-hand side, there is more flexibility. Among other things, you can use fields of the association target, literals, built-in functions, and so on.

In the example, there are two path expressions, one without a filter condition, the other with filter condition OccupiedSeats > MaximumSeats. While the first path expression searches for the earliest flight associated to a flight connection, the second path expression restricts the search to overbooked flights, that is, flights where the number of occupied seats exceeds the number of available seats.

Technically, the filter condition is added to the ON-condition of the generated join on database level.

Let us have a look at the SQL create statement for our example: The first join in our example belongs to the path expression without the filter. The ON-condition compares AIRLINEID, CONNECTIONID, and the client field MANDT. The second join belongs to the path expression with the filter. Can you spot the additional condition from the filtered path expression?

When you filter an association that has a cardinality with max > 1, it can happen that after applying the filter only one entry of the association target remains. The filter turns the "to many" association into a "to one" association. In such a situation, you should add "1: " in front of the filter condition to document the changed cardinality of the association.

In this example, CDS view entity I_Currency defines and exposes association _Text, which is a "to many" association, returning currency names in different languages. Without a filter, the association would read the name of a currency in all languages that are maintained in the system. The two path expressions with a filter, however, read only one currency name each, either in English or in German.

As we saw earlier, the syntax check issues warnings and errors when we use a "to many" association in a path expression that lies outside of an aggregation. But what happens if a filter makes sure that there is only one record? Does the syntax check still issue the warnings and errors? Yes, it does, because it can not know that the filter turns the "to many" association into a "to one" association. We can document this by adding "1:" inside the square brackets, before the filter condition. In the example, there is a syntax warning for the path expression with filter Language = 'E'. But there is no syntax warning for the path expression with filter 1: Language = 'D'.

Try it Out: Filtered Associations

Create a new CDS data definition with /DMO/I_Connection_R as referenced data object.
Remove the element list, including the surrounding curly brackets.
Copy the following code snippet and insert it after as select from /DMO/I_Connection_R:
Code Snippet
Copy code
Switch to dark mode
```
123456789101112131415

{
    key AirlineID,
    key ConnectionID,

        //_Airline._Currency._Text.CurrencyName

        //_Airline._Currency._Text[ Language = 'E' ].CurrencyName

        //_Airline._Currency._Text[ 1: language = 'E' ].CurrencyName
  }
where
      AirlineID    = 'AA'
  and ConnectionID = '0017'
```
Remove the comment markers from the first path expression and analyze the syntax warnings (if any).
Activate the data definition and display the data preview and the SQL create statement for this version of the view.
Comment out the first path expression and remove the comment markers from the second path expression.
Activate and analyze the syntax warnings, the data preview and the SQL create statement, for the path expression that includes a filter.
Repeat with the third path expression.

Path Expressions in ABAP SQL

Another way to use exposed associations are path expressions in ABAP SQL. The concept is exactly the same as in CDS but the syntax is slightly different.

The most important syntax differences are as follows:

Association Prefix: In ABAP SQL path expressions, associations have to be escaped with a backslash (\).
Element Selector: In CDS path expressions, a period sign (.) is used as separator between the association name and the element name. In ABAP SQL, the period sign is not suitable because it would end the statement. Therefore, in ABAP SQL, a hyphen is used as element selector.

Let us look at an example: This ABAP SQL SELECT statement reads from CDS view entity /DMO/I_Connection_R which exposes an association _Airline. The last expression in the FIELDS list is a path expression to read element Name from the association target /DMO/I_Carrier.

Another syntax difference is related to chained associations. When a path expression contains a sequence of associations, there is no dedicated separator. Where in CDS the associations are separated by a period sign (.), they directly follow each other in ABAP SQL. However, note that each association name needs a backslash (\) as a prefix.

In this example, the target of association _Airline exposes an association _Currency that returns details for the local currency of the airline. Expression \_Airline\_Currency-CurrencyISOCode reads the ISO-Code for the local currency of the airline that operates the flight connection.

Note

In CDS, the same path expression reads _Airline._Currency.CurrencyISOCode.

In ABAP SQL, filter conditions are added in square brackets, just like in CDS. However, to specify a cardinality, an entirely different syntax is used. Instead of "1:", you have to add keywords MANY TO ONE WHERE. There are several other options like ONE TO ONE WHERE and MANY TO MANY WHERE. See the ABAP keyword documentation for details.

Note

There is an alternative syntax where you write [ (1) WHERE … ] instead of [ MANY TO ONE WHERE … ], but this alternative syntax is not recommended.

Try It Out: Path Expressions in ABAP SQL

Create a new global class that implements interface IF_OO_ADT_CLASSRUN.
Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
Code Snippet
Copy code
Switch to dark mode
```
1234567891011121314151617181920212223242526272829303132333435

* Data selection
**********************************************************************

    SELECT FROM /dmo/i_connection_R
         FIELDS Airlineid,
                connectionid,

*                 _Airline.Name,
*                \_Airline-Name,
*                 _Airline-Name,
*                 _Airline~Name,
*                \_Airline~Name,
*
*                \_Airline-_Currency-CurrencyISOCode,
*                \_Airline-\_Currency-CurrencyISOCode,
*                \_Airline\_Currency-CurrencyISOCode,
*                \_Airline~\_Currency~CurrencyISOCode,
*                \_Airline\_Currency~CurrencyISOCode,
*
*                \_Airline\_Currency\_Text[                     Language = 'E' ]-CurrencyShortName AS CurrencyNameEN,
*                \_Airline\_Currency\_Text[   MANY TO ONE WHERE Language = 'D' ]-CurrencyShortName AS CurrencyNameDE,
*                \_Airline\_Currency\_Text[                     Language = 'E' ]-CurrencyShortName AS CurrencyNameEN,
*                \_Airline-\_Currency-\_Text[ MANY TO ONE WHERE Language = 'D' ]-CurrencyShortName AS CurrencyNameDE,
*                \_Airline~\_Currency~\_Text[ 1:                Language = 'E' ]-CurrencyShortName AS CurrencyNameEN, 
                DepartureAirport,
                DestinationAirport
                
         INTO TABLE @DATA(result).

* Output
**********************************************************************
    out->write(  data = result
                 name = 'Selection result').
```
This coding contains an ABAP SQL SELECT statement with various path expressions.
Remove the comment from the first block of path expressions and use the syntax check to find the one path expression that is syntactically correct in ABAP SQL.
Remove the syntactically wrong variants, activate the class, execute it as console app, and analyze the console output.
Repeat this with the second and third block of path expressions.

Use Associations in Path Expressions

You defined CDS view entities with exposed associations. You want to make use of the exposed associations. First you do so in a CDS view, then in a SELECT statement in ABAP SQL.

Template:

/LRN/CL_S4D430_RLT_PATH_EXP (Global Class)

Solution:

/LRN/C_EMPLOYEE_QRY (Data Definition)
/LRN/CL_S4D430_RLS_PATH_EXP (Global Class)

Task 1: Use Exposed Associations in CDS

Define a new CDS view entity (suggested name: Z##_C_EmployeeQuery). As data source, use your view entity for employee data which exposes an association (suggested name was: Z##_R_Employee). In the element list, read the employee ID and name. Then, add path expressions to read the department description and the department assistant's last name.

Note

If you have not finished the previous exercise, you can let your new view entity read from the /LRN/R_Employee_Rel view entity.

Steps

In your own package, create a new data definition (suggested name: Z##_C_EMPLOYEEQUERY, where ## is your group number). Specify your CDS view entity Z##_R_Employee as Referenced Object and choose the Define View Entity template to generate the definition statement, some standard annotations and the element list.
1. In the Project Explorer view, right-click your data definition Z##_R_EMPLOYEE to open the context menu.
2. From the context menu, choose New Data Definition.
3. Confirm that the Package field contains the name of your package and that the Referenced Object field contains the name of your database table definition.
4. In the Name field, enter the name for the CDS view entity (Z##_C_EmployeeQuery, where ## is your group number).
5. Enter Employee (Query) in the Description field and choose Next.
6. Confirm the transport request and choose Next.
  Caution
  Make sure you don't choose Finish yet. If you do, you are not able to choose the template that you want to use.
7. From the list of Templates, choose Define View Entity, then choose Finish.
Apply source code formatting.
1. From the eclipse menu, choose Source Code → Format. Alternatively, choose Shift + F1.
Edit the element list of the new data definition. Remove all elements except for EmployeeID, FirstName, LastName, DepartmentID, and the exposed association _Department.
1. Adjust the code as follows:
  Code Snippet
  Copy code
  Switch to dark mode
```
1234567891011121314

define view entity Z##_C_EmployeeQuery
  as select from Z##_R_Employee
  {
    key EmployeeId,
        FirstName,
        LastName,
        DepartmentID, 
 
        /* Associations */
        _Department
       
  }
```
After the DepartmentID element, add a path expression to read the description of the department, to which the employee is assigned (suggested element name: DepartmentDescription).
1. Adjust the code as follows:
  Code Snippet
  Copy code
  Switch to dark mode
```
1234567891011121314

define view entity Z##_C_EmployeeQuery
  as select from Z##_R_Employee
  {
    key EmployeeId,
        FirstName,
        LastName,
        DepartmentID, 
  
        /* Associations */
        _Department
       
  }
```
Save the data definition and display the SQL CREATE statement.
Note
If you don't save the data definition, your changes will not appear in the SQL CREATE statement.
1. Press Ctrl + S to save the development object.
2. Right-click anywhere in the source code of the data definition and choose Show SQL CREATE Statement.
Add a path expression to read the last name of the department assistant (suggested element name: AssistantName).
1. Adjust the code as follows:
  Code Snippet
  Copy code
  Switch to dark mode
```
123456789101112131415

define view entity Z##_C_EmployeeQuery
  as select from Z##_R_Employee
  {
    key EmployeeId,
        FirstName,
        LastName,
        DepartmentID, 

        _Department.Description as DepartmentDescription, 
        /* Associations */
        _Department
       
  }
```
Activate the data definition and display the SQL CREATE statement again.
1. Press Ctrl + F3 to activate the development object.
2. Right-click anywhere in the source code of the data definition and choose Show SQL CREATE Statement.
Display the query result in the Data Preview tool.
1. Place the cursor anywhere in the data definition and press F8 to invoke the Data Preview tool.

Task 2: Use Exposed Associations in ABAP SQL

Create a copy of ABAP class /LRN/CL_S4D430_RLT_PATH_EXPR (suggested name: ZCL_##_PATH_EXPR) and adjust the SELECT statement. Read all fields from the CDS view entity which you just created (suggested name was: Z##_C_EmployeeQuery). In addition, implement a path expression in ABAP SQL to read the department head's name.

Steps

Copy the /LRN/CL_S4D430_RLT_PATH_EXPR class to a class in your own package (suggested name: ZCL_##_PATH_EXPR, where ## is for your group number).
1. In the Project Explorer view, right-click the /LRN/CL_S4D430_RLT_PATH_EXP class to open the context menu.
2. From the context menu, choose Duplicate ....
3. Enter the name of your package in the Package field. In the Name field, enter the name ZCL_##_PATH_EXP, where ## is your group number.
4. Adjust the description and choose Next.
5. Confirm the transport request and choose Finish.
In the IF_OO_ADT_CLASSRUN~MAIN method, change the SELECT statement. Replace /LRN/C_Employee_Ann in the FROM clause with the name of the CDS view entity that you have just created (Z##_C_EmployeeQuery).
1. Adjust the code as follows:
  Code Snippet
  Copy code
  Switch to dark mode
```
12345

    SELECT
    FIELDS employeeid,
```
At the end of the field list, also read the new view elements DepartmentDescription and AssistantName.
1. Adjust the code as follows:
  Code Snippet
  Copy code
  Switch to dark mode
```
123456789

    SELECT
      FROM Z##_C_EmployeeQuery
    FIELDS employeeid,
           firstname,
           lastname,
           departmentid,
    INTO TABLE @DATA(result).
```
After the department assistant's name, implement a path expression that reads the last name of the department head (suggested field name: HEADNAME).
Hint
Use code-completion where possible. Remember that in ABAP SQL, association names require a backslash sign (\) as a prefix and that a dash sign (-) is used between the association name and the element name.
1. Adjust the code as follows:
  Code Snippet
  Copy code
  Switch to dark mode
```
1234567891011

    SELECT
      FROM Z##_C_EmployeeQuery
    FIELDS employeeid,
           firstname,
           lastname,
           departmentid,
           departmentdescription,
           assistantname
    INTO TABLE @DATA(result).
```
Activate the global class and execute it as a console app.
1. Press Ctrl + F3 to activate the development object.
2. Press F9 to execute the global class as a console app.

Continue to quiz