topic Re: Group by SUBSTRING - error not a GROUP BY expression in Application Development and Automation Discussions

Group by SUBSTRING - error not a GROUP BY expression

ajaymukundan1981 — Fri, 26 Jul 2019 19:29:59 GMT

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.

Re: Group by SUBSTRING - error not a GROUP BY expression

Sandra_Rossi — Fri, 26 Jul 2019 20:17:01 GMT

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?

Re: Group by SUBSTRING - error not a GROUP BY expression

Jean_Sagi — Sun, 28 Jul 2019 15:15:27 GMT

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

BTW: What version of ABAP are you using?

Re: Group by SUBSTRING - error not a GROUP BY expression

DoanManhQuynh — Mon, 29 Jul 2019 00:46:29 GMT

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