analytical functions and pl/sql (8.1.6 on NT)

From: Klaus Zeuch <KZeuch_at_hotmail.com>
Date: 2000/07/06
Message-ID: <8k2sqj$s43$18$1@news.t-online.com>#1/1

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

 11 )
 12 where top_earnings_dept = 1
 13 )
 14 loop
 15 null;
 16 end loop;
 17 EXCEPTION
 18 WHEN others THEN null;
 19 END; -- Procedure PRC_ANALYTIC_TEST  20 /

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

---

---

PRC_ANALYTIC_TEST PROCEDURE 9 ,row_number() over (partition by deptno order by sal desc) top_earnings_dept

i am not successful. Any help?

Thanks

Klaus Received on Thu Jul 06 2000 - 00:00:00 CDT