Processing Data Sets 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

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:

ORDER BY

The ORDER BY clause to sort result sets is not supported in ABAP CDS.

DISTINCT

The DISTINCT addition is used to remove duplicate entries from the result set. It is supported in ABAP CDS but with the DISTINCT after keyword SELECT and not at the beginning of the field list.

Aggregations and GROUP BY

Aggregations are used to derive values from an entire data set, for example the number of records, a maximum or an average value. Aggregations are often combined with a GROUP BY clause that defines groups of records before the aggregation is performed for each group separately. Not all aggregations that are available in ABAP SQL are also supported in CDS views, but the most important aggregations, like COUNT, MIN, MAX, SUM and AVG are available. There are also minor differences in syntax. For example, it is mandatory to specify the result type when calculating an average with AVG. In ABAP SQL this is only an option.

JOIN

Joins are used to combine records from different result sets that match a join condition. The same join types are supported by ABAP SQL and ABAP CDS. Again, there are some minor syntax differences. It is mandatory to specify the data source when accessing fields and ABAP CDS uses a different field selector.

UNION

The UNION statement is used to unite the records from different result sets. When using UNION in the definition of CDS views, special rules apply for the key definition and the types, names, and annotations of view elements.

Let us have a look at an example for each of these techniques.

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
    Copy code
    Switch to dark mode
    123456789101112131415161718192021222324
    
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
  }
  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
    Copy code
    Switch to dark mode
    12
     avg( _Flight.OccupiedSeats as abap.dec(15,2)) as AverageOccupation
  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
    Copy code
    Switch to dark mode
    12345678910111213141516171819202122232425262728293031323334
    
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
  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.

Process Data Sets in CDS Views

You want to further analyze employee and department data. To do so, you use the DISTINCT addition, aggregations, grouping, and joins in a CDS view entity.

Template:

  • n.a.

Solution:

  • /LRN/C_DEPARTMENT_QRY (Data Definition)

Task 1: Use DISTINCT

Define a new CDS view entity (suggested name: Z##_C_DepartmentQuery) that returns the ID and description of those departments that have at least one employee assigned to it. As data source, use your view entity for employee data with functions but without parameters (suggested name was: Z##_C_EmployeeQuery). Make sure each department is listed only once.

Note

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

Steps

  1. In your own package, create a new data definition (suggested name: Z##_C_DEPARTMENTQUERY, where ## is your group number). Specify your CDS view entity Z##_C_EmployeeQuery 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##_C_EMPLOYEEQUERY 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_DepartmentQuery, where ## is your group number).

    5. Enter Department (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.

  2. Apply source code formatting.

    1. From the eclipse menu, choose Source Code → Format. Alternatively, choose Shift + F1.

  3. Edit the element list of the new data definition. Remove all elements except for DepartmentID and DepartmentDescription.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345678
      
define view entity Z##_C_DepartmentQuery
  as select from Z##_C_EmployeeQuery
  {  
    DepartmentId,
    DepartmentDescription   
  }

  4. Activate the data definition and display the query result in the Data Preview tool.

    1. Press Ctrl + F3 to activate the development object.

    2. Press F8 to invoke the Data Preview tool.

  5. Edit the data definition and add the DISTINCT addition to the SELECT statement.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345678
      
define view entity Z##_C_DepartmentQuery
  as select  from Z##_C_EmployeeQuery
  {  
    DepartmentId,
    DepartmentDescription   
  }

  6. Activate the data definition and display the query result again.

    1. Press Ctrl + F3 to activate the development object.

    2. Press F8 to invoke the Data Preview tool.

Task 2: Use Aggregations and Grouping

For each department, calculate the average company affiliation and the total annual salary of the employees that are assigned to this department.

Steps

  1. Remove the DISTINCT addition from your data definition.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345678
      
 define view entity Z##_C_DepartmentQuery
   as select from Z##_C_EmployeeQuery
   {  
     DepartmentId,
     DepartmentDescription   
   }

  2. At the end of the element list, add a new view element (suggested name: AverageAffiliation). Use aggregate function AVG with argument CompanyAffiliation.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345678
      
 define view entity Z##_C_DepartmentQuery
   as select from Z##_C_EmployeeQuery
   {  
     DepartmentId,
     DepartmentDescription 
   }

  3. Make sure the result has the same type as the CompanyAffiliation element of the source view entity.

    1. Place the cursor on CompanyAffiliation and press F2 to display the code element information. The type of CompanyAffiliation is abap.dec(11,1).

    2. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      123456789
      
 define view entity Z##_C_DepartmentQuery
   as select from Z##_C_EmployeeQuery
   {  
     DepartmentId,
     DepartmentDescription,
     avg( CompanyAffiliation  ) as AverageAffiliation 
   }

  4. Add a new view element (suggested name: TotalSalary). Use aggregate function SUM with argument AnnualSalaryConverted.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      123456789
      
 define view entity Z##_C_DepartmentQuery
   as select from Z##_C_EmployeeQuery
   {  
     DepartmentId,
     DepartmentDescription,
     avg( CompanyAffiliation as abap.dec(11,1) ) as AverageAffiliation 
   }

  5. Add the currency code to the element list and annotate the amount field with @Semantics.amount.currencyCode.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345678910
      
 define view entity Z##_C_DepartmentQuery
   as select from Z##_C_EmployeeQuery
   {  
     DepartmentId,
     DepartmentDescription,
     avg( CompanyAffiliation as abap.dec(11,1) ) as AverageAffiliation,
     sum( AnnualSalaryConverted ) as TotalSalary
 }

  6. Use a quick fix to generate the GROUP BY clause.

    1. Place the cursor on select and press Ctrl + 1 to invoke the quick fix. Alternatively, choose the error icon with a light bulb on the left-hand side of that code row.

    2. From the list of available quick fixes, choose Add GROUP BY clause.

    3. After applying the quick fix, the code should look as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      1234567891011
      
 define view entity Z##_C_DepartmentQuery
   as select from Z##_C_EmployeeQuery
   {  
     DepartmentId,
     DepartmentDescription,
     avg( CompanyAffiliation as abap.dec(11,1) ) as AverageAffiliation, 
     @Semantics.amount.currencyCode: 'CurrencyCodeUSD'
     sum( AnnualSalaryConverted ) as TotalSalary, 
     CurrencyCodeUSD
 }

  7. Activate the data definition and display the query result again.

    1. Press Ctrl + F3 to activate the development object.

    2. Press F8 to invoke the Data Preview tool.

Task 3: Supply Input Parameters

Replace the data source with your view that provides input parameters (suggested name was: Z##_C_EmployeeQueryP). To supply the parameters, add the same parameters to your new view and forward them to the source view.

Note

If you have not finished the previous exercise, read from CDS view entity /LRN/C_Employee_Par, instead.

Steps

  1. In the FROM clause, replace the data source and use your view entity with parameters. Use code completion to insert the full signature of the data source, that is, the CDS view entity with its parameters.

    1. In the FROM clause, replace Z##_C_EmployeeQuery with Z##_C_EmployeeQueryP. If you used /LRN/C_Employee_Fnc, replace it with /LRN/C_Employee_Par.

    2. Place the cursor immediately after the entity name and press Ctrl + Space to invoke code-completion.

    3. Press Shift + Enter to insert the view entity with full signature.

    4. Code-completion should adjust the code like this:

      Code Snippet
      Copy code
      Switch to dark mode
      123
      
 define view entity Z##_C_DepartmentQuery
   as select from Z##_C_EmployeeQueryP

  2. Define two input parameters for your view. Use the same parameter names, types, and annotations that are used in the data source.

    Hint

    It is easiest if you copy the parameter definitions from the data source.

    1. Hold down the Strg key and choose the view name after FROM to navigate into the data source.

    2. Select the code before as select, including the WITH PARAMETERS addition and press Ctrl + C to copy the parameter definition to the clipboard.

    3. Return to your data definition and insert the parameter definition before as select.

    4. The code should now look as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345
      
 define view entity Z##_C_DepartmentQuery
   as select from Z##_C_EmployeeQueryP( p_target_curr: , p_date: )

  3. Use the new parameters as input for the data source.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      123456789101112
      
 define view entity Z##_C_DepartmentQuery
   with parameters
     p_target_curr : /dmo/currency_code,
     @EndUserText.label: 'Date of evaluation'
     @Environment.systemField: #SYSTEM_DATE
     p_date        : abap.dats
   as select from Z##_C_EmployeeQueryP(  
                    p_target_curr: , 
                    p_date:  )

  4. In the element list, adjust the name of the currency code element, that is, remove the USD suffix.

    1. adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345678910111213141516
      
{
  DepartmentId,
  DepartmentDescription,
  avg( CompanyAffiliation as abap.dec(11,1) ) as AverageAffiliation,
  @Semantics.amount.currencyCode: 'CurrencyCode'
  sum( AnnualSalaryConverted )               as TotalSalary,
  CurrencyCode
}

 group by
   DepartmentId,
   DepartmentDescription,
   CurrencyCode

  5. Activate and test the data definition.

    1. Press Ctrl + F3 to activate the development object.

    2. Press F8 to invoke the Data Preview tool.

    3. Use different values for the P_TARGET_CURR parameter.

Task 4: Use an Outer Join

You want to see all departments, even those which do not have any employees assigned to them. To achieve this, use an outer join of the current data source and your view entity for department data (suggested name was: Z##_R_Department).

Note

If you have not finished the exercise on associations, use CDS view entity /LRN/C_Department_Rel, instead.

Steps

  1. In your view entity Z##_C_DepartmentQuery, adjust the FROM clause. At the end of the clause, add a right outer join of your CDS view entity for department data.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345
      
  as select 
     from Z##_C_EmployeeQueryP(
            p_target_curr: $parameters.p_target_curr,
            p_date:        $parameters.p_date )

  2. Define alias names for the two data sources (suggested names: e for employee and d for department) and add the ON condition.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      123456
      
  as select 
     from Z##_C_EmployeeQueryP(
            p_target_curr: $parameters.p_target_curr,
            p_date:        $parameters.p_date ) 
     right outer join Z##_R_Department

  3. In the element list, read the department ID and the department description from the department view entity rather than from the employee view entity and make the department ID a key element.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      123456789
      
 {
    avg( CompanyAffiliation as abap.dec(11,1) ) as AverageAffiliation,
    @Semantics.amount.currencyCode: 'CurrencyCode'
    sum( AnnualSalaryConverted )                as TotalSalary,
    CurrencyCode
  }

  4. Remove the GROUP BY clause and regenerate it using the quick fix.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12
      
 group by

  5. Add the table alias as a prefix where requested by the syntax check.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      1234567891011121314
      
{
    d.Id,
    d.Description,
    avg( e.CompanyAffiliation as abap.dec(11,1) ) as AverageAffiliation,
    @Semantics.amount.currencyCode: 'CurrencyCode'
    sum( e.AnnualSalaryConverted )                as TotalSalary,
    e.CurrencyCode
  }
group by
    d.Id,
    d.Description,
    e.CurrencyCode

  6. Activate and test the data definition.

    1. Press Ctrl + F3 to activate the development object.

    2. Press F8 to invoke the Data Preview tool.

Log in to track your progress & complete quizzes