Tuesday, August 28, 2012

ORA-00937

This was tested on Oracle 11.2. You might think that, if you wanted to see details from a row containing some maximum value, you could do it as follows:
 
SQL> select owner, segment_name, max(bytes)
  2  from dba_segments
  3  /
select owner, segment_name, max(bytes)
       *
ERROR at line 1:
ORA-00937: not a single-group group function
 
SQL>
 
... but you get an ORA-00937. You have to do it like this:
 
SQL> l
  1  select owner, segment_name, bytes
  2  from dba_segments
  3  where bytes =
  4* (select max(bytes) from dba_segments)
SQL> /
 
OWNER           SEGMENT_NAME         BYTES
--------------- --------------- ----------
SYS             IDL_UB1$         251658240
 
SQL>

No comments: