Re: Brain freeze doing Date comparisons
- From: Jared Still <jkstill@xxxxxxxxx>
- To: wbfergus@xxxxxxxxx
- 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
)
/
begin
for i in 0..9
loop
insert into bill values (i,current_timestamp - ( ( (10-i) /
24))+(1/24));
end loop;
end;
/
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))
/
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
On Thu, Feb 26, 2009 at 6:57 AM, Bill Ferguson <wbfergus@xxxxxxxxx> 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
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
