WRBTR field calculation inside CASE throws error for max decimal places

Question

I have following select:

SELECT FROM bseg
LEFT JOIN aufk ON ( ltrim( aufk~aufnr, '0' ) = bseg~zuonr )
JOIN bkpf ON bkpf~belnr = bseg~belnr AND bkpf~gjahr = bseg~gjahr AND bkpf~bukrs = bseg~bukrs
FIELDS bseg~bukrs, bseg~bschl, bseg~wrbtr, bseg~h_hwaer
INTO TABLE @DATA(output).

When the select is complete I loop over the output table making a calculation when bschl = '50'.

LOOP AT output ASSIGNING FIELD-SYMBOL(<output>) WHERE bschl = '50'.
  <output>-wrbtr = <output>-wrbtr * ( -1 ).
ENDLOOP.

Since ABAP 7.4 I could use CASE statements in the SQL select. This is what I want to use to get rid of the loop.

SELECT FROM bseg
LEFT JOIN aufk ON ( ltrim( aufk~aufnr, '0' ) = bseg~zuonr )
JOIN bkpf ON bkpf~belnr = bseg~belnr AND bkpf~gjahr = bseg~gjahr AND bkpf~bukrs = bseg~bukrs
FIELDS bseg~bukrs,
CASE
  WHEN bseg~bschl = '50' THEN  bseg~wrbtr * ( -1 )
  ELSE bseg~wrbtr
  END AS bseg~wrbtr, bseg~h_hwaer
INTO TABLE @DATA(output).

This is on how I would deal with the requirements described above.

Unfortunately I get an error message:

The maximum possible number of places in the expression starting with WRBTR is 34 places with 2 decimal places.
There can be, however, no more than 31 places and 14 decimal places.`

I also tried to cast bseg~wrbtr:

WHEN bseg~bschl = '50' THEN  CAST( bseg~wrbtr * ( -1 ) )

-> ")" is invalid here (due to grammar).

Or

WHEN bseg~bschl = '50' THEN  CAST( bseg~wrbtr AS test ) * ( -1 )

-> "TEST" is invalid here (due to grammar).

Does someone know how to deal with this?

Post the solution as an answer below, not editing the original question. — József Szikszai, Dec 23 '22 at 12:42

Sandra Rossi · Accepted Answer · 2022-12-30T11:57:46.810

My answer is specific to setting a sign via * -1. It doesn't apply to multiplications with other numbers.

In ABAP 7.52 and S/4HANA 1709, BSEG-WRBTR is still a packed-7-bytes number including 2 decimals, and except END AS bseg~wrbtr which leads to the error "~" is invalid here (due to grammar) and must be replaced with END AS wrbtr, the syntax is valid in my system.

In my system, the inline declaration of the output table chooses a packed-13-bytes number including 2 decimals. It's the multiplication that makes the number of digits in the output table multiplied by 2 (from 7 bytes to 13 bytes). As a comparison, an addition would only declare a packed-8-bytes number.

I guess you have a more recent S/4HANA version with BSEG-WRBTR having many more digits (feature called "Amount Field Length Extension"), it's why the multiplication makes the inline declaration produces an invalid type with too many digits.

Workaround: if you sign without multiplying, it will keep the same number of digits (packed-7-bytes number in my case), and this syntax should also work in your case:

CASE bseg~bschl
  WHEN '50' THEN - bseg~wrbtr  "<=== negative sign, is not the same logic as * -1
  ELSE bseg~wrbtr
  END AS wrbtr

EDIT Dec 30th - I didn't find a clear reference in the ABAP documentation how the inline types are calculated for arithmetic SQL expressions, it's by searching "up" from the behavior I experienced that I could find a logical way "down":

SELECT, INTO target - @DATA(dobj):

The ABAP type to which the result type of an SQL expression is assigned is used for this expression.
sql_exp - sql_arith (it concerns +, -, * and /):

Alongside any integer operands (see above), decimal expression have at least one operand with the type DEC, CURR, or QUAN or p with decimal places. The operator / is not allowed in decimal expressions. The result has the type DEC with the length 31 and a maximum of 14 decimal places. Using the associated assignment rule, it can be assigned to all numeric ABAP types whose value range is large enough, except for decimal floating point numbers.
SELECT, Assignment Rules:

If the target field has a numeric data type, the value of the result field is converted to this data type and the value range of the target field must be large enough. Here, any surplus decimal places in result fields of the type CURR, DEC, or QUAN (numbers in the BCD format) are cut off.

The [help](https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-us/abennumber_types.htm#@@ITOC@@ABENNUMBER_TYPES_2) says: `Helper fields for intermediate results in arithmetic expressions of calculation type p are always 16 bytes long and can thus hold up to 31 places. Before an overflow occurs, an arithmetic expression is calculated again with helper fields that are twice as large or 63 places`. It is highly unlikely that multiplication even increasing length to 13 bytes will cause such error. — Suncatcher, Dec 30 '22 at 05:07
@Suncatcher Not sure that it's the text which applies. I edited my answer to add what I think is the documentation which applies to arithmetic SQL expressions. Concerning the resulting length of 13 bytes in my system, there's no error as I said, but the SAP software used by the Original Poster e.g. S/4HANA 2021 has BSEG~WRBTR with 12 bytes (DEC 23 digits including 2 for decimals) multiplied by an integer (stored in packed 6 bytes) which makes a resulting length of 34 digits (including 2 for decimals), which leads to the error. — Sandra Rossi, Dec 30 '22 at 13:46
yes, probably you are right, the SQL expressions calculation type determined by another logic than arithmetic expressions calc type. I withdraw my comment — Suncatcher, Dec 30 '22 at 20:10

score 3 · Answer 2 · answered Dec 23 '22 at 10:56

3

The proper CASE syntax:

CASE bseg~bschl
  WHEN '50' THEN  bseg~wrbtr * ( -1 )
  ELSE bseg~wrbtr
  END AS bseg~wrbtr

Move bseg~bschl right after case and after WHEN mention only the values for equality

answered Dec 23 '22 at 10:56

József Szikszai

4,791
3
14
24

This is also throwing the error `The maximum possible number of places in the expression starting with WRBTR is 34 places with 2 decimal places. There can be, however, no more than 31 places and 14 decimal places.` – schmelto Dec 23 '22 at 12:14
3

it's not the "proper syntax", it's just the improper way of [complex case](https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/abensql_searched_case.htm), [CASE](https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/abensql_case.htm) in ABAP has two flavors, simple and complex, and the OP was trying to use the complex variant but used the wrong alias naming – Suncatcher Dec 30 '22 at 04:48
1

ouch, I completely missed that :( thanks – József Szikszai Jan 02 '23 at 12:13

score 1 · Answer 3 · answered Dec 30 '22 at 05:04

The results of the calculation of CAST( bseg~wrbtr AS D34N ) * CAST( -1 AS D34N ) in your CASE are put into data object of type calculation type.

According to the docu, the calculation type for the WRBTR (ABAP type P) is also P, but with important remark:

A calculation type p in assignments to an inline declaration can produce the data type p with length 8 and no decimal places and this can produce unexpected results and raise exceptions

SOLUTION: remove the inline declaration INTO TABLE @DATA(output) from your select query and declare your itab in advance with a proper accuracy.

score 1 · Answer 4 · answered Jan 02 '23 at 09:31

1

Here is my working solution for this problem.

CASE bseg~bschl
  WHEN '50' THEN CAST( bseg~wrbtr AS D34N ) * CAST( -1 AS D34N )
  ELSE CAST( bseg~wrbtr AS D34N )
  END AS wrbtr, bseg~h_hwaer,

answered Jan 02 '23 at 09:31

schmelto

427
5
18

WRBTR field calculation inside CASE throws error for max decimal places

4 Answers4