rank function in a procedure

[dj]

I was able to get the rank function to work in a plain sql statement, but
when I try to incorporate it in a PLSQL procedure it fails. I tried to use
the rank function in a cursor that retrieves the top three records from every
group so that I can insert the records into anther table using a for loop.
It errors at the cursor. I use the same statement as I did in the SQL statement
but it errors out. Has anyone used the rank function in a PLSQL procedure?
If so, can you please send me an example. Do you know if the rank function
can be used in a PLSQL procedure?

I greatly appreciate any help or direction you can offer.

Thanks in advance,
dj

[psit]

Hi, dj
I have some idea to rank the data, but not easy to use for unknow
ranking level. In your case only have 3 levels. Lets see below (use Scott/tiger
base)

1 select deptno,ename,sal

2 from emp a

3 where exists (

4 select deptno,max(sal)

5 from emp z

6 where exists

7 ( select deptno,max(sal)

8 from emp x

9 where exists

10 ( select deptno,max(sal)

11 from emp y

12 group by deptno

13 having y.deptno = x.deptno

14 and x.sal < max(sal)

15 )

16 group by deptno
17 having x.deptno = z.deptno

18 and z.sal < max(sal)

19 )
20 group by deptno
21 having z.deptno = a.deptno
22 and a.sal >= max(sal)
23 )

24* order by deptno,sal desc



DEPTNO|ENAME | SAL

--------------------|----------|--------------------

10|PSIT | 6,549

|KING | 5,000

|CLARK | 2,450

20|FORD | 5,000

|SCOTT | 3,000

|JONES | 2,975

30|BLAKE | 2,850

|ALLEN | 1,600

|TURNER | 1,500



9 rows selected.

Hope you can use this idea to solve your problem.

psit

"dj" <dj_pgh@mail.com> wrote:
>
>I was able to get the rank function to work in a plain sql statement, but
>when I try to incorporate it in a PLSQL procedure it fails. I tried to
use
>the rank function in a cursor that retrieves the top three records from
every
>group so that I can insert the records into anther table using a for loop.
> It errors at the cursor. I use the same statement as I did in the SQL
statement
>but it errors out. Has anyone used the rank function in a PLSQL procedure?
> If so, can you please send me an example. Do you know if the rank function
>can be used in a PLSQL procedure?
>
>I greatly appreciate any help or direction you can offer.
>
>Thanks in advance,
>dj

[Clive Bunting]

I've had the same problem. I couldn't work out whether it was the fact that
it was in a package or being used in a cursor that it didn't like. If anyone
has any more information on this I would be grateful if you would share it!

Clive

"dj" <dj_pgh@mail.com> wrote:
>
>I was able to get the rank function to work in a plain sql statement, but
>when I try to incorporate it in a PLSQL procedure it fails. I tried to
use
>the rank function in a cursor that retrieves the top three records from
every
>group so that I can insert the records into anther table using a for loop.
> It errors at the cursor. I use the same statement as I did in the SQL
statement
>but it errors out. Has anyone used the rank function in a PLSQL procedure?
> If so, can you please send me an example. Do you know if the rank function
>can be used in a PLSQL procedure?
>
>I greatly appreciate any help or direction you can offer.
>
>Thanks in advance,
>dj

[LK]

Clive/DJ,

Check out this article, which explains how to utilize the new analytic functions
available in Oracle 8i: RANK():

http://gethelp.devx.com/techtips/oracle_pro/10min/10min1200/10min1200.asp

Also, the RANK() function is probably not supported by the earlier versions
of Oracle. Check the version of your DB.

Hope you find this helpful.

//LK

"Clive Bunting" <clive.bunting@bigfoot.com> wrote:
>
>I've had the same problem. I couldn't work out whether it was the fact that
>it was in a package or being used in a cursor that it didn't like. If anyone
>has any more information on this I would be grateful if you would share
it!
>
>Clive
>
>"dj" <dj_pgh@mail.com> wrote:
>>
>>I was able to get the rank function to work in a plain sql statement, but
>>when I try to incorporate it in a PLSQL procedure it fails. I tried to
>use
>>the rank function in a cursor that retrieves the top three records from
>every
>>group so that I can insert the records into anther table using a for loop.
>> It errors at the cursor. I use the same statement as I did in the SQL
>statement
>>but it errors out. Has anyone used the rank function in a PLSQL procedure?
>> If so, can you please send me an example. Do you know if the rank function
>>can be used in a PLSQL procedure?
>>
>>I greatly appreciate any help or direction you can offer.
>>
>>Thanks in advance,
>>dj
>