Re: Filling in "gaps" in data

On Nov 5, 4:00 pm, Charles Hooper <hooperc2...@xxxxxxxxx> wrote:
On Nov 4, 2:27 am, William Robertson <williamr2...@xxxxxxxxxxxxxx>
wrote:



On Nov 1, 12:56 am, Charles Hooper <hooperc2...@xxxxxxxxx> 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.
.

Relevant Pages

  • Re: Filling in "gaps" in data
    ... There is no special CONNECT BY LEVEL syntax, ... The documentation does not seem to be quite as clear: ... "CONNECT BY specifies the relationship between parent rows and child ... "PRIOR is most commonly used when comparing column values with the ...
    (comp.databases.oracle.server)
  • Re: Sold the Scout - new twist
    ... Documentation establishes the chain of ownership and ... establishes that there are no unfullfilled *prior* liens. ... liability incurred by a prior owner ten years in the past. ... offshore which breaks the USCG chain of title and makes the boat very ...
    (rec.boats)
  • RE: how does #Include work?
    ... A control will do ... Anyway, INCLUDES are pulled into the page PRIOR to ANY parsing, ... "Andy Fish" wrote: ... but I can't find any documentation on msdn about this and I'm trying ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: timeits environment
    ... informed you and send it to the destination mentioned on the documentation page. ... a submission might be viewed prior to going through the work of doing it. ... is a clear explanation of what is wrong and how it might be fixed that will definitely be read. ...
    (comp.lang.python)
  • Re: GetThemeSysColor
    ... GetThemeSysXxxx function. ... Did you even try looking through the documentation I mentioned in my prior ... A quick scan through the available theming functions would tell you ...
    (microsoft.public.vb.winapi.graphics)