Exploring ABAP on HANA [13] : Union in CDS

This post explores how Union can be defined in a CDS View Entity. Union merges the result sets of multiple SELECT statements of CDS view entities into one result set.

Note that,

• Union was available in CDS Views earlier but in CDS Entities, it is allowed only from version 7.56.
• While extending a CDS with an Union clause in it, the extension also needs to have same number of unions.

How to create CDS Entity with union?

Create a new Data Definition.

Provide name and description.

Select a TR.

Use template – Define View Entity. If you are on a lower version in 7.56, choose template for Define View.

Complete the source code.

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Union Example'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
    serviceQuality: #X,
    sizeCategory: #S,
    dataClass: #MIXED
}
define view entity zjp_cds_union
  as select from /dmo/flight
{
  key carrier_id     as CarrierId,
  key connection_id  as ConnectionId,
  key flight_date    as FlightDate,
      @Semantics.amount.currencyCode: 'CurrencyCode'
      price          as Price,
      currency_code  as CurrencyCode,
      plane_type_id  as PlaneTypeId,
      seats_max      as SeatsMax,
      seats_occupied as SeatsOccupied
}
where
  currency_code = 'USD'

union all

select from /dmo/flight
{
  key carrier_id     as CarrierId,
  key connection_id  as ConnectionId,
  key flight_date    as FlightDate,
      price          as Price,
      currency_code  as CurrencyCode,
      plane_type_id  as PlaneTypeId,
      seats_max      as SeatsMax,
      seats_occupied as SeatsOccupied
}
where
  currency_code = 'EUR'

Note that this code is only for demonstration. This query can be written as single query as well.

Data Preview for this sample looks like below.

Important Points

1. The result sets must have the same number of elements
2. They key elements of all SELECT lists must match.
3. The element pairs that occur in the same position of the result set must have a compatible data type. The names can be different. CAST keyword can be used to convert data into same data types.
4. Annotations can be defined only in the first select branch.
5. A union result set can itself be the left side of a further union.
6. If the addition ALL is specified, all entries, even duplicates, are listed in the result set. If ALL is not specified, all duplicate entries are removed from the result set. A keyword DISTINCT can also be specified explicitly.
7. If the addition ALL is specified, all data types are possible in a union view.
8. Only one field with data type LRAW/LCHR is allowed and it must be at the end of the view with a field just before such field representing the maximum length of such field. The length field would be of type INT2 or INT4.
9. Without the addition ALL, data types STRING, RAWSTRING, LCHR, LRAW, and GEOM_EWKB are not supported.

CDS View Example

Below are standard demo examples from SAP Documentation.

Selects from different sources

@AbapCatalog.sqlViewName: '...'
define view ... as
  select
    from demo_join1
      { a as c1, b as c2, c as c3, d as c4 }
    union
      select
        from demo_join2
          { d as c1, e as c2, f as c3, g as c4 }

Select list containing different elements

@AbapCatalog.sqlViewName: '...'
define view ... ( c1, c2, c3, c4 ) as
  select
    from demo_join1
      { a, b, c, d }
    union
      select
        from demo_join2
          { d, e, f, g }

Visit ABAP on HANA series for Tutorials on CDS, AMDP, Eclipse, and ALV IDA.

---

If you like the content, please subscribe…