Re: Question on SQL query ?
Date: Tue, 09 Nov 93 12:01:19 PST
Message-ID: <2boj91$bv4_at_reeve.research.aa.wl.com>
In article <CG8D92.v7q_at_austin.ibm.com>, <rrairao_at_austin.ibm.com> writes:
> Hi Sql users :
>
> I have a question for writing a Sql query.
> I have a table with several fields including a date field :
>
> e.g. Table1
> fld1 fld2 .... .... ..... code dateo
> [data] .... ... .. 10 01-mar-93
> ... 60 10-oct-93 and soon
>
> I have created a similar table ( tablehist ) with same fields to keep an
> archive.
>
> I would like to grab the records from table1 between sysdate ( today )
> and 3-months old ( from today ) based on dateo field in table1 and
> based on code = 'xx' and insert those records in tablehist data.
> I would like to delete those rec's from table1 in same code .
>
> Can I associate a trigger in a form to do this task!
>
> The sql code should run on any given day.
>
> Any ideas and help would be appreciated. Please post on this group.
>
> Thanks.
> Raj
I would use PL/SQL to prevent errors. If you do the inserts and deletes in
separate SQL statements you could delete even though insert has failed.
Here is a rough draft - it may give errors but you get the idea.
I assume that fld1 is a primary key to the table.
declare
cursor cx1 is
select fld1, code, date0
from table1
where date0 between add_months(sysdate,-3) and sysdate
and code = '45';
cx1row cx1%rowtype;
begin
open cx1;
fetch cx1 into cx1row;
while cx1%FOUND loop
insert into tablehist
values(cx1row.fld1, cx1row.code, cx1row,date0);
delete from table1
where fld1 = cx1row.fld1;
end loop
exception
/* add any desired exception handlers */ end
Matteo diTommaso | ditommm_at_aa.wl.com | (313) 996-7148 | This space for rent.Full disclaimer available via e-mail! | Received on Tue Nov 09 1993 - 21:01:19 CET