From: "kazelot" <kazelot@kulci.prv.pl>
Newsgroups: comp.databases.oracle
Subject: query suggestion pls. the windowing thing.
Date: Fri, 8 Aug 2003 14:15:54 +0200
Organization: Portal Gazeta.pl -> http://www.gazeta.pl
Lines: 228
Message-ID: <bh04c7$hi1$1@inews.gazeta.pl>
NNTP-Posting-Host: honza-m.gda.computerland.pl
X-Trace: inews.gazeta.pl 1060345031 17985 195.116.44.3 (8 Aug 2003 12:17:11 GMT)
X-Complaints-To: usenet@agora.pl
NNTP-Posting-Date: Fri, 8 Aug 2003 12:17:11 +0000 (UTC)
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Priority: 3
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-User: kazelotbrain
X-MSMail-Priority: Normal


Hello!

I have a problem with my SQL query.


First, let's set up the stages.
-------------------------------

1. 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