Re: Validating a hierarchical query

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 30 Sep 2004 18:41:25 GMT
Message-ID: <slrnclojto.2cs.rene.nyffenegger@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/

Received on Thu Sep 30 2004 - 13:41:25 CDT