Re: tree logic - connect by clause

I take my previous statement back.

Select all the nodes from the hierarchy that user can see and add those nodes that user have access to. The first set of nodes are originated from the root 'KING' and are marked 'read-only', while let just assume that the second set consists of all nodes reachanble from
'BLAKE'. The query:

select sys_connect_by_path(ename), ename, 'read-only' from emp start with ename ='KING'
connect by prior empno = mgr
union all
select sys_connect_by_path(ename), ename, 'read-write' from emp start with ename ='BLAKE'
connect by prior empno = mgr

Now in order to get the result you want, you need to group by ename (and sys_connect_by_path the purpose of which is explained later), then, choose 'read-write' in a group if the group has one, or
'read-only', otherwise. This is a user-defined aggregate function,
clearly, and there is at least 4 ways to implement it in 9i:

http://asktom.oracle.com/pls/ask/f?p=4950:8:800413::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:229614022562,%7Bconcatenate%7D

(scroll down to my comment and Tom's reply).

The last step is ordering by sys_connect_by_path. Received on Fri May 10 2002 - 12:34:12 CDT