Re: How to retrive Deleted Sequence Number????

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Wed, 03 Nov 2004 08:23:16 -0500
Message-ID: <2us4e8F2eo2euU1@uni-berlin.de>

Holger Baer wrote:

> Serge Rielau wrote:
>

>> Two words: CONNECT BY
>> (footed on DUAL as a row producer)
>>
>> Cheers
>> Serge

>
>
> Come again? What are you talking about?
>
> And who are you answering at all?

The SQL problem in this thread, as I understand it, is how to produce "missing" rows.
The generic way to produce rows out of thin air is to use recursion. In Oracle recursion is done using CONNECT BY, the accepted source to produce a row is DUAL.

Here is what I would write in ANSI SQL.
I'm sure it's easy for an Oracle person to translate to CONNECT BY:

1. Assume we want all rows below the low water mark (FIFO queue):

WITH rec(seqno) AS (SELECT MIN(seqno) -1 FROM SampleTable

                     UNION ALL
                     SELECT seqno - 1 FROM rec WHERE seqno > 1)

SELECT seqno FROM rec;

Assume existing sequence numbers are sparse: WITH rec(seqno) AS (SELECT 1 FROM DUAL

                     UNION ALL
                     SELECT seqno + 1 FROM rec
                      WHERE seqno
                            < (SELECT MAX(seqno) FROM SampleTable))

SELECT seqno FROM rec
EXCEPT
SELECT seqno FROM SampleTable);

Something like that....
Serge Received on Wed Nov 03 2004 - 07:23:16 CST