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 'Oracle Forms and Reports' 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.