查询适用于 Oracle 10g 但不适用于 11g？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4364459/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query works with Oracle 10g but not with 11g?
提问by Peter Lang
DECLARE
trn VARCHAR2(2) := 'DD';
cur SYS_REFCURSOR;
BEGIN
OPEN cur FOR
SELECT
TRUNC(some_date, trn),
NULL AS dummy_2,
COUNT( DISTINCT dummy_1 )
FROM
(SELECT SYSDATE AS some_date, ROWNUM AS dummy_1 FROM dual)
GROUP BY
TRUNC(some_date, trn);
END;
This works with Oracle 10, but with Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Productionit results in:
这适用于 Oracle 10，但Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production会导致：
ORA-00979: not a GROUP BY expression
ORA-06512: at line 5
ORA-00979: 不是 GROUP BY 表达式
ORA-06512: 在第 5 行
Can anyone reproduce/explain this? Thanks!
任何人都可以重现/解释这一点吗？谢谢！
采纳答案by Vincent Malgrat
if you have access to support, it looks like Bug 9478304: LOOP FAILING WITH ORA-00979: NOT A GROUP BY EXPRESSION. This seems to affect 11.2.0.1 only.
如果您可以获得支持，它看起来像Bug 9478304: LOOP FAILING WITH ORA-00979: NOT A GROUP BY EXPRESSION。这似乎只影响 11.2.0.1。
回答by Gabe
I suspect your problem is that the NULLin your SELECTneeds to be in the GROUP BYeven though it is a constant. I can't imagine why it would work in Oracle 10 but not 11, though.
我怀疑你的问题是NULLin 你SELECT需要在GROUP BY即使它是一个常数。不过，我无法想象为什么它可以在 Oracle 10 中运行，而不能在 11 中运行。
Does it work if you remove NULL AS dummy_2?
如果你删除它会起作用NULL AS dummy_2吗？
回答by ksogor
This works without errors:
这工作没有错误：
DECLARE
trn VARCHAR2(2) := 'DD';
cur SYS_REFCURSOR;
BEGIN
OPEN cur FOR
SELECT
TRUNC(some_date, dtrn),
NULL AS dummy_2,
COUNT( DISTINCT dummy_1 )
FROM
(SELECT SYSDATE AS some_date, ROWNUM AS dummy_1 FROM dual) data1,
(SELECT trn AS dtrn FROM dual) data2
GROUP BY TRUNC(some_date, dtrn);
END;
Problem is with trnvariable and variable using in TRUNCfunction. Maybe it is bug.
问题在于函数中trn使用的变量和变量TRUNC。也许是bug。
回答by Douglas Lise
Truncating the sysdate in the inner select appears to work fine:
在内部选择中截断 sysdate 似乎工作正常：
DECLARE
trn VARCHAR2(2) := 'DD';
cur SYS_REFCURSOR;
BEGIN
OPEN cur FOR
SELECT
some_date,
NULL AS dummy_2,
COUNT( DISTINCT dummy_1 )
FROM
(SELECT trunc(SYSDATE, trn) AS some_date, ROWNUM AS dummy_1 FROM dual)
GROUP BY
some_date;
END;
