pl/sql table/cursor question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: pl/sql table/cursor question

  1. #1
    Join Date
    Jan 2002
    Posts
    7

    pl/sql table/cursor question

    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.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483
    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;


  3. #3
    Join Date
    Sep 2001
    Posts
    37
    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

  4. #4
    Join Date
    May 2002
    Posts
    108

    Dunno if u will like it

    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
    Never give up !

    Nanda Kumar - Vellore

  5. #5
    Join Date
    May 2002
    Posts
    108

    Another Soln.

    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
    Never give up !

    Nanda Kumar - Vellore

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width