SUM the NUMC field in SELECT

Question

I need to group a table by the sum of a NUMC-column, which unfortunately seems not to be possible with ABAP / OpenSQL.

My code looks like that:

    SELECT z~anln1
    FROM zzanla AS z
    INTO TABLE gt_
    GROUP BY z~anln1 z~anln2
    HAVING SUM( z~percent ) <> 100  " percent unfortunately is a NUMC -> summing up not possible

What would be the best / easiest practices here as I cannot alter the table itself?

score 3 · Answer 1 · answered Mar 17 '15 at 13:02

Unfortunately the NUMC type is described as numerical text, so at the end it lands in the database as VARCHAR and that is why the functions like SUM or AVG cannot be used.

It all depends on how big your table is. If it is rather small you could get the group fields and the values for sum into an internal table and then sum it using COLLECT statement and eventually remove the rows for which the sum is equal 100%.

score 1 · Answer 2 · answered May 31 '15 at 03:16

1

One solution is to define the field in the table using a more appropriate type.

NUMC is often used for key fields - like document numbers, which there would never be a reason to add together.

answered May 31 '15 at 03:16

Thomas Matecki

629
5
20

It probably was a mistake to define the field as a NUMC. But there are to many programs built around it to change it with justifiable efford. – K B Jun 03 '15 at 08:06

score 0 · Accepted Answer · answered May 27 '15 at 07:46

I didn't find a smooth solution. What I did, was to copy everything in an internal table, looped over it converting the NUMC values to DEC values. Grouping and summing up worked at that point. At the end, I converted the DEC values back to NUMC values.

score 0 · Answer 4 · answered Oct 08 '20 at 15:08

It's been awhile. I came back to this post, because someone voted up my original answer. I was thinking about editing my old answer but I decided to post a new one. As this question was asked in 2017, there were some restictions but now it can be done by using CAST function in new OpenSQL.

SELECT z~anln1
FROM zzanla AS z
INTO TABLE @gt_
GROUP BY z~anln1, z~anln2
HAVING SUM( CAST( z~percent AS INT4 ) ) <> 100

SUM the NUMC field in SELECT

4 Answers4