Re: Sub-query not working

Solution with sub-query that's also works on 7.3

select T.rowcount,I.rowcount
from ( select count(1) rowcount from all_tables) T ,

     ( select count(1) rowcount from all_indexes) I from dual

Regards
Gert

In article <91ullm$f8k$1_at_nnrp1.deja.com>,   Alex Filonov <afilonov_at_pro-ns.net> wrote:
> Subquery in the column works starting with version 8.1 only. For
> previous versions you can use this:
> select max(tablecount) tablecount, max(indexcount) indexcount
> from (select count(*) tablecount, 0 indexcount
> from all_tables
> union all
> select 0, count(*)
> from all_indexes)
>
> You don't need rownum in either query.
>
> By the way, it's easier (and faster) to do this way
>
> select sum(decode(object_name, 'TABLE', 1,0)) tablecount,
> sum(decode(object_name, 'INDEX', 1,0)) indexcount
> from all_objects.
>
> In article <91u72g$441$1_at_nnrp1.deja.com>,
> david_holthus_at_my-deja.com wrote:
> > The following query works on only 1 of our servers (the server with
 the
> > highest version of oracle) :
> >
> > select
> > (select count (*) from all_tables) as tablecount,
> > (select count (*) from all_indexes) as indexcount
> > from all_tables where rownum =1
> >
> > Server Versions:
> > server1 : Oracle8 Release 8.0.5.2.1 - Production
> > server2 : Oracle7 Server Release 7.3.2.3.14 - Production Release
> > server3 : Oracle8 Release 8.0.5.2.0 - Production
> > server4 : Oracle8i Release 8.1.5.0.0 - Production
> >
> > The error I get on the other 3 servers is:
> > ORA-00936: missing expression
> >
> > Is the solution version-dependent, or is there something else I can
 do
> > to fix the problem
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >
>
> Sent via Deja.com
> http://www.deja.com/
>

Sent via Deja.com
http://www.deja.com/ Received on Fri Dec 22 2000 - 02:49:55 CST