Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 3 of 3 total |
Moving records from Tables A to B then from C to D based on those moved to B..... |
Thu, May 31 2018 7:30 PM | Permanent Link |
Ian Branch | Hi all you SQL Gurus,
My history has been pretty much all TTable based. Very little to no SQL. I have some awareness of simple stuff but lose my way once it gets complex. Given that EDB seems to be very much SQL orientated I figure I gotta get my skill level up. Anyway, I have a short term issue I need help with. Scenario: Two sets of tables - Live & Archve. 1. JobTickets & AJobTickets. 2. Lineitems & ALineitems. 3. TechData & ATechData. 4. UsersLog & AUsersLog ..... etc. JobTickets, LineItems & TechData have a common field 'Job#'. Yes I know 'Job#' doesn't work att. Doesn't recognise the '#'. Tim is working on it. Job# currently appears as 'Job_'. UsersLog to AUsersLog is easy. The first task is to set the 'filter' for JobTickets then transfer those records to AJobTickets. {sql} INSERT INTO AJobTickets SELECT * FROM JobTickets WHERE JobStatus = 'CA' or ((JobStatus = 'CO') and (Paid = True) and (date_out < DATE '2018-05-01')) {sql} This works fine. The date_out value to be tested will be a Parameter. I can then easily do.. {sql} UPDATE JobTickets SET Archive = True WHERE JobStatus = 'CA' or ((JobStatus = 'CO') and (Paid = True) and (date_out < DATE '2018-05-01')) {sql} What I now need to do, which I can at the TTable level, but prefer to do with SQL: 1. Copy all records from LineItems to ALineItems where JobTickets.Archive = True. Job# is the common field. Multiple LineItem records per Job# 2. Delete those LineItem records copied. 2. Copy all records from TechData to ATechData where JobTickets.Archive = True. Job# is the common field. One record per Job #. 3. Delete those TechData records copied. 4. Delete all those records in JobTickets with the Archive flag set. Appreciate any proposals. Regards & TIA, Ian |
Fri, Jun 1 2018 3:47 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ian
Quick comment first >JobTickets, LineItems & TechData have a common field 'Job#'. Yes I know 'Job#' doesn't work att. Doesn't recognise the '#'. Tim is working on it. Job# currently appears as 'Job_'. why not use JobNo since # generally translates to number - its a bit more descriptive Right onwards: You now need to start investigating the wonderful world of triggers and transactions. I still don't make enough use of them but I also was more into navigational (table) methods of doing things. Triggers are essentially SQL scripts that are executed by the system in response to a specific event eg BEFORE UPDATE, AFTER DELETE etc If you think of them as the equivalent to the events on a TEDBTable you can't go far wrong. You can make things as simple or as complex as you want. I'd guess that whatever you do with JobTickets / AJobTickets also affects Lineitems & ALineitems. so here goes 1. start a transaction 2. copy ONE job ticket to archive 3. complete transaction or if error rollback I'm suggesting one ticket at a time because it keeps the transaction size down and hence the amount of time the tables are locked to a minimum. It also means that you won't run out of RAM. Step 2 is obviously the important bit and I'd write an AFTER INSERT trigger for AJobTickets (example below - it might even work) which will a) use the NEW id value (Job# ?) to copy the line items from LineItems to ALineItems b) Delete the JobTicket I'd also write an AFTER INSERT trigger for ALineItems which would a) delete the LintItem Roy Lambert TRIGGER "DeleteOldJob" BEGIN EXECUTE IMMEDIATE 'INSERT INTO ALineItems SELECT * FROM LineItems WHERE Job_ = ' + CAST(NEWROW.Job_ AS VARCHAR(10)); EXECUTE IMMEDIATE 'DELETE FROM JobTicket WHERE Job_ = ' + CAST(NEWROW.Job_ AS VARCHAR(10)); END |
Fri, Jun 1 2018 4:53 AM | Permanent Link |
Ian Branch | Hi Roy,
Hmmm. Lots of food for thought there. Let me digest and ruminate on it. Regards, Ian |
This web page was last updated on Saturday, May 4, 2024 at 09:18 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |