Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Location
    By the Seaside
    Posts
    3

    Unanswered: Nesting SELECT statement in FROM clause

    Is this possible??

    Newbie question for some light, post-turkey relief:

    Using subqueries in the WHERE clause is fine, but can they be used in the FROM clause?

    I always return
    ORA-00936: missing expression

    at the nested FROM line.

    If this is possible I will post code otherwise put me out of my misery.

  2. #2
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279
    You mean like this:
    Code:
     SELECT u.file_name,
            u.tablespace_name,
            u.mb,
            u.AUTOEXTENSIBLE,
            u.max_mb,
            (u.increment_by * to_number(p.value))/1024/1024 increment_by,
            u.extent_management,
            u.allocation_type,
            u.status
     FROM  (
        SELECT
           df.file_name,
           df.tablespace_name,
           df.bytes/1024/1024 mb,
           df.AUTOEXTENSIBLE,
           df.maxbytes/1024/1024 max_mb,
           df.increment_by,
           t.extent_management,
           t.allocation_type,
           df.status
        FROM dba_data_files df,
             dba_tablespaces t,
             v$datafile v
        WHERE t.tablespace_name = df.tablespace_name
        AND   v.file# = df.file_id
        UNION ALL
        SELECT
           tf.file_name,
           tf.tablespace_name,
           tf.bytes/1024/1024 mb,
           tf.AUTOEXTENSIBLE,
           tf.maxbytes/1024/1024 max_mb,
           tf.increment_by,
           t.extent_management,
           t.allocation_type,
           tf.status
        FROM dba_temp_files tf,
           dba_tablespaces t,
           v$tempfile v
        WHERE t.tablespace_name = tf.tablespace_name
        AND   v.file#=tf.file_id
         ) u, v$parameter p
     WHERE p.name = 'db_block_size'
     ORDER BY tablespace_name, file_name

  3. #3
    Join Date
    Dec 2002
    Location
    By the Seaside
    Posts
    3
    That's exactly what I mean!!

    I can't see the difference between my syntax and yours though...I'll not be posting the code, because it's time I wasn't here.....

    Thanks for the post - I'll figure it from that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •