Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 15 total |
Updating a column in a script |
Thu, Mar 5 2015 5:46 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Hi
Newbie on scripting ... I'm trying to write a script that moves all the dates in a table forward one month. A simple UPDATE/SET doesn't work because the date is part of the Primary Key and the SET might move the date on to one that already exists. Error on duplicate key. So I conceived this script which attempts to move the dates in reverse order - moving the last date first, eliminating the chance of a duplicate. ================================== SCRIPT BEGIN DECLARE Readings SENSITIVE CURSOR FOR stmt; PREPARE stmt FROM 'SELECT * FROM MeterReadings ORDER BY ReadingDate DESC'; OPEN Readings; FETCH LAST FROM Readings; WHILE NOT BOF(Readings) DO SET ReadingDate = ReadingDate + INTERVAL '1' MONTH; FETCH PRIOR FROM Readings; END WHILE; END ================================== But it gives an error on the SET:- ---------------------------------- ElevateDB Error #700 An error was found in the script at line 8 and column 9 (Expected array variable name, array variable element name, or variable name expression but instead found "ReadingDate") ---------------------------------- I can't see how to do this SET - I've read the manual ... honest to truly Cheers Jeff ================================== CREATE TABLE "MeterReadings" ( "MeterID" INTEGER NOT NULL, "ReadingDate" DATE DEFAULT CURRENT_DATE NOT NULL, "Reading" INTEGER DEFAULT 0 NOT NULL, "TransNo" VARCHAR(6) COLLATE "UNI", "ReadingType" VARCHAR(21) COLLATE "UNI" DEFAULT 'Actual by Supplier', CONSTRAINT "PrimaryKey" PRIMARY KEY ("MeterID", "ReadingDate") ) |
Thu, Mar 5 2015 11:02 PM | Permanent Link |
Mario Enríquez Open Consult | Hi Jeff,
I might be wrong but I'll give it shot.. It appears to me, that the FETCH statement is missing the INTO part to get the ReadingDate value. The fixed script probably should look like this: SCRIPT BEGIN DECLARE ReadingDate DATE; DECLARE NewReadingDate DATE; DECLARE MeterID INTEGER; DECLARE Readings SENSITIVE CURSOR FOR stmt; DECLARE updStmt STATEMENT; PREPARE updStmt FROM 'UPDATE MeterReadings SET ReadingDate = ? WHERE MeterID = ? AND ReadingDate = ?'; PREPARE stmt FROM 'SELECT MeterID, ReadingDate FROM MeterReadings ORDER BY ReadingDate DESC'; OPEN Readings; FETCH LAST FROM Readings('MeterID', 'ReadingDate') INTO MeterID, ReadingDate; WHILE NOT BOF(Readings) DO SET NewReadingDate = ReadingDate + INTERVAL '1' MONTH; EXECUTE updStmt USING NewReadingDate, MeterID, ReadingDate; FETCH PRIOR FROM Readings('MeterID', 'ReadingDate') INTO MeterID, ReadingDate; END WHILE; UNPREPARE updStmt; CLOSE Readings; END Hope you'll find it helpful. Regards, Mario |
Fri, Mar 6 2015 2:53 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Jeff
I'm not much good on scripts either (looking at Tim's example I think Mario's right), but unless you're intending to do additional work in the loop why are you bothering with it? If it can be done in a simple line of SQL(even if you have to build that line) then use EXECUTE IMMEDIATE sql eg your script could be reduced to EXECUTE IMMEDIATE 'UPDATE MeterReadings SET ReadingDate = ReadingDate + INTERVAL ''1'' MONTH'; I'm guessing you're going to be doing a chunk more in there but I thought I'd throw this in. You may find that a combination of EXECUTE IMMEDIATE and loops ends up being more efficient. Roy Lambert |
Fri, Mar 6 2015 4:22 AM | Permanent Link |
Charles Tyson | Besides the other advice you've been given, if you order your cursor by ReadingDate DESC and begin processing on the last record, you'll be changing the earliest date first and may still encounter your duplicate key problem.
Jeff Cook wrote: Hi Newbie on scripting ... I'm trying to write a script that moves all the dates in a table forward one month. A simple UPDATE/SET doesn't work because the date is part of the Primary Key and the SET might move the date on to one that already exists. Error on duplicate key. So I conceived this script which attempts to move the dates in reverse order - moving the last date first, eliminating the chance of a duplicate. ================================== SCRIPT BEGIN DECLARE Readings SENSITIVE CURSOR FOR stmt; PREPARE stmt FROM 'SELECT * FROM MeterReadings ORDER BY ReadingDate DESC'; OPEN Readings; FETCH LAST FROM Readings; WHILE NOT BOF(Readings) DO SET ReadingDate = ReadingDate + INTERVAL '1' MONTH; FETCH PRIOR FROM Readings; END WHILE; END ) |
Fri, Mar 6 2015 6:00 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Jeff
Woops I missed <<A simple UPDATE/SET doesn't work because the date is part of the Primary Key and the SET might move the date on to one that already exists. Error on duplicate key.>> But shouldn't this be easily testable in a WHERE clause? Roy Lambert |
Sun, Mar 8 2015 1:48 AM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Thanks Mario
I'm on family stuff this weekend and jury duty on Monday to whenever ... When I'm properly back to work I'll pay this the attention that it deserves. Cheers Jeff On 6/03/2015 5:02 p.m., Mario Enríquez wrote: > Hi Jeff, > > I might be wrong but I'll give it shot.. > > It appears to me, that the FETCH statement is missing the INTO part to get the ReadingDate value. > > The fixed script probably should look like this: > > SCRIPT > BEGIN > DECLARE ReadingDate DATE; > DECLARE NewReadingDate DATE; > DECLARE MeterID INTEGER; > DECLARE Readings SENSITIVE CURSOR FOR stmt; > DECLARE updStmt STATEMENT; > > PREPARE updStmt FROM 'UPDATE MeterReadings SET ReadingDate = ? WHERE MeterID = ? AND ReadingDate = ?'; > > PREPARE stmt FROM 'SELECT MeterID, ReadingDate FROM MeterReadings ORDER BY ReadingDate > DESC'; > > OPEN Readings; > FETCH LAST FROM Readings('MeterID', 'ReadingDate') INTO MeterID, ReadingDate; > WHILE NOT BOF(Readings) DO > SET NewReadingDate = ReadingDate + INTERVAL '1' MONTH; > EXECUTE updStmt USING NewReadingDate, MeterID, ReadingDate; > FETCH PRIOR FROM Readings('MeterID', 'ReadingDate') INTO MeterID, ReadingDate; > END WHILE; > > UNPREPARE updStmt; > CLOSE Readings; > END > > Hope you'll find it helpful. > > Regards, > Mario > |
Sun, Mar 8 2015 1:59 AM | Permanent Link |
Jeff Cook Aspect Systems Ltd | On 6/03/2015 8:53 p.m., Roy Lambert wrote:
> > EXECUTE IMMEDIATE 'UPDATE MeterReadings SET ReadingDate = ReadingDate + INTERVAL ''1'' MONTH'; > > I'm guessing you're going to be doing a chunk more in there but I thought I'd throw this in. You may find that a combination of EXECUTE IMMEDIATE and loops ends up being more efficient. Hi Roy What you suggest is exactly what I had. The problem is that it doesn't work. For example:- if the table has just these rows MeterID = 123 ReadingDate = 2015-01-01 ... etc MeterID = 123 ReadingDate = 2015-02-01 ... etc Then run the SQL and the first row becomes:- MeterID = 123 ReadingDate = 2015-02-01 ... etc .... which is a duplicate key (same as the second row) and the SQL falls over! That is why I was trying to do the Script working in the reverse order so that the latest readings were moved forward before the older ones came in and clashed on the keys. Cheers Jeff |
Sun, Mar 8 2015 3:05 AM | Permanent Link |
Jeff Cook Aspect Systems Ltd | On 6/03/2015 10:22 p.m., Charles Tyson wrote:
> Besides the other advice you've been given, if you order your cursor by ReadingDate DESC and begin processing on the last record, you'll be changing the earliest date first and may still encounter your duplicate key problem. > > Ahh! Didn't see that! Thanks Charles Of course I should either * order by DESC and do FIRST/NEXT/EOF or * order by ASC and LAST/PRIOR/BOF Cheers Jeff |
Sun, Mar 8 2015 3:17 AM | Permanent Link |
Jeff Cook Aspect Systems Ltd | On 7/03/2015 12:00 a.m., Roy Lambert wrote:
> > But shouldn't this be easily testable in a WHERE clause? Not sure how ... we need to move every reading record forward one month so we can't exclude records in the WHERE ??? Hi Roy Just to clarify the scenario. This isn't a regular processing issue. We have a demonstration database so that prospective users can download and try our software. Have you ever downloaded a bit of software or viewed sample screens and noticed that the data is in 2003 or somewhere else in the dim and distant past (otherwise defined as Windows version x). Makes you think that nothing is being maintained and the supplier might not even be in business ... My colleague runs a series of SQLs monthly that move the data forward by one month, then puts the database into our demo download. Other tables have fields in the primary key that avoid the problem. Cheers Jeff |
Sun, Mar 8 2015 4:27 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Jeff
Two questions: What do you want MeterID = 123 ReadingDate = 2015-01-01 ... etc MeterID = 123 ReadingDate = 2015-02-01 ... etc to become? Dependent on your answer I think I can come up with the necessary SQL. Where can I download the demo data so I can test my "brilliance"? Roy Lambert |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, May 15, 2024 at 08:40 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |