"Rene Nyffenegger" <rene.nyffenegger_at_gmx.ch> wrote in message
news:slrnclojto.2cs.rene.nyffenegger_at_zhnt60m34.netarchitects.com...
| In article <B46dncHq8vXj0MHcRVn-ig_at_comcast.com>, Mark C. Stock wrote:
| >
| > "Rene Nyffenegger" <rene.nyffenegger_at_gmx.ch> wrote in message
| > news:slrncloeb9.2pg.rene.nyffenegger_at_zhnt60m34.netarchitects.com...
| >| In article <455f7154.0409300752.5d3deeab_at_posting.google.com>, Phil
Bewig
| > wrote:
| >| > I can write a hierarchical query against the
| >| > employees sample table like this:
| >| >
| >| > select lpad(' ', (level-1)*2, ' ') ||
| >| > first_name || ' ' || last_name
| >| > from employees
| >| > start with manager_id is null
| >| > connect by prior employee_id = manager_id
| >| > order siblings by last_name
| >| >
| >| > For purposes of writing a validation suite,
| >| > I would like to check that the hierarchy is
| >| > valid, forming a singly-rooted directed
| >| > acyclic graph. It is easy enough to test
| >| > that only one record has a null manager_id,
| >| > that no record has equal employee_id and
| >| > manager_id, and that each manager_id appears
| >| > in the employee_id field of some record in
| >| > the table. But testing that there are no
| >| > cycles, and no breaks in the hierarchical
| >| > chain, is harder. Can anyone suggest how to
| >| > make the needed tests?
| >| >
| >| > Many thanks,
| >| >
| >| > Phil
| >|
| >| Phil,
| >|
| >| There are no cycles if employee_id is a primary
| >| key and manager_id is a foreign key referencing
| >| employee_id.
| >|
| >| hth
| >| Rene
| >|
| >| --
| >| Rene Nyffenegger
| >| http://www.adp-gmbh.ch/
| >
| >
| > actually, Rene, that's how cycles occur, for example:
| >
| > SQL> select empno, ename, mgr
| > 2 from emp
| > 3 order by empno
| > 4 /
| >
| > EMPNO ENAME MGR
| > ---------- ---------- ----------
| > 7369 SMITH 7902
| > ...
| > 7902 FORD 7566
| > 7934 MILLER 7782
| >
| > SQL> update emp
| > 2 set mgr = 7369
| > 3 where empno = 7902;
| >
| > 1 row updated.
| >
| > SQL> select empno, ename, mgr
| > 2 from emp
| > 3 order by empno
| > 4 /
| >
| > EMPNO ENAME MGR
| > ---------- ---------- ----------
| > 7369 SMITH 7902
| > ...
| > 7902 FORD 7369
| > 7934 MILLER 7782
| >
| > the PK/FK constraints do not prevent the cycle, they just enforce that
any
| > MGR value is a valid EMPNO value (could even reference empno in the same
| > record)
| >
| > pre-10g raises an error if there is a cycle
| >
| > SQL> select empno, ename, mgr
| > 2 from emp
| > 3 connect by prior empno = mgr
| > 4 start with mgr = 7902
| > 5 /
| > ERROR:
| > ORA-01436: CONNECT BY loop in user data
| >
| > however, 10G adds the NOCYCLE option and the CONNECT_BY_ISCYCLE
pseudocolumn
| > to make it easier to check for cycles
|
| doh, you're right. I don't know what I was thinking!
|
| Rene
|
|
| --
| Rene Nyffenegger
| http://www.adp-gmbh.ch/
been there myself a few times! ;-)
Received on Thu Sep 30 2004 - 13:55:50 CDT
