Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Help: a difficult SQL request
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;
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;
"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