pl/sql table/cursor question

[Inessa]

Hi Gurus,

I am writing a stored procedure that should calculate
the time person is spending with the most recent title

hr_table

personid, title, date_started, date_finished
1 associate 01/01/2003 01/01/2005
1 associate 01/01/2000 31/12/2002
1 assistant 01/01/1997 31/12/2000
2 assistant 01/01/1998 01/01/2008
3 associate 01/01/1998 01/01/2008

Let's say person can have two titles: associate and assistant .

Usually assistant is preceded to associate, however
sometimes there are cases when person is only in the associate position or in the assistant position.

is anyway to do it without


select title into c_title
from hr_table h
where h.personid=pid and
date_finished = (select max(date_finished) from hr_table h1 where
h1.personid = pid))

How can I make something like this

CURSOR my_time IS
SELECT personid, title, date_started, date_finished
FROM hr_table h WHERE h.personid=pid;

to look only into the most recent titles?

I would like to use cursor and/or pl/sql tables

Any input will be greatly appreciated.

Thank you in advance,
C.

[LKBrwn_DBA]

Try this:

CURSOR my_time IS
SELECT personid, title, date_started, date_finished
FROM hr_table h
WHERE h.personid=pid AND ROWNUM = 1
ORDER BY date_finished desc;

[ccastaneda]

Originally posted by LKBrwn_DBA

CURSOR my_time IS
SELECT personid, title, date_started, date_finished
FROM hr_table h
WHERE h.personid=pid AND ROWNUM = 1
ORDER BY date_finished desc;

This query seems right but its not..

Using a ROWNUM=1 condition in a where clause of a query that involves sort operations (ORDER BY) may not give the results that you expect.

This situation happens because the ROWNUM value is assigned to the rows that compound the result of the query BEFORE the sort operation.

For example if you wanna analyze the result of this query for person id 1.

The result before the sort operation could be like this..

ROWNUM personid, title, date_started, date_finished
1 1 assistant 01/01/1997 31/12/2000
2 1 associate 01/01/2000 31/12/2002
3 1 associate 01/01/2003 01/01/2005

and after the sort will be

ROWNUM personid, title, date_started, date_finished
3 1 associate 01/01/2003 01/01/2005
2 1 associate 01/01/2000 31/12/2002
1 1 assistant 01/01/1997 31/12/2000

Your query will give as result:
assistant 01/01/1997 31/12/2000

when the real result is:
3 1 associate 01/01/2003 01/01/2005

[nandu]

Hi

CURSOR X IS
SELECT a.personid, a.title, a.date_started, a.date_finished
FROM hr_table a, ( Select personid, max(date_finished) date_finished from hr_table GROUP BY personid) b
WHERE a.personid=b.personid AND a.date_finished = b.date_finished

This query will work. It will not have the overhead as equivalent to correlated sub-query. But dunno if u will like it !

HTH.

Cheers
Nandu

[nandu]

You can also use the following query in the cursor which will
work in versions 8i and beyond

Select * from (SELECT personid, title, date_started, date_finished, dense_rank() over (partition personid order by date_finished desc) SlNo FROM hr_table)
WHERE SlNo = 1

Cheers
Nandu