Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Validating a hierarchical query
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
>| > 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/
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