CODEWITHSUNDEEP
sqloracle-databaserecursive-query
Gubi
Gubi

Reputation: 415

recursive query on two fields

I 'm trying to write CONNECT BY PRIOR on two fields. Any got any idea?

Eventually I want something like following. Unfortunately it seems not working

SELECT *
FROM emp
CONNECT BY PRIOR emp.type = emp.parenttype AND emp.ID = emp.ParentID

Create table script:

CREATE TABLE emp ( 
    ConnectionPkID NVARCHAR2(16) Not Null, 
    EmpType NVARCHAR2(2) Not Null, 
    EmpID NVARCHAR2(20) Not Null, 
    ParentID NVARCHAR2(20) Not Null, 
    ParentType NVARCHAR2(2) Not Null, );

Upvotes: 0

Views: 170

Answers (2)

APC
APC

Reputation: 146219

Given this test data:

SQL> select * from t23
  2  /

        ID TYP  PARENT_ID PAR NAME
---------- --- ---------- --- --------------------
         1 P                  Fox in Socks
         2 P                  The Lorax
         1 C            1 P   Mr Knox
         2 C            1 P   Daisy-Headed Maisie
         3 C            2 P   Mr KnoHortonx

SQL>

this version of your query works:

 SQL> select level, t23.*
from t23
connect by prior t23.id = t23.parent_id
and prior t23.type = t23.parent_type 
start with t23.parent_id is null and t23.parent_type is null
/
  2    3    4    5    6  
     LEVEL         ID TYP  PARENT_ID PAR NAME
---------- ---------- --- ---------- --- --------------------
         1          1 P                  Fox in Socks
         2          1 C            1 P   Mr Knox
         2          2 C            1 P   Daisy-Headed Maisie
         1          2 P                  The Lorax
         2          3 C            2 P   Mr KnoHortonx

SQL>

The important bit is to tag each operand in the CONNECT BY clause with PRIOR.

Upvotes: 1

socha23
socha23

Reputation: 10239

PRIOR is a part of condition, not of CONNECT BY clause. You need to qualify each parent field with it. Try this:

SELECT *
FROM emp
CONNECT BY PRIOR emp.type = emp.parenttype AND PRIOR emp.ID = emp.ParentID
START WITH emp.ID = ??? AND emp.type = ???

Upvotes: 2

Related Questions