Path: text.usenetserver.com!out02b.usenetserver.com!news.usenetserver.com!in01.usenetserver.com!news.usenetserver.com!news.tele.dk!news.tele.dk!small.news.tele.dk!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail From: Serge Rielau Newsgroups: comp.databases.oracle.server Subject: Re: Query to return PK sequence gap ?? Date: Mon, 15 Oct 2007 11:02:10 -0400 Lines: 38 Message-ID: <5nhdn4Fi4k48U1@mid.individual.net> References: <1191922422.816953.115940@k79g2000hse.googlegroups.com> <7h1ng3hgaina8q7h4a98b6bno1s7gmu47l@4ax.com> <1191957350.389858.246340@d55g2000hsg.googlegroups.com> <470caea0$0$1342$834e42db@reader.greatnowhere.com> <4gkpg3dcf5akfg4d5cja002oml4c95708h@4ax.com> <470cd3e7$0$16044$5fc30a8@news.tiscali.it> <1jmpg3pjlsbgaqc0dconnjrcb6sajj52e8@4ax.com> <1192186396.307518.227960@e9g2000prf.googlegroups.com> <2gsug3937p63j4etamm2bbv74jonv64347@4ax.com> <1192205992.071697.101280@z24g2000prh.googlegroups.com> <1192226869.411321.38240@t8g2000prg.googlegroups.com> <1192247402.766253.106480@y27g2000pre.googlegroups.com> <1192298945.873331@bubbleator.drizzle.com> <5nd1efFhn4m1U1@mid.individual.net> <1192381453.559637@bubbleator.drizzle.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Trace: individual.net c/DUDSnlbnwwvPtBl/WD6AV6WvrB6qn7e5SuNJb2x1dAtGt8MK Cancel-Lock: sha1:t8zkhio+HFuYjvY749REdRLYQ0E= User-Agent: Thunderbird 2.0.0.6 (Windows/20070728) In-Reply-To: <1192381453.559637@bubbleator.drizzle.com> Xref: usenetserver.com comp.databases.oracle.server:436334 X-Received-Date: Mon, 15 Oct 2007 11:01:57 EDT (text.usenetserver.com) 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