Re: Validating a hierarchical query

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:<B46dncHq8vXj0MHcRVn-ig_at_comcast.com>...
> 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

I'm on Version 9-something, I can never remember which. What I really want is to write a SQL select query like

    select 'CYCLIC HIERARCHY'
    from employees
    where ...

that returns the single record 'CYCLIC HIERARCHY' if there is a cycle and returns no records if there is not, because that will fit neatly in the framework I have established for all the other validation tests. I suppose the answer is a function that I can call as

    select check_for_cycles(employees) from dual

Right? Any other way to get what I'm looking for?

Many thanks,

Phil Received on Thu Sep 30 2004 - 20:36:39 CDT