hierarchy
what is a hierarchy?
I enjoy reading the wikipedia definition :
http://en.wikipedia.org/wiki/Hierarchy
In the doc the hierarchy is as a parent-child connection, CONNECT BY PRIOR defines the relationship.
However, it is possible to have under certain circumstances to connect to a child, regardsless of the parent.
This is no longer a practical relation.
Sterile variant :
SQL> select * from dept connect by 1=2; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
Fertile variant :
SQL> select * from dept connect by 1=1 DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 10 ACCOUNTING NEW YORK 10 ACCOUNTING NEW YORK 10 ACCOUNTING NEW YORK 10 ACCOUNTING NEW YORK 10 ACCOUNTING NEW YORK 10 ACCOUNTING NEW YORK 10 ACCOUNTING NEW YORK 10 ACCOUNTING NEW YORK 10 ACCOUNTING NEW YORK 10 ACCOUNTING NEW YORK 10 ACCOUNTING NEW YORK ...
the connect by does defines a true or a false connection. when true, everyone is your parent and everyone is your child. If false, you are the parent, but you have no child.
connect by level
November 9th, 2005 at 18:44
>> I enjoy reading the wikipedia definition :
I take it you will be expanding the “Hierarchies in programming” section to include the fruits of your research into hierarchies in Oracle.
Cheers, APC
November 10th, 2005 at 13:19
Dubious as it is, this has been posted by Tom Kyte as a way of generating a series of integers (which you can then use as the basis of a data generator - I’ve used it to generate a date series).
The PRIOR no longer appears to be mandatory.
The question is whether it should behave in the way that it does, or whether this is a ‘feature’ - and as it’s not a documented behaviour, whether it’s safe to rely on it in code.
(And certainly whether that should then be documented as how CONNECT BY behaves. It’s definitely how it behaves on Oracle 9.2 to 10g)
November 10th, 2005 at 15:34
what is a hierarchy? …
In the doc the hierarchy is as a parent-child connection
You really ought to correct that … documentation doesn’t say that is all there is to a hierarchy.
November 10th, 2005 at 17:17
> The PRIOR no longer appears to be mandatory
well, either the doc is wrong, or there is a bug that should be detected one day…
> documentation doesn’t say that is all there is to a hierarchy
CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy.