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

  1. Create a new CDS data definition with /DMO/I_Connection_R as referenced data object.
  2. Remove the element list, including the surrounding curly brackets.
  3. Copy the following code snippet and insert it after as select from /DMO/I_Connection_R:
    Code snippet
    
    {
    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'
    
    
    Expand
  4. 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.
  5. 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.
  6. Activate the data definition despite the warning and display the data preview. Now you see several data sets.
  7. Comment out the additional conditions in the WHERE clause. Notice the syntax error for the path expression that includes association _Flight.
  8. Remove the element list and WHERE-clause and replace it with the following syntax:
    Code snippet
    
    {
        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
    
    Expand
  9. Notice the syntax warning for the path expression including association _Flight. Activate and display the data preview.
  10. 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

  1. Create a new CDS data definition with /DMO/I_Connection_R as referenced data object.
  2. Remove the element list, including the surrounding curly brackets.
  3. Copy the following code snippet and insert it after as select from /DMO/I_Connection_R:
    Code snippet
    
    {
        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'
    
    Expand
  4. Remove the comment markers from the first path expression and analyze the syntax warnings (if any).
  5. Activate the data definition and display the data preview and the SQL create statement for this version of the view.
  6. Comment out the first path expression and remove the comment markers from the second path expression.
  7. Activate and analyze the syntax warnings, the data preview and the SQL create statement, for the path expression that includes a filter.
  8. 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

  1. Create a new global class that implements interface IF_OO_ADT_CLASSRUN.
  2. Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
    Code snippet
    
    * 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').
    
    Expand

    This coding contains an ABAP SQL SELECT statement with various path expressions.

  3. 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.
  4. Remove the syntactically wrong variants, activate the class, execute it as console app, and analyze the console output.
  5. Repeat this with the second and third block of path expressions.

Log in to track your progress & complete quizzes