This discussion is archived
6 Replies Latest reply: Feb 2, 2013 1:05 PM by Frank Kulash RSS

Help Needed

800849 Newbie
Currently Being Moderated
Hi ,

We have a table and it contains a column like last_updated_date and this column is updated every 6 hrs interval through some concurrent program. and we need the data which are older more than 100 days.

So could you please help me how to get the data which are older than 100days.

Please help me.

Regards
das
  • 1. Re: Help Needed
    Etbin Guru
    Currently Being Moderated
    Maybe
    <tt><br>
    <strike>where trunc(sysdate) - your_date_column > 100</strike><br>
    </tt>
    Regards

    Etbin

    Edited by: Etbin on 1.2.2013 18:18
    sorry misread your requirement <tt>last_updated_date</tt> most probably means last updated date :(
  • 2. Re: Help Needed
    Hoek Guru
    Currently Being Moderated
    When your column is of DATE datatype, then you can try something like:
    select ...
    from   ...
    where  last_updated_date < (sysdate-100);
    Use TRUNC if you do not need to take the time component into account...
  • 3. Re: Help Needed
    krissco Newbie
    Currently Being Moderated
    Just to add on to what Hoek said.

    If you need to use trunc() make sure it is on the sysdate, not your column value, or you will ruin your query plan if it is using an index on the last_updated_date column.
    where last_updated_date < trunc(sysdate-100)
    NOT
    where trunc(last_updated_date+100) < trunc(sysdate)
  • 4. Re: Help Needed
    Hoek Guru
    Currently Being Moderated
    Just to add on to what krissco added ;)
    Reminds me of Frank's recent reply @ optimal SQL
  • 5. Re: Help Needed
    800849 Newbie
    Currently Being Moderated
    Hi,

    Thanks for your update.but the thing is that the concurrent job is running on every 6 hrs basis and it will update all the record on daily basis. so thedata always update as SYSDATE.

    and it will update the status column as either OBSOLETE or ACTIVE.


    For example .

    here is my table called xxcss_test_customer and it contains the following 4 columns

    id , status , service level , last updateddate


    There are some concurrent program and it is running on every 6hrs and it will update the status column as Active or OBSOLETE and it will update the last_updated_date column as
    sysdate and then how we can identify whether the service level age is more than 100 days as OBSOLETE.means the service level is more than 100 days as OBSOLETE.


    Please suggest.

    regards
    Das
  • 6. Re: Help Needed
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Das,
    797846 wrote:
    ... There are some concurrent program and it is running on every 6hrs and it will update the status column as Active or OBSOLETE and it will update the last_updated_date column as
    sysdate and then how we can identify whether the service level age is more than 100 days as OBSOLETE.means the service level is more than 100 days as OBSOLETE.
    Oracle does not automatically keep track of when every change was made. If you need that information, you have to store it yourself.
    Add another DATE column, service_level_date, and update it only when the service level changes.
    You can write a trigger to auotmatically populate service_level_date whenever you INSERT or UPDATE a row.

    Once you have the service_level_date, you can use it the way Etbin, Koek and Krissco suggested earlier.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points