Hierarchical query

Question

I hope I can explain the problem that puzzles me. I have the following set of hierarchical data (this is just a subset of 34K records)

PARENT_ID CHILD_ID EXAM TUDA12802 TUDA12982 N TUDA12982 TUDA12984 J TUDA12984 TUDA999 J TUDA12982 TUDA12983 N TUDA12983 TUDA15322 J TUDA12983 TUDA15323 J 

This is a view of a tree.

 TUDA12982 N - TUDA12984 J -- TUDA999 J - TUDA12983 N -- TUDA15322 J -- TUDA15323 J 

I need a list of all records with exam = N and basic exam records "J", which can be nested.

 select * from test1 connect by prior child_id = parent_id start with child_id = 'TUDA12982' order siblings by child_id; 

Gives me

 PARENT_ID CHILD_ID EXAM TUDA12802 TUDA12982 N TUDA12982 TUDA12984 J TUDA12984 TUDA999 J TUDA12982 TUDA12983 N TUDA12983 TUDA15323 J TUDA12983 TUDA15322 J 

But i need

 TUDA12802 TUDA12982 N TUDA12982 TUDA12984 J TUDA12984 TUDA999 J 

Moving must be stopped when I encounter EXAM = 'N'.

I need something like a "stop with" clause.

 select * from test1 connect by prior child_id = parent_id start with child_id = 'TUDA12982' stop with exam = 'N' order siblings by child_id; 

How can I do that?

Answer 1

Robert

You can do this by adding "exam = 'J" to the connect by clause:

 SQL> create table test1(parent_id,child_id,exam) 2 as 3 select 'TUDA12802', 'TUDA12982', 'N' from dual union all 4 select 'TUDA12982', 'TUDA12984', 'J' from dual union all 5 select 'TUDA12984', 'TUDA999', 'J' from dual union all 6 select 'TUDA12982', 'TUDA12983', 'N' from dual union all 7 select 'TUDA12983', 'TUDA15322', 'J' from dual union all 8 select 'TUDA12983', 'TUDA15323', 'J' from dual 9 / Tabel is aangemaakt. SQL> select parent_id 2 , child_id 3 , exam 4 , level 5 , lpad(' ',2*level) || sys_connect_by_path(parent_id||'-'||child_id,'/') scbp 6 from test1 7 start with exam = 'N' 8 connect by prior child_id = parent_id 9 and exam = 'J' 10 / PARENT_ID CHILD_ID E LEVEL SCBP --------- --------- - ------ ---------------------------------------------------------------------- TUDA12802 TUDA12982 N 1 /TUDA12802-TUDA12982 TUDA12982 TUDA12984 J 2 /TUDA12802-TUDA12982/TUDA12982-TUDA12984 TUDA12984 TUDA999 J 3 /TUDA12802-TUDA12982/TUDA12982-TUDA12984/TUDA12984-TUDA999 TUDA12982 TUDA12983 N 1 /TUDA12982-TUDA12983 TUDA12983 TUDA15322 J 2 /TUDA12982-TUDA12983/TUDA12983-TUDA15322 TUDA12983 TUDA15323 J 2 /TUDA12982-TUDA12983/TUDA12983-TUDA15323 6 rijen zijn geselecteerd. 

Regards, Rob.

Answer 2

It looks like a simple request that the requested element receives, and its children “J” is what you want, so that would not work:

 select * from test1 where child_id = 'TUDA12982' or exam = 'J' connect by prior child_id = parent_id start with child_id = 'TUDA12982' order siblings by child_id; 

I don’t have Oracle, so I can’t check if this works, but from what I understand from the syntax and the fact that I'm just Googled, it looks like it will work.