[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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- group by 和 order by 一起使用,報錯 ORA-00979:不是 GROUP BY 表示式
- 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
- [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated cExpressAI
- [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains 的問題 MySQLExpressAIMySql
- mysql主給備賦予許可權時報錯,MySQL [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clauseMySqlExpress
- pl developerDeveloper
- Leetcode 10 Regular Expression MatchingLeetCodeExpress
- 【shell 】syntax error in conditional expressionErrorExpress
- find: paths must precede expression:Express
- Study for Go ! Chapter two - ExpressionGoAPTExpress
- xcrun: error: invalid active developer path (/Library/Developer/CommandLineTools)ErrorDeveloper
- [LeetCode] 282. Expression Add OperatorsLeetCodeExpress
- Syntax error, unrecognized expression: li[value=]ErrorZedExpress
- condition expression returns non-BooleanExpressBoolean
- 瞭解C#的ExpressionC#Express
- 動態拼接表示式——ExpressionExpress
- PLSQL Developer 14SQLDeveloper
- std::sort 錯誤"Expression : invalid operator <"Express
- PLSQL Developer 行號SQLDeveloper
- PLSQL Developer配置使用SQLDeveloper
- [20221123]19cDBA_EXPRESSION_STATISTICS查詢expression_text中字串帶雙引號的問題Express字串
- 聊聊JavaScript和Scala的表示式 ExpressionJavaScriptExpress
- Hyperscan is generally vulnerable to regular expression denial of service (ReDoS)Express
- 05.表示式目錄樹ExpressionExpress
- group conv
- group_replication_bootstrap_group 用於什麼boot
- 資料庫的sort group by和hash group by資料庫
- Android switch語句報錯Constant expression requiredAndroidExpressUI
- perl next, last, regular expression 實用場景ASTExpress
- ABAP mesh表示式, JavaScript和Scala的 expressionJavaScriptExpress
- [C# Expression] 之動態建立表示式C#Express
- oracle partition by group by,詳解partition by和group by對比Oracle
- PLSQL Developer 12 註冊碼SQLDeveloper
- plsql developer 視窗的使用SQLDeveloper
- [LeetCode] Group AnagramLeetCode
- MySQL Group ReplicationMySql
- Group by 優化優化
- object dict cannot be used in await expression報錯解釋ObjectAIExpress