ABAP CDS: Sum=> value out of range

[kammaje_cis]

I have written simple CDS view with below code.

@AbapCatalog.sqlViewName: 'ZA_HOURS'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@ClientDependent: true
@EndUserText.label: 'Employee hours reported'
define view Z_Hours
as select from catsdb 
 {
  key pernr,
  sum(catshours) as totalHours
} 
group by pernr

I get below error in ST22.

Database error text: "SQL message: numeric value out of range: not enough space for packed decimal at function __copy_trex_field_Fixed8_check__() (at pos 54) "

I think the problem is with totaling and value exceeding the allowed range. Any help please?

[ceedee666]

Hi,

I guess the problem is that the sum expressions uses the data type of catshours as a return value. This is a P 3. Therefore you get the value out of range when calculating the sum.

Try to cast the casthours to an other data type (e.g.abap.dec(len,decimals))using the CAST operator (https://help.sap.com/abapdocu_750/en/abencds_f1_cast_expression.htm) before calculating the sum. That way you should be able to get rid of the exception .

Christian

[kammaje_cis]

Can you please suggest the syntax for it? Below one gives a syntax error.

sum(cast(catshours) as abap.dec(15, 2)) as totalHours

[MattHarding]

Only way I could do this was to insert another CDS to do the conversion (in my case, I was using a 0..* with CATSDB, so I simply created a CDS view named something like ZI_TimeEntryForAgg that cast catshours)