Re: Brain freeze doing Date comparisons
Date: Thu, 26 Feb 2009 10:14:40 -0800
If I understand the question properly, here's a first take on it.
drop table bill;
create table bill (
dep_id number, update_date timestamp
for i in 0..9 loop insert into bill values (i,current_timestamp - ( ( (10-i) / 24))+(1/24)); end loop;
select * from bill;
with maxkey as (
select max(dep_id) dep_id from bill
select b.dep_id, b.update_date
from bill b, maxkey m
where b.dep_id = m.dep_id
and b.update_date >= (systimestamp-(1/24)) /
Certifiable Oracle DBA and Part Time Perl Evangelist
On Thu, Feb 26, 2009 at 6:57 AM, Bill Ferguson <wbfergus_at_gmail.com> wrote:
> Hi all,
> Dealing with management has made my brain freeze and I'm stumped.
> I have a table as :
> dep_id number -- my primary key for most tables in system
> xml_clob --manually generated clob field
> update_date timestamp -- when this record was added
> updated_by varchar2(30) --userid of person
> Management wants a 'robust' auditing system in place, so my approach
> is if a piece of data in any table for a particular record has been
> changed or added, etc., then I'll copy my current xml_clob field from
> my master search table (which is always up-to-date) into this other
> table. But, I only want to do it if this hasn't been done within the
> last hour. So, in essence, at the most, I'll only have one record in
> this table for every 60 minutes.
> So, my problem is selecting only the most current record in this table
> (if one exists), and checking to see if it was added within the last
> 60 minutes.
> This is for Oracle 11 on Windows.
> Thanks for your help.
> -- Bill Ferguson