Skip to Content
0
Oct 27, 2022 at 04:50 PM

Substring and Group by in CDS-View

2241 Views

Hey there!

I wanted to create a CDS-View where the Flight price of /DMO/BOOKING is grouped by Airline and year and month of flight date.

I tried this but it does not work because the group by clause only referrs to FLIGHT_DATE and not the created fields:

@AbapCatalog.sqlViewName: 'ZDOJOTOTALFLP'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Dojo total flight price by airline'
define view zdojo_total_flp as select from /dmo/booking  {
    key carrier_id as CarrierId,
    key substring(flight_date, 1, 4) as FlightYear,
    key substring(flight_date, 5, 2) as FlightMonth,
    sum(flight_price) as TotalFlightPrice
}
group by
   flight_date, carrier_id

GROUP BY only accepts origin Fields of the data source but not the created fields FlightYear and FlightMonth.

Is this requirement possible in CDS-Views?

Thanks

Enno