using path in association
Learning
/Browse/Courses/Building Data Models with the ABAP Dictionary and ABAP Core Data Services/Using Associations in Path Expressions
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
{
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
{
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
{
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
* 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.
In the Project Explorer view, right-click your data definition Z##_R_EMPLOYEE to open the context menu.
From the context menu, choose New Data Definition.
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.
In the Name field, enter the name for the CDS view entity (Z##_C_EmployeeQuery, where ## is your group number).
Enter Employee (Query) in the Description field and choose Next.
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.
From the list of Templates, choose Define View Entity, then choose Finish.
Apply source code formatting.
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.
Adjust the code as follows:
Code Snippet
Copy code
Switch to dark mode
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).
Adjust the code as follows:
Code Snippet
Copy code
Switch to dark mode
define view entity Z##_C_EmployeeQuery
as select from Z##_R_Employee
{
key EmployeeId,
FirstName,
LastName,
DepartmentID,
_Department.Description as DepartmentDescription,
/* 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.
Press Ctrl + S to save the development object.
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).
Adjust the code as follows:
Code Snippet
Copy code
Switch to dark mode
define view entity Z##_C_EmployeeQuery
as select from Z##_R_Employee
{
key EmployeeId,
FirstName,
LastName,
DepartmentID,
_Department.Description as DepartmentDescription,
_Department._Assistant.LastName as AssistantName,
/* Associations */
_Department
}
Activate the data definition and display the SQL CREATE statement again.
Press Ctrl + F3 to activate the development object.
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.
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).
In the Project Explorer view, right-click the /LRN/CL_S4D430_RLT_PATH_EXP class to open the context menu.
From the context menu, choose Duplicate ....
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.
Adjust the description and choose Next.
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).
Adjust the code as follows:
Code Snippet
Copy code
Switch to dark mode
SELECT
FROM Z##_C_EmployeeQuery
FIELDS employeeid,
At the end of the field list, also read the new view elements DepartmentDescription and AssistantName.
Adjust the code as follows:
Code Snippet
Copy code
Switch to dark mode
SELECT
FROM Z##_C_EmployeeQuery
FIELDS employeeid,
firstname,
lastname,
departmentid,
departmentdescription,
assistantname
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.
Adjust the code as follows:
Code Snippet
Copy code
Switch to dark mode
SELECT
FROM Z##_C_EmployeeQuery
FIELDS employeeid,
firstname,
lastname,
departmentid,
departmentdescription,
assistantname,
\_department\_head-lastname AS headname
INTO TABLE @DATA(result).
Activate the global class and execute it as a console app.
Press Ctrl + F3 to activate the development object.
Press F9 to execute the global class as a console app.
Log in to track your progress & complete quizzes
Log in
Register
Was this lesson helpful?
Yes
No
Continue to quiz
Learning
Quick links
Learning Support
About SAP
Site Information
Using Associations in Path Expressions
More details
Learning /Browse/Courses/Building Data Models with the ABAP Dictionary and ABAP Core Data Services/Using Associations in Path Expressions 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. This join is optimized for situations where a source record is expected to have at most one related target record. 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, implying a source record can have multiple related target records. 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 design choice, referred to as Join on Demand, enhances performance by avoiding unnecessary data retrieval and join operations until the associated data is explicitly requested. 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. This restriction occurs because a "to many" association can potentially return multiple records for a single source entry, leading to ambiguous or unpredictable results if used directly in a filtering condition. 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. This happens because the system might return multiple rows for each source entity if there are multiple associated target entities, which might not be the consumer's expected behavior without explicit aggregation. 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 { 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 { 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. For example, a filter could be _Flight[ConnectionID = '0400' AND DepartureAirport = 'FRA']. In the provided example, there are two path expressions, one without a filter condition, the other with filter condition OccupiedSeats > MaximumSeats. Technically, the filter condition is crucially added to the ON-condition of the generated join on database level, which allows for early filtering of associated data and can significantly improve query performance by reducing the dataset before further processing. 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 { 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 (). This applies to each association name within a chained path expression, ensuring they are correctly identified as associations rather than part of the field name. * 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 the element selector to explicitly separate the association from the target element. 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. * Chained Associations: When a path expression contains a sequence of associations, there is no dedicated separator between association names in the same way CDS uses a period (e.g., _Department._Head). Instead, they directly follow each other, each prefixed by a backslash (e.g., \_Department\_Head). * Cardinality Specification with Filters: 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 like MANY TO ONE WHERE directly before the filter condition. There are several other options like ONE TO ONE WHERE and MANY TO MANY WHERE to explicitly define the expected cardinality of the filtered association, providing better semantic clarity and enabling the database to potentially optimize the join. See the ABAP keyword documentation for more 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 * 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. In the Project Explorer view, right-click your data definition Z##_R_EMPLOYEE to open the context menu. From the context menu, choose New Data Definition. 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. In the Name field, enter the name for the CDS view entity (Z##_C_EmployeeQuery, where ## is your group number). Enter Employee (Query) in the Description field and choose Next. 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. From the list of Templates, choose Define View Entity, then choose Finish. Apply source code formatting. 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. Adjust the code as follows: Code Snippet Copy code Switch to dark mode 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). Adjust the code as follows: Code Snippet Copy code Switch to dark mode define view entity Z##_C_EmployeeQuery as select from Z##_R_Employee { key EmployeeId, FirstName, LastName, DepartmentID, _Department.Description as DepartmentDescription, /* 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. Press Ctrl + S to save the development object. 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). Adjust the code as follows: Code Snippet Copy code Switch to dark mode define view entity Z##_C_EmployeeQuery as select from Z##_R_Employee { key EmployeeId, FirstName, LastName, DepartmentID, _Department.Description as DepartmentDescription, _Department._Assistant.LastName as AssistantName, /* Associations */ _Department } Activate the data definition and display the SQL CREATE statement again. Press Ctrl + F3 to activate the development object. 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. 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). In the Project Explorer view, right-click the /LRN/CL_S4D430_RLT_PATH_EXP class to open the context menu. From the context menu, choose Duplicate …. 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. Adjust the description and choose Next. 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). Adjust the code as follows: Code Snippet Copy code Switch to dark mode SELECT FROM Z##_C_EmployeeQuery FIELDS employeeid, At the end of the field list, also read the new view elements DepartmentDescription and AssistantName. Adjust the code as follows: Code Snippet Copy code Switch to dark mode SELECT FROM Z##_C_EmployeeQuery FIELDS employeeid, firstname, lastname, departmentid, departmentdescription, assistantname 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. Adjust the code as follows: Code Snippet Copy code Switch to dark mode SELECT FROM Z##_C_EmployeeQuery FIELDS employeeid, firstname, lastname, departmentid, departmentdescription, assistantname, \department\head-lastname AS headname INTO TABLE @DATA(result). Activate the global class and execute it as a console app. Press Ctrl + F3 to activate the development object. Press F9 to execute the global class as a console app. Log in to track your progress & complete quizzes Log in Register Was this lesson helpful? Yes No Continue to quiz Learning Quick links Learning Support About SAP Site Information Using
Uses Cases for Path Expressions in CDS and ABAP SQL
Accessing Basic Related Entity Attributes in CDS Views:
An employee view displaying the description of their department by using
_Department.Descriptionto pull data from a 'to one' association.
Retrieving Data Through Chained Associations in CDS:
An employee view showing the last name of their department's assistant using a chained path expression like
_Department._Assistant.LastName.
Ad-hoc Data Enrichment within a Defining CDS View:
A CDS view entity (
R_Employee) directly pulling related data, such as theDepmentDesignationfrom_Department, without explicitly exposing the association to its consumers.
Displaying Specific Filtered Related Information in CDS Views:
A currency view showing the currency name only in English by filtering the
_Textassociation:_Text[Language = 'E'].CurrencyName, effectively turning a 'to many' into a 'to one' result.
Aggregating Data from 'To Many' Associations in CDS:
A department view calculating the total number of occupied seats across all associated flights for that department using an aggregation like
sum(_Flight.OccupiedSeats)within aGROUP BYclause.
Implicitly Joining Tables via Path Expressions in CDS:
A path expression such as
_Department.DepmentDesignationautomatically translates into an optimizedLEFT OUTER MANY TO ONE JOINorLEFT OUTER MANY TO MANY JOINon the underlying database tables (e.g., SQL viewsR_EMPLOYEEandR_DEPARTMENT).
Optimizing Performance with "Join on Demand" in CDS:
An association
_Departmentis defined, exposed, and propagated, but the actual database join only occurs when the association is actively used in a path expression, deferring data retrieval until needed.
Retrieving Associated Data in ABAP SQL SELECT Statements:
An ABAP program querying flight connection data and including the associated airline's name using the ABAP SQL path expression
\_Airline-Name.
Chaining Associations in ABAP SQL for Multi-Level Data Retrieval:
An ABAP program getting the ISO code for an airline's local currency using a chained path expression like
\_Airline\_Currency-CurrencyISOCode.
Filtering 'To Many' Associations in ABAP SQL with Cardinality Specification:
An ABAP program querying currency short names in German, explicitly defining
MANY TO ONE WHEREfor the filtered\_Textassociation, for example:\_Airline\_Currency\_Text[ MANY TO ONE WHERE Language = 'D' ]-CurrencyShortName AS CurrencyNameDE.
Here are some interview questions and answers based on the provided notes on 'Using Associations in Path Expressions':
Interview Questions and Answers
Question: What are path expressions in CDS, and what is their primary purpose?
Answer: In CDS, a path expression is used to read data from an association target by adding a period sign.and an element name after the association name. Their primary purpose is to simplify data retrieval from related entities, automatically translating into joins on the database level.Question: Differentiate between "propagating an association" and "using an association" in a CDS view entity.
Answer: Propagating an association means adding the association name to the element list, making the association visible to the consumers of the view (e.g.,_Department). Using an association, on the other hand, involves building a path expression (e.g.,_Department.Description) to explicitly read data from the associated target entity. The key difference is that propagating only exposes the association, while using it actually retrieves data through it.Question: Explain the concept of "Join on Demand" in the context of CDS associations and its benefits.
Answer: "Join on Demand" refers to the feature where a join on the database level is only built when an association is actively used in a path expression. Defining, exposing, or propagating an association alone does not trigger a join. This design enhances performance by avoiding unnecessary data retrieval and join operations until the associated data is explicitly requested, thus optimizing resource usage.Question: How does the cardinality of an association (specifically 'to many' associations) influence path expressions in CDS, and what are the restrictions?
Answer: For 'to many' associations (where maximum cardinality is greater than 1):In the
WHEREclause of a CDS view definition, using 'to many' associations in path expressions is not allowed and results in a syntax error. This is because it can lead to ambiguous or unpredictable filtering.In the element list, 'to many' associations are allowed but display a warning. This is because they can modify the number of result sets, potentially returning multiple rows for a single source entity, which might be misleading for consumers.
Question: What are two recommended ways to correctly use 'to many' associations in CDS path expressions to avoid unexpected results or errors?
Answer:Place the path expression inside an aggregation: Using an aggregate function with a
GROUP BYclause ensures that the number of data sets remains consistent with the source entity (e.g.,sum(_Flight.OccupiedSeats)).Add a filter condition: Applying a filter condition that guarantees only one entry of the association target remains turns the 'to many' association into a 'to one' association for that specific query. You can document this change by adding
1:in front of the filter condition (e.g.,_Text[1: Language = 'D'].CurrencyName).
Question: Describe what an "ad-hoc usage of associations" is in CDS views and when the join is built in this scenario.
Answer: An "ad-hoc usage of associations" occurs when a CDS view entity defines an association and then uses it directly in a path expression within its own definition, either instead of or in addition to exposing it to consumers. In this case, the database join for the association is built immediately in the SQL view for that CDS view entity, as the data is being consumed internally within the same view.Question: How do you apply filters in CDS path expressions, and where is this filter condition technically added on the database level?
Answer: Filters in CDS path expressions are applied by placing the condition in square brackets[]immediately after the association name (e.g.,_Flight[OccupiedSeats > MaximumSeats]). Technically, this filter condition is added to theON-condition of the generated join on the database level. This allows for early filtering, improving query performance by reducing the dataset before further processing.Question: What are the main syntax differences when using path expressions in ABAP SQL compared to CDS?
Answer: The most important syntax differences are:Association Prefix: In ABAP SQL, associations must be escaped with a backslash
\(not_). This applies to each association name in a chained path.Element Selector: In ABAP SQL, a hyphen
-is used as the element selector between the association name and the element name, instead of the period.used in CDS.Chained Associations: In ABAP SQL, chained associations follow each other directly, each prefixed by a backslash (e.g.,
\_Airline\_Currencyfor_Airline._Currencyin CDS).Cardinality Specification: When filtering, ABAP SQL uses keywords like
MANY TO ONE WHERE(orONE TO ONE WHERE,MANY TO MANY WHERE) directly before the filter condition, instead of1:.
Question: Provide an example of a chained path expression in ABAP SQL and explain what it retrieves.
Answer: An example is\_Airline\_Currency-CurrencyISOCode. This path expression starts from a source entity that has an association_Airline. The target of_Airlinethen exposes another association_Currency. Finally,-CurrencyISOCodeaccesses theCurrencyISOCodefield of the_Currencytarget. This retrieves the ISO code for the local currency of the airline associated with the flight connection.Question: In ABAP SQL, if you filter a 'to many' association to effectively make it 'to one', how do you express this cardinality explicitly in the path expression filter, and why is this important?
Answer: In ABAP SQL, you use keywords likeMANY TO ONE WHEREdirectly before the filter condition, for example:\_Airline\_Currency\_Text[ MANY TO ONE WHERE Language = 'D' ]-CurrencyShortName. This is important for semantic clarity and can enable the database to potentially optimize the join. It explicitly documents the expected cardinality of the filtered association, even though the syntax check in ABAP SQL (like CDS without1:) typically cannot infer this reduction in cardinality on its own.