-
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.
-
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
-
Thanks Ales. I see what you are saying !!
Shiva.
-
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.
