[Developer] ORA-00979:not a group by expression
今天在執行如下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;
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;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/24237320/viewspace-1993619/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ORA-00979: not a GROUP BY expression報錯處理Express
- Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggreExpressAI
- mysql報錯:Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggreMySqlExpressAI
- 【筆記】ora-00979 bug?筆記
- [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains 的問題 MySQLExpressAIMySql
- [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated cExpressAI
- Expression BlendExpress
- mysql主給備賦予許可權時報錯,MySQL [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clauseMySqlExpress
- ACM Arithmetic ExpressionACMExpress
- Expression Date FunctionsExpressFunction
- VS Could not evaluate expressionExpress
- 上海聘:SCM Java Developer/Senior DeveloperJavaDeveloper
- find: paths must precede expression:Express
- Study for Go ! Chapter two - ExpressionGoAPTExpress
- switch case 的 expected expressionExpress
- HTTP2 Expression of InterestHTTPExpressREST
- A Simple Sample for Expression Tree (轉)Express
- pl developerDeveloper
- [Developer] CubeDeveloper
- [Developer] RollupDeveloper
- 動態拼接表示式——ExpressionExpress
- Syntax error, unrecognized expression: li[value=]ErrorZedExpress
- 瞭解C#的ExpressionC#Express
- 資料結構:Expression Tree資料結構Express
- CSS中expression使用簡介CSSExpress
- 【shell 】syntax error in conditional expressionErrorExpress
- 正規表示式(regular expression)Express
- PLSQL Developer 14SQLDeveloper
- Oracle GroupOracle
- Oracle正規表示式(regular expression)OracleExpress
- 請問:Cron-Expression如何表示?Express
- CSS自定義屬性Expression(轉)CSSExpress
- group_replication_bootstrap_group 用於什麼boot
- 資料庫的sort group by和hash group by資料庫
- PLSQL Developer 行號SQLDeveloper
- PLSQL Developer配置使用SQLDeveloper
- Developer Zone Android*DeveloperAndroid
- [Developer] Grouping setsDeveloper