Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 7 of 7 total |
SQL help please.. |
Tue, Jun 12 2018 5:22 AM | Permanent Link |
Ian Branch | Hi Team,
I have already copied selected records from one table to another.. {sql} insert into ajobs select * from jobs where status = 'CA' or (status = 'CO' and paid = true and (date_out > date '2000-01-01' and date_out < date :EndDate)) {sql} Deleting those copied records from jobs is not a problem. Now I need to copy all records from table 'Items' to 'AItems' that have the same job # as any of those copied into ajobs. Then delete those copied into aItems, from Items. Help! Regards & TIA, Ian |
Tue, Jun 12 2018 7:17 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ian
Not to difficult (as long as my understanding and guess is coirrect) INSERT INTO AItems SELECT * from Items WHERE Items.Job# IN (SELECT Job~ FROM AJobs) followed by DELETE FROM Items WHERE itemid IN (SELECT itemid from AItems) I'm guessing there's a unique id for Items/AItems Untested since I don't have your file structures / data Roy Lambert |
Tue, Jun 12 2018 5:59 PM | Permanent Link |
Ian Branch | Hi Roy,
As simple as that.. Clearly I have a long way to go with SQL as well. All good thank you. Regards, Ian |
Tue, Jun 12 2018 7:37 PM | Permanent Link |
Ian Branch | So, extending the SQL and having a play with a script I have created the following in EDBMgr to put into a EDBScript..
{sql} SCRIPT BEGIN EXECUTE IMMEDIATE 'insert into ajobtickets select * from jobtickets where jobstatus = ''CA'' or (jobstatus = ''CO'' and paid = true and (date_out > date ''2000-01-01'' and date_out < :EndDate))'; execute immediate 'delete * from JobTickets where jobstatus = ''CA'' or (jobstatus = ''CO'' and paid = true and (date_out > date ''2000-01-01'' and date_out < :EndDate))'; execute immediate 'INSERT INTO ALineItems SELECT * from LineItems WHERE LineItems.JobNo IN (SELECT JobNo FROM AJobTickets)'; execute immediate 'DELETE FROM LineItems WHERE JobNo IN (SELECT JobNo from ALineItems)'; execute immediate 'INSERT INTO ATechData SELECT * from TechData WHERE TechData.JobNo IN (SELECT JobNo FROM AJobTickets)'; execute immediate 'DELETE FROM TechData WHERE JobNo IN (SELECT JobNo from ALineItems)'; END {sql} It Prepares OK but when run I get the following error.. "ElevateDB Error #700 An error was found in the statement at line 4 and column 22 (Expected expression but instead found The only ':' is for the Parameter :EndDate and it is probably falling over because the parameter isn't defined. I have the Parameters tab open but no way to set the parameter value. Is it possible to set parameters in a script like this? Regards, Ian |
Tue, Jun 12 2018 9:22 PM | Permanent Link |
Ian Branch | I think I have started the head banging.
So. Found out how to get the Parameters set. Getting closer but still having issues. Given the following.. {sql} SCRIPT (IN EndDate Date) BEGIN EXECUTE IMMEDIATE 'insert into aUsersLog select * from userslog where CAST(DTG as DATE date format ''yyyy-mm-dd'') < ?'; execute immediate 'delete from userslog where CAST(DTG as DATE date format ''yyyy-mm-dd'') < ?'; END {sql} n.b. DTG is a TimeStamp field. And that EndDate has been set as '2018-02-01' as a Date type, In mode, and that there are records in UsersLog that are prior to 2018-02-01, and the Prepare & Execute appear to be successful, nothing appears in AUsersLog and the relevant records are not deleted from UsersLog.?? Help!! Regards & TIA, Ian |
Wed, Jun 13 2018 2:39 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ian
First point is just use CAST(DTG AS DATE) no need for the format stuff Second point (I think I'm right) is that whilst you've put the placeholder for the parameter you haven't told it which parameter to use. Try SCRIPT (IN EndDate DATE) BEGIN EXECUTE IMMEDIATE 'insert into aUsersLog select * from userslog where CAST(DTG as DATE) < ?' USING EndDate; execute immediate 'delete from userslog where CAST(DTG as DATE) < ?' USING EndDate; END Roy Lambert |
Wed, Jun 13 2018 3:45 AM | Permanent Link |
Ian Branch | Hi Roy,
Ah Ha! Thank you. A fog lifts. All good now. Regards & Tks again, 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 |