Open SQL SUM aggregate overflows, despite GROUP BY

[joltdx]

Hello, I'm happy to welcome you to my question! 🙂

The code

This Open SQL statement has suddenly started causing dumps after running fine for quite some time:

SELECT raufnr, SUM( catshours ) AS catshours
  FROM catsdb
  WHERE raufnr IN @lr_aufnr AND
        status IN ( '20', '30' )
  GROUP BY raufnr
  INTO TABLE @DATA(lt_actual_time).

This is part of a PM app. The purpose of the SQL is to get the total number of (released or approved) hours reported in CATS per work order for a set of work orders.

lr_aufnr in the above code snippet is a local range of aufnr (order number). In this case at runtime containing 1252 work orders.

The short dump

The dump is an DBSQL_SQL_ERROR, Exception CX_SY_OPEN_SQL_DB.

SQL error "SQL code: -10811" occurred while accessing table "CATSDB".

Database error text: "SQL message: Numeric overflow for parameter/column (2) source type DECIMAL, target type DECIMAL, value 'unknown'"

The theory

The field CATSHOURS in the CATSDB table is of the data element CATSHOURS, which is QUAN of length 4, 2 decimal places. (This will technically be a P(3) DEC 2 at runtime according to the debugger.)

Now, to analyse this I read the corresponding CATSDB data into Excel to investigate. The total sum of the CATSHOURS column for all the workorders (i.e. not grouped), is 10 657,64. This is indeed more than 4 digits + 2 decimals.

But there is in fact a grouping per work order in the SELECT. And per work order, this is not exceeded.

I found this in the ABAP documentation regarding aggregate functions:

• The database platform determines whether an overflow occurs if the result of an aggregate function exceeds its value range. On some database platforms, intermediate results outside the value range are allowed. The overflow behavior of SQL expressions, on the other hand, is platform-independent. An aggregate expression with an SQL expression raises an exception on every platform in the case of an overflow, even if a corresponding result of the aggregate function would not raise an exception on every platform.

Ok, NOW the theory

Does this exception occur now due to the fact that the non-grouped aggregate SUM is overflowing, even though the grouped lines will be perfectly fine? Relating to the documentation snippet above, would it really check the aggregate without the grouping before grouping?

If so, how to ABAP around this? I would prefer to not modify the data element CATSHOURS... o_O

Running different variations of the SELECT in the SQL Console in ADT also lead to exceptions for larger data sets, and will work when the SUM( catshours ) is withing 4 digits + 2 decimals.

For the time being, a workaround for the users is to limit the number of selected work orders. That way the app get its data correctly (the total non-grouped SUM is not overflowed?), but the users need to run it twice for different data sets which, of course, is not optimal...

Please advice... Are we on track with the theory?

(Of course, we only have this error in the production system so I should need to create some fake data in dev to experiment with solutions...)

We're on HDB 2.00.033.00.1535711040, SAP Kernel 749, SAP Basis 750 009

[reedtzj]

Maybe horst.keller could provide some insight here?

Or someone else with deep ABAP SELECT knowledge...

[michael_piesche · Accepted solution]

Have you tried casting it before creating the sum

SELECT raufnr, SUM( CAST( catshours as DEC( 16, 2 ) ) ) AS catshours
  FROM catsdb
  WHERE raufnr IN @lr_aufnr AND
        status IN ( '20', '30' )
  GROUP BY raufnr
  INTO TABLE @DATA(lt_actual_time)

By the way, when declaring a data type decimal, the length is the total length, and the decimals are included in that length, it is not added to the total length. (Because you wrote: <<This is indeed more than 4 digits + 2 decimals.>>)

Min/Max Value is +/-999.99 for DEC(4,2) in SAP ABAP, which becomes a packed number P(3) with 2 decimals, because: Data objects of type p can be 1 to 16 bytes long, with two places packed into each byte, and one place and the sign packed into the last byte. A packed number consists of the length multiplied by 2 minus 1 digits and can have a maximum of 14 decimal places. Packed numbers are used to implement fixed point numbers.

So, for a DEC(4,2), there are 2 bytes necessary for the places and a half byte for the sign, giving it one extra half byte. This why it translates to a packed number with 3 bytes and thats why the min/max for a DEC(4,2) is not +/-99.99 but instead +/-999.99.

Now the above is for the definition and range of a decimal / packed number in ABAP. There can be slight differences when an ABAP type definition is applied to the SQL application (e.g. CATSHOURS in Oracle and MaxDB is a Number(4,2) and in HANA it is a Decimal(4,2), so it could be that for different DB systems different rules apply.)

[joltdx · Accepted solution]

Oh, dang, it was the fundamentals where I got it wrong and then just kept on thinking... 😄 I know very well that the decimals are included in the length of DEC and QUAN and that it's not 1:1 with the P. Oh well, I got carried away...

Further testing: When using SQL Console and casting the catshours inside of the SUM aggregate I got a result, which showed me that for two of the workorders the grouped sum was actually larger than 999.99. When excluding these two individual workorders the select works also without the cast, leading me to think that my theory was off and the SUM aggregate does NOT care about the sum total before the grouping.

Thank you Michael. Spot on!

// Jörgen