Re: Help with select statement

From: Stan Towianski <stantow_at_ibm.net>
Date: 1997/06/04
Message-ID: <3395c7a0.0@news1.ibm.net>#1/1

Hi,

If you don't mind... I am trying to learn how to deal with sql statements and am finding 'group by' to be a pain to understand and use. In this case I got my main statement to work but then tried adding ' and count( extent_id ) > 20' to the end of the 'where' clause.

select owner, segment_name, segment_type, count( extent_id ) from dba_extents
where ( owner like 'MANU%' or owner like 'ACCOUNT%' ) and count( extent_id ) > 20
group by owner, segment_name, segment_type /

It complains with:
Connected to:
Oracle7 Server Release 7.3.2.3.0 - Production Release PL/SQL Release 2.3.2.3.0 - Production

SQL> @tab_exts
where ( owner like 'MANU%' or owner like 'ACCOUNT%' ) and count( extent_id ) > 20

                                                      *

ERROR at line 3:
ORA-00934: group function is not allowed here

SQL> Do you know why it does not like this? and how and can do what I want? Without the extra 'and ...' clause it works and gives me a list tables and indexes in my database and the number of extents used for each. This is so I can keep track to see which tables, indexes are growing to the end of their max extents limit.

What I wanted to do with the additional clause is cut this report down to just the tables/indexes that had at least 20 extents, figuring I don't need to worry about smaller ones.

Any help is appreciated.
Thanks.

(posted newsgroup reply and sent email)

stantow_at_ibm.net Received on Wed Jun 04 1997 - 00:00:00 CDT