russ.brooks_at_dayzim.com (Russ Brooks) wrote in message news:<a7f817f2.0205031022.2b97ce89_at_posting.google.com>...
No, what I'm trying to get is the number of indices, not the index
names, for tables over 1,000,000 bytes.
> Hi,
> I'm having trouble with a character conversion coming out of a
> subquery.
> My query is:
>
> 1 select segment_name, bytes, index_name
> 2 from dba_segments s, dba_indexes i
> 3 where segment_type = 'TABLE'
> 4 and bytes > 1000000
> 5 and index_name = (select count(*)
> 6 from dba_indexes
> 7 where table_name = segment_name
> 8 group by table_name)
> 9* order by bytes desc
>
> If I execute this query I get an ORA-01722:invalid number on
> index_name in line 5. I've tried putting an alias in the subquery and
> to_number without success. Does anyone have any thoughts?
>
> TIA,
> Russ
Received on Mon May 06 2002 - 06:40:36 CDT
