Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help: a difficult SQL request

Re: Help: a difficult SQL request

From: Bill Davison <wdavison_at_fpcc.net>
Date: Sun, 23 Feb 2003 01:35:03 -0700
Message-ID: <3e5887ae@news.starnetinc.com>


Would something like this do the job? Remember to type 'set serveroutput on' before executing.

DECLARE

  ctr number := 0;
  prev_vol number;
  start_time date;

  CURSOR csr IS
    select time, volume from test order by time; BEGIN
  FOR x IN csr LOOP
    IF x.volume = prev_vol THEN
      ctr := ctr+1;
    ELSE
      IF ctr > 7  THEN
        dbms_output.put_line('Starting at '||to_char(x.time,'MM-DD-YY
HH:MI')||' volume did not change for '||ctr||' minutes');
      END IF;
      ctr := 1;
      prev_vol := x.volume;
      start_time := x.time;

    END IF;
  END LOOP;
/* need the following in case last row is part of a series of unchanging volumes */
  IF ctr > 7 THEN
    dbms_output.put_line('Starting at '||to_char(start_time,'MM-DD-YY HH:MI')||' volume did not change for '||ctr||' minutes');   END IF;
END;
/

"charlie cs" <cs3526(no-spam)@yahoo.com> wrote in message news:sZV5a.9581$ep5.7067_at_nwrddc02.gnilink.net...
> 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
> >
>
>
Received on Sun Feb 23 2003 - 02:35:03 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US