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
>> 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
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)
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.
> 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.