Re: with clause for subquery factoring

From: Mark Bole <makbo_at_pacbell.net>
Date: Tue, 05 Apr 2005 23:17:53 GMT
Message-ID: <B0F4e.17809$zl.166@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_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 Tue Apr 05 2005 - 18:17:53 CDT