Group by SUBSTRING - error not a GROUP BY expression

[ajaymukundan1981]

Hi Gurus,

I have a requirement wherein I need to get aggregate of local currency for particular GL based on Division.

Consider the below query-

  select BSEG~BUKRS ,
         BSEG~GJAHR ,
          BKPF~MONAT,
         BSEG~HKONT ,
         case substring( setleaf~SETNAME , 1 , 3 ) 
           when 'MBG' then 'MBG'
           when 'ETB' THEN 'ETB'
           when 'EMP' THEN 'EMP'
           ELSE setleaf~SETNAME
         END  AS Division_type,  
         BKPF~WAERS,


        SUM(  BSEG~DMBTR ) AS TOTO_LOCAL        
    from ( BSEG AS BSEG
           inner join BKPF AS BKPF
           on  BKPF~BELNR = BSEG~BELNR
           and BKPF~BUKRS = BSEG~BUKRS
           and BKPF~GJAHR = BSEG~GJAHR
           inner join setleaf as setleaf
           on setleaf~VALFROM = bseg~prctr 
         )   
          
    group by bseg~bukrs,
             bseg~gjahr,
             bkpf~monat,
             BSEG~HKONT , 
            ( case substring( setleaf~SETNAME , 1 , 3 ) 
         when 'MBG' then 'MBG'
         when 'ETB' THEN 'ETB'
         when 'EMP' THEN 'EMP'
         ELSE setleaf~SETNAME
        END   ) ,
       BKPF~WAERS     
    ORDER BY bseg~bukrs,
             bseg~gjahr,
             bkpf~monat,
             BSEG~HKONT, 
             Division_type,
             BKPF~WAERS

On executing syntax check I am getting the below error-

Only elementary arithmetic types can be used in arithmetic expressions. The type of 'MBG' is invalid.

Can someone suggest on resolving this error.

[Sandra_Rossi]

The error is probably in the CASE of the GROUP BY clause. Could you try removing the parentheses around CASE? Not sure if CASE is fully supported in GROUP BY, could you try removing the whole CASE from both SELECT and GROUP BY? What ABAP version do you use?

[Jean_Sagi]

Do your query "without" the "group by" works?

BTW: What version of ABAP are you using?

[DoanManhQuynh]

I think you can directly group by setleaf~setname instead, result should be the same.