Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Problems with a subquery.
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
>
? Why would you like index with name 5 (mind you - not name '5', which would be correct - try to_char!). Received on Fri May 03 2002 - 15:28:44 CDT
![]() |
![]() |