Re: Filling in "gaps" in data

On Nov 5, 4:00 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Nov 4, 2:27 am, William Robertson <williamr2..._at_googlemail.com>
> wrote:
>
>
>
> > On Nov 1, 12:56 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
>
> > > It is a bit interesting that a search of the Oracle documentation for
> > > "connect by level" returns 0 results.  A Metalink search for the same
> > > phrase only returns a couple hits, most of which report bugs.  I first
> > > saw the method of using connect by level used in this Usenet forum,
> > > and I recall having difficulty understanding why it worked
>
> > There is no special CONNECT BY LEVEL syntax, which is why this exact
> > phrase is not documented. There is just "CONNECT BY [condition]". The
> > documentation then links to "Conditions" ("A condition specifies a
> > combination of one or more expressions and logical (Boolean) operators
> > and returns a value of TRUE, FALSE, or UNKNOWN.") I personally prefer
> > ROWNUM <= n, but it could be any condition you like that is true until
> > you want it to be false, e.g.
>
> > SELECT ROWNUM FROM dual CONNECT BY DBMS_RANDOM.VALUE(0,1) > 0.5
>
> William,
>
> Your explanation and example make perfect sense, if you read the above
> as connect child rows to parent rows until the specified condition is
> false.  The documentation does not seem to be quite as clear:http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/querie...
> "CONNECT BY specifies the relationship between parent rows and child
> rows of the hierarchy."
>
> Note the term "relationship", which seems to indicate that the
> relationship between child rows and parent rows must be defined.
>
> The samples in the documentation all include the "PRIOR" syntax, which
> might be part of the source of the confusion (at least it was for me
> as I was first trying to understand this syntax).  From the same link:
> "PRIOR is most commonly used when comparing column values with the
> equality operator. (The PRIOR keyword can be on either side of the
> operator.) PRIOR causes Oracle to use the value of the parent row in
> the column. Operators other than the equal sign (=) are theoretically
> possible in CONNECT BY clauses. However, the conditions created by
> these other operators can result in an infinite loop through the
> possible combinations. In this case Oracle detects the loop at run
> time and returns an error."
>
> The above hints at the possibility of using something other than an
> equal sign in the connect by syntax.
>
> Regardless of how clear the documentation is, your explanation
> describes how it actually works.  Thanks for the reply.

Since that section of the documentation is about hierarchical queries, perhaps the author didn't think it was worth going into the mechanism and just described the use of CONNECT BY and PRIOR in that context.

I notice none of the examples show PRIOR used in the SELECT list either. Received on Thu Nov 06 2008 - 01:25:52 CST