Re: HELP! PL\SQL experts unite! Or I'll get fired!

From: Steve mouatt <steve_at_bedrockcomputers.demon.co.uk>
Date: 1997/12/10
Message-ID: <348E67FC.5DD4_at_bedrockcomputers.demon.co.uk>#1/1

Mark Seto wrote:
>
> ok, getting fired is an exageration, but I could use help with the
> following problem:
>
> Assume a table which contains raw data from a source system. This table
> contains service calls of machines that contain meter readings. fields
> in this table:
> - Call # (PK)
> - Serial # of machine that was serviced
> - Call meter reading (number)
> - Call Date
> - technician (who did the service call)
>
> I need to report the change in meter reading from the previous cal of
> the machinel. The problem is I cannot reference the last call properly

There are a number of ways of doing this but probably the easiest is to have open a cursor on a select statement that returns the records for the machine that you are interested sorted in descending order. The first entry that is returned will be you latest one the next row will be the previous one. If you need to update the latest record then you can retain the rowid and use this to do it. The code would look something like below.

DECLARE
   latest_row ROWID;
   prev_row ROWID;
   latest_reading NUMBER;
   prev_reading NUMBER;

   CURSOR mycur IS

      SELECT rowid, meterreading 
      FROM mytable
      WHERE SERIAL='M01'
      ORDER BY read_date DESC;

BEGIN
   OPEN mycur;
   FETCH mycur INTO latest_row,latest_reading;    IF mycur%FOUND
   THEN

      FETCH mycur INTO prev_row,prev_reading;
      IF mycur%FOUND
      THEN
         UPDATE mytable
         SET meterchange=latest_reading-prev_reading
	 WHERE rowid=latest_row;
      END IF;

   END IF;
   CLOSE mycur; Received on Wed Dec 10 1997 - 00:00:00 CET