Skip to Content
0
Jul 26, 2019 at 07:29 PM

Group by SUBSTRING - error not a GROUP BY expression

132 Views

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.