Validating a hierarchical query

From: Phil Bewig <pbewig_at_swbell.net>
Date: 30 Sep 2004 08:52:10 -0700
Message-ID: <455f7154.0409300752.5d3deeab@posting.google.com>

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 Received on Thu Sep 30 2004 - 10:52:10 CDT