Reputation: 55524
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 Production
it results in:
ORA-00979: not a GROUP BY expression
ORA-06512: at line 5
Can anyone reproduce/explain this? Thanks!
Upvotes: 7
Views: 8486
Reputation: 1486
Truncating the sysdate in the inner select appears to work fine:
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;
Upvotes: 1
Reputation: 67722
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.
Upvotes: 6
Reputation: 873
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 trn
variable and variable using in TRUNC
function. Maybe it is bug.
Upvotes: 1
Reputation: 86718
I suspect your problem is that the NULL
in your SELECT
needs to be in the GROUP BY
even though it is a constant. I can't imagine why it would work in Oracle 10 but not 11, though.
Does it work if you remove NULL AS dummy_2
?
Upvotes: 2