hierarchical query without direct parent-child relationship

From: Roland Spatzenegger <c_at_npg.net>
Date: Fri, 01 Jun 2007 12:35:40 +0200
Message-ID: <f3ospq$i6a$1@svr7.m-online.net>

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
5014 5017 READ -> 5009 5017 CREATE 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 Received on Fri Jun 01 2007 - 05:35:40 CDT