with clause for subquery factoring

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • web_poster03@yahoo.com
    #1

    with clause for subquery factoring

    Hi,

    The following does not return result:

    with free_space_by_t ablespace as
    ( select sum(bytes)/1024 Kbytes_free,
    max(bytes)/1024 largest,
    tablespace_name
    from sys.dba_free_sp ace
    group by tablespace_name
    select * from free_space_by_t ablespace ;

    BUT this does:
    select * from (with free_space_by_t ablespace as
    ( select sum(bytes)/1024 Kbytes_free,
    max(bytes)/1024 largest,
    tablespace_name
    from sys.dba_free_sp ace
    group by tablespace_name
    select * from free_space_by_t ablespace
    )


    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.

    Tags: None
    • Mark Bole
      #2
      Re: with clause for subquery factoring

      web_poster03@ya hoo.com wrote:
      Hi,
      >
      The following does not return result:
      >
      with free_space_by_t ablespace as
      ( select sum(bytes)/1024 Kbytes_free,
      max(bytes)/1024 largest,
      tablespace_name
      from sys.dba_free_sp ace
      group by tablespace_name
      select * from free_space_by_t ablespace ;
      >
      BUT this does:
      select * from (with free_space_by_t ablespace as
      ( select sum(bytes)/1024 Kbytes_free,
      max(bytes)/1024 largest,
      tablespace_name
      from sys.dba_free_sp ace
      group by tablespace_name
      select * from free_space_by_t ablespace
      )
      >
      >
      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@binc04 .treewith free_space_by_t ablespace as
      2 ( select sum(bytes)/1024 Kbytes_free,
      3 max(bytes)/1024 largest,
      4 tablespace_name
      5 from sys.dba_free_sp ace
      6 group by tablespace_name
      7 select * from free_space_by_t ablespace ;
      select * from free_space_by_t ablespace
      *
      ERROR at line 7:
      ORA-00907: missing right parenthesis

      oracheck@binc04 .treelist
      1 with free_space_by_t ablespace as
      2 ( select sum(bytes)/1024 Kbytes_free,
      3 max(bytes)/1024 largest,
      4 tablespace_name
      5 from sys.dba_free_sp ace
      6 group by tablespace_name )
      7* select * from free_space_by_t ablespace
      oracheck@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



        Comment

        • web_poster03@yahoo.com
          #3
          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@ya hoo.com wrote:
          >
          Hi,

          The following does not return result:

          with free_space_by_t ablespace as
          ( select sum(bytes)/1024 Kbytes_free,
          max(bytes)/1024 largest,
          tablespace_name
          from sys.dba_free_sp ace
          group by tablespace_name
          select * from free_space_by_t ablespace ;

          BUT this does:
          select * from (with free_space_by_t ablespace as
          ( select sum(bytes)/1024 Kbytes_free,
          max(bytes)/1024 largest,
          tablespace_name
          from sys.dba_free_sp ace
          group by tablespace_name
          select * from free_space_by_t ablespace
          )


          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@binc04 .treewith free_space_by_t ablespace as
          2 ( select sum(bytes)/1024 Kbytes_free,
          3 max(bytes)/1024 largest,
          4 tablespace_name
          5 from sys.dba_free_sp ace
          6 group by tablespace_name
          7 select * from free_space_by_t ablespace ;
          select * from free_space_by_t ablespace
          *
          ERROR at line 7:
          ORA-00907: missing right parenthesis
          >
          oracheck@binc04 .treelist
          1 with free_space_by_t ablespace as
          2 ( select sum(bytes)/1024 Kbytes_free,
          3 max(bytes)/1024 largest,
          4 tablespace_name
          5 from sys.dba_free_sp ace
          6 group by tablespace_name )
          7* select * from free_space_by_t ablespace
          oracheck@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

            Comment

            Previous template Next
            Working...