Home » SQL & PL/SQL » SQL & PL/SQL » SQL : Connect BY PRIOR How to
icon5.gif  SQL : Connect BY PRIOR How to [message #245472] Sun, 17 June 2007 15:43 Go to next message
kriv
Messages: 2
Registered: June 2007
Junior Member
Hi,

I am not so well versed in Oracle analytic functions and am having trouble with a sql.

Oracle Version: 9.2.0.1.0

I have a tree table storing heirarchies upto 4 levels.

create table FAMILY_TREE
(
  TREE_ID   NUMBER not null,
  PARENT_ID NUMBER,
  NAME      VARCHAR2(100)
);


I know that using a CONNECT BY PRIOR, I can find out who the previous ancestar is.

select
f.tree_id   TreeId,
f.parent_id ParentId,
f.Name      Name
from family_tree f
connect by
prior f.tree_id = f.parent_id
start with f.parent_id is null


I need to display all the ancestars in a single line (in different columns), which is where I am stumped.

I need the output as :
Joe Jack Harry Rick Ned

Bascially, I need to display in a single row, all the ancestars.

I tried to use joins but it was very very slow due to the fact that I had to perform multiple joins.

Can anyone please point me in the right direction?



Data:
insert into FAMILY_TREE (TREE_ID, PARENT_ID, NAME)
values (1, null, 'Joe');
insert into FAMILY_TREE (TREE_ID, PARENT_ID, NAME)
values (2, 1, 'Jack');
insert into FAMILY_TREE (TREE_ID, PARENT_ID, NAME)
values (3, 2, 'Harry');
insert into FAMILY_TREE (TREE_ID, PARENT_ID, NAME)
values (4, 3, 'Rick');
insert into FAMILY_TREE (TREE_ID, PARENT_ID, NAME)
values (5, 4, 'Ned');


[Updated on: Sun, 17 June 2007 18:16]

Report message to a moderator

Re: SQL : Connect BY PRIOR How to [message #245473 is a reply to message #245472] Sun, 17 June 2007 15:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

First:
Please always post your Oracle version (4 decimals).
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS

Then have a look at SYS_CONNECT_BY_PATH function.

Regards
Michel
Re: SQL : Connect BY PRIOR How to [message #245484 is a reply to message #245473] Sun, 17 June 2007 18:18 Go to previous messageGo to next message
kriv
Messages: 2
Registered: June 2007
Junior Member
Sorry. I cleaned up the post.

SYS_CONNECT_BY_PATH does give the path as <Level>\<Level>\....

But problem is, I want them in seperate columns.

I tried to use the string functions to split, but that is proving too resource intensive.

Basically trying to find a way of showing the ancestors as columns in a single row

Thanks!
Kriv
Re: SQL : Connect BY PRIOR How to [message #245501 is a reply to message #245484] Sun, 17 June 2007 22:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@10gXE> column name format a10
SCOTT@10gXE> select * from family_tree
  2  /

   TREE_ID  PARENT_ID NAME
---------- ---------- ----------
         1            Joe
         2          1 Jack
         3          2 Harry
         4          3 Rick
         5          4 Ned

SCOTT@10gXE> column hierarchies format a30
SCOTT@10gXE> select tree_id, parent_id, name,
  2  	    sys_connect_by_path (name, '/') AS hierarchies
  3  from   family_tree
  4  start  with parent_id is null
  5  connect by prior tree_id = parent_id
  6  /

   TREE_ID  PARENT_ID NAME       HIERARCHIES
---------- ---------- ---------- ------------------------------
         1            Joe        /Joe
         2          1 Jack       /Joe/Jack
         3          2 Harry      /Joe/Jack/Harry
         4          3 Rick       /Joe/Jack/Harry/Rick
         5          4 Ned        /Joe/Jack/Harry/Rick/Ned

SCOTT@10gXE> select t.hierarchies
  2  from   (select tree_id, parent_id, name,
  3  		    sys_connect_by_path (name, '/') AS hierarchies
  4  	     from   family_tree
  5  	     start  with parent_id is null
  6  	     connect by prior tree_id = parent_id) t
  7  where  not exists
  8  	    (select *
  9  	     from   family_tree f
 10  	     where  f.parent_id = t.tree_id)
 11  /

HIERARCHIES
------------------------------
/Joe/Jack/Harry/Rick/Ned

SCOTT@10gXE> CREATE OR REPLACE FUNCTION list_element
  2  	(p_string    VARCHAR2,
  3  	 p_element   INTEGER,
  4  	 p_separator VARCHAR2 DEFAULT '/')
  5  	RETURN	     VARCHAR2
  6  AS
  7    v_string      VARCHAR2 (32767);
  8  BEGIN
  9    v_string := p_string || p_separator;
 10    FOR i IN 1 .. p_element - 1 LOOP
 11  	 v_string := SUBSTR (v_string,
 12  			     INSTR (v_string, p_separator)
 13  			       + LENGTH (p_separator));
 14    END LOOP;
 15    RETURN SUBSTR (v_string, 1, INSTR (v_string, p_separator) - 1);
 16  END list_element;
 17  /

Function created.

SCOTT@10gXE> column level1 format a10
SCOTT@10gXE> column level2 format a10
SCOTT@10gXE> column level3 format a10
SCOTT@10gXE> column level4 format a10
SCOTT@10gXE> column level5 format a10
SCOTT@10gXE> select list_element (t.hierarchies, 2) as level1,
  2  	    list_element (t.hierarchies, 3) as level2,
  3  	    list_element (t.hierarchies, 4) as level3,
  4  	    list_element (t.hierarchies, 5) as level4,
  5  	    list_element (t.hierarchies, 6) as level5
  6  from   (select tree_id, parent_id, name,
  7  		    sys_connect_by_path (name, '/') AS hierarchies
  8  	     from   family_tree
  9  	     start  with parent_id is null
 10  	     connect by prior tree_id = parent_id) t
 11  where  not exists
 12  	    (select *
 13  	     from   family_tree f
 14  	     where  f.parent_id = t.tree_id)
 15  /

LEVEL1     LEVEL2     LEVEL3     LEVEL4     LEVEL5
---------- ---------- ---------- ---------- ----------
Joe        Jack       Harry      Rick       Ned

SCOTT@10gXE> 


Re: SQL : Connect BY PRIOR How to [message #245513 is a reply to message #245501] Sun, 17 June 2007 23:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
You could also do it without a function:

SCOTT@10gXE> 
SCOTT@10gXE> column level1 format a10
SCOTT@10gXE> column level2 format a10
SCOTT@10gXE> column level3 format a10
SCOTT@10gXE> column level4 format a10
SCOTT@10gXE> column level5 format a10
SCOTT@10gXE> select substr (level1, 1, instr (level1, '/') - 1) as level1,
  2  	    substr (level2, 1, instr (level2, '/') - 1) as level2,
  3  	    substr (level3, 1, instr (level3, '/') - 1) as level3,
  4  	    substr (level4, 1, instr (level4, '/') - 1) as level4,
  5  	    substr (level5, 1, instr (level5, '/') - 1) as level5
  6  from   (select substr (t.hierarchies, instr (t.hierarchies, '/', 1, 1) + 1) as level1,
  7  		    substr (t.hierarchies, instr (t.hierarchies, '/', 1, 2) + 1) as level2,
  8  		    substr (t.hierarchies, instr (t.hierarchies, '/', 1, 3) + 1) as level3,
  9  		    substr (t.hierarchies, instr (t.hierarchies, '/', 1, 4) + 1) as level4,
 10  		    substr (t.hierarchies, instr (t.hierarchies, '/', 1, 5) + 1) as level5
 11  	     from   (select tree_id, parent_id, name,
 12  			    sys_connect_by_path (name, '/') || '/' AS hierarchies
 13  		     from   family_tree
 14  		     start  with parent_id is null
 15  		     connect by prior tree_id = parent_id) t
 16  	     where  not exists
 17  		    (select *
 18  		     from   family_tree f
 19  		     where  f.parent_id = t.tree_id))
 20  /

LEVEL1     LEVEL2     LEVEL3     LEVEL4     LEVEL5
---------- ---------- ---------- ---------- ----------
Joe        Jack       Harry      Rick       Ned

SCOTT@10gXE> 


[Updated on: Sun, 17 June 2007 23:59]

Report message to a moderator

Re: SQL : Connect BY PRIOR How to [message #245523 is a reply to message #245501] Mon, 18 June 2007 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or just:
SQL> select substr(sys_connect_by_path(name, ' '),2) res
  2  from family_tree
  3  where connect_by_isleaf = 1
  4  connect by prior tree_id = parent_id
  5  start with parent_id is null
  6  /
RES
---------------------------------------------------------
Joe Jack Harry Rick Ned

1 row selected.

Regards
Michel
Re: SQL : Connect BY PRIOR How to [message #245524 is a reply to message #245523] Mon, 18 June 2007 00:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
Michel,

The original poster wants separate columns.
Re: SQL : Connect BY PRIOR How to [message #245528 is a reply to message #245524] Mon, 18 June 2007 00:52 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh I didn't see "(in different columns)" the first time I read it yesterday... but was it here yesterday (Paris time)? Wink

Regards
Michel

[Updated on: Mon, 18 June 2007 00:52]

Report message to a moderator

Previous Topic: which table are updated
Next Topic: Select query fetching more than expected records
Goto Forum:
  


Current Time: Tue Dec 06 13:50:44 CST 2016

Total time taken to generate the page: 0.12563 seconds