Including Aggregations, Joins, and Unions in CDS Views

Objectives

After completing this lesson, you will be able to:

  • Describe the techniques for processing datasets in ABAP CDS
  • Use the DISTINCT addition in ABAP CDS views
  • Include aggregations and grouping in CDS views
  • Use joins and unions in CDS views

Overview

Manipulating and Combining Result Sets in ABAP CDS

ABAP SQL offers various SQL techniques to manipulate and combine data sets. Are they also supported in ABAP CDS? And if they are, what are the differences?

Here is a list of the most important SQL techniques.

Select each SQL technique to learn more.

Distinct Results in ABAP CDS

Addition DISTINCT allows you to remove duplicates from the result set of a SELECT statement. In ABAP SQL, it is placed at the beginning of the field list. In CDS view definitions, DISTINCT always comes directly after the SELECT statement. It is not part of the element list in curly brackets.

Aggregations in ABAP CDS

The example shows the most common aggregate functions and how they are used in ABAP SQL and CDS view definitions. Note that addition as abap.fltp inside the parentheses of aggregate function AVG( ) specifies the numeric type for the result. The AS addition is mandatory in ABAP CDS. In ABAP SQL it is optional and type FLTP is used by default.

If the element list of a SELECT statement contains a mixture of aggregate functions and other elements, like columns, expressions, and so on, the syntax of SQL requires a GROUP BY clause. The GROUP BY clause has to list all fields of the data source that appear in the field list outside of aggregations.

The example shows that there is no difference in the way you define the GROUP BY clause in ABAP SQL and ABAP CDS.

Hint

The editor for CDS data definitions offers a quick fix to generate the GROUP BY-clause. This can be particularly helpful, when the element list is long or contains complicated expressions. Another quick fix is offered after you made changes to the element list, allowing you to adjust the GROUP BY-clause.

Try it Out: Aggregations in CDS View Definitions

  1. Create a new CDS data definition without a referenced data object.
  2. Remove the entire SELECT statement, including the element list and the surrounding curly brackets.
  3. Copy the following code snippet and insert it after as:
    Code snippet
    
    select from /DMO/I_Connection_R
      {
        key AirlineID,
        key ConnectionID,
    
            concat_with_space(
              concat_with_space(
                concat_with_space(
                  concat(
                     concat( AirlineID,
                             ConnectionID ),
                     ': ' ),
                  DepartureAirport, 1 ),
               '->', 1 ),
            DestinationAirport, 1 ) as Description
    
            // ,
            //count( distinct _Flight.FlightDate ) as NumberOfFlights,
            //min( _Flight.FlightDate )            as FirstFlightDate,
            //sum( _Flight.OccupiedSeats )         as TotalOccupiedSeats,
            //avg( _Flight.OccupiedSeats )         as AverageOccupation
      }
    
    Expand
  4. Activate the data definition, open the Data Preview tool and analyze the output.
  5. Remove the comment markers from the aggregations in the element list. Note that the error icon for syntax error The GROUP BY clause is missing includes a light bulb.
  6. Select the error icon to invoke quick fix Add GROUP BY clause. Note, that the generated GROUP BY clause contains fields DepartureAirport and DestinationAirport because these fields are used to calculate view element Description.
  7. Note the syntax error Function AVG is only allowed with addition "as" for the last element in the element list.
  8. Remove the last element from the element list and replace it with the following code:
    Code snippet
     avg( _Flight.OccupiedSeats as abap.dec(15,2)) as AverageOccupation
    
    Expand
  9. Activate the data definition, open the Data Preview tool and analyze the output.

Joins and Unions in ABAP CDS

In SQL, a join is the combination of two data sources into one result set, consisting of columns of both data sources. The rows are determined by the join type and the join condition, also called the ON-condition.

ABAP CDS and ABAP SQL support the same join types, among them INNER JOIN, LEFT OUTER JOIN, and RIGHT OUTER join. For other supported join types, refer to the ABAP keyword documentation.

The syntax for joins is very similar in ABAP SQL and ABAP CDS, though not identical. Let's look at an example.

In ABAP CDS, the period sign (.) is used as separator between a field name and the data source name or, if an alias is defined for this data source, the data source alias name. In this ABAP SQL follows the SQL standard. In ABAP SQL, the tilde sign (~) is used for this purpose. The reason is, that in ABAP, the period sign is already used as statement delimiter.

The other syntax difference concerns the use of fields in the field list or element list. In CDS view entities, the data source name is mandatory for all fields. In ABAP SQL, the source name is only required when there are fields of the same name in different data sources. In the example, this is the case for field carrier_id because a field of that name exists in both data sources.

A UNION merges the rows of the result sets of multiple SELECT statements of CDS view entities into one result set. Without the ADD addition, ALL, full duplicates are removed after the merge.

As a prerequisite for UNION or UNION ALL, the result sets must have the same number of elements and the element pairs that occur in the same position of the result set must have a compatible data type. This prerequisite is identical in ABAP SQL and in ABAP CDS.

In addition, the following rules apply when using UNION or UNION ALL in CDS view definitions:

Element names

The direct element names or the alias names must match for each column in the element lists.

Key elements

The key elements of all element lists must match. Otherwise, a syntax check error occurs.

Element annotations

Only the element list of the first SELECT statement can define element annotations. In all subsequent branches, element annotations a forbidden.

To avoid conflicts, annotation @Metadata.ignorePropagatedAnnotations: true is mandatory when a CDS view definition contains UNION or UNION ALL.

Try it Out: Joins and Unions in CDS view definitions

  1. Create a new CDS data definition without a referenced data object.
  2. Remove the entire SELECT statement, including the element list and the surrounding curly brackets.
  3. Copy the following code snippet and insert it after as:
    Code snippet
    
    select from /dmo/customer as c
        inner join   /dmo/travel   as t
          on c.customer_id = t.customer_id
      {
        key c.customer_id                                     as id,
        key 'C'                                               as type,
            concat_with_space( c.last_name, c.first_name, 1 ) as name,
    
            @EndUserText.label: 'Number of Bookings'
            count( distinct t.travel_id )                     as TravelsCount
      }
      group by
        c.customer_id,
        c.last_name,
        c.first_name
    
    union all
    
    select from  /dmo/agency as a
      inner join /dmo/travel as t
        on a.agency_id = t.agency_id
      {
        key a.agency_id,
    //    key 'A'                          as type,
            a.name,
    
            @EndUserText.label: 'Number of Bookings'
            count( distinct t.travel_id) as TravelsCount
      }
      group by
        a.agency_id,
        a.name
    
    Expand
  4. Analyze the view definition. It contains a UNION ALL of two SELECTS. The first returns customers, each with the number of travel they booked. The second SELECT returns travel agencies, each with the number of travels that were booked at that travel agency. You cannot activate the data definition, yet, because of syntax errors.
  5. Note the syntax error Number of fields in selection lists in unions must be the same and resolve it by removing the comment markers from element type in the second SELECT.
  6. Note the syntax error Annotations are not allowed in this branch in the second SELECT and resolve it by removing the @endUserText.label. from the second SELECT.
  7. Note the syntax error Key definition of branch 2 must match key definition of branch 1 and resolve it by adding key in front of element type in the second SELECT.
    Hint
    This error message is currently only displayed on the Problems view, not on the editor view.
  8. When all syntax errors have gone, activate the data definition, open the Data Preview tool and analyze the output.

Log in to track your progress & complete quizzes