1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Retrieve the last and second last dates in a cursor to display in a report

Discussion in 'SQL PL/SQL' started by NewApps, May 16, 2011.

  1. NewApps

    NewApps Active Member

    Likes Received:
    Trophy Points:

    I need to retrieve 2 dates from a table and use them in an oracle report (9i)

    The field is STAMP_JOB_CLOSE and stores a date.

    Below is an example of three dates associated with my query:


    I have a formula column on my report (CF_LAST_YEAR_SERVICE) that uses the following statement to retrieve the date 03/08/2010 onto my report for LAST YEAR SERVICE

    function CF_LAST_YEAR_SERVICEFormula return Char is

    cursor c1 is
    select stamp_job_close
    from envirocallsc e, rhm_job_additional_info i
    where e.enviroref = i.reference
    and i.division_code = 2
    and i.type_code = 'WORKS_TYPE'
    and to_char(additional_info) = 'INSPECTION_ANNUAL'
    and service_category = 10
    and mtg_id = :mtg_id
    and stamp_job_close is not null
    and status = 'F'
    order by stamp_job_close desc;

    v_last_year_service varchar2(500);
    v_2ndlast_year_service varchar2(500);

    open c1;
    fetch c1 into v_last_year_service;
    close c1;
    return v_last_year_service;

    A request has been made to use a placeholder column to store the second last date (SECOND LAST YEAR SERVICE) - (in this case 08/05/2009) and this is where I am having problems.

    How do I use the placeholder column to store the next date and how do I get it.

    I've been using Oracle Reports for just over a week now, so I apologise in advance for being a bit vague.

  2. zargon

    zargon Community Moderator Forum Guru

    Likes Received:
    Trophy Points:
    Aurora, CO
    You're certain that in every case your function only returns one date? Have you added more data to a test table to verify your function works as expected? I see a problem with your code as you could receive an 'exact fetch returns more than requested number of rows' error. Please test this before you go any further.
  3. NewApps

    NewApps Active Member

    Likes Received:
    Trophy Points:
    Hi and thanks for your response.

    I've ran a query for a different Mtg Id Value (the last example of 3 dates shown above used mtg id = 654429, and I've ran another using 654430 which returns 30/08/2010, 27/04/2009, 10/05/2007 and the report only shows the 30/08/2010 which is what I want. I'm looking at how to have the next value in a placeholder column (I think).

    Thanks again.