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.
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.