Re: hierarchical query without direct parent-child relationship

On Jun 1, 6:35 am, Roland Spatzenegger <c..._at_npg.net> wrote:
> Hi,
> I have a problem to make a correct hierarchical query in Oracle 10g. I
> have the following table:
>
> ELEMENT PROCESS ACCESS
> 5011 5015 CREATE
> 5014 5015 CREATE
> 5005 5015 READ
> 5004 5015 READ
> 5009 5017 CREATE
> 5010 5017 CREATE
> 5014 5017 READ
> 5009 5020 READ
>
> I have processes (identified by PROCESS) which can read elements
> (ELEMENT and ACCES=READ) and write elements (ELEMENT and ACCESS=WRITE).
> Now I want to find out which elements are generated a certain element.
> The whole table builds the following tree for the element 5004:
>
> 5004 -PROCESS=5015-> 5014 -PROCESS=5017-> 5009
> 5014 -PROCESS=5017-> 5010
> 5004 -PROCESS=5015-> 5011
>
> The path from 5004 to 5009:
> ELEM PROC ACC ELEM PROC ACC
> 5004 5015 READ -> 5014 5015 CREATE

here your hierarchy is based on the process ID

> 5014 5017 READ -> 5009 5017 CREATE

here your hierarchy is based on the element

So that is why this query stops:
>
> I thought that the following query could do this:
>
> select ELEMENT, LEVEL from PRODUCTELEMENTACCESS
> start with ELEMENT=5004
> connect by PRIOR PROCESS = PROCESS
> AND PRIOR ACCESS='READ'
> AND ACCESS='CREATE'
>
> but I get only the elements for the first 2 levels:
> ELEMENT LEVEL
> 5004 1
> 5011 2
> 5014 2
>
> any ideas?
> bye
> Roland

I don't think you can track two connections at once in CONNECT BY. I think you are stuck with with either a table redesign or a PL/SQL procedure.

I was trying to think of how to make a view that would solve this, but I cannot come up with one off hand. What do you end up with if you run:

select a.ELEMENT, a.PROCESS as CREATOR, b.PROCESS as READER from PRODUCTELEMENTACCESS a, PRODUCTELEMENTACCESS b

where a.ACCESS='CREATE'
  and b.ACCESS='READ'
  and a.ELEMENT=b.ELEMENT ;

and run the CONNECT BY on that as a view? I'm not sure how to handle the "starting elements" such as 5004. Seems like there must be an INITIAL process that CREATEs them.

5004 0001 CREATE HTH,
   Ed Received on Fri Jun 01 2007 - 11:06:07 CDT