Problem with INSERT INTO
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Problem with INSERT INTO

  1. #1
    Join Date
    Nov 2000
    Location
    London
    Posts
    83

    Exclamation

    Using the following script, I want to populate table TEST with 4 columns

    insert into test
    values (select name, sysdate from v$database),
    (SELECT A.TABLESPACE_NAME,
    ROUND(((NVL(B.BYTES,0) * 100) / A.BYTES), 1)
    FROM (select tablespace_name, sum(bytes) bytes
    from dba_data_files
    group by tablespace_name) A,
    (select tablespace_name,
    sum(bytes) bytes
    from dba_extents
    group by tablespace_name) B
    WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+)
    order BY 1))

    which does not work: help please!
    Fiona

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    You can't use multiple, isolated SELECTS as you are trying to do. You can only use a single SELECT. If you need columns from multiple tables, then you need to join those tables into a singular SELECT.

    - Chris

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    insert into test
    select c.name, sysdate, a.tablespace_name, a.bytes, b.bytes
    from
    (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) a,
    (select tablespace_name, sum(bytes) bytes from dba_extents group by tablespace_name ) b,
    v$database c
    where a.tablespace_name = b.tablespace_name

    ----
    I have simplified the query, but you should get the idea.

    You have a couple of issues with your select:
    1. you are using the "values" clause when you are not using any values.
    2. you are trying to use "order by" with an insert query ( a no-no)
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

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


Click Here to Expand Forum to Full Width