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