Re: with clause for subquery factoring

Thanks Mark.

After I upgrade my oracle client from 8.1.17 to 9.2 then it works. Hope this is useful for other person

Mark Bole wrote:
> web_poster03_at_yahoo.com wrote:
>
> > Hi,
> >
> > The following does not return result:
> >
> > with free_space_by_tablespace as
> > ( select sum(bytes)/1024 Kbytes_free,
> > max(bytes)/1024 largest,
> > tablespace_name
> > from sys.dba_free_space
> > group by tablespace_name
> > select * from free_space_by_tablespace ;
> >
> > BUT this does:
> > select * from (with free_space_by_tablespace as
> > ( select sum(bytes)/1024 Kbytes_free,
> > max(bytes)/1024 largest,
> > tablespace_name
> > from sys.dba_free_space
> > group by tablespace_name
> > select * from free_space_by_tablespace
> > )
> >
> >
> > What is wrong with my query?
> >
> > This occurs in Sql/plus and SQL navigator v4.4.5. In MS ACCESS pass
> > through query, I also got the no records returned warning.
> >
> > Is it due to the version problem on my client software? My Oracle
> > server is 9.2 and above
> >
> > Thanks for any points on this.
> >
>
> Missing parenthesis from first version?
>
> oracheck_at_binc04.tree> with free_space_by_tablespace as
> 2 ( select sum(bytes)/1024 Kbytes_free,
> 3 max(bytes)/1024 largest,
> 4 tablespace_name
> 5 from sys.dba_free_space
> 6 group by tablespace_name
> 7 select * from free_space_by_tablespace ;
> select * from free_space_by_tablespace
> *
> ERROR at line 7:
> ORA-00907: missing right parenthesis
>
> oracheck_at_binc04.tree> list
> 1 with free_space_by_tablespace as
> 2 ( select sum(bytes)/1024 Kbytes_free,
> 3 max(bytes)/1024 largest,
> 4 tablespace_name
> 5 from sys.dba_free_space
> 6 group by tablespace_name)
> 7* select * from free_space_by_tablespace
> oracheck_at_binc04.tree> /
>
> KBYTES_FREE LARGEST TABLESPACE_NAME
> ----------- ---------- ------------------------------
> 51136 51136 LOBS
> 768 768 MONITOR
> 16320 10944 SYSAUX
> 96512 96192 SYSTEM
> 153408 51136 TESTDB_A
> 142912 98240 UNDO01
> 51136 51136 USER01
> 34752 34752 XDB
>
> 8 rows selected.
>
> -Mark Bole
Received on Wed Apr 06 2005 - 16:11:43 CEST