Problems with a subquery.

From: Russ Brooks <russ.brooks_at_dayzim.com>
Date: 3 May 2002 11:22:54 -0700
Message-ID: <a7f817f2.0205031022.2b97ce89@posting.google.com>

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 - 13:22:54 CDT