Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Sub-query not working

Re: Sub-query not working

From: <raymondlopez_at_my-deja.com>
Date: Fri, 22 Dec 2000 12:56:54 GMT
Message-ID: <91vj2l$48f$1@nnrp1.deja.com>

I reckon the 'from dual' is not required.

Regards,
Ray.

In article <91v4ji$qlp$1_at_nnrp1.deja.com>,   denevge_at_my-deja.com wrote:
> 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/
>

Sent via Deja.com
http://www.deja.com/ Received on Fri Dec 22 2000 - 06:56:54 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US