今天在执行如下SQL报了ORA-00979:not a group by expression
SELECT   aaaaa,bbbbb,ccccc,COUNT(*) QTY
         FROM 
        (
        select CASE WHEN to_char(a.updatettime,'HH24')>='22' THEN  to_char(a.updatettime +1,'YYYYMMDD')  ELSE to_char(a.updatettime,'YYYYMMDD')  END aaaaa, 
                       CASE WHEN (SUBSTR(a.COMMENTDESC,1,4) IN ('x') or SUBSTR(a.COMMENTDESC,1,1)='y') THEN 'OK' ELSE 'NG' END bbbbb,
                       CASE WHEN SUBSTR(a.COMMENTDESC,-2) ='z' THEN 'OK' ELSE 'NG' END ccccc
         from xxxxxxx a,
              yyyyyyy b
        where a.activity='xxxxxxxxx' 
        and a.updatettime >= to_date( '20150211220000', 'YYYYMMDDHH24MISS')-1
        and a.updatettime < to_date('20150211220000', 'YYYYMMDDHH24MISS')-2
        and substr(a.id, 1, 12) = b.itemid(+)
       ) 
        GROUP BY aaaaa,bbbbb,ccccc;

baidu了一下，发现了其他人也遇到这个问题，是oracle的bug
http://blog.itpub.net/29154652/viewspace-772504/

加入了hint   /*+ CURSOR_SHARING_EXACT */后不再报错。
SELECT  /*+ CURSOR_SHARING_EXACT */ aaaaa,bbbbb,ccccc,COUNT(*) QTY
         FROM 
        (
        select CASE WHEN to_char(a.updatettime,'HH24')>='22' THEN  to_char(a.updatettime +1,'YYYYMMDD')  ELSE to_char(a.updatettime,'YYYYMMDD')  END aaaaa, 
                       CASE WHEN (SUBSTR(a.COMMENTDESC,1,4) IN ('x') or SUBSTR(a.COMMENTDESC,1,1)='y') THEN 'OK' ELSE 'NG' END bbbbb,
                       CASE WHEN SUBSTR(a.COMMENTDESC,-2) ='z' THEN 'OK' ELSE 'NG' END ccccc
         from xxxxxxx a,
              yyyyyyy b
        where a.activity='xxxxxxxxx' 
        and a.updatettime >= to_date( '20150211220000', 'YYYYMMDDHH24MISS')-1
        and a.updatettime < to_date('20150211220000', 'YYYYMMDDHH24MISS')-2
        and substr(a.id, 1, 12) = b.itemid(+)
       ) 
        GROUP BY aaaaa,bbbbb,ccccc;