Results 1 to 3 of 3

Thread: INSERT Help!

  1. #1
    Join Date
    Nov 2005
    Posts
    6

    Unanswered: INSERT Help!

    Hi Gurus

    I am trying to insert into a table I have created however the SQL always errors with the following:

    select tg1.tablespace_name,sysdate - 1,
    *
    ERROR at line 2:
    ORA-00936: missing expression


    The SQL I am using is the following:

    insert into tsgrowth (tablespace_name, growth_date, growth_size, percent_growth)
    values (select tg1.tablespace_name,
    sysdate - 1,
    ((tg1.total_size - tg1.total_free) - (tg2.total_size - tg2.total_free)) growth_size,
    round(((tg1.total_size - tg1.total_free) - (tg2.total_size - tg2.total_free)) *
    100 / tg2.total_size,2) percent_growth
    from thusdba.tsreport tg1,
    thusdba.tsreport tg2,
    (select ts1.report_date report_date,max(ts2.report_date)
    prev_report_date
    from thusdba.tsreport ts1,
    thusdba.tsreport ts2
    where ts1.instance = ts2.instance(+)
    and ts1.tablespace_name = ts2.tablespace_name(+)
    and ts1.report_date > ts2.report_date
    group by ts1.report_date
    order by ts1.report_date) rep_dates
    where tg1.tablespace_name = tg2.tablespace_name
    and tg1.report_date = rep_dates.report_date
    and tg2.report_date = rep_dates.prev_report_date
    and tg1.report_date >= sysdate - 1
    and tg1.report_date <= sysdate
    and tablespace_name = 'UNDOTS')
    /


    The actual SELECT SQL works:

    1 select tg1.tablespace_name,sysdate - 1,
    2 ((tg1.total_size - tg1.total_free) - (tg2.total_size - tg2.total_free)) gro
    wth_size,
    3 round(((tg1.total_size - tg1.total_free) - (tg2.total_size - tg2.total_free
    )) *
    4 100 / tg2.total_size,2) percent_growth
    5 from thusdba.tsreport tg1,
    6 thusdba.tsreport tg2,
    7 (select ts1.report_date report_date,max(ts2.report_date)
    8 prev_report_date
    9 from thusdba.tsreport ts1,
    10 thusdba.tsreport ts2
    11 where ts1.instance = ts2.instance(+)
    12 and ts1.tablespace_name = ts2.tablespace_name(+)
    13 and ts1.report_date > ts2.report_date
    14 group by ts1.report_date
    15 order by ts1.report_date) rep_dates
    16 where tg1.tablespace_name = tg2.tablespace_name
    17 and tg1.report_date = rep_dates.report_date
    18 and tg2.report_date = rep_dates.prev_report_date
    19 and tg1.report_date >= sysdate - 1
    20 and tg1.report_date <= sysdate
    21* and tg1.tablespace_name = 'UNDOTS'
    thusdba@ICT1.WORLD> /

    TABLESPACE_NAME SYSDATE-1 GROWTH_SIZE PERCENT_GROWTH
    ------------------------- ---------------- ----------- --------------
    UNDOTS 09/11/2005 18:22 -41091072 -.16


    However the INSERT is failing despite the table 'tsgrowth' existing:

    > desc tsgrowth;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    TABLESPACE_NAME VARCHAR2(25)
    GROWTH_DATE DATE
    GROWTH_SIZE NUMBER
    PERCENT_GROWTH NUMBER(5,2)


    Any ideas???

    Cheers
    David

  2. #2
    Join Date
    Nov 2005
    Posts
    6
    Its okay I worked it out. The word VALUES had to be removed.

  3. #3
    Join Date
    Jul 2004
    Posts
    94
    good on you
    Priyanka
    Windows web hosting with ASP,.Net,PHP,Perl,MySQL support
    We help in making your dreams come true on internet!

Posting Permissions

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