Solved
PL/SQL, row_number () over (partition by ......)
Posted on 2006-05-12
I have a scprit (Oracle 8i), and part of it looks like this
--test.sql
SET SERVEROUTPUT ON
BEGIN
.............
............
............
insert into temp_emp (emp_cd,dep_num_1,emp_dt_1,dep_num_2,emp_dt_2,
dep_num_3,emp_dt_3)
(select base.emp_cd,
max(decode(base.rn, 1, dep_num, null)) dep_num_1,
max(decode(base.rn, 1, emp_dt, null)) emp_dt_1,
max(decode(base.rn, 2, dep_num, null)) dep_num2,
max(decode(base.rn, 2, emp_dt, null)) emp_dt_2,
max(decode(base.rn, 3 dep_num, null)) dep_num3,
max(decode(base.rn, 3 emp_dt, null)) emp_dt_3
from
(select distinct a.emp_cd,b.dep_num,a.emp_dt, row_number () over (partition by emp_cd order by emp_dt desc)rn
from emp a , dep b
where a.emp_num = b.emp_num
group by a.emp_cd,b.dep_num,a.emp_dt
order by a.emp_cd,b.dep_num,a.emp_dt)base
group by base.emp_cd
order by base.emp_cd);
COMMIT;
..............................
...............................
................................
END;
/
when I run the above script , I get the following error
ORA-06550: line #, column #:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
, from into bulk
the above works fine when I run it as a SQL statement, but doesn't work in the script,is there any work around for it???
Thanks in advance