Very good. Thanks.
Prince, July 23, 2002 - 1:49 pm UTC
What about ordering?
Alla, September 15, 2003 - 11:48 am UTC
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
September 15, 2003 - 2:30 pm UTC
i'd probably use "level" instead but yes, should work. I'd use an inline view to be safe about it:
select *
from (
select emp.* , rownum r
from EMP
start with EMPNO = :x
connect by prior MGR = EMPNO
)
order by r desc;
Thanks
Alla, September 15, 2003 - 2:48 pm UTC
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!
September 15, 2003 - 2:56 pm UTC
level is a psuedo column associated with connect bys! it is there like rownum is.
Thanks
Alla, September 15, 2003 - 3:08 pm UTC
Damn it, I feel so stupid :-(
September 15, 2003 - 4:16 pm UTC
don't -- i learn something new about oracle each and every day -- honest. It is a really big topic
Raghu
Raghu, September 17, 2003 - 3:12 am UTC
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.
September 17, 2003 - 7:10 am UTC
could be -- you can try using a record_group with "populate_group_from_query" (make sure to bind -- set up a control block and dynamically execute
select .. from tab_nodes start with node_id = :block.item1 .....
then, you can access the result set in the record group. or use EXEC_SQL.
that is what I do when the environment rejects some new sql syntax.
Can we go a bit higher?
Neolle, January 05, 2005 - 2:41 am UTC
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!!!!
January 05, 2005 - 9:50 am UTC
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
Re:Can we go a bit higher?
Neolle, January 05, 2005 - 3:05 am UTC
I got it already Tom.
Thanks anyway.
re: Can we get a bit higher?
Neolle, January 06, 2005 - 2:51 am UTC
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.
January 06, 2005 - 10:45 am UTC
level is not a field, it is a builtin psuedo column assigned by the connect by processing.
not sure what examples you are talking about.
The closest ancestors named 'X'
David, March 02, 2005 - 7:25 am UTC
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!!
March 02, 2005 - 7:48 am UTC
i cannot help, the queries return ora-942 on my system
but with scalar subqueries, the correlation names only go "one deep", you are trying two levels deep
how about one level
select max( decode(rownum,1,fr_id) )
from t
where fr_name='x'
start with fr_id = table2.fr_id
connect by prior parent_fr_id = fr_id
recursively traversing the tree with connect by
Kubilay, June 28, 2005 - 11:35 am UTC
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
June 28, 2005 - 12:45 pm UTC
would the information returned by sys_connect_by_path be sufficient (all of the parents on the same row)?
recursively traversing the tree with connect by
Kubilay, June 29, 2005 - 7:50 am UTC
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
June 29, 2005 - 9:14 am UTC
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.
recursively traversing the tree with connect by
kubilay, June 29, 2005 - 9:54 am UTC
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
Reader, August 11, 2006 - 4:25 pm UTC
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.
August 11, 2006 - 6:09 pm UTC
the only way to decide is to have a question to be asked.
If you want to go from manager to managed - you have to take the prior records EMPNO and find all of the records that have MGR equal to that EMPNO (take manager key and find the managed people)
If you want to go from the managed person to their manager and their managers manager and so on, you have to take the prior records MGR and find those records.
It is 100% in the question you are asking.
As it is with all SQL :)
recursively finding all parents.
Dan, August 24, 2006 - 7:42 pm UTC
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
August 27, 2006 - 8:10 pm UTC
would sys_connect_by_path on a regular hierarchical query be sufficient?
recursively finding all parents
Dan, August 28, 2006 - 5:26 pm UTC
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
August 28, 2006 - 5:37 pm UTC
My question was more along the lines of "is what sys connect by path returns more than sufficient"
do you really need it broken down like that. or is "across" better than good enough?
recusively finding all parents
Dan, August 29, 2006 - 1:52 pm UTC
Yes, I need the data in that specific format.
Thats the problem.
Thanks,
Dan
August 29, 2006 - 4:38 pm UTC
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.
How can I do this
Parag J Patankar, November 11, 2006 - 7:10 am UTC
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
November 11, 2006 - 8:20 am UTC
doesn't seem like an entirely "reasonable real world" sort of thing, but...
scott%ORA10GR2> select a.*, rownum
2 from (
3 select rpad('* ',2*level,'* ')||ename EmpName, dname
4 from v
5 start with mgr is null
6 connect by prior empno = mgr
7 order siblings by ename
8 ) a
9 order by rownum DESC;
EMPNAME DNAME ROWNUM
-------------------- -------------- ----------
* * * * ADAMS RESEARCH 14
* * * SCOTT RESEARCH 13
* * * * SMITH RESEARCH 12
* * * FORD RESEARCH 11
* * JONES RESEARCH 10
* * * MILLER ACCOUNTING 9
* * CLARK ACCOUNTING 8
* * * WARD SALES 7
* * * TURNER SALES 6
* * * MARTIN SALES 5
* * * JAMES SALES 4
* * * ALLEN SALES 3
* * BLAKE SALES 2
* KING ACCOUNTING 1
14 rows selected.
Traverse from leaf to root, print from root to leaf !!!
yg, November 14, 2006 - 9:15 pm UTC
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
November 15, 2006 - 6:56 am UTC
are you on 10g or not.
Re:Traverse from leaf to root, print from root to leaf !!!
yg, November 14, 2006 - 9:27 pm UTC
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
Re:Traverse from leaf to root, print from root to leaf !!!
yg, November 15, 2006 - 2:58 pm UTC
Yes. I'm on 10g.
November 16, 2006 - 8:19 am UTC
use connect by isroot, you can get just the roots.
new function.
To: yg
Tom Fox, November 15, 2006 - 7:07 pm UTC
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)
To: yg
Tom Fox, November 15, 2006 - 7:33 pm UTC
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;
To: Tom Fox
yg, November 15, 2006 - 7:46 pm UTC
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
November 16, 2006 - 3:03 pm UTC
where "isleaf" = 1
So was my query correct too?
Tom Fox, November 16, 2006 - 4:02 pm UTC
Just looking for confirmation that I was actually able to put something together correctly in SQL. Boy, is this a massive world to learn.
November 16, 2006 - 4:09 pm UTC
don't know :) didn't read it, just knew why isleaf was not 'referenced' correctly.
yes, it is large.
rain
rain, November 30, 2006 - 10:10 pm UTC
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,
December 01, 2006 - 5:17 am UTC
why didn't my car start?
You have as much chance of coming up with a good, complete answer for that as I do for this.
We have about the same amount of information.
YG, December 09, 2008 - 9:15 pm UTC
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
December 10, 2008 - 9:26 am UTC
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
first one is 9i compatible, second two are 10g and above.
I liked the last one
Yelena Galuzo, December 22, 2008 - 6:07 pm UTC
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
Tree Aggregation
A reader, October 19, 2009 - 5:46 pm UTC
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;
October 22, 2009 - 4:16 pm UTC
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.
Connect By ?
BC, October 27, 2009 - 9:39 am UTC
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;
October 27, 2009 - 12:05 pm UTC
... 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 ?
...
why would connect by be relevant?
select *
from (select * from visits where visit_type='P'),
(select * from (select * from visits where visit_type='S' order by visit_date) where rownum = 1)
/
Awesome ....
BC, October 27, 2009 - 12:21 pm UTC
Tom,
Duh ..., I feel like such and idiot :)
Thank you so much for your time and the quick response.
hierarchical 11g r2
A reader, March 11, 2010 - 10:50 am UTC
March 12, 2010 - 2:23 pm UTC
I have not, but I would anticipate (in fact, I know) different plans from the get-go since the recursive with subquery factoring can do things that connect by simply cannot. It is a different beast.
A reader, July 31, 2010 - 2:50 am UTC
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
Very helpful queries
sneha Dhanya, April 21, 2011 - 12:54 pm UTC
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
April 25, 2011 - 8:25 am UTC
The sql language reference teaches you the syntax,
time, practice, application of the technique
is necessary to learn HOW to use it. I doubt you need help with the syntax - that is very cut and dry. You need time spent using the syntax to "learn" how it works.
Just like you needed time with FOR when learning to program and such.
A reader, July 26, 2011 - 2:05 pm UTC
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.
July 28, 2011 - 6:47 pm UTC
define "forever", describe the machine this is run on, share the plan.
doesn't seem like it should take more than a few seconds - given that it is such a tiny table, just a 1/2 million records.
for example, on my laptop....
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
A reader, August 01, 2011 - 9:11 am UTC
Many thanks, Tom.
A reader, August 15, 2011 - 8:38 am UTC
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.
Hierarchical Query with time dimention
Poorni, November 12, 2014 - 8:41 pm UTC
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.
Remove the lowest node from the result
Lovlesh, September 21, 2017 - 11:05 am UTC
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
September 22, 2017 - 12:46 am UTC
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
Jitendra, February 22, 2018 - 7:31 pm UTC
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
February 23, 2018 - 1:54 am UTC
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.