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")
)

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

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

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
)

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

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
>

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

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

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

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