Re: Problems with a subquery.

Huh? The expression "count(*)" is a number, "index_name" is a string, why are you expecting these to be equal? This query has several problems really. If I had to guess what you were trying to do, I'd guess you want all indexes on tables that have segments over 1,000,000 bytes ...

select segment_name, bytes, index_name
from dba_segments, dba_indexes
where bytes > 1000000
  and segment_type = 'TABLE'
  and table_name = segment_name
order by bytes desc

Of course, this is a little deceiving since the indexes aren't in those segments.

Richard

Russ Brooks wrote:
>
> 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 Fri May 03 2002 - 14:26:53 CDT