query suggestion pls. the windowing thing.
Date: Fri, 8 Aug 2003 14:15:54 +0200
Message-ID: <bh04c7$hi1$1_at_inews.gazeta.pl>
Hello!
I have a problem with my SQL query.
First, let's set up the stages.
- oracle version SQL> select * from v$version;
BANNER
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for IBM/AIX RISC System/6000: Version 8.1.7.4.0 - Production NLSRTL Version 3.4.1.0.0 - Production
2. the table
CREATE TABLE P
(
ID NUMBER(10) NOT NULL, EQN VARCHAR2(25) NOT NULL, NR NUMBER(10) NOT NULL, STAN NUMBER(1) NOT NULL);
3. the data
declare
i number;
begin
for i in 1 .. 10 loop
insert into p(id, nr, eqn, stan) values (i, i-1, 'aa'||(i-1), 0);
end loop;
end;
/
commit;
FIRST VERSION
Ok. In the first version of our application I had a table that hold
continuos numbers, starting from 0.
My task was to write sql (sinlge query) to find all continous blocks of data
(of given length and given condition)
and return the first element for the blocks.
For example, I have to find 5 rows that have stan = 0 and are next to
eachother.
The ilustration below shows the mechanism
(the digits represent the numbers of a row -- nr column,
|---| is the "results window")
<pre>
|---|....
0123456789
..|---|...
0123456789
.....|---|
0123456789
</pre>
If all the rows match the condition (stan = 0) the query should return NR
--
0
1
2
4
5
6 is not to be returned, because the block starting with 6 is 4 items long,
etc.
The query looks like this:
SELECT nr, eqn
FROM (SELECT ID, nr, eqn,
SUM(stan) OVER(ORDER BY nr ROWS BETWEEN CURRENT ROW AND 5 -
1 FOLLOWING)
AS zajety
FROM p)
WHERE nr <= (SELECT (MAX(nr) - 5 + 1)
FROM p) AND zajety = 0
ORDER BY nr;
NR EQN
---------- ------------
0 aa0
1 aa1
2 aa2
3 aa3
4 aa4
5 aa5
If the nr 7 had stan = 1, the query should only return nr = 0, 1, 2
<pre>
0123456789
|---|..x..
.|---|.x..
..|---|x..
</pre>
Let's see:
UPDATE p
SET stan = 1
WHERE nr = 7;
commit;
SQL> SELECT nr, eqn
2 FROM (SELECT ID, nr, eqn,
3 SUM(stan) OVER(ORDER BY nr ROWS BETWEEN CURRENT ROW
AND 5 - 1 FOLLOWING)
4
AS zajety
5 FROM p)
6 WHERE nr <= (SELECT (MAX(nr) - 5 + 1)
7 FROM p) AND zajety = 0
8 ORDER BY nr;
NR EQN
---------- -------------------------
0 aa0
1 aa1
2 aa2
SECOND VERSION
--------------
So far so good. All worked fine. But then the requirements changed. Now
1. the numbers in the table may be not continous
2. the first number can be <> 0
My english is very poor, so again I use an ilustration
(dots mean that the row is present in the table,
x means stan = 1, |---| is the result window)
<pre>
0000000000111111111122222
nr: 0123456789012345678901234
....x.......x........
|---|
|---|
|---|
|---|
|---|
|---|
0000000000111111111122222
nr: 0123456789012345678901234
</pre>
resulting rows should be: 9, 10, 11, 17, 18, 19, 20
Let's prepare data from this ilustration.
delete from p;
declare
i number;
begin
for i in 4 .. 24 loop
insert into p(id, nr, eqn, stan) values (i+1, i, 'aa'|| i, 0);
end loop;
end;
/
update p
set stan = 1
where nr in (8, 16);
commit;
And test the query gives anticipated results:
NR EQN
---------- ----------
9 aa9
10 aa10
11 aa11
17 aa17
18 aa18
19 aa19
20 aa20
But when you delete the rows with stan = 1 you got rubbish. "Rows between"
don't work anymore.
delete from p where stan = 1 ;
commit;
NR EQN
---------- ------
4 aa4
5 aa5
6 aa6
7 aa7
9 aa9
10 aa10
11 aa11
12 aa12
13 aa13
14 aa14
15 aa15
17 aa17
18 aa18
19 aa19
20 aa20
21 and above are added because max() "lies".
SUGGESTION PLEASE
-----------------
Is there any way to do what I want in one query?
Thanks for any information,
kazelot
Received on Fri Aug 08 2003 - 14:15:54 CEST