ODATA-Service and Database Query return different values

[tobias_grimm_ksg]

I use an OData V2 Service generated automatically by an Analytical CDS Query View.
The OData-Service returns different values than a query on the corresponding database view.

My data model looks like this: 

View 5 selects a CalendarDate and a Measure from some SAP Standard View. The dataset contains a Date and a Measure for each CalendarDate. View B groups the Measures by Month and Year and useses the AVG() Function to calculate the average of the measure per month. View 4 selects from View 5 and uses a UNION to combine these rows with the average from View B. But I only select the first day of each month in that UNION. For example, this means, that there now will be exactly 2 rows with the date 01.01.2024 and exactly one row for all other days of January 2024. This would cause NULL values in the UNION result, so I use "0" as a dummy value or all fields, that would result in NULL. All the other analytical views do not change the data.

View 4:

Spoiler

@AbapCatalog.sqlViewName: 'VIEW4'
@ClientHandling.type: #CLIENT_DEPENDENT
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AbapCatalog.dataMaintenance: #DISPLAY_ONLY
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.viewType: #COMPOSITE

define view VIEW4
with parameters
FiscalYearFrom : fins_fyearperiod,
FiscalYearTo : fins_fyearperiod,
PlanningCategory : fcom_category,
CurrencyRole : fac_crcyrole,
CalendarCode : smi_countrycode_fctryclndr,
Plant : werks_d
as select from VIEW5
(FiscalYearFrom : $parameters.FiscalYearFrom, FiscalYearTo : $parameters.FiscalYearTo,
PlanningCategory : $parameters.PlanningCategory, CurrencyRole : $parameters.CurrencyRole, Plant : $parameters.Plant)
{
  DateField,
  @Semantics.amount.currencyCode: 'MyMeasureCurrency'
   MyMeasure,
   MyMeasureCurrency,
  @Semantics.amount.currencyCode: 'MyMeasureCurrency'
  cast(0 as abap.curr(23, 2)) as AverageMeasureInMonth
  //...
}
union select from VIEWB
(FiscalYearFrom : $parameters.FiscalYearFrom, FiscalYearTo : $parameters.FiscalYearTo,
PlanningCategory : $parameters.PlanningCategory, CurrencyRole : $parameters.CurrencyRole, Plant : $parameters.Plant)
as AverageInMonth

join I_CalendarDate as CalendarDateAVGMonth
on CalendarDateAVGMonth.CalendarMonth = AverageInMonth.CalendarMonth
and CalendarDateAVGMonth.CalendarYear = AverageInMonth.CalendarYear
{
  CalendarDateAVGMonth.CalendarDate as DateField,
  @Semantics.amount.currencyCode: 'MyMeasureCurrency'
  cast(0 as abap.curr(23, 2)) as MyMeasure,
  cast('EUR' as waers) as MyMeasureCurrency,
  @Semantics.amount.currencyCode: 'MyMeasureCurrency'
  AverageInMonth.AverageMeasureInMonth as AverageMeasureInMonth
  //...
}
where CalendarDateAVGMonth.CalendarDay = '01'

View 1:

Spoiler

@AbapCatalog.sqlViewName: 'VIEW1'
@ClientHandling.type: #CLIENT_DEPENDENT
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AbapCatalog.dataMaintenance: #DISPLAY_ONLY
@AccessControl.authorizationCheck: #NOT_REQUIRED
@Analytics.query: true
@OData.publish: true
@VDM: {viewType: #CONSUMPTION }

define view VIEW1 
with parameters
 //...
as select from VIEW2
(FiscalYearFrom : $parameters.FiscalYearFrom, FiscalYearTo : $parameters.FiscalYearTo, PlanningCategory : $parameters.PlanningCategory,
 CurrencyRole : 'EUR', CalendarCode : $parameters.CalendarCode, Plant : $parameters.Plant) as Query
{
  //...
}

The expected result looks like this when I filter the dataset by the date and only select January 2024:

Date	MyMeasure	AverageMeasureInMonth
01.01.2024	81.10	0.00 (Dummy Value by UNION)
01.01.2024	0.00 (Dummy Value by UNION)	67.50
02.01.2024	50.00	0.00 (Dummy Value by UNION)
03.01.2024	75.50	0.00 (Dummy Value by UNION)
...	...	...

One row per day, but 2 rows for the first of january wich contains the average value.

Now, the problem is, when I query my analytical View (View 1) in ADT SQL Console or DBACOCKPIT in SAP, I receive the expected results. It does not matter wether I query the actual CDS View or the Database View:

select DateField, MyMeasure, AverageMeasureInMonth from VIEW1( fiscalyearfrom = '2023005', fiscalyearto = '2024001', planningcategory = 'PLANORD02', calendarcode = 'Z5', plant = '1010' )
where DateField like '202401%'
order by DateField

 Correct result is returned: 

Now, when I send a GET request to my OData Service that is generated by the View1, it returns different values. AverageMeasureInMonth is "0" wich represents a NULL value (Type is edm.decimal)

This is the GET request on my service:

https://mySAPSystem/sap/opu/odata/sap/VIEW1_CDS/View1(FiscalYearFrom=%272023005%27,FiscalYearTo=%272024002%27,PlanningCategory=%27PLANORD02%27,CalendarCode=%27Z5%27,Plant=%271010%27)/Results?sap-client=200&$select=DateField_T,MyMeasure,AverageMeasureInMonth&$filter=(((CalendarYear%20eq%20%272024%27)%20and%20(CalendarMonth%20eq%20%2701%27)))

 I shortened the result to only the row that should contain the average value.
But instead, it returns "0" (not 0.0) NULL and not the correct value "67.50".
The MyMeasure field correctly returns "0.0".

//... 
<content type="application/xml">
 <m:properties 
    xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" 
    xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices">
       <d:DateField>20240101</d:DateField>
       <d:MyMeasure>0.0</d:MyMeasure>
       <d:AverageMeasureInMonth>0</d:AveragePlanCostInMonth>
  </m:properties>
</content>
//...

 I do not understand how it is possible that the OData Service returns different results than a database query on the underlying view of that service. Selection Parameters are the same, Filters are the same and the request and query are run on the same client.