Complation error in "Order by"...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Complation error in "Order by"...

  1. #1
    Join Date
    Apr 2002
    Posts
    50
    Hi all, I have a query :
    insert into temp_bam_1hr (sample, sample_cnt, seq)
    select
    round(avg(greatest(inbitssec,outbitssec))),
    count(*),
    seq_bam_1hr.nextval
    from BAM_UTIL_PARSED
    WHERE entry between str_dt and end_dt and
    EQP_ID = eqpid and
    port_id = portid
    group by to_number(to_char(entry, 'HH24'))
    order by round(avg(greatest(inbitssec,outbitssec)));

    This runs just fine on SQLPLUS, but I get this compilation error when it is part of a procedure :

    Line # = 34 Column # = 3 Error Text = PLS-00103: Encountered the symbol "ORDER" when expecting one of the following:
    . ( , * % & - + ; / at mod rem return returning

    Does anyone know what is wrong here ?

    Shiva.

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    PL/SQL uses different parser than SQL. PL/SQL parser doesn't allow using ORDER BY clause in insert statement.
    It doesn't make sense to do that since Oracle doesn't ensure that with SELECT you get rows in the order you INSERTed them.
    Ales


  3. #3
    Join Date
    Apr 2002
    Posts
    50
    Thanks Ales. I see what you are saying !!

    Shiva.

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    begin
    insert into temp_bam_1hr (sample, sample_cnt, seq)
    select * from
    (
    select
    round(avg(greatest(inbitssec,outbitssec))),
    count(*),
    seq_bam_1hr.nextval
    from BAM_UTIL_PARSED
    WHERE entry between str_dt and end_dt and
    EQP_ID = eqpid and
    port_id = portid
    group by to_number(to_char(entry, 'HH24'))
    order by round(avg(greatest(inbitssec,outbitssec)))
    );
    end;
    /

    Try this code. It, probably, have to work.

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