Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> analytical functions and pl/sql (8.1.6 on NT)
I'm facing problems when using analytical functions in pl/sql cursor for loops. The following statement works in sql*plus (it's only an example, please no postings with alternative / better solutions):
select *
from ( select deptno
,ename
,sal
,row_number() over (partition by deptno order
by sal desc) top_earnings_dept from scott.emp ) where top_earnings_dept = 1
Desired result: Top earning per department (if two or more persons getting paid the top salary in one department then only one row is returned for that department; that's a desired result)
This statement works fine:
DEPTNO ENAME SAL TOP_EARNINGS_DEPT
---------- ---------- ---------- ----------------- 10 KING 5000 1 20 SCOTT 3000 1 30 BLAKE 2850 1
When embedded in pl/sql like
SQL> create or replace procedure prc_analytic_test
2 is
3 begin
4 for v_c1 in (select *
5 from (
6 select deptno
7 ,ename 8 ,sal 9 ,row_number() over (partition by deptno order by sal desc) top_earnings_dept 10 from scott.emp
Warning: Procedure created with compilation errors.
SQL> show errors procedure prc_analytic_test; Errors for PROCEDURE PRC_ANALYTIC_TEST:
LINE/COL ERROR
-------- ----------------------------------------------------------------- 9/20 PLS-00103: Encountered the symbol "(" when expecting one of the following: , from
SQL> select * from user_source where name = 'PRC_ANALYTIC_TEST' and line = 9;
NAME TYPE LINE ------------------------------ ------------ ----------TEXT
i am not successful. Any help?
Thanks
Klaus Received on Thu Jul 06 2000 - 00:00:00 CDT