Re: hierarchical query without direct parent-child relationship

From: Roland Spatzenegger <cymric_at_npg.net>
Date: Fri, 01 Jun 2007 21:43:11 +0200
Message-ID: <f3pssk$sua$1@svr7.m-online.net>

Hi,
thank you for your answer. Meanwhile I got a solution which uses a view based on the elements
select p1.ELEMENT, p2.ELEMENT, p1.PROCESS   from PRODUCTELEMENTACCESSTRACE p1, PRODUCTELEMENTACCESSTRACE p2    where p1.PROCESS=p2.PROCESS
   and p1.ACCESS='READ' and p2.ACCESS='CREATE' The table looks like:

A->B
B->C
B->D

...
here I can make a "connect by prior p2.ELEMENT, p1.ELEMENT ... I think I will also try your suggestion next Monday, because now I have some nasty problems with cycles (the data has some wrong rows (Same process writes and reads the same element ...)) :-(

Ed Prochak schrieb:

> 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?

> 
> 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
> 

Yes, there must be such an initial row, I've forgotten to insert it in my example.

bye
Roland Received on Fri Jun 01 2007 - 14:43:11 CDT