Extra question on ordering.

If I run the query "as is", it'll show me the employee first, his manager next etc

I would like to see the "big boss" first and my employee last

The only thing I could think of was "order by rownum desc"

Do you see any problems with this approach???

Thanks in advance for your help

Alla

I would've used "level" as well, but unfortunately, there is no such a column in the table (it was designed long before my time)

Thanks a lot!

Damn it, I feel so stupid :-(

Hi Tom,

Here is a query.

SELECT node_id
,description
,parent_id
,level
FROM tab_nodes
START WITH node_id = 1
CONNECT BY PRIOR node_id = parent_id
ORDER SIBLINGS BY description;

This query works fine with SQL*PLUS. I am using 9i database and forms9i.
But the same query gives error when i put it in forms.
Is it that ORDER SIBLINGS BY is not allowed within forms.

Hi Tom,

I have a table named my_tree

SQL> select * from my_tree;
PARENT     CHILD
---------- -----------
S1         S2
           S6
S2         S3
S3         S4
           S5
           
7 rows selected    
SQL>

Is it possible to have a list of all the parents that a child has? Example for S5, the following parents will be returned (S3,S2 and S1).

One more thing if I want to return a common parent between two children. Example (S6 and S5 returns S1).

Thanks!!!! 

S5 has no parents, sorry -- this does not compute.

assuming you meant to carry down the parents, so S6 -> S1 and S5 -> S3, a simple connect by 


select * 
  from my_tree
 start with child = 'S5'
connect by prior parent = child;

gives you such a list -- it is what connect by does.


ops$tkyte@ORA9IR2> select *
  2    from t
  3   start with child = 's5'
  4  connect by prior parent = child;
 
PA CH
-- --
s3 s5
s2 s3
s1 s2
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select parent
  2    from t
  3   start with child in ( 's5', 's6' )
  4  connect by prior parent = child
  5   group by parent having count(*) = 2
  6  /
 
PA
--
s1
 

I got it already Tom.
Thanks anyway.

Hello Tom,
Thanks for the answers, I was also able to figure it out afterwards.
Glad to know we came up with the same solution.

However, I noticed in some of your examples you use a field named Level (1,2,3,4) in determining the closest relative of a child. Would it be possible to get the same result without using Levels?

Thanks.

Hi,

I want use a "reverse" hirarchical query to find the closest ancestors named 'X' of a list of row.

This works well for a single row

select * from (
SELECT FR_ID, FR_NAME, LEVEL, rownum r1
FROM HIERAR_TABLE
where FR_NAME='X'
START WITH FR_ID = 2682200
CONNECT BY PRIOR PARENT_FR_ID = FR_ID
) where r1 = 1


But when I want to find the closest ancestors for A LIST of rows, it doesn't work (ORA-00904: "Table2"."FR_ID": invalid identifier):

select Table2.FR_ID,
(
select FR_ID from (
SELECT FR_ID, FR_NAME, LEVEL, rownum r1
FROM HIERAR_TABLE
where FR_NAME='X'
START WITH FR_ID = Table2.FR_ID
CONNECT BY PRIOR PARENT_FR_ID = FR_ID
) where r1 = 1
) from Table2 where [condition]

And this is taking way too long :

select FR_ID from (
SELECT FR_ID, FR_NAME, LEVEL, rownum r1
FROM HIERAR_TABLE
where FR_NAME='X'
START WITH FR_ID in (select Table2.FR_ID from Table2 where [condition])
CONNECT BY PRIOR PARENT_FR_ID = FR_ID
) where r1 = 1


Thanks for your help!!

Hi Tom

I have a question in hierarchical queries.

Can we use 'connect by' for the following tree to look for grand-grand parents, parents,
their children and the children's children (then the children
must be listed as parents) if they have any.
From the root of the tree to the bottom node.

Let me explain more:


My situation is like this:

create table loc
(
par_id number(12)
loc_id number(12),
);

insert into loc values (-1, 1);
insert into loc values (-1, 8);
insert into loc values (-1, 7);
insert into loc values (1, 12);
insert into loc values (1, 13);
insert into loc values (12,40);
insert into loc values (12,21);
insert into loc values (40,31);
insert into loc values (7, 10);
insert into loc values (10,18);


select * from loc;

PAR_ID LOC_ID
---------- ----------
-1 1
-1 8
-1 7
1 12
1 13
12 40
12 21
40 31
7 10
10 18


10 rows

Tree is like this:


-1
-------------------
| | |
1 8 7
____________ __________
| | | |
12 13 10
| |
______ 18
| |
40 21
|
31



I use connect by like this :
Which correctly gives me all parents and their children.
But it doesn't list me the grand-grand parents of the children and of the parents.


select par_id, loc_id
from loc
start with par_id in (select loc_id from loc where par_id=-1)
connect by prior loc_id=par_id


PAR_ID LOC_ID
---------- ----------
1 12
12 40
40 31
12 21
1 13
7 10
10 18



7 rows selected.


But what I want is to create a 2 column table like this:

All grand-grand parents (up to -1), the parents, and children which are parents in one column,
and all their children in another column.
Last it should show that every parent is a child of itself as well. Exactly like
below.

Kind of double checking every node if it has children and listing it, as traversing the tree.


The table I am after is like this:

PAR_ID LOC_ID
---------- ----------
1 1
1 12
1 13
1 40
1 21
1 31
8 8
7 7
7 10
7 18
12 12
12 40
12 21
12 31
40 40
40 31
21 21
31 31
13 13
10 10
10 18
18 18


Is that possible in one SQL statment with 'connect by' or a pl/sql procedure.
If a procedure then how should start.


Many thanks for all your help.

Regards

Kubilay

   Hi Tom
   
   Many Thanks for your reply!
   
   Yeah, will be sufficient, data-wise is what I want! Great!
   
   But how can I display all that in a tabular format with 2 columns.
   Column 1 all 'parents' possible, column 2 all 'children' possible.
   
   
   I get this when I run it on my test case which correctly shows all the hierarchy
   info.
   
   
    SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(loc_id, '/') "Path"
      FROM loc
      START WITH par_id = -1
      CONNECT BY PRIOR loc_id = par_id;  2    3    4
   
   Path
   --------------------------------------------------------------------------------
    /1
      /1/12
        /1/12/40
          /1/12/40/31
        /1/12/21
      /1/13
    /8
    /7
      /7/10
        /7/10/18
   
   10 rows selected.
   
   
But what I want is this:
(it is the same as abovove but with all levels in between)

    PAR_ID         LOC_ID
----------     ----------
1            1 
1            12 
1                       13 
1            40 
1            21 
1            31 
8                       8 
7            7 
7            10 
7            18 
12            12 
12            40  
12            21 
12                      31 
40            40 
40            31 
21                      21 
31                      31 
13                      13 
10            10 
10            18 
18                      18 


I am not sure if SQL can do that:

I have written the following procedure but does half of the job
it just put the grandfather of all lowest level children
and not anything in between like children who are parents
and their children. I want to go through all the levels
in between as well.

create or replace procedure loc_children as
cursor cur_locchi is select * from loc where par_id=-1;
rec_locchi loc%rowtype;
 begin
   open cur_locchi;
   loop
       fetch cur_locchi into rec_locchi;
       exit when cur_locchi%notfound;
       insert all
       into test_loc values (par_id, chi_id)
       select rec_locchi.loc_id par_id, loc_id chi_id  from loc
       start with par_id = rec_locchi.loc_id
       connect by prior loc_id=par_id;
   end loop;
   dbms_output.put_line( cur_locchi%rowcount || ' rows inserted' );
   close cur_locchi;
   commit;
end loc_children;



All it does is to fill the table like this, 
which is OK but all levels in between can't get hold of them.


SQL> select * from test_loc;

    PAR_ID     LOC_ID
---------- ----------
         1         12
         1         40
         1         31
         1         21
         1         13
         7         10
         7         18


Many thanks

Kubilay
 

so, the answer is "no, it is not sufficient" really :)

here is one approach

ops$tkyte@ORA9IR2> select distinct
  2         to_number( substr(path,1,instr(path,',')-1) ) par_id,
  3         to_number( column_value ) loc_id
  4    from (
  5   SELECT substr(SYS_CONNECT_BY_PATH(loc_id, ','),2) || ',' Path
  6     FROM loc
  7  CONNECT BY PRIOR loc_id = par_id ), TABLE( str2tbl(rtrim(path,',')) )
  8    order by 1, 2
  9  /
 
    PAR_ID     LOC_ID
---------- ----------
         1          1
         1         12
         1         13
         1         21
         1         31
         1         40
         7          7
         7         10
         7         18
         8          8
        10         10
        10         18
        12         12
        12         21
        12         31
        12         40
        13         13
        18         18
        21         21
        31         31
        40         31
        40         40
 
22 rows selected.




ops$tkyte@ORA9IR2> create or replace type str2tblType as table of varchar2(30)
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace function str2tbl( p_str in varchar2, p_delim in varchar2 default ',' ) return str2tblType
  2  PIPELINED
  3  as
  4      l_str      long default p_str || p_delim;
  5      l_n        number;
  6  begin
  7      loop
  8          l_n := instr( l_str, p_delim );
  9          exit when (nvl(l_n,0) = 0);
 10          pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) );
 11          l_str := substr( l_str, l_n+1 );
 12      end loop;
 13      return;
 14  end;
 15  /
 
Function created.


 

Many Thanks Tom!

I didn't know that there is function called TABLE in oracle's collections let about the PIPELINED/PIPE ROW iteration. Could never figure out that for sure and would still be trying to make that procedure work.

You are such a great Teacher!


Best Regards

Kubilay

If I have to traverse from root to branch in the EMP table, we have to put the prior clause before EMPNO.
select *
from EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR

If I have to traverse from branch to root in the emp table, we have to put the prior clause before MGR.

select *
from EMP
start with EMPNO = :x
connect by prior MGR = EMPNO;

Please explain me how to decide, where to put the PRIOR clause.

Hi Tom,
This thread has been very helpful explaining
how to write hierarchical queries. But I'm stuck
trying to do the following:

Let say we have the following data set.

PARENT CHILD
---------- -----------
S1 S2
S2 S3
S3 S4
S4 S5
S5

I'd like list every parent in the tree for each child.
For example,

CHILD PARENT
---------- -----------
S5 S4
S5 S3
S5 S2
S5 S1
S4 S3
S4 S2
S4 S1
etc......


Thanks,
Dan

Yes, the sys_connect_by_path contain the correct information I'm looking for, but I'm not clear on how to get the final end results I'm looking for.

Thanks,
Daniel

Yes, I need the data in that specific format.
Thats the problem.

Thanks,
Dan

ops$tkyte%ORA10GR2> select a child, p parent
  2    from (
  3  select p, connect_by_root p a
  4    from t
  5   connect by prior p = c
  6         )
  7   where p <> a
  8  order by 1 desc, 2 desc
  9  /

CH PA
-- --
S5 S4
S5 S3
S5 S2
S5 S1
S4 S3
S4 S2
S4 S1
S3 S2
S3 S1
S2 S1

10 rows selected.


you can use sys_connect_by_path in 10gr1 and before in place of connect by root and substr off the root. 

Hi Tom,

I am learning connect by clause, I want to generate output using bottoms up approch or reverse order details are

create or replace view v
as
select emp.ename, emp.empno, emp.mgr, dept.dname from emp, dept
where emp.deptno = dept.deptno
/

View created.

1 select rpad('* ',2*level,'* ')||ename EmpName, dname
2 from v
3 start with mgr is null
4 connect by prior empno = mgr
5* order siblings by ename
14:18:57 SQL> /

* KING ACCOUNTING
* * BLAKE SALES
* * * ALLEN SALES
* * * JAMES SALES
* * * MARTIN SALES
* * * TURNER SALES
* * * WARD SALES
* * CLARK ACCOUNTING
* * * MILLER ACCOUNTING
* * JONES RESEARCH
* * * FORD RESEARCH
* * * * SMITH RESEARCH
* * * SCOTT RESEARCH
* * * * ADAMS RESEARCH

14 rows selected.

Now I want bottoms up approch ( or reverse order ) how can I do this without using in A VIEW.

Desired O/P like 

* * * * SMITH RESEARCH
* * * FORD RESEARCH
* * * * ADAMS RESEARCH
* * * SCOTT RESEARCH
* * JONES RESEARCH
* * * ALLEN SALES
* * * JAMES SALES
* * * MARTIN SALES
* * * TURNER SALES
* * * WARD SALES
* * BLAK SALES
* * * MILLER ACCOUNTING
* * CLARK ACCOUNTING
* KING ACCOUNTING


Kindly help.

thanks & regards
PJP 



 

Hi Tom,

Heres the table & data -

CREATE TABLE TEST1
(
"ID1" NUMBER,
"NAME" VARCHAR2(100 BYTE),
"ID2" NUMBER
);

Insert into TEST1 ("ID1","NAME") values ('5','VP21');
Insert into TEST1 ("ID1","NAME") values ('4','VP11');
Insert into TEST1 ("ID1","NAME") values ('3','SVP2');
Insert into TEST1 ("ID1","NAME") values ('2','SVP1');
Insert into TEST1 ("ID1","NAME") values ('1','Boss');
Insert into TEST1 ("ID1","NAME") values ('7','EMP1111');
Insert into TEST1 ("ID1","NAME") values ('6','DIR111');

I run the following query -

select distinct sys_connect_by_path(name, '/') "path", level
from test1 start with id1 in (7, 3)
connect by prior id2 = id1
order by level;

to get -

/EMP1111 1
/SVP2 1
/EMP1111/DIR111 2
/SVP2/Boss 2
/EMP1111/DIR111/VP11 3
/EMP1111/DIR111/VP11/SVP1 4
/EMP1111/DIR111/VP11/SVP1/Boss 5

I need to filter and restructure the o/p to give me -
Boss/SVP1/VP11/DIR111/EMP1111
Boss/SVP2

i.e. I need to see the entries -
1. which are leaf nodes (no intermediate nodes).
2. the order of printing should be from root -> leaf node.

How can I acheive that ?

Thanks,

- yg

The insert for the previous post should read like following -

Insert into TEST1 ("ID1","NAME","ID2") values (5,'VP21',3);
Insert into TEST1 ("ID1","NAME","ID2") values (4,'VP11',2);
Insert into TEST1 ("ID1","NAME","ID2") values (3,'SVP2',1);
Insert into TEST1 ("ID1","NAME","ID2") values (2,'SVP1',1);
Insert into TEST1 ("ID1","NAME","ID2") values (1,'Boss',null);
Insert into TEST1 ("ID1","NAME","ID2") values (7,'EMP1111',6);
Insert into TEST1 ("ID1","NAME","ID2") values (6,'DIR111',4);

Sorry abt the confusion (I exported the data in a SQL file
using SQLDeveloper and it somehow didn't insert the last col).

- yg

Yes. I'm on 10g.

Tom can surely walk circles around me, but I'm going to take a stab anyway, since my role is transitioning to more SQL query oriented versus DBA.

To reverse the tree, flip the values in the CONNECT BY clause (from id2 = id1 to id1 = id2). Now, use an inline view:

select path, max(level)
from
(select distinct sys_connect_by_path(name, '/') "path", level, id1
from test1 start with id1 in (7, 3)
connect by prior id1 = id2
order by level)
where id1 in (7,3)

Wait a sec, that query won't work. How about this?

select path, level
from
(select distinct sys_connect_by_path(name, '/') "path", level, connect_by_isleaf "isleaf"
from test1 start with id1 in (7, 3)
connect by prior id1 = id2
order by level)
where isleaf = 1;

Thanks Tom for taking time to answer.

The query you suggested errors out -

Error starting at line 1 in command:
select path, level
from
(select distinct sys_connect_by_path(name, '/') "path", level, connect_by_isleaf "isleaf"
from test1 start with id1 in (7, 3)
connect by prior id1 = id2
order by level)
where isleaf = 1
Error report:
SQL Error: ORA-00904: "ISLEAF": invalid identifier

Just looking for confirmation that I was actually able to put something together correctly in SQL. Boy, is this a massive world to learn.

Hi,Tom
When I use Hierarchical SQL combine text like:
select sys_connect_by_path(t160_name,'>'),
sys_connect_by_path(t158_folder_name,'>') from
(select t158_id, t158_file_name,t158_parent_f_category from category) c
join
(select t160_f_category, t160_name from category_locale where contains(t160_text,'%it% within t160_name')>0 ) l
on c.t158_id=l.t160_f_category
start with c.t158_parent_f_category is null
connect by prior t158_id=t158_parent_f_category;

Why it use normal index(on t160_f_category) rather than
use the text index ??
even if i use hint /*+ index ...*/
thanks,

Hello,
I am trying to traverse tree in reverse direction and return full path (from leaf to root) with leaf's data in the same row.
Here's my test case:
create table t(id number,
               p_id number,
               name varchar2(10));
insert into t values(1,null,'root');
insert into t values(2,1,'a2');
insert into t values(3,1,'a3');
insert into t values(4,1,'a4');
insert into t values(5,2,'x1');
insert into t values(6,2,'x2');
insert into t values(7,3,'b3');
insert into t values(8,3,'b4');
insert into t values(9,6,'w3');
insert into t values(10,6,'w5');
insert into t values(11,7,'w3');
17:56:21 YG1> select * from t
17:56:59   2  /

          ID         P_ID NAME
------------ ------------ ------
           1              root
           2            1 a2
           3            1 a3
           4            1 a4
           5            2 x1
           6            2 x2
           7            3 b3
           8            3 b4
           9            6 w3
          10            6 w5
          11            7 w3
I can get full path:
SELECT LEVEL lvl,
       id,
       p_id,
       SYS_CONNECT_BY_PATH(name, '|') path_by_name
  FROM t
  START WITH name = 'w3'
  CONNECT BY id = PRIOR p_id
/
         LVL           ID         P_ID PATH_BY_NAME
------------ ------------ ------------ ------------------
           1            9            6 |w3
           2            6            2 |w3|x2
           3            2            1 |w3|x2|a2
           4            1              |w3|x2|a2|root
           1           11            7 |w3
           2            7            3 |w3|b3
           3            3            1 |w3|b3|a3
           4            1              |w3|b3|a3|root

It works fine but I need id from level 1 and path from level 4 in the same row.

This is the closeast I've got so far:
SELECT lvl,
       id,
       path_by_name 
  FROM(SELECT lvl,
              id,
              path_by_name,
              MIN(lvl) OVER() min_lvl,
              MAX(lvl) OVER() max_lvl
  FROM (SELECT LEVEL lvl,
               DECODE(LEVEL,1,id,NULL) id,
               SYS_CONNECT_BY_PATH(name, '|') path_by_name
          FROM t
         START WITH name = 'w3'
         CONNECT BY id = PRIOR p_id
       )
)
WHERE lvl = max_lvl
   OR lvl = min_lvl
/

         LVL           ID PATH_BY_NAME
------------ ------------ --------------------
           1            9 |w3
           4              |w3|x2|a2|root
           1           11 |w3
           4              |w3|b3|a3|root

What I need to get:
  ID PATH_BY_NAME
---- --------------------
   9 |w3|x2|a2|root
  11 |w3|b3|a3|root

Is it possible to do it in SQL, without PL/SQL?
Thank you

ops$tkyte%ORA10GR2> select to_number(substr(scbp,1,10)) ID, max(substr(scbp,11)) path_by_name
  2    from (
  3  select substr( sys_connect_by_path(decode(level,1,to_char(id,'fm0000000000')) || name,'|'), 2 ) scbp
  4    from t
  5   start with name = 'w3'
  6   connect by prior p_id = id
  7         )
  8   group by to_number(substr(scbp,1,10))
  9   order by to_number(substr(scbp,1,10))
 10  /

        ID PATH_BY_NAME
---------- -------------------------
         9 w3|x2|a2|root
        11 w3|b3|a3|root

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select id, max(scbp) path_by_name
  2    from (
  3  select connect_by_root id id,
  4             sys_connect_by_path( name,'|') scbp
  5    from t
  6   start with name = 'w3'
  7   connect by prior p_id = id
  8         )
  9   group by id
 10   order by id
 11  /

        ID PATH_BY_NAME
---------- -------------------------
         9 |w3|x2|a2|root
        11 |w3|b3|a3|root

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select id, (select max( sys_connect_by_path(name,'|') )
  2                from t t2
  3                           start with t2.rowid = t1.rowid
  4                           connect by prior p_id = id) path_by_name
  5    from t t1
  6   where t1.name = 'w3'
  7   order by id
  8  /

        ID PATH_BY_NAME
---------- -------------------------
         9 |w3|x2|a2|root
        11 |w3|b3|a3|root

Hello,
This version looks the most elegant:
select id, (select max( sys_connect_by_path(name,'|') )
from t t2
start with t2.rowid = t1.rowid
connect by prior p_id = id) path_by_name
from t t1
where t1.name = 'w3'
order by id
/
It works great in 9.2.0.8.0.
Thanks a lot,
YG

How do I roll up numbers up a tree? Suppose I have the following tree (diagram #1). Each node is designated with an ID number,

representing a primay key in a table. Only leaf nodes have a number (preceded by the @ sign) associated with it (see

diagram #1). How do I write an efficient SQL that will add up numbers from the leaf nodes and roll up the numbers

up the entire tree all the way to the root node so the final tree looks like that depicted in diagram #2?

-----------------------
Diagram #1
-----------------------
                            (1)
                             |
                             |
                   ------------------------
                   |         |            |
                   |         |            |
                  (2)       (3)          (4)@10
                   |         |
                   |         |
              -------        |
              |     |        |
              |     |        |
             (5)   (6)@20   (7)@5
              |
              |
             (8)@30


-----------------------
Diagram #2
-----------------------
                            (1)@65
                             |
                             |
                   ------------------------
                   |         |            |
                   |         |            |
                  (2)@50    (3)@5        (4)@10
                   |         |
                   |         |
           ----------        |
           |        |        |
           |        |        |
          (5)@30   (6)@20   (7)@5
           |
           |
          (8)@30



CREATE TABLE tree (
  id       INTEGER PRIMARY KEY,
  parentID INTEGER REFERENCES tree(id),
  score    INTEGER
);

INSERT INTO tree VALUES (1, NULL, NULL);
INSERT INTO tree VALUES (2, 1, NULL);
INSERT INTO tree VALUES (3, 1, NULL);
INSERT INTO tree VALUES (4, 1, 10);
INSERT INTO tree VALUES (5, 2, NULL);
INSERT INTO tree VALUES (6, 2, 20);
INSERT INTO tree VALUES (7, 3, 5);
INSERT INTO tree VALUES (8, 5, 30);

COMMIT;

ops$tkyte%ORA10GR2> select rpad('*',2*level,'*') || id idstr, parentid, score,
  2         (select sum(score)
  3                from tree t2
  4                   start with t2.id = tree.id
  5                   connect by prior id = parentid) score2
  6    from tree
  7  start with parentid is null
  8  connect by prior id = parentid
  9  /

IDSTR                  PARENTID      SCORE     SCORE2
-------------------- ---------- ---------- ----------
**1                                                65
****2                         1                    50
******5                       2                    30
********8                     5         30         30
******6                       2         20         20
****3                         1                     5
******7                       3          5          5
****4                         1         10         10

8 rows selected.

Is is possible to achieve the below using connect by ?

user@mydb > select * from visits;

   SITE_ID   VISIT_ID VISIT_DATE              VISIT_TYPE
---------- ---------- ----------------------- ----------
         1          1 28-Oct-2009 10:28:18 AM P
         1          2 29-Oct-2009 10:28:18 AM P
         1          3 30-Oct-2009 10:28:18 AM P
         1          4 31-Oct-2009 10:28:18 AM S
         1          5 01-Nov-2009 10:28:18 AM S
         1          6 02-Nov-2009 10:28:18 AM S
         1          7 03-Nov-2009 10:28:18 AM S
         1          8 04-Nov-2009 10:28:18 AM S
         1          9 05-Nov-2009 10:28:18 AM S
         1         10 06-Nov-2009 10:28:18 AM S

is it possible to select one visit_type = 'S' record with the least visit_date for every visit_type = 'P' record using a single sql statement ?

the view that I am working on, self joins the vist table 4 times to achieve the same ....., 

This is what is expected ...

   SITE_ID   VISIT_ID VISIT_DATE              VISIT_TYPE RELATED_VISIT_ID
---------- ---------- ----------------------- ---------- ----------------
         1          1 28-Oct-2009 10:28:18 AM P                         4
         1          2 29-Oct-2009 10:28:18 AM P                         4
         1          3 30-Oct-2009 10:28:18 AM P                         4
         
             
visits table is defined as

create table visits
(
   site_id     number(10),
   visit_id    number(10),
   visit_date  date,
   visit_type  varchar2(1)
);

test data can be inserted using the below

insert into visits
select   1,
         rownum,
         sysdate + rownum,
         case
            when rownum < 4 then
              'P'
            else
              'S'
         end
from     all_objects
where    rownum < 11;

Tom,

Duh ..., I feel like such and idiot :)

Thank you so much for your time and the quick response.

Tom,

I saw this article;

http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it

Seems like it's a more "ansi-compliant" method of walking trees starting 11gR2, it looks more cumbersome than the "old" connect by. Wonder if you've seen/done any performance comparision between the "old" and "new" methods? I haven't got enough hardware capacity to load up 11gR2 to try, just wonder if you know about it already.

Hi Tom,
If parent havig child(para_id-->prior child),the child record should come up with parent ,If not havig parent,is it sort the records based on can/pending status?.
[code]
par_id child x_status x_status1 date
100 null NEW pending 10/02/2009
102 null NEW pending 10/02/2009
101 100 UPD fixed 11/02/2009
200 102 upd fixed 11/02/2009
209 102 upd fixed 11/02/2009
201 xxx can pendig 10/02/2009
202 xxx can peding 10/02/2009
203 xxx upd fixed 11/02/2009
204 xxx upd fixed 11/02/2009

expecting o/p

par_id child x_status x_status1 date
100 null NEW pending 10/02/2009
101 100 UPD fixed 11/02/2009
102 null NEW pending 10/02/2009
200 102 upd fixed 11/02/2009
209 102 upd fixed 11/02/2009
201 xxx can pendig 10/02/2009
203 xxx upd fixed 11/02/2009
202 xxx can peding 10/02/2009
204 xxx upd fixed 11/02/2009
[/code]

Regards,
MR

Hello Tom,

Can you please tell me a good book to refer for learning hierarchicahl queries syntaxes?
I really have a question but as it is mentioned not to ask new questions.. I am asking for a good book to refer.

Thanks in advance
Sneha

Tom,

I need to finish data merging for half a million of records, which, I think, could be done by "connect by". For instance,
prm_id dup_id
1 2
2 3
2 4
3 4
10 11
11 12

Final result need to be like this:
prm_id dup_id
1 2
1 3
1 4
10 11
10 12

That means that 2, 3, 4 will be merged to 1, and 11 and 12 will be merged to 10. To find the merging list, I used the following query:

select distinct prm_id, dup_id
from match_list
where connect_by_isleaf =1
connect by prior prm_id = dup_id;

I tried a few records and the results seem fine. However, when running it on the half a million records, it takes forever. Any suggestion on it? Thanks.

ops$tkyte%ORA11GR2> create table t ( prm_id number, dup_id number );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t values ( 1, 2 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 2, 3 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 2, 4 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 3, 4 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 10, 11 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 11, 12 );

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select distinct prm_id, dup_id
  2  from t
  3  where connect_by_isleaf =1
  4  connect by prior prm_id = dup_id;

    PRM_ID     DUP_ID
---------- ----------
         1          2
        10         11

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t
  2  with data as (select 100 + 100*level l from dual connect by level <= 100000)
  3  select prm_id+l, dup_id+l from t, data
  4  /

600000 rows created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set timing on
ops$tkyte%ORA11GR2> set autotrace traceonly statistics
ops$tkyte%ORA11GR2> select distinct prm_id, dup_id
  2  from t
  3  where connect_by_isleaf =1
  4  connect by prior prm_id = dup_id;

200002 rows selected.

Elapsed: 00:00:14.78

Statistics
----------------------------------------------------------
          6  recursive calls
          1  db block gets
       1455  consistent gets
        525  physical reads
        176  redo size
    4293486  bytes sent via SQL*Net to client
     147083  bytes received via SQL*Net from client
      13335  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     200002  rows processed

ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> select count(*) from t;

  COUNT(*)
----------
    600006

Elapsed: 00:00:00.04
ops$tkyte%ORA11GR2> set timing off

Many thanks, Tom.

In case that someone else has the similar problem in the future. Our data contains 54 records which match each other. That is the bottleneck and make the query choked. Fortunately, 54 records are "test" data. After deleting them, the query results of ~500,000 records came out quickly. Thanks, Tom and see you at OOW in Oct.

Hi Tom ,

We have a table something like below which captures the change of hierarchy over time.

Employee Manager fromdate todate

Scott king 2010 Null
King smith 2010 Null
Smith Adam 2010 2011
Smith Fox 2012 Null
Adam Fox 2009 Null
Fox Null 2009 Null

and need an output like

fox 2009 null
Fox :Adam 2009 null
Fox :Adam :Smith 2010 2011
Fox :Adam :Smith:king 2010 2011
Fox :Adam:Smith:King:Scott 2010 2011
Fox :Smith 2012 null
Fox :Smith : king 2012 null
Fox :Smith:King:Scott 2012 null

When I use the connect by approach , it ends up with Cartesian product.Thanks in advance. Appreciate the help.

Hi,

i am using the below query to get the parent hierarchy,

SELECT LISTAGG (child, ' - ')
WITHIN GROUP (ORDER BY LEVEL DESC)
FROM dummy_table pt1
START WITH child = 5
CONNECT BY PRIOR parent = child

i get the result as '1 - 2 -3 -4 - 5'.
how can i remove the '5' from the above result i.e. the lowest node from the end result

SQL> create table t ( p int, c int );

Table created.

SQL>
SQL> insert into t values (4,5);

1 row created.

SQL> insert into t values (3,4);

1 row created.

SQL> insert into t values (2,3);

1 row created.

SQL> insert into t values (1,2);

1 row created.

SQL> insert into t values (null,1);

1 row created.

SQL>
SQL> select listagg (
  2              case when c != connect_by_root c then c end,
  3          ' - ') within group (order by level desc)
  4  from t
  5  start with c = 5
  6  connect by prior p = c ;

LISTAGG(CASEWHENC!=CONNECT_BY_ROOTCTHENCEND,'-')WITHINGROUP(ORDERBYLEVELDESC)
------------------------------------------------------------------------------------
1 - 2 - 3 - 4

Hello,

I am trying to search a child in tree and return all parent nodes (from leaf to root).
Here's my test case:

create table t(id number,
p_id number,
name varchar2(10), display_order number);
insert into t values(1,1,'root',1);
insert into t values(2,1,'a2',1);
insert into t values(3,1,'a3',2);
insert into t values(4,1,'a4',3);
insert into t values(5,2,'x1',1);
insert into t values(6,2,'x2',2);
insert into t values(7,3,'b3',1);
insert into t values(8,3,'b4',2);
insert into t values(9,6,'w3',1);
insert into t values(10,6,'w5',2);
insert into t values(11,7,'w3',1);

This is the closeast I've got so far:
SELECT p_id, id, name, display_order
FROM t
START WITH name like 'w%'
CONNECT BY nocycle id = PRIOR p_id
order by rownum desc

-----------------------
Table #1
-----------------------

P_ID ID Name display_order
------------ ------------ ------------ ------------------
1 3 a3 2
3 7 b3 1
7 11 w3 1
1 2 a2 1
2 6 x2 2
6 10 w5 2
1 2 a2 1
2 6 x2 2
6 9 w3 1

But i need the rows in following order

-----------------------
Table #2
-----------------------

P_ID ID Name display_order
------------ ------------ ------------ ------------------
1 1 root 1
1 2 a2 1
2 6 x2 2
6 9 w3 1
6 10 w5 2
1 3 a3 2
3 7 b3 1
7 11 w3 1

-----------------------
Diagram #1
-----------------------

root
|__a2
| |__x2
| |__w3
| |__w5
|
|__a3
|__b3
|__w3


Is it possible to get the rows as in the Table #2
*sibling should be in the order of display order

SQL> with
  2    t_fixed as
  3       ( select id, decode(name,'root',null,p_id) p_id, name, display_order from t ),
  4    nodes_of_interest as
  5       ( select distinct id,p_id,name,display_order
  6          from t_fixed
  7          start with name like 'w%'
  8          connect by prior p_id =  id
  9       )
 10  select *
 11  from   nodes_of_interest
 12  start with p_id is null
 13  connect by p_id = prior id
 14  order siblings by display_order;

        ID P_ID                                     NAME       DISPLAY_ORDER
---------- ---------------------------------------- ---------- -------------
         1                                          root                   1
         2 1                                        a2                     1
         6 2                                        x2                     2
         9 6                                        w3                     1
        10 6                                        w5                     2
         3 1                                        a3                     2
         7 3                                        b3                     1
        11 7                                        w3                     1

8 rows selected.