6 Replies Latest reply: Feb 2, 2013 3:05 PM by Frank Kulash RSS

    Help Needed

    800849
      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
          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
            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
              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
                Just to add on to what krissco added ;)
                Reminds me of Frank's recent reply @ optimal SQL
                • 5. Re: Help Needed
                  800849
                  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
                    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.