Home » SQL & PL/SQL » SQL & PL/SQL » Connect by Prior parent-child into string of values (10.2)
Connect by Prior parent-child into string of values [message #607517] Fri, 07 February 2014 17:42 Go to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
I have a table like this
WITH data AS
 (SELECT 1 child, 10 PARENT
    FROM dual
  UNION
  SELECT 2, 10
    FROM dual
  UNION
  SELECT 3, 20
    FROM dual
  UNION
  SELECT 4, 30
    FROM dual
  UNION
  SELECT 5, 40
    FROM dual
  UNION
  SELECT 6, 40
    FROM dual
  
  UNION
  SELECT 10, 100
    FROM dual
  
  UNION
  SELECT 20, 200
    FROM dual
  
  UNION
  SELECT 30, 300
    FROM dual
  UNION
  SELECT 40, 300
    FROM dual

  UNION
  SELECT 100, 0
    FROM dual
  UNION
  SELECT 200, 0
    FROM dual
  UNION
  SELECT 300, 0 FROM dual
  )
SELECT LEVEL, data.* FROM data CONNECT BY PRIOR PARENT = child;


I want output like this
Child                entire_tree_level
1                    1,2,10,100      
2                    1,2,10,100
3                    3,20,200
4                    4,5,6,30,40,300
5                    4,5,6,30,40,300
6                    4,5,6,30,40,300
10                   1,2,10,100
20                   3,20,200
30                   4,5,6,30,40,300
40                   4,5,6,30,40,300
100                  1,2,10,100
200                  3,20,200
300                  4,5,6,30,40,300


where for each candidate child record, I want all of it's prior and next siblings based upon the LAST parent record found.

I think the code will use a structure similar to this
with data
   as
   (
      --                                                    asc/desc 
      select myvalues, row_number() over (order by myvalues desc) rn, count(*) over () cnt
         from
         (
            --Your SQL statement here
            select data myvalues from dummy where 1=1
         )
   )
select ltrim(sys_connect_by_path(myvalues, ','),',') catvalues
   from data
   where rn = cnt
   start with rn = 1
   connect by prior rn = rn-1;


but I'm not sure what goes where yet. any suggestions??
I HOPE I gave the correct results on Output, I just did an "eyeball" test...

Thanks

P.S.
I'll be out of the office for the weekend so I won't get a chance to write/test until Monday

Re: Connect by Prior parent-child into string of values [message #607518 is a reply to message #607517] Fri, 07 February 2014 17:58 Go to previous messageGo to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
Forgive my "hack" job but this is my first attempt but it doesn't really do anything Surprised(

SELECT child, substr(MAX(sys_connect_by_path(PARENT, ',')), 2) PARENT
  FROM (SELECT data.child,
               data.parent,
               row_number() over(PARTITION BY data.child ORDER BY 1) rn
          FROM data
         WHERE 1 = 1)
 START WITH rn = 1
CONNECT BY PRIOR PARENT = child
       AND PRIOR rn = rn - 1
 GROUP BY child
 ORDER BY child


   	CHILD	PARENT
1	1	10
2	2	10
3	3	20
4	4	30
5	5	40
6	6	40
7	10	100
8	20	200
9	30	300
10	40	300
11	100	0
12	200	0
13	300	0


Re: Connect by Prior parent-child into string of values [message #607524 is a reply to message #607517] Sat, 08 February 2014 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59992
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> col siblings format a50
SQL> WITH data AS
  2   (SELECT 1 child, 10 PARENT FROM dual UNION all
  3    SELECT 2, 10 FROM dual UNION all 
  4    SELECT 3, 20 FROM dual UNION all 
  5    SELECT 4, 30 FROM dual UNION all 
  6    SELECT 5, 40 FROM dual UNION all
  7    SELECT 6, 40 FROM dual UNION all
  8    SELECT 10, 100 FROM dual UNION all
  9    SELECT 20, 200 FROM dual UNION all
 10    SELECT 30, 300 FROM dual UNION all
 11    SELECT 40, 300 FROM dual UNION all
 12    SELECT 100, 0 FROM dual UNION all
 13    SELECT 200, 0 FROM dual UNION all
 14    SELECT 300, 0 FROM dual
 15    ),
 16    all_paths as (
 17      select connect_by_root child ancestor, child
 18      from data
 19      connect by prior child = parent
 20      start with parent = 0
 21    ),
 22    all_siblings as (
 23      select distinct 
 24             a.ancestor, b.child sibling, 
 25             dense_rank() over (partition by a.ancestor order by b.child) rk
 26      from all_paths a, all_paths b
 27      where b.ancestor = a.ancestor
 28    ),
 29    sibling_paths as (
 30      select ancestor, substr(sys_connect_by_path(sibling,','),2) siblings
 31      from all_siblings
 32      where connect_by_isleaf = 1
 33      connect by prior ancestor = ancestor and prior rk = rk-1
 34      start with rk = 1
 35    ),
 36    ancestors as (
 37      select connect_by_root child child, child ancestor
 38      from data
 39      where connect_by_isleaf = 1
 40      connect by prior parent = child
 41    )
 42  select a.child, siblings 
 43  from ancestors a, sibling_paths p
 44  where p.ancestor = a.ancestor 
 45  order by 1
 46  /
     CHILD SIBLINGS
---------- --------------------------------------------------
         1 1,2,10,100
         2 1,2,10,100
         3 3,20,200
         4 4,5,6,30,40,300
         5 4,5,6,30,40,300
         6 4,5,6,30,40,300
        10 1,2,10,100
        20 3,20,200
        30 4,5,6,30,40,300
        40 4,5,6,30,40,300
       100 1,2,10,100
       200 3,20,200
       300 4,5,6,30,40,300

13 rows selected.

icon14.gif  Re: Connect by Prior parent-child into string of values [message #607540 is a reply to message #607524] Sat, 08 February 2014 15:02 Go to previous messageGo to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
Wow, just incredible, Thanks Michel Smile

Boy, much more complex than I thought it would be. Sorry for my futile attempt.

When I get back to the office, I'll try to pick it apart and try to learn from this!!
Re: Connect by Prior parent-child into string of values [message #607657 is a reply to message #607540] Mon, 10 February 2014 12:40 Go to previous messageGo to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
Michel, I'm playing around with this code today and I created a view with the sql
CREATE OR REPLACE VIEW TEST_VW AS
WITH data AS
 (SELECT 1 child, 10 PARENT
    FROM dual
  UNION all
  SELECT 2, 10
    FROM dual
  UNION all
  SELECT 3, 20
    FROM dual
  UNION all
  SELECT 4, 30
    FROM dual
  UNION all
  SELECT 5, 40
    FROM dual
  UNION all
  SELECT 6, 40
    FROM dual
  UNION all
  SELECT 10, 100
    FROM dual
  UNION all
  SELECT 20, 200
    FROM dual
  UNION all
  SELECT 30, 300
    FROM dual
  UNION all
  SELECT 40, 300
    FROM dual
  UNION all
  SELECT 100, 0
    FROM dual
  UNION all
  SELECT 200, 0
    FROM dual
  UNION all
  SELECT 300, 0 FROM dual),
all_paths as
 (select connect_by_root child ancestor, child
    from data
  connect by prior child = parent
   start with parent = 0),
all_siblings as
 (select distinct a.ancestor,
                  b.child sibling,
                  dense_rank() over(partition by a.ancestor order by b.child) rk
    from all_paths a, all_paths b
   where b.ancestor = a.ancestor),
sibling_paths as
 (select ancestor, substr(sys_connect_by_path(sibling, ','), 2) siblings
    from all_siblings
   where connect_by_isleaf = 1
  connect by prior ancestor = ancestor
         and prior rk = rk - 1
   start with rk = 1),
ancestors as
 (select connect_by_root child child, child ancestor
    from data
   where connect_by_isleaf = 1
  connect by prior parent = child)
select a.child, siblings
  from ancestors a, sibling_paths p
 where p.ancestor = a.ancestor
 order by 1;


If I perform a select
SELECT t.siblings AS str FROM test_vw t where child = 20


I get
3,20,200


but, if I try to split the string, I get error
ORA-03113
Process Id: 0

and 
ORA-03114


SELECT regexp_substr(str, '[^,]+', 1, LEVEL) val
  FROM (SELECT t.siblings AS str FROM test_vw t where child = 20)
CONNECT BY LEVEL <= length(str) - length(REPLACE(str, ',')) + 1


1. Do I need to create the view differently??
2. Not create a view and try it in an "inline" view??
3. Try not to split the "siblings" values??
4. None of the above??


Thanks again.

Re: Connect by Prior parent-child into string of values [message #607658 is a reply to message #607657] Mon, 10 February 2014 12:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59992
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Most of the time ORA-03113 indicates a bug (if you have no network problem).
Have a look at your alert.log file for any ORA-00600.
Also you have to post the result of:
select * from v$version;

Anyway, you must not use ORDER BY at the end of a view definition, so first try without it.

Re: Connect by Prior parent-child into string of values [message #607659 is a reply to message #607658] Mon, 10 February 2014 13:02 Go to previous messageGo to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
v$version
   	BANNER
1	Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
2	PL/SQL Release 10.2.0.4.0 - Production
3	CORE	10.2.0.4.0	Production
4	TNS for Solaris: Version 10.2.0.4.0 - Production
5	NLSRTL Version 10.2.0.4.0 - Production


I took out the "Order By" too and error still appears.

I'll go talk to our DBA.
Re: Connect by Prior parent-child into string of values [message #607660 is a reply to message #607659] Mon, 10 February 2014 13:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59992
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With the same version I get the same error.
Trace file shows:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_qknviAllocate+15] [PC:0x21E1FFB] [ADDR:0x0] [UNABLE_TO_READ] []
which denotes an Oracle bug.

Re: Connect by Prior parent-child into string of values [message #607661 is a reply to message #607660] Mon, 10 February 2014 13:29 Go to previous messageGo to next message
Michel Cadot
Messages: 59992
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Note that it works in 11.2.0.1:
SQL> SELECT regexp_substr(str, '[^,]+', 1, LEVEL) val
  2    FROM (SELECT t.siblings AS str FROM test_vw t where child = 20)
  3  CONNECT BY LEVEL <= length(str) - length(REPLACE(str, ',')) + 1;
VAL
-----------------------------------------------------------------------
3
20
200

Re: Connect by Prior parent-child into string of values [message #607662 is a reply to message #607661] Mon, 10 February 2014 14:05 Go to previous messageGo to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
This isn't the most elegant but it seems to work for my version

SELECT tw.child
  FROM (SELECT t.siblings || ',' AS str FROM test_vw t where child = 20) t,
       test_vw tw
 where instr(t.str, tw.child || ',') > 0


3
20
200



Re: Connect by Prior parent-child into string of values [message #607663 is a reply to message #607662] Mon, 10 February 2014 14:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59992
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just one question; why do you want to split a string I had difficulty to build from its elements?

Re: Connect by Prior parent-child into string of values [message #607666 is a reply to message #607663] Mon, 10 February 2014 15:35 Go to previous messageGo to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
One scenario that I can see is that we need to get all unique ancestry info based upon user selection. If user selects employee ID(3,20)

with emp_info as
 (select 1 id, '1 info' info
    from dual
  union all
  select 2, '2 info'
    from dual
  union all
  select 3, '3 info'
    from dual
  union all
  select 4, '4 info'
    from dual
  union all
  select 5, '5 info'
    from dual
  union all
  select 6, '6 info'
    from dual
  union all
  select 10, '10 info'
    from dual
  union all
  select 20, '20 info'
    from dual
  union all
  select 30, '30 info'
    from dual
  union all
  select 40, '40 info'
    from dual
  union all
  select 100, '100 info'
    from dual
  union all
  select 200, '200 info'
    from dual
  union all
  select 300, '300 info' from dual)
SELECT /*distinct*/
 emp_info.id, emp_info.info
  FROM (SELECT t.siblings || ',' AS str
          FROM test_vw t
         where child in (3, 20)) t,
       test_vw tw,
       emp_info
 where instr(t.str, tw.child || ',') > 0
   and tw.child = emp_info.id


ID	INFO
3	3 info
20	20 info
200	200 info
3	3 info
20	20 info
200	200 info

Re: Connect by Prior parent-child into string of values [message #607668 is a reply to message #607666] Mon, 10 February 2014 15:57 Go to previous messageGo to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
I'll probably eventually need to add to the view two more columns(children_level, parent_level) based upon the child column that look something like this
Child                children_level		parent_level
1                    1				1,10,100
2                    2				2,10,100
3                    3				3,20,200
4                    4				4,30,300
5                    5				5,30,300
6                    6				6,30,300
10                   1,2,10			10,100
20                   3,20			20,200
30                   4,30			30,300
40                   5,6,40			40,300
100                  1,2,10,100			100
200                  3,20,200			200
300                  4,5,6,30,40,300		300


Hopefully, I got that right. I think this is more the typical traverse the tree forward and backward. This would give me one denormalized "view"
of all my hierarchical data into one place. I could traverse any and all data this way.

I'll try to give this a go based upon the data above and your excellent code example.
Re: Connect by Prior parent-child into string of values [message #607678 is a reply to message #607666] Tue, 11 February 2014 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59992
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What I meant is you already have the answer in the query, you just have to remove "siblings_paths" from your view definition:
SQL> CREATE OR REPLACE VIEW TEST_VW AS
  2  WITH data AS
  3    (SELECT 1 child, 10 PARENT FROM dual UNION all
  4     SELECT 2, 10 FROM dual UNION all
  5     SELECT 3, 20 FROM dual UNION all
  6     SELECT 4, 30 FROM dual UNION all
  7     SELECT 5, 40 FROM dual UNION all
  8     SELECT 6, 40 FROM dual UNION all
  9     SELECT 10, 100 FROM dual UNION all
 10     SELECT 20, 200 FROM dual UNION all
 11     SELECT 30, 300 FROM dual UNION all
 12     SELECT 40, 300 FROM dual UNION all
 13     SELECT 100, 0 FROM dual UNION all
 14     SELECT 200, 0 FROM dual UNION all
 15     SELECT 300, 0 FROM dual
 16    ),
 17    all_paths as (
 18      select connect_by_root child ancestor, child
 19      from data
 20      connect by prior child = parent
 21      start with parent = 0
 22    ),
 23    all_siblings as (
 24      select distinct
 25             a.ancestor, b.child sibling
 26      from all_paths a, all_paths b
 27      where b.ancestor = a.ancestor
 28    ),
 29    ancestors as (
 30      select connect_by_root child child, child ancestor
 31      from data
 32      where connect_by_isleaf = 1
 33      connect by prior parent = child
 34    )
 35  select a.child, s.sibling
 36  from ancestors a, all_siblings s
 37  where s.ancestor = a.ancestor
 38  /

View created.

SQL> SELECT sibling
  2  FROM test_vw t
  3  where child = 20
  4  /
   SIBLING
----------
       200
         3
        20

3 rows selected.

Re: Connect by Prior parent-child into string of values [message #607757 is a reply to message #607678] Tue, 11 February 2014 10:23 Go to previous message
lott42
Messages: 100
Registered: June 2010
Senior Member
Thanks, I didn't realize you already had the answer there. Thanks Again for spending time on this Smile
Previous Topic: Pragma Autonomous_transaction
Next Topic: UTL_MAIL
Goto Forum:
  


Current Time: Fri Dec 19 10:13:45 CST 2014

Total time taken to generate the page: 0.14236 seconds