with clause for subquery factoring

[web_po...@yahoo.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.

[Mark Bole]

web_po...@yahoo.com wrote:

Missing parenthesis from first version?

orac...@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

orac...@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
orac...@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

[web_po...@yahoo.com]

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