Re: Help: a difficult SQL request

charlie cs wrote:

> Thanks for your answer, Deniel.
>
> First of all, there is a mistake in my previous posting, what I want is the
> records which DOES NOT change for at least 8 minutes.
>
> 2nd of all, it is not because we did not test it thouroughly. Our sensors
> collect the data every minute, and sensors can not function well all the
> time, it is inevitable that there will be some malfunction from time to
> time. Arbituarily, we set it that data DOES NOT change for continuous 8
> minute will be consided invlid. It is our DBA's responsiblity to find those
> INVALID data and through them out of our database.
>
> Forget about sensor, malfunction, etc, my question is simple, for a table
> like
> > > TIME VOLULME
> > > --------------------
> > > 1:01 12
> > > 1:02 13
> > > 1:04 15
> > > 1:05 3
> > > .......
> > > 1:10 3
> > > 1:11 3
> > > 1:12 3
> > > 1:13 3
> > > 1:14 3
> > > 1:15 3
> > > 1:16 3
> >> 1:17 3
>
> How could I find the data that IS THE SAME, in other words, that DOES NOT
> CHANGE for 8 minutes? Which is my example, is from 1:10 to 1:17?
>
> Thanks again for your help.
>
> "DA Morgan" <damorgan_at_exesolutions.com> wrote in message
> news:3E57D0D8.6A1B3083_at_exesolutions.com...
> > charlie cs wrote:
> >
> > > we have a huge table, with data coming in every minute, table will be
> like
> > > this
> > > TIME VOLULME
> > > --------------------
> > > 1:01 12
> > > 1:02 13
> > > 1:04 15
> > > 1:05 3
> > > .......
> > >
> > > but sometimes the data collector went bad, so the volume will inherit
> the
> > > privious one, the data will be like
> > >
> > > TIME VOLULME
> > > --------------------
> > > 1:01 12
> > > 1:02 13
> > > 1:04 15
> > > 1:05 3
> > > .......
> > > 1.10 3
> > > 1.11 3
> > > 1.12 3
> > > 1.13 3
> > > 1.14 3
> > > 1.15 3
> > > 1.16 3
> > > ...............
> > >
> > > How could we find those records which did change for at least 8 minutes?
> > >
> > > Thanks for your help
> >
> > If I understand your question correctly, which is highly suspect as your
> post
> > is somewhat ambiguous, you can't except by manually using log miner to
> review
> > the log files.
> >
> > In the future you might be able to do so with a trigger and an audit
> trail.
> > But based on what you have posted my advice would be to not put things
> into
> > production without better testing.
> >
> > Daniel Morgan
> >

The following offered with the intent of being helpful ... not to aggrevate.

Your missing information doesn't change the advice. The only way to know what has not been changed is the same thing as what is required to know what has been changed. You must have an audit trail.

The fact that you know sensors make mistakes is sufficient cause to know that you must design your application, and test your code, to handle that situation. You can't blame the problem on sensor readings as the issue of erroneous readings from any sensors is predictable.

One solution would be to write your code such that all updates to existing records are flagged. Another would be to log all sensor readings chronologically as inserts as well as to keep your existing structure with updates. Personally ... and this is based on way to little information ... my instinct would be to redesign to handle anticipated errors using user defined exception handling. Here's an example of what I am thinking assuming all reads contain a timestamp field containing date and time a record was last inserted/updated.

BEGIN
   SELECT COUNT(*)
   INTO x
   FROM table
   WHERE record was updated in some defined time period

   IF x > 0 THEN

      RAISE user_defined_exception;
   ELSE
      UPDATE the record
   END IF;
EXCEPTION
   WHEN user_defined_exception THEN

      do something to handle a possible sensor error; END; Hope this helps even if the specific example does not meet your need.

Daniel Morgan Received on Sun Feb 23 2003 - 12:20:45 CST