Re: with clause for subquery factoring
Date: Tue, 05 Apr 2005 23:17:53 GMT
Message-ID: <B0F4e.17809$zl.166_at_newssvr13.news.prodigy.com>
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_name7 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 - 01:17:53 CEST