Sunday, September 16, 2012

ORA-00934

This was tested on Oracle 11.1.0.6.0 running on Windows XP. You can only use a WHERE clause to select or reject individual rows. You cannot use it on a group function:

SQL> l
  1  select owner, round(avg(bytes))
  2  from dba_segments
  3  where avg(bytes) > 60000
  4  group by owner
  5* order by owner
SQL> /
where avg(bytes) > 60000
      *
ERROR at line 3:
ORA-00934: group function is not allowed here

SQL>

You must use HAVING instead:

SQL> l
  1  select owner, round(avg(bytes))
  2  from dba_segments
  3  group by owner
  4  having avg(bytes) > 60000
  5* order by owner
SQL> /

OWNER                ROUND(AVG(BYTES))
-------------------- -----------------
CTXSYS                           66298
DBSNMP                           65536
EXFSYS                           65536
FLOWS_030000                    123339
FLOWS_FILES                      65536
HR                               65536
IX                               65536
MDSYS                           139147
OE                              124955
OLAPSYS                          65536
ORDSYS                           80766
OUTLN                            65536
PM                              311296
SCOTT                            65536
SH                              204600
SYS                             623750
SYSMAN                          107652
SYSTEM                           65536
TSMSYS                           65536
WKSYS                            65536
WK_TEST                         244065
WMSYS                            65536
XDB                             124663

23 rows selected.

SQL>



I read about this in the following excerpt from page 184 of the book advertised above. As usual, click on the picture to enlarge it and bring it into focus:


No comments: