OraFAQ Forum: SQL & PL/SQL » Connect by Prior parent-child into string of values

Home » SQL & PL/SQL » SQL & PL/SQL » Connect by Prior parent-child into string of values (10.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Connect by Prior parent-child into string of values [message #607517]

Fri, 07 February 2014 17:42

lott42_gmail
Messages: 146
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

Report message to a moderator

Re: Connect by Prior parent-child into string of values [message #607518 is a reply to message #607517]

Fri, 07 February 2014 17:58

lott42_gmail
Messages: 146
Registered: June 2010

Senior Member

Forgive my "hack" job but this is my first attempt but it doesn't really do anything Surprised

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

Report message to a moderator

Re: Connect by Prior parent-child into string of values [message #607524 is a reply to message #607517]

Sat, 08 February 2014 02:46

Michel Cadot
Messages: 68645
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.

Report message to a moderator

Re: Connect by Prior parent-child into string of values [message #607540 is a reply to message #607524]

Sat, 08 February 2014 15:02

lott42_gmail
Messages: 146
Registered: June 2010

Senior Member

Wow, just incredible, Thanks Michel Smile

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!!

Report message to a moderator

Re: Connect by Prior parent-child into string of values [message #607657 is a reply to message #607540]

Mon, 10 February 2014 12:40

lott42_gmail
Messages: 146
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.

Report message to a moderator

Re: Connect by Prior parent-child into string of values [message #607658 is a reply to message #607657]

Mon, 10 February 2014 12:53

Michel Cadot
Messages: 68645
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.

Report message to a moderator

Re: Connect by Prior parent-child into string of values [message #607659 is a reply to message #607658]

Mon, 10 February 2014 13:02

lott42_gmail
Messages: 146
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.

Report message to a moderator

Re: Connect by Prior parent-child into string of values [message #607660 is a reply to message #607659]

Mon, 10 February 2014 13:14

Michel Cadot
Messages: 68645
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.

Report message to a moderator

Re: Connect by Prior parent-child into string of values [message #607661 is a reply to message #607660]

Mon, 10 February 2014 13:29

Michel Cadot
Messages: 68645
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

Report message to a moderator

Re: Connect by Prior parent-child into string of values [message #607662 is a reply to message #607661]

Mon, 10 February 2014 14:05

lott42_gmail
Messages: 146
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

Report message to a moderator

Re: Connect by Prior parent-child into string of values [message #607663 is a reply to message #607662]

Mon, 10 February 2014 14:15

Michel Cadot
Messages: 68645
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?

Report message to a moderator

Re: Connect by Prior parent-child into string of values [message #607666 is a reply to message #607663]

Mon, 10 February 2014 15:35

lott42_gmail
Messages: 146
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

Report message to a moderator

Re: Connect by Prior parent-child into string of values [message #607668 is a reply to message #607666]

Mon, 10 February 2014 15:57

lott42_gmail
Messages: 146
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.

Report message to a moderator

Re: Connect by Prior parent-child into string of values [message #607678 is a reply to message #607666]

Tue, 11 February 2014 01:14

Michel Cadot
Messages: 68645
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.

Report message to a moderator

Re: Connect by Prior parent-child into string of values [message #607757 is a reply to message #607678]

Tue, 11 February 2014 10:23

lott42_gmail
Messages: 146
Registered: June 2010

Senior Member

Thanks, I didn't realize you already had the answer there. Thanks Again for spending time on this Smile

Smile

Report message to a moderator

Re: Connect by Prior parent-child into string of values [message #630142 is a reply to message #607757]

Sun, 21 December 2014 00:59

zenthil86
Messages: 9
Registered: December 2014
Location: fremont

Junior Member

Please refer the attachment containing the extact table containing the table list and foreign key constraints. Below is the actual requirement.

requirement
example for ALERTS_GNE__C_L table the insert statement must be generated as below
insert into id_xref_tbl select A.ID as ID_VALUE, 'ALERTS_GNE__C_L' as OBJECT_NAME from ALERTS_GNE__C_L A, CASE_L B where A.CASE_GNE__C = B.ID

example for CASE_INSURANCE_GNE__C table the insert statement must be generated as below
insert into id_xref_tbl select A.ID as ID_VALUE, 'BENEFIT_INVESTIGATION_GNE__C_L' as OBJECT_NAME from BENEFIT_INVESTIGATION_GNE__C_L A, INSURANCE_GNE__C_L B, CASE_L C where A.BI_INSURANCE_GNE__C = B.ID and B.CASE_INSURANCE_GNE__C = C.ID

likewise for all the 21 tablelist in the child column the insert statement should be generated and executed and id_xref_tbl is a cross refencence table where the id value from all the table should be populated table having columns ID_VALUE and OBJECT_NAME

Report message to a moderator

Re: Connect by Prior parent-child into string of values [message #630145 is a reply to message #630142]

Sun, 21 December 2014 01:32

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

1/ There is no attachment
2/ Create your own topic
3/ Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
4/ Always post your Oracle version, with 4 decimals.
5/ Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

[Updated on: Sun, 21 December 2014 01:33]

Report message to a moderator

Previous Topic:	generic queries
Next Topic:	SQL Query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 04:09:53 CDT 2024