Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 3 of 3 total |
Convert method to a script |
Sat, Sep 26 2009 10:52 PM | Permanent Link |
Peter | Hello
I have a method that uses TEDBQuery to move records that are over a (variable) age to an archive, selected by.. 'SELECT RecdID FROM "TblReg" WHERE (EventDate < :DFrom)' The method loops through the result set, and copies the row to the archive table... INSERT INTO TblArc (RecdID, ClientID, EventCode, EventDate, Note, ArchiveDT) (SELECT RecdID, ClientID, EventDate, Note, Current_TimeStamp FROM TblReg WHERE TblReg.RecdID = :RecdID) If there is a 1004 error (duplicate index) it swallows the error, as somehow the record already exists in the archive, then deletes the the record in TblReg. That is why I prefer to loop through the result set, as sometimes one record amongst the set is already archived. I have tried to put that into a script, with the added feature of first checking to see if that RecdID exists in TblArc, but I have not been able to get it to work. How should this method be laid out, in script form? I am new to declaring cursors and the like, so be gentle. Regards & TIA Peter |
Sun, Sep 27 2009 3:19 AM | Permanent Link |
Uli Becker | Peter,
> I have tried to put that into a script, with the added feature of first checking to see if that RecdID exists in TblArc, but I have not been able to get it > to work. > How should this method be laid out, in script form? I am new to declaring cursors and the like, so be gentle. You can do it in one statement: INSERT INTO TblArc (RecdID, ClientID, EventCode, EventDate, Note, ArchiveDT) (SELECT RecdID, ClientID, EventDate, Note, Current_TimeStamp FROM TblReg WHERE EventDate < :DFrom AND RecdID not in (SELECT RecdID from TblArc)) In order to delete duplicate records just use: DELETE FROM TblReg where RecdID in (select RecdID from TblArc) Regards Uli |
Mon, Sep 28 2009 6:30 AM | Permanent Link |
Peter | Uli
Of course - why didn't I think of that? Thanks, again Regards Peter |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |