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: Query to return PK sequence gap ??

Re: Query to return PK sequence gap ??

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Mon, 15 Oct 2007 11:02:10 -0400
Message-ID: <5nhdn4Fi4k48U1@mid.individual.net>


DA Morgan wrote:
> Serge Rielau wrote:

>> DA Morgan wrote:
>>> Gokul wrote:
>>>> SQL> select * from (select level from dual connect by level < 5);
>>>>
>>>>      LEVEL
>>>> ----------
>>>>          1
>>>>          2
>>>>          3
>>>>          4
>>>>
>>>> SQL>
>>>>
>>>
>>> Works properly in 11.1.0.6.
>> Shouldn't this return a cycle error?
>> (because the same row in DUAL is returned multiple times).
>>
>> Cheers
>> Serge

>
> CONNECT BY specifies a condition that identifies the relationship
> between parent rows and child rows of a hierarchy. It isn't, in fact,
> returning the same row ... it is returning the level. Each recursion
> is at a different level.

With that definition, how do you define a cycle? In order to define "cycle" one must define the notion of "same". If "same" is not based on the rowid (or a primary key) then what you are saying that "sameness" is defined by the the CONNECT BY predicates?

Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Received on Mon Oct 15 2007 - 10:02:10 CDT

Original text of this message

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