with clause for subquery factoring

From: <web_poster03_at_yahoo.com>
Date: 4 Apr 2005 19:47:46 -0700
Message-ID: <1112669266.018887.158100@z14g2000cwz.googlegroups.com>

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. Received on Mon Apr 04 2005 - 21:47:46 CDT