ORA-00979: not a group by expression error handling

Environment: Oracle Database 11gR2 (11.2.0.2) on Linux
Fault symptom: The ORA-00979 is reported when the Group By statement is executed.
The specific SQL statement is as follows:
SQL> select a. d1, a. EXIT_type, round (a. cnt1/B. cnt2 * 100,2) from
2 (select substr (LOGIN_DATE,) d1, EXIT_type, count (*) cnt1 from xxx_connect_log group by substr (LOGIN_DATE,), EXIT_type),
3 (select substr (LOGIN_DATE,) d2, count (*) cnt2 from xxx_connect_log group by substr (LOGIN_DATE,) B
4 where A. d1 = B. d2 order by a. d1, a. EXIT_type;
(Select substr (LOGIN_DATE, 1, 8) d2, count (*) cnt2 from xxx_connect_log group by substr (LOGIN_DATE, 1, 8) B
*
ERROR at line 3:
ORA-00979: not a group by expression

This statement is executed on the DB of other regions. If yes, an error is returned on the DB of the trial server. In terms of syntax, there is no problem at all.

In this case of ORA-error, we can trace the error stack by setting ErrorStack, and dump the error background information to the tracking file for analysis and research.

Four levels and descriptions of ErrorStack:
0 Error stack only
1 Error stack and function call stack
2 As level 1 plus the process state
3 As level 2 plus the context area

ErrorStack is triggered only when a specific error occurs. You can set it at the instance or session level.

Next we will track the error stack of 979:
SQL> alter system set events = '979 trace name errorstack forever, level 3 ';

System altered.

SQL> select a. d1, a. EXIT_type, round (a. cnt1/B. cnt2 * 100,2) from
2 (select substr (LOGIN_DATE,) d1, EXIT_type, count (*) cnt1 from xxx_connect_log group by substr (LOGIN_DATE,), EXIT_type),
3 (select substr (LOGIN_DATE,) d2, count (*) cnt2 from xxx_connect_log group by substr (LOGIN_DATE,) B
4 where A. d1 = B. d2 order by a. d1, a. EXIT_type;
(Select substr (LOGIN_DATE, 1, 8) d2, count (*) cnt2 from xxx_connect_log group by substr (LOGIN_DATE, 1, 8) B
*
ERROR at line 3:
ORA-00979: not a group by expression

SQL> alter system set events = '979 trace name errorstack off ';

System altered.

SPFILE error causing database startup failure (ORA-01565)

ORA-01172, ORA-01151 error handling

ORA-00600 [2662] troubleshooting

Troubleshooting for ORA-01078 and LRM-00109

Notes on ORA-00471 Processing Methods

1 2 Next Page