sys_connect_by_path is very useful ... when it works
vc, July 31, 2003 - 9:43 am UTC
Please consider this:
select count(*) from (select sys_connect_by_path employee_id,',') from employees
start with employee_id=100
connect by prior employee_id=manager_id).
This query crashes each time with the error message 'ORA-03113 end-of-file on communication channel'.
The Oracle version/OS:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
The same behaviour can be seen under the 9.2.0.3 Windows and 9.2.0.1 Linux versions.
I have a couple of other sys_connect_by_path queries crashing and producing a core log file but this one is the simplest (uses the HR sample schema). It's a real problem for us since we'll have to resort to writing PL/SQL in the cases where sys_connect_by_path would suffice had it worked.
Thanks.
July 31, 2003 - 11:46 am UTC
and you have filed a bug with support right -- if you don't do that, not much i can do myself.
but do contact support and reference bug2711380, possibility for a patch and a workaround exist
another 8i method
Barbara Boehmer, August 01, 2003 - 11:37 pm UTC
Here is another 8i method, using a package by Solomon Yakobson:
SQL> -- Hierarchy package by Solomon Yacobson:
SQL> CREATE OR REPLACE
2 PACKAGE Hierarchy
3 IS
4 TYPE BranchTableType IS TABLE OF VARCHAR2(4000)
5 INDEX BY BINARY_INTEGER;
6 BranchTable BranchTableType;
7 FUNCTION Branch(vLevel IN NUMBER,
8 vValue IN VARCHAR2,
9 vDelimiter IN VARCHAR2 DEFAULT CHR(0))
10 RETURN VARCHAR2;
11 PRAGMA RESTRICT_REFERENCES(Branch,WNDS);
12 END Hierarchy;
13 /
Package created.
SQL> CREATE OR REPLACE
2 PACKAGE BODY Hierarchy
3 IS
4 ReturnValue VARCHAR2(4000);
5 FUNCTION Branch(vLevel IN NUMBER,
6 vValue IN VARCHAR2,
7 vDelimiter IN VARCHAR2 DEFAULT CHR(0))
8 RETURN VARCHAR2
9 IS
10 BEGIN
11 BranchTable(vLevel) := vValue;
12 ReturnValue := vValue;
13 FOR I IN REVERSE 1..vLevel - 1 LOOP
14 ReturnValue := BranchTable(I)|| vDelimiter || ReturnValue;
15 END LOOP;
16 RETURN ReturnValue;
17 END Branch;
18 END Hierarchy;
19 /
Package body created.
SQL> COLUMN emp_name FORMAT A15
SQL> COLUMN scbp FORMAT A35
SQL> SELECT RPAD ('*', 2 * LEVEL, '*') || ename emp_name, job,
2 hierarchy.branch (LEVEL, job, '/') scbp
3 FROM emp
4 START WITH mgr IS NULL
5 CONNECT BY PRIOR empno = mgr
6 /
EMP_NAME JOB SCBP
--------------- --------- -----------------------------------
**KING PRESIDENT PRESIDENT
****JONES MANAGER PRESIDENT/MANAGER
******SCOTT ANALYST PRESIDENT/MANAGER/ANALYST
********ADAMS CLERK PRESIDENT/MANAGER/ANALYST/CLERK
******FORD ANALYST PRESIDENT/MANAGER/ANALYST
********SMITH CLERK PRESIDENT/MANAGER/ANALYST/CLERK
****BLAKE MANAGER PRESIDENT/MANAGER
******ALLEN SALESMAN PRESIDENT/MANAGER/SALESMAN
******WARD SALESMAN PRESIDENT/MANAGER/SALESMAN
******MARTIN SALESMAN PRESIDENT/MANAGER/SALESMAN
******TURNER SALESMAN PRESIDENT/MANAGER/SALESMAN
******JAMES CLERK PRESIDENT/MANAGER/CLERK
****CLARK MANAGER PRESIDENT/MANAGER
******MILLER CLERK PRESIDENT/MANAGER/CLERK
14 rows selected.
Hierarchy tree
Raj, April 20, 2004 - 7:46 am UTC
Tom,
Please help me in getting the query for the below simplified example
create table emp
(
emp_code varchar2(10),
emp_name varchar2(40),
mgr_code varchar2(10)
);
sample data is :
emp_code emp_name mgr_code
1005 Frank 1020
1001 David 1005
1003 Ram 1005
1004 Rose 1003
1006 Carl 1004
1007 mike 1006
1002 Susan 1005
the output should be either of the one ways below:
1)
Mgr1 Mgr2 Mgr3 Mgr4 Mgr5
Frank
David
Ram
Rose
Carl
Mike
Susan
i need the output in the above way because, i need to join this data with another transaction table and display sub totals at each level.
or
2)
Mgr1 Mgr2 Emp
Frank
David
Ram
Rose
Carl
Mike
Susan
here it is maximum only three levels
April 20, 2004 - 8:55 am UTC
select decode( mgr_code, 1020, ename ) mgr1,
decode( mgr_code, nnnn, ename ) mgr2,
.... and so on ....
from emp
/
Hierarchial tree
Raj, April 20, 2004 - 7:48 am UTC
forgot to put the version we are using in my previous comment, we use Oracle 9i(9.2)
Hierarcial tree
Raj, April 20, 2004 - 11:57 pm UTC
Tom,
sorry , i am not clear how this query does that
"select decode( mgr_code, 1020, ename ) mgr1,
decode( mgr_code, nnnn, ename ) mgr2,
.... and so on ....
from emp"
what is "nnnn" ?
is it the emp_code? may be i am not clear.
my version is oracle 9i(9.2)
this is the table:
SQL> create table emp
2 (
3 emp_code varchar2(10),
4 emp_name varchar2(40),
5 mgr_code varchar2(10)
6 );
SQL> insert into emp values(1005,'Frank',1020);
1 row inserted
SQL> insert into emp values(1001,'David',1005);
1 row inserted
SQL> insert into emp values(1003,'Ram',1005);
1 row inserted
SQL> insert into emp values(1004,'Rose',1003);
1 row inserted
SQL> insert into emp values(1006,'Carl',1003);
1 row inserted
SQL> insert into emp values(1007,'Mike',1004);
1 row inserted
SQL> insert into emp values(1008,'Lilly',1007);
1 row inserted
SQL> insert into emp values(1002,'Susan',1002);
1 row inserted
SQL> insert into emp values(1020,'Clarke',null);
1 row inserted
SQL> select * from emp;
EMP_CODE EMP_NAME MGR_CODE
---------- ---------------------------------------- ----------
1005 Frank 1020
1001 David 1005
1003 Ram 1005
1004 Rose 1003
1006 Carl 1003
1007 Mike 1004
1008 Lilly 1007
1002 Susan 1002
1020 Clarke
now if i have to see the hierarchy for "Frank", then i write the below query :
SQL> select lpad(' ',level * 10) || emp_name from emp start with emp_code = 1005
2 connect by prior emp_code = mgr_code;
LPAD('',LEVEL*10)||EMP_NAME
--------------------------------------------------------------------------------
Frank
David
Ram
Rose
Mike
Lilly
Carl
7 rows selected
now i want to join this with another table and display the totals/subtotals at each level, so i need each level to be in a seperate column like below, such that i get totals at each level
by giving rollup(mgr1,mgr2...)
Mgr1 Mgr2 Mgr3 Mgr4 Mgr5 Mgr6 Total1 ...
Frank
David
Ram
Rose
Mike
Lilly
Carl
OR the below output is also ok, where max level columns is fixed to 3(all further levels fall in the 3rd):
2)
Mgr1 Mgr2 Emp
Frank
David
Ram
Rose
Mike
Lilly
Mike
April 21, 2004 - 7:40 pm UTC
nnnn is your various mgr_codes.
"How to Rollup Hierarchy from CONNECT BY into Singe Row?", version 8.1.7
Mahesh, July 21, 2004 - 7:42 pm UTC
This helps but how do I join this query to my organization table(the link is organization_id) for the Org Name in V8.1.7 ?
EMP_NAME JOB SCBP ORGS
------------------------------ --------- ---------------
**KING PRESIDENT /PRESIDENT FINANCE
****JONES MANAGER /PRESIDENT/MANAGER EXPENSE
How to Rollup Hierarchy from CONNECT BY into Singe Row?", version 8.1.7
mahesh, July 21, 2004 - 7:55 pm UTC
The context is for this above example--
SQL> COLUMN emp_name FORMAT A15
SQL> COLUMN scbp FORMAT A35
SQL> SELECT RPAD ('*', 2 * LEVEL, '*') || ename emp_name, job,
2 hierarchy.branch (LEVEL, job, '/') scbp
3 FROM emp
4 START WITH mgr IS NULL
5 CONNECT BY PRIOR empno = mgr
6 /
I would like to do something like --
select a.emp_name, a.job, hierarchy.branch (a.LEVEL, a.job, '/') scbp, b.org
from (SELECT RPAD ('*', 2 * LEVEL, '*') || ename emp_name, job,
hierarchy.branch (LEVEL, job, '/') scbp
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr) a, organization b
where a.organization_id =b.organization_id;
How do I join this query to my organization table(the link is organization_id) for the Org Name in V8.1.7 ?
EMP_NAME JOB SCBP ORGS
------------------------------ --------- ---------------
**KING PRESIDENT /PRESIDENT FINANCE
****JONES MANAGER /PRESIDENT/MANAGER EXPENSE
-thanks a ton !!
July 21, 2004 - 8:40 pm UTC
select emp_name, job, scbp, (select org from organization b where b.organization_id = a.organization_id) org
from (
SELECT RPAD ('*', 2 * LEVEL, '*') || ename emp_name, job,
hierarchy.branch (LEVEL, job, '/') scbp
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
)
scalar subqueries...
How to Rollup Hierarchy from CONNECT BY into Singe Row?", version 8.1.7
Mahesh, August 25, 2004 - 8:52 pm UTC
That was useful....but wanted to extend this query...
This query does not work if I have the same person existing in multiple organizations...That is if we have more than one organization_id value joins ...Pls need some help on this ...-thx
August 26, 2004 - 9:29 am UTC
so, which of the N random orgs would you like to see returned?
phrase the question as a specification and software can be developed.
just stating "i have more than one org" cannot lead to a solution!
How to Rollup Hierarchy from CONNECT BY into Singe Row?", version 8.1.7
mahesh, August 25, 2004 - 9:43 pm UTC
Basically, the same organization_id returns multiple rows(Different organizations names, which means we cannot use a scalar query..)..So how do I get this done in ONE query...
-thx
August 26, 2004 - 9:30 am UTC
specification: A description of the essential technical requirements for items (hardware and software), materials, and processes that includes verification criteria for determining whether the requirements are met.
so, what do you WANT to have happen here -- inputs, outputs, what is the answer to be?
Mahesh, August 26, 2004 - 7:31 pm UTC
pm_directory_categories
DIRECTORY_CATEGORY_ID PARENT_ID DISPLAY_NAME
--------------------- ---------- ---------------
3027212 3027138 test1
3027215 3027138 test2
3027216 3027215 test2a
3027138 3027174 tboulos
SQL>
pm_addressables
DIRECTORY_CATEGORY_ID ADDRESSABLE_ID ORGANIZATION_ID ADDRESSABLE_NAM
--------------------- ------------------------- --------------- ---------------
3027138 10434983836251397 2429584 tbombmailbox
3027212 10434983839393536 2429584 mirror <<
3027138 10434983839159832 2429584 certecho
3027138 10434983838279492 2429584 echo
3027138 10434983836214988 2429584 calc <<<<
3027138 10434983838344964 2429584 client
3027138 10434983839397640 2429584 deny
3027138 10434983838106645 2429584 allow <<<
3027216 10434983838106645 2429584 allow <<<<
3027138 10434983836042717 2429584 calc_2_2
3027138 10434983838172245 2429584 calc_2_1
3027138 10434983836108189 2429584 spm11
REQUIRED OUTPUT format(Sample of 4 rows only, it will be all and more)
--------------
- calc , tboulos
- mirror, 3027212,tboulos(Instead of the number 3027138)
- allow, 3027216,3027215,tboulos(Instead of the number 3027138)
- allow, tboulos
The query which does work for some cases but not all, like the one for "allow"...
SELECT
a.addressable_name_lowercase||',cn=services,o=Public,o=gcc'
,' dircat:'||c.directory_category_id ||'levl'||c.lvl
, 'gcCategories:cn='|| mp_prov_cats_hierarchy.branch (c.lvl, c.directory_category_id, ',cn=')||',cn=providers,o=public,o
=gcc'
from mp_addressables a, (select directory_category_id , (level-1) lvl
FROM mp_directory_categories
connect by prior directory_category_id = parent_id
start with directory_category_id in -- =3027174 )c
(select directory_category_id
from mp_directory_categories
--where name='subjects' and parent_id is null)) c
where parent_id is null)) c
where
a.delete_flag=0
and a.directory_category_id = c.directory_category_id
and a.organization_id =2429584
and a.addressable_type=1
---The package
CREATE OR REPLACE PACKAGE mp_prov_cats_Hierarchy
IS
TYPE BranchTableType IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;
BranchTable BranchTableType;
FUNCTION Branch(vLevel IN NUMBER,
vValue IN VARCHAR2,
vDelimiter IN VARCHAR2 DEFAULT CHR(0))
RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(Branch,WNDS);
END mp_prov_cats_Hierarchy;
/
CREATE OR REPLACE PACKAGE BODY MP_prov_cats_Hierarchy IS
ReturnValue VARCHAR2(4000);
FUNCTION Branch(vLevel IN NUMBER,
vValue IN VARCHAR2,
vDelimiter IN VARCHAR2 DEFAULT CHR(0))
RETURN VARCHAR2
IS
dir_root varchar2(80);
BEGIN
BranchTable(vLevel) := vValue;
ReturnValue := vValue;
FOR I IN REVERSE 1..vLevel - 1 LOOP
ReturnValue := ReturnValue || vDelimiter || BranchTable(I) ;
-- ReturnValue := BranchTable(I)|| vDelimiter || ReturnValue;
END LOOP;
IF vlevel =1 THEN
select substr(name , 1,INSTR(name,'.')-1 )||',cn='||substr(name, INSTR(name,'.') +1) into dir_root
from gc_directory_categories where directory_category_id=BranchTable(1) ;
RETURN dir_root;
ELSE
select ',cn='||substr(name , 1,INSTR(name,'.')-1 )||',cn='||substr(name, INSTR(name,'.') +1) into dir_root
from gc_directory_categories where directory_category_id=BranchTable(1) ;
RETURN substr(Returnvalue,1, INSTR(ReturnValue,vDelimiter,-1)-1)|| dir_root ;
END IF;
END Branch;
END mp_prov_cats_Hierarchy;
/
August 26, 2004 - 7:36 pm UTC
pretty, but ....
create table
insert into table
and an english (eg: textual) explaination. i'd rather not reverse engineer code that doesn't do what you want in the first place..
Mahesh, August 26, 2004 - 8:18 pm UTC
create table pm_directory_categories
(
directory_category_id number,
parent_id number,
display_name varchar2(80)
);
create table pm_addressables
(directorory_category_id number,
addressable_id number,
oragnization_id number,
addressable_name varchar2(80));
Insert Into pm_directory_categories values(3027212, 3027138, 'test1');
Insert Into pm_directory_categories values(3027215, 3027138, 'test2');
Insert Into pm_directory_categories values(3027216, 3027215, 'test2a');
Insert Into pm_directory_categories values(3027138, 3027174, 'mahesh');
insert into pm_addressables values( 3027138, 10434983836251397,2429584,'tbombmailbox');
insert into pm_addressables values( 3027212, 10434983839393536,2429584,'mirror');
insert into pm_addressables values( 3027138, 10434983836214988,2429584,'calc');
insert into pm_addressables values( 3027138, 10434983838106645,2429584,'allow');
insert into pm_addressables values( 3027216, 10434983838106645,2429584,'allow');
insert into pm_addressables values( 3027138, 10434983836042717,2429584,'calc_2_2');
insert into pm_addressables values( 3027138, 10434983838172245,2429584,'calc_2_1');
insert into pm_addressables values( 3027138, 10434983839397640,2429584,'deny');
I like to join the two tables and generate this following ouput---
OUTPUT format
--------------
- calc , mahesh
- mirror, 3027212,mahesh (Instead of the number 3027138)
- allow, 3027216,3027215,mahesh (Instead of the number 3027138)
- allow, mahesh
- deny, mahesh
Basically, the top table maintains a tree structure of different categories . Each categories and have muliple entries in the addressables....SO I like to flatten the tree structure to make sure I have the lowest child(with its path to its top most root) and have the addressable name against it. Only the root will have the name while its childs just conatin the id's...
Both the tables are linked with using the directory_category_id and I would like to generate the output based on a specific organization_id.
August 27, 2004 - 7:28 am UTC
ops$tkyte@ORA9IR2> select addressable_name,
2 nm,
3 substr( data, 2, instr(data,',',-1)-2 ) data
4 from (
5 select addressable_name,
6 rtrim(substr( data, 5, 80)) nm,
7 substr( data, 85 ) data
8 from (
9 select addressable_name,
10 (select max( to_char(level,'fm0000') ||
11 rpad( display_name, 80 ) ||
12 sys_connect_by_path(directory_category_id,',') )
13 from pm_directory_categories b
14 start with b.directory_category_id = a.directorory_category_id
15 connect by prior b.parent_id = b.directory_category_id ) data
16 from pm_addressables a
17 where a.oragnization_id = 2429584
18 )
19 )
20 /
ADDRESSABLE_NAM NM DATA
--------------- -------------------- ------------------------------
tbombmailbox mahesh
mirror mahesh 3027212
calc mahesh
allow mahesh
allow mahesh 3027216,3027215
calc_2_2 mahesh
calc_2_1 mahesh
deny mahesh
8 rows selected.
Mahesh, August 27, 2004 - 11:52 am UTC
I want to run this query in 8.1.7.4, which does not have -sys_connect_by_path...Thanks
August 27, 2004 - 12:56 pm UTC
does your data have a maximum level it will go down, you can use decode, max and || instead.
Mahesh, August 27, 2004 - 1:23 pm UTC
The max level will be 6 from the root. - thx
August 27, 2004 - 1:36 pm UTC
so, max/decode/||
ops$tkyte@ORA9IR2> select addressable_name,
2 nm,
3 substr( data, 2, instr(data,',',-1)-2 ) data
4 from (
5 select addressable_name,
6 rtrim(substr( data, 5, 80)) nm,
7 substr( data, 85 ) data
8 from (
9 select addressable_name,
10 (select rtrim(
11 max( to_char(level,'fm0000') ||
12 rpad( display_name, 80 ) ) ||
13 ',' || max( decode( level, 1, directory_category_id ) ) ||
14 ',' || max( decode( level, 2, directory_category_id ) ) ||
15 ',' || max( decode( level, 3, directory_category_id ) ) ||
16 ',' || max( decode( level, 4, directory_category_id ) ) ||
17 ',' || max( decode( level, 5, directory_category_id ) ) ||
18 ',' || max( decode( level, 6, directory_category_id ) ), ',' )
19 from pm_directory_categories b
20 start with b.directory_category_id = a.directorory_category_id
21 connect by prior b.parent_id = b.directory_category_id ) data
22 from pm_addressables a
23 where a.oragnization_id = 2429584
24 )
25 )
26 /
ADDRESSABLE_NAM NM DATA
--------------- ---------- ------------------------------
tbombmailbox mahesh
mirror mahesh 3027212
calc mahesh
allow mahesh
allow mahesh 3027216,3027215
calc_2_2 mahesh
calc_2_1 mahesh
deny mahesh
8 rows selected.
mahesh, August 27, 2004 - 2:56 pm UTC
Thx a lot...but a minor change ...
My data had one problem that is mahesh is the child of the ROOT.
root
/ \
steve mahesh
Pls if its not a bother...
ADDRESSABLE_NAM NM DATA
--------------- ---------- ------------------------------
tbombmailbox root mahesh
mirror root 3027212,mahesh
calc root mahesh
allow root mahesh
allow root 3027216,3027215,mahesh
calc_2_2 root mahesh
calc_2_1 root mahesh
deny root mahesh
August 27, 2004 - 3:03 pm UTC
give it a try -- you've seen the entire technique, give it a go and see what you can do.
(given that "root" doesn't exist in your sample data it would be hard for me to give you anymore)
mahesh, August 27, 2004 - 3:56 pm UTC
Thx a ton, it was useful and I am trying to understand your query and see if I can modify it....basically, I will be doing more manipulation based on the result set.......
Here is the missing row and required output, if its simple for you....
Insert Into pm_directory_categories values(3027174, null, 'root');
ADDRESSABLE_NAM NM DATA
--------------- ---------- ------------------------------
tbombmailbox mahesh
mirror mahesh 3027212
calc mahesh
allow mahesh
allow mahesh 3027216,3027215
calc_2_2 mahesh
calc_2_1 mahesh
deny mahesh
Then I could just concatenate the data||nm later...
August 27, 2004 - 4:30 pm UTC
go for it -- give it a whirl please! at least try.
A reader, August 27, 2004 - 7:50 pm UTC
I actually have been trying this for a few days...I am in a time crunch...:)
August 27, 2004 - 7:57 pm UTC
what did your attempts look like? maybe I can fix it.
Mahesh, August 27, 2004 - 8:49 pm UTC
(select rtrim(
min(to_char(level,'fm0000') ||
rpad( display_name ,80 )) ||
I am stuck here as to how do I get it to display the name one level below the root....
August 28, 2004 - 9:38 am UTC
sigh, substr the data, so as to get the number you want to convert into a name and add yet another scalar subquery to the outer most level of the query to retrieve that name.
Mahesh, August 27, 2004 - 8:56 pm UTC
mimemirror mahesh
mimeecho mahesh
mirror test1 3027212 <<<<
deny mahesh
tbombmailbox mahesh
allow mahesh
allow test2a 3027216,3027215 <<<<
rcv mahesh
select addressable_name_lowercase,
nm,
substr( data, 2, instr(data,',',-1,2)-2 ) data
from (
select addressable_name_lowercase,
rtrim(substr( data, 5, 80)) nm,
substr( data, 85 ) data
from (
select addressable_name_lowercase,
(select rtrim(
min(to_char(level,'fm0000') ||
rpad( display_name ,80 )) ||
-- ',' || max( decode( level, 2, display_name ) ) ||
-- ',' || max( decode( level, 3, display_name ) ) ||
-- ',' || max( decode( level, 4, display_name ) ),80) ||
',' || max( decode( level, 1, directory_category_id ) ) ||
',' || max( decode( level, 2, directory_category_id ) ) ||
',' || max( decode( level, 3, directory_category_id ) ) ||
',' || max( decode( level, 4, directory_category_id ) ) ||
',' || max( decode( level, 5, directory_category_id ) ) ||
',' || max( decode( level, 6, directory_category_id ) ), ',' )
from gc_directory_categories b
start with b.directory_category_id = a.directory_category_id
connect by prior b.parent_id = b.directory_category_id ) data
from mp_vw_addressables a
where a.organization_id = 2429584
and a.delete_flag=0
and a.addressable_type=1
)
)
mahesh, August 29, 2004 - 3:42 am UTC
Thx a ton. I finally was able to complete the query with 2 additional scalar's...:)
Reversing SYS_CONNECT_BY_PATH
Bob B, October 19, 2004 - 3:10 pm UTC
Is there a way to reverse the order the sys_connect_by_path displays the data in.
CREATE TABLE CODES (
code_id NUMBER(10) NOT NULL,
pt_code_id NUMBER(10),
code_desc VARCHAR2(10) NOT NULL,
PRIMARY KEY (code_id),
FOREIGN KEY (pt_code_id) REFERENCES CODES (code_id)
);
INSERT INTO CODES VALUES( 1, NULL, 'Top' );
INSERT INTO CODES VALUES( 2, 1, 'Middle' );
INSERT INTO CODES VALUES( 3, 2, 'Bottom' );
SELECT SYS_CONNECT_BY_PATH( c.CODE_DESC, '/' ) full_desc
FROM CODES c
WHERE c.CODE_ID = 3
START WITH c.PT_CODE_ID IS NULL
CONNECT BY PRIOR c.CODE_ID = c.PT_CODE_ID
Displays: '/Top/Middle/Bottom'
SELECT SYS_CONNECT_BY_PATH( c.CODE_DESC, '/' ) full_desc
FROM CODES c
WHERE c.PT_CODE_ID IS NULL
START WITH c.CODE_ID = 3
CONNECT BY PRIOR c.PT_CODE_ID = c.CODE_ID
Displays: '/Bottom/Middle/Top'
The second query works more efficiently to retrieve the full description of a single code, but returns it in the reverse order of what is desired (the order is certainly correct, just not for my purposes).
I've considered using an FBI on a decode (DECODE( PT_CODE_ID, NULL, 1 )), however, this still results in a full scan of the codes table as enough of the codes are top level to make the full scan worthwhile. I've also considered writing a function that splits on '/', reverses the order, and then rejoins. I've ruled that out as it would be just as easy to order all the codes by level (using the second query) and then use PL/SQL to print the codes out with a delimiter.
So is there a way to do this in straight sql?
October 19, 2004 - 4:17 pm UTC
do you have a "maximum" depth? (level)
Depth
Bob B, October 19, 2004 - 4:21 pm UTC
There is no limit on how deep it can go.
October 19, 2004 - 4:26 pm UTC
then a plsql function to reverse it procedurally would be my call -- will work find for hundreds of rows.
short of:
SELECT SYS_CONNECT_BY_PATH( c.CODE_DESC, '/' ) full_desc
FROM CODES c
WHERE c.CODE_ID = 3
START WITH rowid in ( SELECT rowid
FROM CODES c
WHERE c.PT_CODE_ID IS NULL
START WITH c.CODE_ID = 3
CONNECT BY PRIOR c.PT_CODE_ID = c.CODE_ID)
CONNECT BY PRIOR c.CODE_ID = c.PT_CODE_ID
Minor Correction
Bob B, October 19, 2004 - 5:12 pm UTC
I think you meant
SELECT SYS_CONNECT_BY_PATH( c.CODE_DESC, '/' ) full_desc
FROM CODES c
WHERE c.CODE_ID = 3
START WITH rowid = ( SELECT rowid
FROM CODES c
WHERE c.PT_CODE_ID IS NULL
START WITH c.CODE_ID = 3
CONNECT BY PRIOR c.PT_CODE_ID = c.CODE_ID)
CONNECT BY PRIOR c.CODE_ID = c.PT_CODE_ID
Testing on the data in my tables, '=' was 14 times faster than 'in'. Seeing as how there is only one path from any leaf to the parent, we can count on the scalar subquery returning exactly one row.
October 19, 2004 - 8:52 pm UTC
I meant in, cause in generale there could be more than one. You have specific knowledge that i did not so you can use "="
two comments ...
Gabe, October 19, 2004 - 9:46 pm UTC
1. <quote>I meant in, cause in generale there could be more than one</quote>
Well, the way I understand the inner-most query ... it returns the rowid of the root of the hierarchy containing code_id=3 ... since the path from a node to its root is unique in a hierarchy, that query should return maximum one rowid. Now the only way to get more than one rowid back (and hence the "in") would be for that table to contain multiple hierarchies and node_id=3 to be part of more than one of them. True ... but look at the table definition ...
CREATE TABLE CODES (
code_id NUMBER(10) NOT NULL,
pt_code_id NUMBER(10),
code_desc VARCHAR2(10) NOT NULL,
PRIMARY KEY (code_id), <=== HERE
FOREIGN KEY (pt_code_id) REFERENCES CODES (code_id)
);
node_id=3 can only belong to one hierarchy ... and hence, we do have the specific knowledge to safely use "=".
Am I missing something?
2. Not that anyone should care by now (we are in 10g times after all) ... but that "Hierarchy" package presented last year as a 8i method for doing what sys_connect_by_path does is a bug waiting to happen. One needs to understand how it works in order to used it safely.
October 20, 2004 - 7:03 am UTC
1) right -- but i didn't look at your create table so I missed the "primary key" part :)
2) "One needs to understand how
it works in order to used it safely"
that would be a great tagline in an email signature for "general life advice" -- that would be TRUE of pretty much everything!
not my intention though ...
Gabe, October 20, 2004 - 2:14 pm UTC
<quote>that would be TRUE of pretty much everything</quote>
I'm not much into "general life advice" :) ... all I was trying to say is ... functionality wise, "Hierarchy" package != sys_connect_by_path.
Cheers.
Deleting hierarchy data
Reader, October 26, 2004 - 9:58 pm UTC
On a table that contains the rollup hierarchy for a organization (ie Division, Office, Group, Program) is there a way to easily delete data from one Divisoin that will also remove all children and grandchildren, etc ..
October 27, 2004 - 7:01 am UTC
like I said on the other page you asked this same thing just a short while ago --
think "in"
delete from where in (your_query_that_finds_rows_to_delete)
Reversing SYS_CONNECT_BY_PATH
Jon, November 30, 2004 - 8:13 am UTC
Going back to what Bob was asking a few weeks back, isn't reversing the display order of SCBP as simple as:
set linesize 200
select substr(sys_connect_by_path(short_name,'\'),2) hier_asc
from organisation_unit ou
where ou_subtype = 'DV'
start with ou_id = 403
connect by ou.ou_id = prior ou.parent_ou_id
HIER_ASC
-----------------------------------------------------------------------
Sydney 2316 (E51)\PCS Sydney 2316\PCS NSW SUMMARY\PCS REGION NSW/QLD SUMMARY\EXECUTIVE BANKING\PCS\PFS
1 row selected
select reverse(substr(sys_connect_by_path(reverse(short_name),'\'),2)) hier_desc
from organisation_unit ou
where ou_subtype = 'DV'
start with ou_id = 403
connect by ou.ou_id = prior ou.parent_ou_id
HIER_DESC
------------------------------------------------------------------------
PFS\PCS\EXECUTIVE BANKING\PCS REGION NSW/QLD SUMMARY\PCS NSW SUMMARY\PCS Sydney 2316\Sydney 2316 (E51)
1 row selected
November 30, 2004 - 8:26 am UTC
why yes it is ;)
thanks (note: reverse is un-documented but has been there for a long long time)
Resolve Hierarchy Upfront in Oracle 8i.
wor, April 13, 2005 - 4:59 pm UTC
Tom,
The following query displays the hierarchy in scbp column in Oracle 9.
select rpad('*',2*level,'*') || ename emp_name, job,sys_connect_by_path( job, '/' ) scbp
from my_table
start with mgr is null
connect by prior empno = mgr
We have Oracle 8i ( Please give me solution in Oracle 8i.)
What I want is create a new column in table EMP say
ALTER TABLE "SCOTT"."EMP"
ADD ("FULLPATH" VARCHAR2(100) NULL)
and update the full hierarchy path in that column for that row.
e.g.
COLUMN FULLPATH
/PRESIDENT
/PRESIDENT/MANAGER
/PRESIDENT/MANAGER/ANALYST
/PRESIDENT/MANAGER/ANALYST/CLERK
/PRESIDENT/MANAGER/ANALYST
/PRESIDENT/MANAGER/ANALYST/CLERK
/PRESIDENT/MANAGER
/PRESIDENT/MANAGER/SALESMAN
/PRESIDENT/MANAGER/SALESMAN
/PRESIDENT/MANAGER/SALESMAN
/PRESIDENT/MANAGER/SALESMAN
/PRESIDENT/MANAGER/CLERK
/PRESIDENT/MANAGER
/PRESIDENT/MANAGER/CLERK
I have requirement to resolve the hierarchy in nightly batch and not at run time.
Please give me solution in Oracle 8i.
Thanks always,
April 13, 2005 - 5:15 pm UTC
sys connect by path did not exist in 8i.
do you have a fixed number of maximum levels (the deepest node in the tree is known to be "level=N")?
Resolve Hierarchy Upfront in Oracle 8i
Wor, April 13, 2005 - 5:50 pm UTC
Tom,
No the max levels are not fixed.
As of now the tree can be expandable.
Is there any solution in oracle 8i if the max levels is not fixed ?
If there is no solution than can it be safe to assume a bigger number say 1000.
Thanks always,
April 13, 2005 - 7:30 pm UTC
but the string at 1,000 would be > 4000 -- you sure about that?
how many rows do you have to create the hierarchy for?
Resolve Hierarchy Upfront in Oracle 8i
Wor, April 13, 2005 - 8:06 pm UTC
Tom,
>>> but the string at 1,000 would be > 4000 -- you sure about that?
To simplify the question I asked you for Job Title since your sample query was displaying job title hierarchy , but I will replace that Job Title with Job code or say empno ( e.g. code length can be fixed to 4 or 3 ). so that the hierarchy length can be retricted within 4000 including the separator. I do not think we will ever have 1000 levels down but at the same time I cannot restrict to single digit.
>>> how many rows do you have to create the hierarchy for?
All the records in the table. Basically update column FULLPATH with its hierarchy for each row in the table.
Please help,
Thanks always.
April 13, 2005 - 8:13 pm UTC
but how many records is "all records"
if you said "max level was 25", i would show you sql to do this.
but since max level is big, I'll show you how to write a plsql function to do this in your case, but if you tell me "a kabillion records", I'll rethink showing you that function. If you say "couple thousand" I will show you.
Resolve Hierarchy Upfront in Oracle 8i
Wor, April 13, 2005 - 8:21 pm UTC
Tom,
First of all sorry for all the trouble I am giving you.
Lets take my current database state.
We have around 100000 records in a table and around 100 levels of hierarchy ( level = N = 100 ).
Thanks again,
April 13, 2005 - 8:27 pm UTC
if you want pure sql:
ops$tkyte@ORA9IR2> select empno, ename,
2 ltrim( (select max(decode(level,5,ename)) ||'/'||
3 max(decode(level,4,ename)) ||'/'||
4 max(decode(level,3,ename)) ||'/'||
5 max(decode(level,2,ename)) ||'/'||
6 max(decode(level,1,ename))
7 from emp e2
8 start with empno = e1.empno
9 connect by prior mgr = empno ), '/') data
10 from emp e1;
EMPNO ENAME DATA
---------- ---------- ----------------------------------------
7369 SMITH KING/JONES/FORD/SMITH
7499 ALLEN KING/BLAKE/ALLEN
7521 WARD KING/BLAKE/WARD
7566 JONES KING/JONES
7654 MARTIN KING/BLAKE/MARTIN
7698 BLAKE KING/BLAKE
7782 CLARK KING/CLARK
7788 SCOTT KING/JONES/SCOTT
7839 KING KING
7844 TURNER KING/BLAKE/TURNER
7876 ADAMS KING/JONES/SCOTT/ADAMS
7900 JAMES KING/BLAKE/JAMES
7902 FORD KING/JONES/FORD
7934 MILLER KING/CLARK/MILLER
14 rows selected.
or plsql:
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace function scbp( p_empno in number ) return varchar2
2 as
3 l_return varchar2(4000);
4 begin
5 for x in ( select ename
6 from emp
7 start with empno = p_empno
8 connect by prior mgr = empno )
9 loop
10 l_return := x.ename || '/' || l_return;
11 end loop;
12 return l_return;
13 end;
14 /
Function created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select empno, ename, scbp(empno) data
2 from emp;
EMPNO ENAME DATA
---------- ---------- ----------------------------------------
7369 SMITH KING/JONES/FORD/SMITH/
7499 ALLEN KING/BLAKE/ALLEN/
7521 WARD KING/BLAKE/WARD/
7566 JONES KING/JONES/
7654 MARTIN KING/BLAKE/MARTIN/
7698 BLAKE KING/BLAKE/
7782 CLARK KING/CLARK/
7788 SCOTT KING/JONES/SCOTT/
7839 KING KING/
7844 TURNER KING/BLAKE/TURNER/
7876 ADAMS KING/JONES/SCOTT/ADAMS/
7900 JAMES KING/BLAKE/JAMES/
7902 FORD KING/JONES/FORD/
7934 MILLER KING/CLARK/MILLER/
14 rows selected.
Resolve Hierarchy Upfront in Oracle 8i
Wor, April 14, 2005 - 3:57 pm UTC
Tom,
Thanks alot.
1) I created the fucntion scbp
2) I created a new column in table EMP say
TABLE "SCOTT"."EMP"
ADD ("PATH" VARCHAR2(4000) NULL)
3) executed the following query
update EMP a set a.path = ( select scbp(empno) data
from EMP b where b.empno = a.empno )
I get the following error message
"ORA-04091 table string is mutating, trigger/function may not see it"
All I need is to update the hierarchy code generated in the new column for each row.
I am sure I have messed up somewhere.
Please help.
Regards,
April 14, 2005 - 3:59 pm UTC
so, you have a trigger that is causing this, nothing about a trigger was written so far.
has nothing to do with the scbp function, has everything to do with a trigger on that table that prevents updates.
I do not have any trigger on that table
wor, April 14, 2005 - 4:02 pm UTC
April 14, 2005 - 4:43 pm UTC
ah, i see, make it an autonomous transaction
add pragma autonomous_transaction
create function...
as
pragma autonomous_transaction
......
one valid use of that almost entirely otherwise evil capability.
wor
wor, April 18, 2005 - 12:25 pm UTC
Hi Tom,
I have Oracle 8i and I would like to flatten the hierarchy.
e.g.
I have a table T1 with 2 columns ( t1_id, t1_parent id ) which defines an hierarchy.
e.g.
1
/ \
2 3
/ \ \
4 5 6
t1_id t1_parent_id
1 NULL
2 1
3 1
4 2
5 2
6 3
I have another table T2 with 2 cols ( t2_parent_id, t2_id )
I want to update table T2 from table T1 with flatten hierarchy ( all childs below )
output required:
e.g.
t2_parent_id t2_id
1 2
1 3
1 4
1 5
1 6
2 4
2 5
3 6
I have more levels (e.g 100 or more ) and more records in T1 real data (100000 or more )( the above data is just for sample )
regards,
April 18, 2005 - 12:38 pm UTC
piece of cake in 9i with sys_connect_by_path.
even easier in 10g with connect_by_root.
If I had but a create table and some inserts, I might even play with some ideas in 8i...
Combination of SYS_CONNECT_BY_PATH and XMLAGG
Richard Smith, April 18, 2005 - 1:15 pm UTC
Since my original question was posted, we've upgraded to 9.2.0.6 and new requirements in our application are to generate the hierarchy in XML.
I'm new to XML and just started to look at the XMLDB functions built into 9.2 sql syntax. Tips on converting from sys_connect_by_path to xmlelement/xmlagg would be appreciated.
For example, to get from:
/root
/root/parent1
/root/parent1/child1
/root/parent1/child2
/root/parent2
/root/parent2/sublevel1
/root/parent2/sublevel1/child1
/root/parent2/sublevel1/child2
/root/parent2/sublevel2
/root/parent2/sublevel2/child1
/root/parent2/sublevel2/child2
To XML:
<root>
<node name="parent1">
<children>
<node name="child1" />
<node name="child2" />
</children>
</node>
<node name="parent2">
<children>
<node name="sublevel1">
<children>
<node name="child1" />
<node name="child2" />
</children>
</node>
<node name="sublevel2">
<children>
<node name="child1" />
<node name="child2" />
</children>
</node>
</children>
</node>
</root>
flatten hierarchy
wor, April 18, 2005 - 2:52 pm UTC
Hi Tom,
I have Oracle 8i and I would like to flatten the hierarchy.
e.g.
I have a table T1 with 2 columns ( t1_id, t1_parent id ) which defines an
hierarchy.
e.g.
1
/ \
2 3
/ \ \
4 5 6
t1_id t1_parent_id
1 NULL
2 1
3 1
4 2
5 2
6 3
I have another table T2 with 2 cols ( t2_parent_id, t2_id )
I want to update table T2 from table T1 with flatten hierarchy ( all childs
below )
CREATE TABLE "T1" ("t1_id" VARCHAR2(10), "t1_parent_id" VARCHAR2(10)) ;
CREATE TABLE "T2" ("t2_parent_id" VARCHAR2(10), "t2_id" VARCHAR2(10)) ;
insert into t1 values ( '1', null )
/
insert into t1 values ( '2', '1' )
/
insert into t1 values ( '3', '1' )
/
insert into t1 values ( '4', '2' )
/
insert into t1 values ( '5', '2' )
/
insert into t1 values ( '6', '3' )
/
output required and Insert statement which will insert values in table T2 as follows:
e.g.
t2_parent_id t2_id
1 2
1 3
1 4
1 5
1 6
2 4
2 5
3 6
I have more levels (e.g 100 or more ) and more records in T1 real data (100000
or more )( the above data is just for sample )
regards,
April 18, 2005 - 3:49 pm UTC
tkyte@ORA8IW> select t1_id, l, r,
2 (select t1_parent_id
3 from t1 X
4 where level = (a.l - b.r + 1)
5 start with X.t1_id = A.t1_id
6 connect by prior t1_parent_id = t1_id) the_parent
7 from (
8 select t1_id, t1_parent_id, level-1 l
9 from t1
10 start with t1_parent_id is null
11 connect by prior t1_id = t1_parent_id
12 ) a,
13 (
14 select rownum r
15 from all_objects
16 where rownum <= 10
17 ) b
18 where r <= l
19 order by 1, 4
20 /
T1_ID L R THE_PARENT
---------- ---------- ---------- ----------
2 1 1 1
3 1 1 1
4 2 1 1
4 2 2 2
5 2 1 1
5 2 2 2
6 2 1 1
6 2 2 3
8 rows selected.
build the hierarch, that is (a)
get a set of R's where R >= max level (YOU are responsible for getting that set together), that is (b)
join together to "explode" the rows out...
use the scalar subquery to find the i'th parent.
create table syntax updated
wor, April 18, 2005 - 3:45 pm UTC
tom,
I am sorry i put quotes ( for table names/columns ) the create table syntax should be
CREATE TABLE T1 (t1_id VARCHAR2(10), t1_parent_id VARCHAR2(10)) ;
CREATE TABLE T2 (t2_parent_id VARCHAR2(10), t2_id VARCHAR2(10)) ;
By now you must have figured out I am newbie ..
Please help,
please help
wor, April 18, 2005 - 4:02 pm UTC
Tom,
I am a newbie and am struggling alot but not able to acheive
I am sorry but I cannot understand your answer , its too complicated for me to understand since my oracle knowledge lacks.
Is it possible for you provide an insert statement ( or pl sql block ) in Oracle 8i for the above sample records. I will try to learn from your query ( or pl sql ) so that next time I can attempt similar thing on my own.
>>>>>> your reply <<<<<
build the hierarch, that is (a)
get a set of R's where R >= max level (YOU are responsible for getting that set
together), that is (b)
join together to "explode" the rows out...
use the scalar subquery to find the i'th parent
Regards,
April 18, 2005 - 6:37 pm UTC
I'm afraid to give you stuff you don't understand.
for the real fear is you might not have communicated to me everything I needed to understand.
insert into other_table
select .....;
that is all, if you can run a query, you can put the results into the other table.
Is this doable in one query?
A reader, May 19, 2005 - 6:09 pm UTC
create table t(C1 varchar2(10), C2 varchar2(10));
insert into T values('P501', 'C102');
insert into T values('P502 ', 'C105');
insert into T values('P503 ', 'C102');
insert into T values('P503 ', 'C103');
insert into T values('P503', 'C104');
insert into T values('P504 ', 'C105');
insert into T values('P504 ', 'C106');
insert into T values('P505 ', 'C106');
insert into T values('P505 ', 'C107');
insert into T values('P506 ', 'C108');
Is it possible to use one query to group the C2. For example,
using the data above:
Group 1: C101, C102, C103, C104
Group 2: C105, c106, C107
Group 3: C108
For example, C105 is in P502 and also in P504. Since
C106 is also in P504, then C105 and C106 are grouped
together. Further, C106 is also in P505. Since C107
is also in P505, then they are related and also put
into the group. The group is thus: C105, c106, C107
May 20, 2005 - 7:09 am UTC
well, you have a c101 in there too - what if things are in more than one group. begs more questions than answers right now.
I forget one insert
A reader, May 20, 2005 - 12:21 pm UTC
insert into T values('P501', 'C101');
insert into T values('P501', 'C102');
insert into T values('P502 ', 'C105');
insert into T values('P503 ', 'C102');
insert into T values('P503 ', 'C103');
insert into T values('P503', 'C104');
insert into T values('P504 ', 'C105');
insert into T values('P504 ', 'C106');
insert into T values('P505 ', 'C106');
insert into T values('P505 ', 'C107');
insert into T values('P506 ', 'C108');
"what if things are in more than one group. "
then keep merging the groups into a bigger group.
Thanks in advance.
Let me try to formulate question again:
A reader, May 21, 2005 - 4:49 pm UTC
create table t(C1 varchar2(10), C2 varchar2(10));
insert into T values('P501', 'C101');
insert into T values('P501', 'C102');
insert into T values('P502', 'C105');
insert into T values('P503', 'C102');
insert into T values('P503', 'C103');
insert into T values('P503', 'C104');
insert into T values('P504', 'C105');
insert into T values('P504', 'C106');
insert into T values('P505', 'C106');
insert into T values('P505', 'C107');
insert into T values('P506', 'C108');
1. if any data in C2 belongs to more than one value in C1
then merge c1:
SQL> break on c2;
SQL> select c1, c2 from t order by c2;
C1 C2
---------- ----------
P501 C101
<b>P501 C102
P503</b>
P503 C103
P503 C104
P502 C105
P504
P504 C106
P505
P505 C107
P506 C108
11 rows selected.
so C102 belongs to both 'P501' and 'P503', let's merge 'P501' and 'P503' to name the new one be 'P501'
SQL> update t set c1='P501' where c1='P503';
3 rows updated.
SQL> select c1, c2 from t order by c2;
C1 C2
---------- ----------
P501 C101
P501 C102
P501
P501 C103
P501 C104
<b>P502 C105
P504</b>
P504 C106
P505
P505 C107
P506 C108
11 rows selected.
NOw since 'C105' belong to both 'P502' and 'P504', let's merge them:
SQL> update t set c1='P502' where c1='P504';
2 rows updated.
SQL> select c1, c2 from t order by c2;
C1 C2
---------- ----------
P501 C101
P501 C102
P501
P501 C103
P501 C104
P502 C105
P502
<b>P502 C106
P505</b>
P505 C107
P506 C108
11 rows selected.
Since 'C106' now belongs to both 'P505' and 'P502', let's merge them:
SQL> update t set c1='P502' where c1='P505';
2 rows updated.
SQL> select c1, c2 from t order by c2;
C1 C2
---------- ----------
P501 C101
P501 C102
P501
P501 C103
P501 C104
P502 C105
P502
P502 C106
P502
P502 C107
P506 C108
11 rows selected.
Now we have merged all the C1 rows...
SQL> break on c1;
SQL> select c1 , c2 from t order by c1;
C1 C2
---------- ----------
P501 C101
C102
C102
C104
C103
P502 C105
C105
C106
C106
C107
P506 C108
11 rows selected.
we can derive the following group:
Group 1: C101, C102, C103, C104
Group 2: C105, c106, C107
Group 3: C108
Now the question is: can we generate the final result in a single SQL without doing the updates to merge the group?
Thanks!
May 22, 2005 - 8:07 am UTC
cannot think of any SQL way to perform this one. Sorry.
Is that a challenge?
Padders, May 23, 2005 - 11:22 am UTC
Can't believe I got here before Gabe.
Assuming you had some handy tricks like STRAGG/CONCAT_ALL and STRING_TO_TABLE around you might do something like this...
Personal Oracle Database 10g Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> CREATE TABLE t (c1 VARCHAR2(10), c2 VARCHAR2(10));
Table created.
SQL> INSERT INTO t VALUES ('P501', 'C101');
1 row created.
SQL> INSERT INTO t VALUES ('P501', 'C102');
1 row created.
SQL> INSERT INTO t VALUES ('P502', 'C105');
1 row created.
SQL> INSERT INTO t VALUES ('P503', 'C102');
1 row created.
SQL> INSERT INTO t VALUES ('P503', 'C103');
1 row created.
SQL> INSERT INTO t VALUES ('P503', 'C104');
1 row created.
SQL> INSERT INTO t VALUES ('P504', 'C105');
1 row created.
SQL> INSERT INTO t VALUES ('P504', 'C106');
1 row created.
SQL> INSERT INTO t VALUES ('P505', 'C106');
1 row created.
SQL> INSERT INTO t VALUES ('P505', 'C107');
1 row created.
SQL> INSERT INTO t VALUES ('P506', 'C108');
1 row created.
SQL> SELECT c1, c2
2 FROM (SELECT c2,
3 MIN (SUBSTR (column_value, 1, 4)) OVER (
4 PARTITION BY c1) c1,
5 ROW_NUMBER () OVER (
6 PARTITION BY c1, c2
7 ORDER BY SUBSTR (column_value, 1, 4)) row#
8 FROM (SELECT c1, c2,
9 SUBSTR (SYS_CONNECT_BY_PATH (c1, ','), 2) path
10 FROM (SELECT c1, c2, '/' ||
11 CONCAT_ALL (CONCAT_EXPR (c2, '/')) OVER (
12 PARTITION BY c1) || '/' members
13 FROM t)
14 CONNECT BY c1 > PRIOR c1
15 AND PRIOR members LIKE '%/' || c2 || '/%'),
16 TABLE (string_to_table (path)))
17 WHERE row# = 1
18 ORDER BY c1, c2;
C1 C2
---- ----------
P501 C101
P501 C102
P501 C102
P501 C103
P501 C104
P502 C105
P502 C105
P502 C106
P502 C106
P502 C107
P506 C108
11 rows selected.
SQL> COLUMN c2 FORMAT A30;
SQL> SELECT c1, CONCAT_ALL (CONCAT_EXPR (c2, ', ')) c2
2 FROM (SELECT DISTINCT c1, c2
3 FROM (SELECT c2,
4 MIN (SUBSTR (column_value, 1, 4)) OVER (
5 PARTITION BY c1) c1,
6 ROW_NUMBER () OVER (
7 PARTITION BY c1, c2
8 ORDER BY SUBSTR (column_value, 1, 4)) row#
9 FROM (SELECT c1, c2,
10 SUBSTR (SYS_CONNECT_BY_PATH (c1, ','), 2) path
11 FROM (SELECT c1, c2, '/' ||
12 CONCAT_ALL (CONCAT_EXPR (c2, '/')) OVER (
13 PARTITION BY c1) || '/' members
14 FROM t)
15 CONNECT BY c1 > PRIOR c1
16 AND PRIOR members LIKE '%/' || c2 || '/%'),
17 TABLE (string_to_table (path)))
18 WHERE row# = 1)
19 GROUP BY c1;
C1 C2
---- ------------------------------
P501 C101, C102, C103, C104
P502 C105, C106, C107
P506 C108
SQL>
May 23, 2005 - 3:29 pm UTC
:)
it was hurting my head to think about it.
Thanks a lot, Padders !!
A reader, May 24, 2005 - 1:29 am UTC
Excellent!!
sys_connect_by_path rocks!
Bharath Sivaram, July 12, 2005 - 9:12 pm UTC
Hi Tom,
This problem and solution are dedicated to you for your knowledge, time, patience and ceaseless efforts!
Problem: I have a table of 2 columns, from_id and to_id. The from_id merges into a to_id and any given to_id could again merge into another to_id like the example below.
etluser@veihui >>select * from test;
FROM_ID TO_ID
---------- ----------
1 2
2 3
3 4
5 6
7 8
8 9
323 14
14 721
721 2021
2021 677
677 423
FROM_ID TO_ID
---------- ----------
34 56
67 101
101 568
568 31
22 39
39 423
17 rows selected.
So, for any given from_id, I want the final to_id that it merges to, i.e like
from_id to_id
1 4
2 4
3 4
5 6
....
....
2021 423
677 423 and so on.
I could not find my exact solution here, but using sys_connect_by_path, I was able to get my results using sql itself! Of course, mine is a small table of a few hundred records and so I do not care about performance.
Solution:
---------
etluser@veihui >>select distinct q2.from_id||' '||q3.to_id from (select from_id, to_id,
decode(level, 1, sys_connect_by_path(from_id, '/')||'/', substr(sys_connect_by_path(from_id,
'/'), 1, instr(sys_connect_by_path(from_id, '/'), '/', 1, 2))) as string from test start
with from_id = from_id connect by prior to_id = from_id)
q2,
(select q1.from_id, q1.to_id as to_id, q1.lvl, q1.string from (
select from_id, to_id, level as lvl, decode(level, 1, sys_connect_by_path(from_id,
'/')||'/', substr(sys_connect_by_path(from_id, '/'), 1, instr(sys_connect_by_path(from_id,
'/'), '/', 1, 2))) as string from test start with from_id = from_id connect by prior to_id =
from_id) q1
where (q1.lvl, q1.string) in (
select max(lvl), string from (select level as lvl, decode(level, 1,
sys_connect_by_path(from_id, '/')||'/', substr(sys_connect_by_path(from_id, '/'), 1,
instr(sys_connect_by_path(from_id, '/'), '/', 1, 2))) as string from test start with from_id
= from_id connect by prior to_id = from_id) q group by q.string)) q3
where q2.string = q3.string;
Q2.FROM_ID||''||Q3.TO_ID
---------------------------------------------------------------------------------------
1 4
101 31
14 423
2 4
2021 423
22 423
3 4
323 423
34 56
39 423
5 6
Q2.FROM_ID||''||Q3.TO_ID
---------------------------------------------------------------------------------------
568 31
67 31
677 423
7 9
721 423
8 9
17 rows selected.
Sorry about the formatting, but it works!
Cheers,
Bharath
Analytics rock Analytics roll !! ( A pure SQL Solution for the tricky query)
Frank Zhou, March 01, 2006 - 7:01 pm UTC
Tom,
Here is a pure SQL solution for the very tricky recursive query qustion posted on May 19,2005
(question : can we generate the final result in a single SQL without
doing the updates to merge the group? )
No user defined functions such as STRAGG/CONCAT_ALL/STRING_TO_TABLE are required.
Analytics rock...
Analytics roll....
Thanks,
Frank
SQL>SELECT c1, MAX(LTRIM( sys_connect_by_path( c2, ',' ) , ',')) c2
2 FROM (
3 SELECT c1, c2, row_number() over (PARTITION BY c1 ORDER BY c2) rn
4 FROM (
5 SELECT DISTINCT
MIN(SUBSTR(str, 1, 4))OVER(PARTITION BY c1) c1, c2
6 FROM (
7 SELECT c1, c2, path,
LTRIM(sys_connect_by_path(c1, '-'), '-') str
8 FROM (
9 SELECT c1, c2 , path ,INSTR(path, '-', -1 ) last,
INSTR(path, '-', 1 ) first
10 FROM (
11 SELECT c1, c2, rn,
LTRIM(sys_connect_by_path( c1 , '-' ) , '-') path
12 FROM (SELECT c1, c2,
13 row_number() over
(PARTITION BY c2 ORDER BY c1 ) rn
14 FROM t )
15 START WITH rn = 1
16 CONNECT BY c2 = PRIOR c2
AND PRIOR rn = rn -1
17 )
18 )
19 CONNECT BY PRIOR c1 <c1
20 AND PRIOR SUBSTR(path, last+1) = SUBSTR (path, 0, first-1)
21 )
22 )
23 )
24 START WITH rn = 1
25 CONNECT BY c1 = PRIOR c1 AND PRIOR rn = rn -1
26 GROUP BY c1;
C1 C2
-------- ------------------------------
P501 C101,C102,C103,C104
P502 C105,C106,C107
P506 C108
SQL> spool off
Is this an Oracle 10G "Connect By" Bug ?
Frank Zhou, December 26, 2006 - 4:11 pm UTC
Hi Tom,
I just tested my SQL solution posted on March 01, 2006 again using Oracle 10G (The original sql soultion was using 9I)
(I found that just swap the order of the "connect by" in the query can produce a different result set in Oracle 10G)
Is this an Oracle 10G "connect by" bug ?
25 CONNECT BY PRIOR SUBSTR(path, last+1) = SUBSTR (path, 0, first-1)
26 AND PRIOR c1 < c1
and
25 CONNECT BY PRIOR c1 <c1
26 AND PRIOR SUBSTR(path, last+1) = SUBSTR (path, 0, first-1)
Can produce a different result set in the query.
(The first one generated the correct answer,
the seconds gave a wrong answer)
Thanks,
Frank
Here is an Example :
SQL> select version from v$instance;
VERSION
-----------------
10.2.0.2.0
SQL> drop table t;
Table dropped.
SQL> CREATE TABLE t (c1 VARCHAR2(10), c2 VARCHAR2(10));
Table created.
SQL> INSERT INTO t VALUES ('P501', 'C101');
1 row created.
SQL> INSERT INTO t VALUES ('P501', 'C102');
1 row created.
SQL> INSERT INTO t VALUES ('P502', 'C105');
1 row created.
SQL> INSERT INTO t VALUES ('P503', 'C102');
1 row created.
SQL> INSERT INTO t VALUES ('P503', 'C103');
1 row created.
SQL> INSERT INTO t VALUES ('P503', 'C104');
1 row created.
SQL> INSERT INTO t VALUES ('P504', 'C105');
1 row created.
SQL> INSERT INTO t VALUES ('P504', 'C106');
1 row created.
SQL> INSERT INTO t VALUES ('P505', 'C106');
1 row created.
SQL> INSERT INTO t VALUES ('P505', 'C107');
1 row created.
SQL> INSERT INTO t VALUES ('P506', 'C108');
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT c1, MAX(LTRIM( sys_connect_by_path( c2, ',' ) , ',')) c2
2 FROM (
3 SELECT c1, c2, row_number() over (PARTITION BY c1 ORDER BY c2) rn
4 FROM (
5 SELECT DISTINCT
6 MIN(SUBSTR(str, 1, 4))OVER(PARTITION BY c1) c1, c2
7 FROM (
8 SELECT c1, c2, path,
9 LTRIM(sys_connect_by_path(c1, '-'), '-') str
10 FROM (
11 SELECT c1, c2 , path ,INSTR(path, '-', -1 ) last,
12 INSTR(path, '-', 1 ) first
13 FROM (
14 SELECT c1, c2, rn,
15 LTRIM(sys_connect_by_path( c1 , '-' ) , '-') path
16 FROM (SELECT c1, c2,
17 row_number() over
18 (PARTITION BY c2 ORDER BY c1 ) rn
19 FROM t )
20 START WITH rn = 1
21 CONNECT BY c2 = PRIOR c2
22 AND PRIOR rn = rn -1
23 )
24 )
25 CONNECT BY PRIOR SUBSTR(path, last+1) = SUBSTR (path, 0, first-1)
26 AND PRIOR c1 < c1
27 )
28 )
29 )
30 START WITH rn = 1
31 CONNECT BY c1 = PRIOR c1 AND PRIOR rn = rn -1
32 GROUP BY c1;
C1
----
C2
--------------------------------------------------------------------------------
P501
C101,C102,C103,C104
P502
C105,C106,C107
P506
C108
SQL> SELECT c1, MAX(LTRIM( sys_connect_by_path( c2, ',' ) , ',')) c2
2 FROM (
3 SELECT c1, c2, row_number() over (PARTITION BY c1 ORDER BY c2) rn
4 FROM (
5 SELECT DISTINCT
6 MIN(SUBSTR(str, 1, 4))OVER(PARTITION BY c1) c1, c2
7 FROM (
8 SELECT c1, c2, path,
9 LTRIM(sys_connect_by_path(c1, '-'), '-') str
10 FROM (
11 SELECT c1, c2 , path ,INSTR(path, '-', -1 ) last,
12 INSTR(path, '-', 1 ) first
13 FROM (
14 SELECT c1, c2, rn,
15 LTRIM(sys_connect_by_path( c1 , '-' ) , '-') path
16 FROM (SELECT c1, c2,
17 row_number() over
18 (PARTITION BY c2 ORDER BY c1 ) rn
19 FROM t )
20 START WITH rn = 1
21 CONNECT BY c2 = PRIOR c2
22 AND PRIOR rn = rn -1
23 )
24 )
25 CONNECT BY PRIOR c1 <c1
26 AND PRIOR SUBSTR(path, last+1) = SUBSTR (path, 0, first-1)
27 )
28 )
29 )
30 START WITH rn = 1
31 CONNECT BY c1 = PRIOR c1 AND PRIOR rn = rn -1
32 GROUP BY c1;
C1
----
C2
--------------------------------------------------------------------------------
P501
C101,C102
P502
C105
P503
C102,C103,C104
C1
----
C2
--------------------------------------------------------------------------------
P504
C105,C106,C107
P506
C108
SQL> spool off
December 26, 2006 - 9:27 pm UTC
didn't read it all - but does your example rely on some implicit ordering of data?
To: Frank Zhou
Michel Cadot, December 27, 2006 - 2:49 am UTC
Have a look at both execution plans to see if there is any difference between your two queries.
Michel
Re: Is this an Oracle 10G "Connect By" Bug ?
Frank Zhou, December 27, 2006 - 11:55 am UTC
Hi Tom,
This query doesn't depend on implict ordering of data.
I have compared both plans, the (cr, pr pw time) number
and rows returned for each step are different, but the execution steps are the same.
Thanks,
Frank
Here are the Tkprof reports:
********************************************************************************
SELECT c1, MAX(LTRIM( sys_connect_by_path( c2, ',' ) , ',')) c2
FROM (
SELECT c1, c2, row_number() over (PARTITION BY c1 ORDER BY c2) rn
FROM (
SELECT DISTINCT
MIN(SUBSTR(str, 1, 4))OVER(PARTITION BY c1) c1, c2
FROM (
SELECT c1, c2, path,
LTRIM(sys_connect_by_path(c1, '-'), '-') str
FROM (
SELECT c1, c2 , path ,INSTR(path, '-', -1 ) last,
INSTR(path, '-', 1 ) first
FROM (
SELECT c1, c2, rn,
LTRIM(sys_connect_by_path( c1 , '-' ) , '-') path
FROM (SELECT c1, c2,
row_number() over
(PARTITION BY c2 ORDER BY c1 ) rn
FROM t )
START WITH rn = 1
CONNECT BY c2 = PRIOR c2
AND PRIOR rn = rn -1
)
)
CONNECT BY PRIOR SUBSTR(path, last+1) = SUBSTR (path, 0, first-1)
AND PRIOR c1 < c1
)
)
)
START WITH rn = 1
CONNECT BY c1 = PRIOR c1 AND PRIOR rn = rn -1
GROUP BY c1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.04 0.04 0 150 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.04 0 150 0 3
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 57
Rows Row Source Operation
------- ---------------------------------------------------
3 SORT GROUP BY (cr=150 pr=0 pw=0 time=43511 us)
8 CONNECT BY WITH FILTERING (cr=150 pr=0 pw=0 time=43385 us)
3 VIEW (cr=30 pr=0 pw=0 time=23223 us)
8 WINDOW SORT PUSHED RANK (cr=30 pr=0 pw=0 time=23215 us)
8 VIEW (cr=30 pr=0 pw=0 time=23137 us)
8 SORT UNIQUE (cr=30 pr=0 pw=0 time=23126 us)
17 WINDOW SORT (cr=30 pr=0 pw=0 time=23049 us)
17 VIEW (cr=30 pr=0 pw=0 time=22867 us)
17 CONNECT BY WITHOUT FILTERING (cr=30 pr=0 pw=0 time=22861 us)
11 VIEW (cr=30 pr=0 pw=0 time=22612 us)
11 CONNECT BY WITH FILTERING (cr=30 pr=0 pw=0 time=22594 us)
8 VIEW (cr=10 pr=0 pw=0 time=287 us)
11 WINDOW SORT PUSHED RANK (cr=10 pr=0 pw=0 time=282 us)
11 TABLE ACCESS FULL T (cr=10 pr=0 pw=0 time=126 us)
3 HASH JOIN (cr=20 pr=0 pw=0 time=21926 us)
11 CONNECT BY PUMP (cr=0 pr=0 pw=0 time=52 us)
22 VIEW (cr=20 pr=0 pw=0 time=443 us)
22 WINDOW SORT (cr=20 pr=0 pw=0 time=417 us)
22 TABLE ACCESS FULL T (cr=20 pr=0 pw=0 time=215 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 WINDOW SORT (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL T (cr=0 pr=0 pw=0 time=0 us)
5 HASH JOIN (cr=120 pr=0 pw=0 time=19814 us)
8 CONNECT BY PUMP (cr=0 pr=0 pw=0 time=10 us)
32 VIEW (cr=120 pr=0 pw=0 time=17397 us)
32 WINDOW SORT (cr=120 pr=0 pw=0 time=17388 us)
32 VIEW (cr=120 pr=0 pw=0 time=17247 us)
32 SORT UNIQUE (cr=120 pr=0 pw=0 time=17241 us)
68 WINDOW SORT (cr=120 pr=0 pw=0 time=17038 us)
68 VIEW (cr=120 pr=0 pw=0 time=16820 us)
68 CONNECT BY WITHOUT FILTERING (cr=120 pr=0 pw=0 time=16744 us)
44 VIEW (cr=120 pr=0 pw=0 time=16248 us)
44 CONNECT BY WITH FILTERING (cr=120 pr=0 pw=0 time=16234 us)
32 VIEW (cr=40 pr=0 pw=0 time=404 us)
44 WINDOW SORT PUSHED RANK (cr=40 pr=0 pw=0 time=387 us)
44 TABLE ACCESS FULL T (cr=40 pr=0 pw=0 time=226 us)
12 HASH JOIN (cr=80 pr=0 pw=0 time=15145 us)
44 CONNECT BY PUMP (cr=0 pr=0 pw=0 time=13 us)
88 VIEW (cr=80 pr=0 pw=0 time=928 us)
88 WINDOW SORT (cr=80 pr=0 pw=0 time=907 us)
88 TABLE ACCESS FULL T (cr=80 pr=0 pw=0 time=483 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 WINDOW SORT (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL T (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 WINDOW SORT (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us)
0 WINDOW SORT (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 CONNECT BY WITH FILTERING (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 WINDOW SORT PUSHED RANK (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL T (cr=0 pr=0 pw=0 time=0 us)
0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us)
0 CONNECT BY PUMP (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 WINDOW SORT (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL T (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 WINDOW SORT (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL T (cr=0 pr=0 pw=0 time=0 us)
********************************************************************************
********************************************************************************
SELECT c1, MAX(LTRIM( sys_connect_by_path( c2, ',' ) , ',')) c2
FROM (
SELECT c1, c2, row_number() over (PARTITION BY c1 ORDER BY c2) rn
FROM (
SELECT DISTINCT
MIN(SUBSTR(str, 1, 4))OVER(PARTITION BY c1) c1, c2
FROM (
SELECT c1, c2, path,
LTRIM(sys_connect_by_path(c1, '-'), '-') str
FROM (
SELECT c1, c2 , path ,INSTR(path, '-', -1 ) last,
INSTR(path, '-', 1 ) first
FROM (
SELECT c1, c2, rn,
LTRIM(sys_connect_by_path( c1 , '-' ) , '-') path
FROM (SELECT c1, c2,
row_number() over
(PARTITION BY c2 ORDER BY c1 ) rn
FROM t )
START WITH rn = 1
CONNECT BY c2 = PRIOR c2
AND PRIOR rn = rn -1
)
)
CONNECT BY PRIOR c1 <c1
AND PRIOR SUBSTR(path, last+1) = SUBSTR (path, 0, first-1)
)
)
)
START WITH rn = 1
CONNECT BY c1 = PRIOR c1 AND PRIOR rn = rn -1
GROUP BY c1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.02 0.02 0 120 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.02 0.02 0 120 0 5
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 57
Rows Row Source Operation
------- ---------------------------------------------------
5 SORT GROUP BY (cr=120 pr=0 pw=0 time=23528 us)
10 CONNECT BY WITH FILTERING (cr=120 pr=0 pw=0 time=23440 us)
5 VIEW (cr=30 pr=0 pw=0 time=4881 us)
10 WINDOW SORT PUSHED RANK (cr=30 pr=0 pw=0 time=4875 us)
10 VIEW (cr=30 pr=0 pw=0 time=4844 us)
10 SORT UNIQUE (cr=30 pr=0 pw=0 time=4824 us)
13 WINDOW SORT (cr=30 pr=0 pw=0 time=4774 us)
13 VIEW (cr=30 pr=0 pw=0 time=4689 us)
13 CONNECT BY WITHOUT FILTERING (cr=30 pr=0 pw=0 time=4684 us)
11 VIEW (cr=30 pr=0 pw=0 time=4514 us)
11 CONNECT BY WITH FILTERING (cr=30 pr=0 pw=0 time=4487 us)
8 VIEW (cr=10 pr=0 pw=0 time=194 us)
11 WINDOW SORT PUSHED RANK (cr=10 pr=0 pw=0 time=191 us)
11 TABLE ACCESS FULL T (cr=10 pr=0 pw=0 time=119 us)
3 HASH JOIN (cr=20 pr=0 pw=0 time=3982 us)
11 CONNECT BY PUMP (cr=0 pr=0 pw=0 time=7 us)
22 VIEW (cr=20 pr=0 pw=0 time=284 us)
22 WINDOW SORT (cr=20 pr=0 pw=0 time=260 us)
22 TABLE ACCESS FULL T (cr=20 pr=0 pw=0 time=129 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 WINDOW SORT (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL T (cr=0 pr=0 pw=0 time=0 us)
5 HASH JOIN (cr=90 pr=0 pw=0 time=18237 us)
10 CONNECT BY PUMP (cr=0 pr=0 pw=0 time=8 us)
30 VIEW (cr=90 pr=0 pw=0 time=14933 us)
30 WINDOW SORT (cr=90 pr=0 pw=0 time=14898 us)
30 VIEW (cr=90 pr=0 pw=0 time=14736 us)
30 SORT UNIQUE (cr=90 pr=0 pw=0 time=14725 us)
39 WINDOW SORT (cr=90 pr=0 pw=0 time=14568 us)
39 VIEW (cr=90 pr=0 pw=0 time=14759 us)
39 CONNECT BY WITHOUT FILTERING (cr=90 pr=0 pw=0 time=14587 us)
33 VIEW (cr=90 pr=0 pw=0 time=13753 us)
33 CONNECT BY WITH FILTERING (cr=90 pr=0 pw=0 time=13711 us)
24 VIEW (cr=30 pr=0 pw=0 time=402 us)
33 WINDOW SORT PUSHED RANK (cr=30 pr=0 pw=0 time=392 us)
33 TABLE ACCESS FULL T (cr=30 pr=0 pw=0 time=274 us)
9 HASH JOIN (cr=60 pr=0 pw=0 time=12768 us)
33 CONNECT BY PUMP (cr=0 pr=0 pw=0 time=13 us)
66 VIEW (cr=60 pr=0 pw=0 time=720 us)
66 WINDOW SORT (cr=60 pr=0 pw=0 time=707 us)
66 TABLE ACCESS FULL T (cr=60 pr=0 pw=0 time=379 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 WINDOW SORT (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL T (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 WINDOW SORT (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us)
0 WINDOW SORT (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 CONNECT BY WITH FILTERING (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 WINDOW SORT PUSHED RANK (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL T (cr=0 pr=0 pw=0 time=0 us)
0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us)
0 CONNECT BY PUMP (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 WINDOW SORT (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL T (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 WINDOW SORT (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL T (cr=0 pr=0 pw=0 time=0 us)
********************************************************************************
To: Frank Zhou
Michel Cadot, December 28, 2006 - 5:06 am UTC
Use explain plan/dbms_xplan.display or find the cursors in the sqlarea and use dbms_xplan.display_cursor, and look if there is no difference in the predicates/filters sections.
Michel
Re: Is this an Oracle 10G "Connect By" Bug ?
Frank Zhou, December 29, 2006 - 1:34 pm UTC
The only different is :
access("C1">PRIOR NULL AND SUBSTR("PATH",0,INSTR("PATH",'-',1)-1)=PRIOR NULL)
VS
access(SUBSTR("PATH",0,INSTR("PATH",'-',1)-1)=PRIOR NULL AND "C1">PRIOR NULL)
So I think this one is indeed a 10G "connect by " bug.
Frank
----------------------------------------------------------------------- Here is the detail info for the first sql
CONNECT BY PRIOR SUBSTR(path, last+1) = SUBSTR (path, 0, first-1)
AND PRIOR c1 < c1
-------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=PRIOR NULL AND "RN"-1=PRIOR NULL)
3 - filter("RN"=1)
4 - filter(ROW_NUMBER() OVER ( PARTITION BY "C1" ORDER BY "C2")<=1)
9 - access("C1">PRIOR NULL AND SUBSTR("PATH",0,INSTR("PATH",'-',1)-1)=PRIOR NULL)
11 - access("C2"=PRIOR NULL AND "RN"-1=PRIOR NULL)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
12 - filter("RN"=1)
13 - filter(ROW_NUMBER() OVER ( PARTITION BY "C2" ORDER BY "C1")<=1)
15 - access("C2"=PRIOR NULL AND "RN"-1=PRIOR NULL)
23 - access("C1"=PRIOR NULL AND "RN"-1=PRIOR NULL)
31 - access("C1">PRIOR NULL AND SUBSTR("PATH",0,INSTR("PATH",'-',1)-1)=PRIOR NULL)
33 - access("C2"=PRIOR NULL AND "RN"-1=PRIOR NULL)
34 - filter("RN"=1)
35 - filter(ROW_NUMBER() OVER ( PARTITION BY "C2" ORDER BY "C1")<=1)
37 - access("C2"=PRIOR NULL AND "RN"-1=PRIOR NULL)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
51 - access("C1">PRIOR NULL AND SUBSTR("PATH",0,INSTR("PATH",'-',1)-1)=PRIOR NULL)
53 - access("C2"=PRIOR NULL AND "RN"-1=PRIOR NULL)
54 - filter("RN"=1)
55 - filter(ROW_NUMBER() OVER ( PARTITION BY "C2" ORDER BY "C1")<=1)
57 - access("C2"=PRIOR NULL AND "RN"-1=PRIOR NULL)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
===============================================================Here is the second query
CONNECT BY PRIOR c1 <c1
AND PRIOR SUBSTR(path, last+1) = SUBSTR(path, 0, first-1)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=PRIOR NULL AND "RN"-1=PRIOR NULL)
3 - filter("RN"=1)
4 - filter(ROW_NUMBER() OVER ( PARTITION BY "C1" ORDER BY "C2")<=1)
9 - access("C1">PRIOR NULL AND SUBSTR("PATH",0,INSTR("PATH",'-',1)-1)=PRIOR NULL)
11 - access("C2"=PRIOR NULL AND "RN"-1=PRIOR NULL)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
12 - filter("RN"=1)
13 - filter(ROW_NUMBER() OVER ( PARTITION BY "C2" ORDER BY "C1")<=1)
15 - access("C2"=PRIOR NULL AND "RN"-1=PRIOR NULL)
23 - access("C1"=PRIOR NULL AND "RN"-1=PRIOR NULL)
31 - access("C1">PRIOR NULL AND SUBSTR("PATH",0,INSTR("PATH",'-',1)-1)=PRIOR NULL)
33 - access("C2"=PRIOR NULL AND "RN"-1=PRIOR NULL)
34 - filter("RN"=1)
35 - filter(ROW_NUMBER() OVER ( PARTITION BY "C2" ORDER BY "C1")<=1)
37 - access("C2"=PRIOR NULL AND "RN"-1=PRIOR NULL)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
51 - access("C1">PRIOR NULL AND SUBSTR("PATH",0,INSTR("PATH",'-',1)-1)=PRIOR NULL)
53 - access("C2"=PRIOR NULL AND "RN"-1=PRIOR NULL)
54 - filter("RN"=1)
55 - filter(ROW_NUMBER() OVER ( PARTITION BY "C2" ORDER BY "C1")<=1)
57 - access("C2"=PRIOR NULL AND "RN"-1=PRIOR NULL)
Single record - sys_connect_by_path
Parag J Patankar, April 09, 2007 - 4:43 am UTC
Hi,
For simple query, I am able to print single record using sys_connect_by_path
ql>select ltrim(max(sys_connect_by_path(department_id,',')),',')
from (select rownum n,department_id from departments)
start with n = 1
connect by prior n = n-1;
LTRIM(MAX(SYS_CONNECT_BY_PATH(DEPARTMENT_ID,',')),',') 10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270
But suppose, I am having following records
1* select deptno, empno from emp order by deptno
11:32:39 SQL> /
DEPTNO EMPNO
---------- ----------
10 7782
10 7934
20 7369
20 7788
20 7777
20 7876
20 7566
30 7499
30 7844
30 7698
30 7654
30 7521
I want output like
10,7782,7934,20,7369,7788,7777,7876,7566,30,7499....etc
Kindly suggest me how to do this ?
thanks & regards
PJP
April 09, 2007 - 10:48 am UTC
of what use would this be. how would you tell the difference between
a) deptno
b) empno
single record output
Parag J Patankar, April 09, 2007 - 11:08 am UTC
Hi Tom,
Thanks for your answer.
q1/ Actually I do not need to identify the deptno and empno. I am calculating CRC32 checksum algoritham as per "different" requirement of a client. He wants all data to be merged in a single record and then calculate crc32. I am using java program for calculating CRC32 checksum. Using same data I am generating XML output using SQLX and printing this checksum in this file using unix shell script
q2/ Currently I am able to get an answer for my question
1 select replace(replace(max(sys_connect_by_path(x1, '^^')),'/',null),'^^',null) x2
2 from
3 (
4 select deptno, dense_rank() over (order by deptno) n,
5 deptno||max(sys_connect_by_path(empno, '/')) x1
6 from
7 (
8 select deptno, empno,
9 row_number() over ( partition by deptno order by deptno, empno ) n1
10 from emp
11 order by deptno, empno
12 )
13 start with n1 = 1
14 connect by n1 = prior n1 + 1
15 and deptno = prior deptno
16 group by deptno
17 )
18 start with n = 1
19* connect by n = prior n + 1
16:02:06 SQL> /
X2
--------------------------------------------------------------------------------------------------
107782793420736975667777778878763074997521765476987844
Pl correct me, If I missed out something. In this situation max record size can appear 32767. How can I overcome this limitation in 9.2 database ? Kindly show me.
thanks & best regards
PJP
April 09, 2007 - 1:02 pm UTC
the 'max record' is actually 4000 bytes using this. Not 32k.
... . He wants all data to be merged in a single record and then calculate crc32. I am using java program for calculating CRC32 checksum. ...
why is the client dictating the algorithm.
You want to
a) fetch data a row or set of rows at a time
b) calculate crc as you go along.
Parag J Patankar, April 10, 2007 - 4:51 am UTC
Hi Tom,
Thanks for your answer. Client is using our xml output to input to his system. I do not know why they are calculating CRC32 checksum after putting xml data into single record. This method is out of my control. You said
"You want to
a) fetch data a row or set of rows at a time
b) calculate crc as you go along. "
"
Will you pl explain ( or show ) in specifically how can I achive this ?
thanks & regards
pjp
April 10, 2007 - 10:45 am UTC
you just run a query, fetch data, do your math?
Rollup to the Top
Amit, May 14, 2007 - 8:29 am UTC
Scenario is
Parent Group Count
Group1
Group2
Group3
Task1 1
Task2 2
Task3 1
Group4
Group5
Task4 1
Task5 1
Expected output is
Column Count by Level
Parent Group 6
Group1 4
Group2 4
Group3 4
Group4 2
Group5 2
Above hierarchy is being retrieved using connect by prior syntax.
I was trying to use sys_connect_by_path to acheive the above result, but could not do that.
Tom, could you please help in resolving this problem?
Thanks
Amit
May 14, 2007 - 2:12 pm UTC
nope, no create table, no insert into, no look....
not that you are assured an answer if you supply that, there might not be one, just that until we have an example to work with... no look..
Rollup to the Top
Amit, May 15, 2007 - 2:23 am UTC
Apologies for that Tom.
Following are the Table creation and insert scripts
create table test_hierarchy
(GROUP_ID Number,
GROUP_NAME varchar2(100),
DEPTH number,
PARENT_GROUP_NAME varchar2(100),
INITIAL_LEVEL number,
SBIWCWOPEN number,
SBIWCWCLOSED number,
TASKSOPEN number,
TASKSCLOSED number,
TASKSPARKED number,
TASKSIP number,
MODIFIED_GROUP_NAME varchar(100),
MANAGED_BY varchar2(100),
SBI_CATEGORY varchar2(100),
WCW_SBI_STATE varchar2(100)
)
/
Insert into test_hierarchy
values(100000155,'Parent Group',1, null,1.00, null,null,null,null,null,null,'Reading PT1',
'Hopkins, Bob',null,null)
/
Insert into test_hierarchy
values(100000155,'Group4',2,'Parent Group',-1,null,null,1,1,null,null,'Reading PT1.pot',
'Hopkins, Bob',null,null)
/
Insert into test_hierarchy
values(100000155,'Group5',3,'Group4',-1,null,null,1,1,null,null,'Reading PT1.pot',
'Hopkins, Bob',null,null)
/
Insert into test_hierarchy
values(105062883,'10506288',4,'Group5',0,null,1,null,null,null,null,'105062883 ',
'Hopkins, Bob',null,'Assigned')
/
Insert into test_hierarchy
values(105062883,'105062883',4,'Group5',0,null,1,null,null,null,null,'105062883 ',
'Hopkins, Bob',null,'Assigned')
/
Insert into test_hierarchy
values(100000155,'Group1',2,'Parent Group',-1,1,null,null,null,1,null,'Trail, Neil',
'Hopkins, Bob',null,null)
/
Insert into test_hierarchy
values(100000155,'Group2',3,'Group1',-1,1,null,null,null,1,null,'Trail, Neil',
'Hopkins, Bob',null,null)
/
Insert into test_hierarchy
values(100000155,'Group3',4,'Group2',-1,1,null,null,null,1,null,'Trail, Neil',
'Hopkins, Bob',null,null)
/
Insert into test_hierarchy
values(105062884,'105062884',5,'Group3',0,null,1,null,null,null,null,'105062884 ',
'Hopkins, Bob',null,'Assigned')
/
Insert into test_hierarchy
values(105062885,'105062885',5,'Group3',0,null,1,null,null,null,null,'105062885 ',
'Hopkins, Bob',null,'Assigned')
/
Insert into test_hierarchy
values(105062886,'105062886',5,'Group3',0,null,1,null,null,null,null,'105062886 ',
'Hopkins, Bob',null,'Assigned')
/
Query Output:
SQL> select substr(lpad(' ',level*2)||group_name,1,20)group_hier,sbiwcwclosed
2 from test_hierarchy
3 start with parent_group_name is null
4 connect by parent_group_name = prior group_name;
GROUP_HIER SBIWCWCLOSED
-------------------- ------------
Parent Group
Group4
Group5
10506288 1
105062883 1
Group1
Group2
Group3
105062884 1
105062885 1
105062886 1
Expected output:
Group_name SBIWCWCLOSED
Parent Group 5
Group1 3
Group2 3
Group3 3
Group4 2
Group5 2
Thanks for all your support.
Regards,
Amit
May 15, 2007 - 4:03 pm UTC
ops$tkyte%ORA10GR2> select group_name,
2 (select sum(b.sbiwcwclosed) from test_hierarchy b start with b.rowid = a.rowid connect by parent_group_name = prior group_name) sbi
3 from test_hierarchy a
4 where sbiwcwclosed is null
5 /
GROUP_NAME SBI
--------------- ----------
Parent Group 5
Group4 2
Group5 2
Group1 3
Group2 3
Group3 3
6 rows selected.
Rollup to top
Amit, May 16, 2007 - 9:47 am UTC
Brilliant. Thanks a lot.
Regards,
Amit
order siblings by
sara, May 24, 2007 - 12:47 pm UTC
I have a table with id, parent_id and position.
so i use:
select id, parent_id, position from <table> START WITH parent_id = 1 CONNECT BY PRIOR id = parent_id
But I need the results to be ordered by position too
I am using 8i and I could not use order siblings by position.
Could you suggest me a way i can do this?
May 26, 2007 - 11:33 am UTC
not reliably - no.
8i did not have that feature a decade ago.
9i does.
Another Query...
Akki, June 22, 2007 - 9:59 am UTC
CREATE TABLE EMPLOYEE
(
EMP_NO NUMBER,
ENAME VARCHAR2(10 BYTE),
MGRNAME VARCHAR2(10 BYTE)
)
/
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (1, 'emp1', 'mgr2');
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (2, 'emp2', 'mgr2');
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (3, 'emp3', 'mgr2');
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (4, 'emp4', 'mgr1');
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (5, 'emp5', 'mgr1');
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (6, 'emp6', 'mgr1');
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (7, 'mgr1', 'topmgr1');
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (8, 'mgr2', 'topmgr1');
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (9, 'topmgr1',null);
COMMIT;
select * from employee;
EMP_NO ENAME MGRNAME
---------- ---------- ----------
1 emp1 mgr2
2 emp2 mgr2
3 emp3 mgr2
4 emp4 mgr1
5 emp5 mgr1
6 emp6 mgr1
7 mgr1 topmgr1
8 mgr2 topmgr1
9 topmgr1
9 rows selected.
.. Now I am trying to write a SELEECT statement which will generate 4th column and populate mgr_no based on the emp_no and ename
.. Please note that there is a self referential integrity between ename and mgrname
So, the output should look like
EMP_NO ENAME MGRNAME MGR_NO
---------- ---------- ---------- ------
1 emp1 mgr2 8
2 emp2 mgr2 8
3 emp3 mgr2 8
4 emp4 mgr1 7
5 emp5 mgr1 7
6 emp6 mgr1 7
7 mgr1 topmgr1 9
8 mgr2 topmgr1 9
9 topmgr1
It might look very simple at first sight, but still I am not close to the solution.
I have tried different LEVEL queries, but no luck so far... Can you PLEASE help me resolving this query.
June 22, 2007 - 10:40 am UTC
this is a rather simple join??
ops$tkyte%ORA10GR2> select e1.emp_no, e1.ename, e1.mgrname, e2.emp_no mgr_no
2 from employee e1, employee e2
3 where e1.mgrname = e2.ename(+)
4 /
EMP_NO ENAME MGRNAME MGR_NO
---------- ---------- ---------- ----------
6 emp6 mgr1 7
5 emp5 mgr1 7
4 emp4 mgr1 7
3 emp3 mgr2 8
2 emp2 mgr2 8
1 emp1 mgr2 8
8 mgr2 topmgr1 9
7 mgr1 topmgr1 9
9 topmgr1
9 rows selected.
Response to your last followup
Akki, June 26, 2007 - 4:36 am UTC
.. yes but here we are joining the same table twice. I gave you an example of small set of records. But in our case, we have a very large table (with millions of records)... So, I was trying to build a query which will scan the table only once to get the output.
July 02, 2007 - 9:10 am UTC
do you understand why that will not be possible from a "physics" point of view?
Akki, June 27, 2007 - 7:20 am UTC
Apologies... I gave you the incomplete information to start with. Create script remains same. But I have changed to insert script.
CREATE TABLE EMPLOYEE
(
EMP_NO NUMBER,
ENAME VARCHAR2(10 BYTE),
MGRNAME VARCHAR2(10 BYTE)
)
/
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (1, 'topmgr1',null);
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (2, 'mgr2', 'topmgr1');
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (3, 'mgr1', 'topmgr1');
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (4, 'emp6', 'mgr1');
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (5, 'emp5', 'mgr1');
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (6, 'emp4', 'mgr1');
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (7, 'emp3', 'mgr2');
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (8, 'emp2', 'mgr2');
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (9, 'emp1', 'mgr2');
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (10, 'topmgr2',null);
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (11, 'mgr3', 'topmgr2');
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (12, 'mgr4', 'topmgr2');
Insert into EMPLOYEE (emp_no, ename, mgrname) Values (13, 'emp7', 'mgr3');
COMMIT;
select * from employee;
EMP_NO ENAME MGRNAME
--------- ---------- ----------
1 topmgr1
2 mgr2 topmgr1
3 mgr1 topmgr1
4 emp6 mgr1
5 emp5 mgr1
6 emp4 mgr1
7 emp3 mgr2
8 emp2 mgr2
9 emp1 mgr2
10 topmgr2
11 mgr3 topmgr2
12 mgr4 topmgr2
13 emp7 mgr3
13 rows selected.
This is just an example of data. But in reality there will be milliions of inserts per day. The requirement is at the time of inserting a row, we should identify the depth of that record and the top most parent of that record. All the data would be inserted in 'tree' fashion. i.e. top most
record (here record no 1) would be inserted 1st.. its child nodes inserted second and their child nodes inserted third and so on...
Now, say for example we are inserting record for 'emp3' and its immediate manager 'mgr2'. We already have the record for 'mgr2' and its seniors in this table. What we want is to identify the level of that employee and the top most 'mgr'
i.e. the query should return that 'emp3' is level 2 employee and in its hierarchy 'topmgr1' is the top manager.
So, the output would be :
EMP_NO ENAME MGRNAME DEPTH TOPMGR_ID
-------- ------- ---------- ----- ---------
7 emp3 mgr2 2 1
In above case, we are inserting record for emp_no 3. and getting 'DEPTH' and 'TOPMGR_ID' from the query.
Similarly, at the time of inserting emp_no 2 the query would return
EMP_NO ENAME MGRNAME DEPTH TOPMGR_ID
-------- ------- ---------- ----- ---------
2 mgr2 topmgr1 1 1
... and so on
Again for the next set (topmgr2) we will have new depth and new topmgr_id.
i.e. for emp_no 13... the query would return
EMP_NO ENAME MGRNAME DEPTH TOPMGR_ID
-------- ------- ---------- ----- ---------
13 emp7 mgr3 2 10
Here ya go Akki
Yuan, June 27, 2007 - 1:58 pm UTC
SELECT :Emp Emp_No, E.EName, E.MgrName, M.Depth, M.Emp_No TopMgrId
FROM Employee E
JOIN (SELECT Emp_No, Level - 1 Depth, CONNECT_BY_ISLEAF Leaf
FROM Employee
CONNECT BY PRIOR MgrName = EName
START WITH Emp_No = :Emp) M ON (M.Leaf = 1)
WHERE E.Emp_No = :Emp
hierarchial query..?
preet, July 04, 2007 - 5:39 am UTC
Tom,
In my database, I have a table TRADES which stores all the data pertaining to the trades.
There's another table RELATED_TRADES which stores all the related trades.
SELECT * FROM TRADES;
/
TRADE_ID TRADE_TYPE
------- ---------
1 A
2 B
3 A
4 B
5 A
6 B
7 B
8 B
SELECT * FROM RELATED_TRADES;
/
TRADE_ID RELATED_TRADE TRADE_STATUS
------- ------------- ------------
1 3 C
3 5 C
4 6 X
5 7 C
7 9 C
8 10 X
Now, a trade may or may not have a related trade. A related trade may or may not a further related trade.
There can be a complete hierarchy of related trades. E.G. in above example- trade 1 is related
to trade 3, trade 3 is related to trade 5 which in turn is related to trade 7. Trade 7 is related to trade 9 and so on.
I need to
1. Get all the trade_ids from TRADES one by one.
2. Get all the related_trades( and their related trades) for each trade_id.
3. Check the TRADE_STATUS of each. If it is 'X', archive the parent trade in TRADES to another table.
How do I do that in PlSql. I am using oracle 8i.
Please help
Thanks and Regards,
Preet
July 05, 2007 - 11:35 am UTC
no create table
no inserts
and I don't even bother to look. no idea if there is an answer for you, I just know that by not reading the page you used to submit this information - you lost a chance to have us look.
A reader, July 10, 2007 - 4:28 am UTC
Tom,
In this month¿s Oracle magazine, you¿ve done this following query, can you confirm the way prior rn = rn-1 executes?
I was thinking it will do the Prior RN first and then look for any rows that have RN-1. I mean take the first parent, find its child and then its children, in which case rn = rn + 1 would have been more appropriate.
But because the following query works, I presume the rows are may be in Random order (presumably that¿s what SQL guarantees), so EACH child is checked for A parent and the hierarchy is built up?
select deptno,
max(sys_connect_by_path
(ename, ' ' )) scbp
from (select deptno, ename,
row_number() over
(partition by deptno
order by ename) rn
from emp
)
start with rn = 1
connect by prior rn = rn-1
and prior deptno = deptno
group by deptno
order by deptno
July 10, 2007 - 11:55 am UTC
connect by prior rn = rn-1
if the prior row was "5" we want to connect it to row "6" (the next row) if the row "6" is what we want, "6"-1 is the number we need.
Left to right traversal
A reader, July 30, 2007 - 10:42 am UTC
Hi Tom,
I have following hierarchy.
A
/ \
B C
/\ /\
D E F G
I would like to achieve following in Oracle 10g.(Left to Right Traversal)
Parent Sort_Value
A 1
B 2
C 5
D 3
E 4
F 6
G 7
Create table relationship
(
parent varchar2(100),
child varchar2(100)
);
insert into relationship values ('A','B');
insert into relationship values ('A','C');
insert into relationship values ('B','D');
insert into relationship values ('B','E');
insert into relationship values ('C','F');
insert into relationship values ('C','G');
commit;
Regards
July 30, 2007 - 5:37 pm UTC
good? not sure what you want.
To A reader
Michel CADOT, July 31, 2007 - 2:25 am UTC
How Oracle can know the order in the siblings?
Which one is the first child, second child...?
Regards
Michel
A reader, July 31, 2007 - 5:22 am UTC
Hi Tom,
Thanks for looking into the issue.
I have following hierarchy.
A
/ \
B C
/\ /\
D E F G
I would like to achieve following in Oracle 10g.(Left to Right Traversal)
Parent Sort_Value
A 1
B 2
C 5
D 3
E 4
F 6
G 7
Create table relationship
(
parent varchar2(100),
child varchar2(100)
);
insert into relationship values ('A','B');
insert into relationship values ('A','C');
insert into relationship values ('B','D');
insert into relationship values ('B','E');
insert into relationship values ('C','F');
insert into relationship values ('C','G');
commit;
We have a reporting tool which wants to display the data in hierarchial fashion i.e. Algorithm goes like this
1First display the parent and mark it
2check for the left child
3if present and not already marked
dislay,mark it,treat this as parent and goto step 2
else
check for the right child
if present and not already marked
display,mark it,treat this as parent and goto step 2
else
move up the hierarchy by one level and goto step 2
Actually what I want is preorder traversal.
http://datastructures.itgo.com/trees/traversal.htm Regards
Here ya go Reader
Yuan, August 09, 2007 - 9:49 am UTC
select parent, rownum
from (select 'A' parent from dual
union all
select child
from relationship
start with parent = 'A'
connect by prior child = parent)
order by parent
How to pivot the results of the SYS_CONNECT_BY_PATH results
Gary Wicke, June 19, 2008 - 2:19 pm UTC
Hi Tom
I have a follow up based on the results shown at the very top of this thread. Given the results below, which represent a completed assembly ('OO%' entries) and all the sub-assemblies ('ST%' and 'WO%') that go into it, I would like to end up with a table that would have two (2) columns (completed_assembly, component_assembly) such that a completed assembly would appear as many times as there are sub-assemblies that go into it.
Here is what my query results look like:
/OO2114621000T701/ST000012
/OO2114641001T701/ST000016
/OO2114751003T701/ST000015
/OO2114921000T4001/ST000002/ST000003/ST000004/ST000005/WO3334094
Based on the data above I would want to see:
COMPLETED COMPONENT
---------------- ---------
OO2114621000T701 ST000012
OO2114641001T701 ST000016
OO2114751003T701 ST000015
OO2114921000T4001 ST000002
OO2114921000T4001 ST000003
OO2114921000T4001 ST000004
OO2114921000T4001 ST000005
OO2114921000T4001 WO3334094
Thanks very much, have a great day.
Regards
Gary
June 19, 2008 - 3:40 pm UTC
ops$tkyte%ORA10GR2> create table t ( data varchar2(80) );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( '/OO2114621000T701/ST000012');
1 row created.
ops$tkyte%ORA10GR2> insert into t values ( '/OO2114641001T701/ST000016');
1 row created.
ops$tkyte%ORA10GR2> insert into t values ( '/OO2114751003T701/ST000015');
1 row created.
ops$tkyte%ORA10GR2> insert into t values ( '/OO2114921000T4001/ST000002/ST000003/ST000004/ST000005/WO3334094');
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select t.first, x.column_value cv
2 from ( select data || '/' data, substr(data,2,instr(data,'/',1,2)-2) first, length(data)-length(replace(data,'/',''))-1 num
3 from t ) t,
4 table( cast( multiset( select substr(data,instr(data,'/',1,level+1)+1, instr(data,'/',1,level+2)-instr(data,'/',1,level+1)-1 )
5 from dual
6 connect by level <= t.num ) as sys.odcivarchar2list ) ) x;
FIRST CV
------------------ ------------------
OO2114621000T701 ST000012
OO2114641001T701 ST000016
OO2114751003T701 ST000015
OO2114921000T4001 ST000002
OO2114921000T4001 ST000003
OO2114921000T4001 ST000004
OO2114921000T4001 ST000005
OO2114921000T4001 WO3334094
8 rows selected.
Thanks! Couldn't find the 10-star rating
Gary, June 20, 2008 - 4:48 pm UTC
Tom
Many thanks! I would have struggled many hours/days/weeks, etc. to get this result. Looks like its time to go study the table(cast(multiset syntax to figure out what's going on.
Best resource on the web!
Looking forward to hearing you in July here in Indy.
Have a great vacation.
-gary
Inverse order of sys_connect_by_path
Eduardo de Paula Neves, September 12, 2008 - 1:22 pm UTC
How can I do the inverse path?
Like this:
**KING PRESIDENT /PRESIDENT
****JONES MANAGER /MANAGER/PRESIDENT
******SCOTT ANALYST /ANALYST/MANAGER/PRESIDENT
Thanks.
September 16, 2008 - 1:54 pm UTC
it doesn't work that way.
I am NOT RECOMMENDING THIS APPROACH
ops$tkyte%ORA11GR1> select rpad('*',2*level,'*') || ename nm, job,
2 sys_connect_by_path( job, '/' ) scbp,
3 (
4 select max( sys_connect_by_path( e2.job, '/' ))
5 from emp e2
6 start with e2.rowid = e1.rowid
7 connect by prior mgr = empno
8 ) scbp2
9 from emp e1
10 start with mgr is null
11 connect by prior empno = mgr;
NM JOB SCBP SCBP2
-------------------- --------- ---------------------------------------- ----------------------------------------
**KING PRESIDENT /PRESIDENT /PRESIDENT
****JONES MANAGER /PRESIDENT/MANAGER /MANAGER/PRESIDENT
******SCOTT ANALYST /PRESIDENT/MANAGER/ANALYST /ANALYST/MANAGER/PRESIDENT
********ADAMS CLERK /PRESIDENT/MANAGER/ANALYST/CLERK /CLERK/ANALYST/MANAGER/PRESIDENT
******FORD ANALYST /PRESIDENT/MANAGER/ANALYST /ANALYST/MANAGER/PRESIDENT
********SMITH CLERK /PRESIDENT/MANAGER/ANALYST/CLERK /CLERK/ANALYST/MANAGER/PRESIDENT
****BLAKE MANAGER /PRESIDENT/MANAGER /MANAGER/PRESIDENT
******ALLEN SALESMAN /PRESIDENT/MANAGER/SALESMAN /SALESMAN/MANAGER/PRESIDENT
******WARD SALESMAN /PRESIDENT/MANAGER/SALESMAN /SALESMAN/MANAGER/PRESIDENT
******MARTIN SALESMAN /PRESIDENT/MANAGER/SALESMAN /SALESMAN/MANAGER/PRESIDENT
******TURNER SALESMAN /PRESIDENT/MANAGER/SALESMAN /SALESMAN/MANAGER/PRESIDENT
******JAMES CLERK /PRESIDENT/MANAGER/CLERK /CLERK/MANAGER/PRESIDENT
****CLARK MANAGER /PRESIDENT/MANAGER /MANAGER/PRESIDENT
******MILLER CLERK /PRESIDENT/MANAGER/CLERK /CLERK/MANAGER/PRESIDENT
14 rows selected.
a lot of work to reverse the string.
How to "reverse engineer" hierarchy?
Kim Berg Hansen, March 02, 2010 - 9:25 am UTC
Hi, Tom
I use hierarchial queries quite a bit, but now I have received data from an external source that has a hierarchy but not a "parent" relation. I'm trying to "reverse engineer" and create the "parent" so that I can use "connect by" queries on the data in the future.
What I receive is a flat file containing data that are like the data from this query:
SQL> select
2 e.empno,
3 level lvl,
4 e.ename
5 from scott.emp e
6 start with e.mgr is null
7 connect by e.mgr = prior e.empno
8 order siblings by e.empno;
EMPNO LVL ENAME
---------- ---------- ----------
7839 1 KING
7566 2 JONES
7788 3 SCOTT
7876 4 ADAMS
7902 3 FORD
7369 4 SMITH
7698 2 BLAKE
7499 3 ALLEN
7521 3 WARD
7654 3 MARTIN
7844 3 TURNER
7900 3 JAMES
7782 2 CLARK
7934 3 MILLER
14 rows selected.
The file is sorted as this output (like "order siblings by") and I read the file as an external table, so I get the data in that order.
Now I wish to create a table with these data and a new column MGR - that is I want to "reverse engineer" and figure out that both CLARK and JONES has MGR=7839 and MARTIN has MGR=7698.
For testing purposes I create this table:
SQL> create table t as
2 select
3 s1.*,
4 rownum recid
5 from (
6 select
7 e.empno,
8 level lvl,
9 e.ename
10 from scott.emp e
11 start with e.mgr is null
12 connect by e.mgr = prior e.empno
13 order siblings by e.empno
14 ) s1;
Table created.
(When I select from this table order by recid I emulate reading the external table in "line order" :-)
I have attempted solving this using analytic functions using last_value with "ignore nulls" in order to find the "parent" in the hierarchy:
SQL> select
2 empno,
3 lvl,
4 ename,
5 last_value(lag_emp ignore nulls) over (order by recid) mgr
6 from (
7 select
8 s1.*,
9 case
10 when lag(lvl) over (order by recid) = lvl then null
11 else lag(empno) over (order by recid)
12 end lag_emp
13 from (
14 select
15 empno,
16 lvl,
17 ename,
18 recid
19 from t
20 order by recid
21 ) s1
22 ) s2;
EMPNO LVL ENAME MGR
---------- ---------- ---------- ----------
7839 1 KING
7566 2 JONES 7839
7788 3 SCOTT 7566
7876 4 ADAMS 7788
7902 3 FORD 7876
7369 4 SMITH 7902
7698 2 BLAKE 7369
7499 3 ALLEN 7698
7521 3 WARD 7698
7654 3 MARTIN 7698
7844 3 TURNER 7698
7900 3 JAMES 7698
7782 2 CLARK 7900
7934 3 MILLER 7782
14 rows selected.
(I know the inner select s1 isn't really needed - it's mostly to show that it "emulates" reading from an external table "order by rownum" style :-)
The idea of this code was that for each row the logic would be "if I'm the same level as the one before me, then LAG_EMP is null, but if I'm a different level, then LAG_EMP is the empno of the one before me." And then use the "ignore nulls" trick to populate MGR of those with null LAG_EMP.
That works dandy as long as LVL is increasing. For example the MGR of ALLEN, WARD, MARTIN, TURNER and JAMES have all correctly been set to 7698 (BLAKE). But when LVL is decreasing it fails - CLARK should not have MGR=7900 (JAMES), he should have MGR=7839 (KING)...
I can't think of a way to set up a range in an analytic window clause that allows me for each record to say "go backwards until you find a LVL that is 1 less than my LVL and take the EMPNO value of that record"? (Yes, I think this would be a case for the "Comparative Window Functions" idea you mentioned on your blog - but that's in the future, alas :-)
I probably should scrap this approach and start with a fresh mindset (maybe some procedural code, this will be a job to be run not very often) - but right now I can't think of a better way than some analytics (they rock'n'roll, you know :-) Perhaps I might go "model clause"?
If you have a hint or two on how I might go about this, I would be most grateful :-)
Thanks for your help to all of us out there...
Addendum to my previous comment
Kim Berg Hansen, March 03, 2010 - 1:48 am UTC
Hi again, Tom
Addendum to my previous comment where I ask for a hint as to how to "reverse engineer" a hierarchy:
I have now a procedural solution that will work:
SQL> declare
2 type fathers_t is table of t.empno%type index by binary_integer;
3 fathertable fathers_t;
4 mgr t.empno%type;
5 prev_lvl t.lvl%type;
6 begin
7 prev_lvl := 0;
8 for e in (
9 select
10 empno,
11 lvl,
12 ename,
13 recid
14 from t
15 order by recid
16 ) loop
17 if e.lvl = 1 then
18 mgr := null;
19 else
20 mgr := fathertable(e.lvl-1);
21 end if;
22 dbms_output.put_line(e.empno||' '||e.lvl||' '||rpad(e.ename,10)||mgr);
23 if e.lvl <> prev_lvl then
24 fathertable(e.lvl) := e.empno;
25 end if;
26 prev_lvl := e.lvl;
27 end loop;
28 end;
29 /
7839 1 KING
7566 2 JONES 7839
7788 3 SCOTT 7566
7876 4 ADAMS 7788
7902 3 FORD 7566
7369 4 SMITH 7902
7698 2 BLAKE 7839
7499 3 ALLEN 7698
7521 3 WARD 7698
7654 3 MARTIN 7698
7844 3 TURNER 7698
7900 3 JAMES 7698
7782 2 CLARK 7839
7934 3 MILLER 7782
PL/SQL procedure successfully completed.
As it is a job to be run not very often, this approach is acceptable even if perhaps not the fastest way :-)
I guess over the last years I have been so thoroughly indoctrinated in the mantra "do it in SQL if possible", that I don't think about a PL/SQL solution until I'm pretty sure I can't do it in SQL :-)
Anyway - my problem I must declare to be solved. (But my curiosity would still be happy if you know of a technique to solve this in SQL before we get Comparative Window Functions :-)
Thanks for everything
March 03, 2010 - 7:06 am UTC
ops$tkyte%ORA10GR2> select empno, ename,
2 (select max( to_char(t2.recid, 'fm00000') || ' ' || empno || ',' || ename ) from t t2 where t2.lvl = t1.lvl-1 and t2.recid < t1.recid ) mgr
3 from t t1
4 order by recid
5 /
EMPNO ENAME MGR
---------- ---------- ----------------------------------------------------------
7839 KING
7566 JONES 00001 7839,KING
7788 SCOTT 00002 7566,JONES
7876 ADAMS 00003 7788,SCOTT
7902 FORD 00002 7566,JONES
7369 SMITH 00005 7902,FORD
7698 BLAKE 00001 7839,KING
7499 ALLEN 00007 7698,BLAKE
7521 WARD 00007 7698,BLAKE
7654 MARTIN 00007 7698,BLAKE
7844 TURNER 00007 7698,BLAKE
7900 JAMES 00007 7698,BLAKE
7782 CLARK 00001 7839,KING
7934 MILLER 00013 7782,CLARK
14 rows selected.
it would not be very efficient - unless the data set was rather small and we could index - even then, it would not be excessively efficient.
Thanks Tom - I got a model clause working too :-)
Kim Berg Hansen, March 03, 2010 - 9:35 am UTC
Thanks for your response, Tom.
D'oh, yeah, sure I can use a subselect :-)
In the meantime 5 minutes ago (before I read your response) I have just completed a way of doing it with model clause (can't resist playing with rock'n'roll :-) :
SQL> select
2 empno,
3 lvl,
4 ename,
5 mgr
6 from (
7 select
8 empno,
9 lvl,
10 ename,
11 recid,
12 recid recid2
13 from t
14 order by recid
15 ) s1
16 where rownum >= 1
17 model
18 dimension by (lvl,recid)
19 measures (empno,ename,cast(null as number(4)) mgr,recid2)
20 rules
21 (
22 mgr[any,any] = max(empno) keep (dense_rank last order by recid2)[lvl=cv()-1,recid<cv()]
23 )
24 order by recid;
EMPNO LVL ENAME MGR
---------- ---------- ---------- ----------
7839 1 KING
7566 2 JONES 7839
7788 3 SCOTT 7566
7876 4 ADAMS 7788
7902 3 FORD 7566
7369 4 SMITH 7902
7698 2 BLAKE 7839
7499 3 ALLEN 7698
7521 3 WARD 7698
7654 3 MARTIN 7698
7844 3 TURNER 7698
7900 3 JAMES 7698
7782 2 CLARK 7839
7934 3 MILLER 7782
14 rows selected.
In a way what I'm doing is to use level for a two-dimensional array to do the same as I did procedurally in my second review.
The thing that took me some time to figure out was a little trick:
This line:
22 mgr[any,any] = max(empno) keep (dense_rank last order by RECID)[lvl=cv()-1,recid<cv()]
will not work. It seems that a
dimension cannot be used in the order by clause. So therefore I select the recid twice, so I can get a
measure recid2 to use in the order by:
22 mgr[any,any] = max(empno) keep (dense_rank last order by RECID2)[lvl=cv()-1,recid<cv()]
That works fine - I just have to test it on larger datasets :-)
Reason I wish to do this in a single sql statement is, that one of my preferred way to import data from external sources is this:
1) Create an external table reading the data
2) Create a materialized view refresh complete on demand that selects from the external table (optionally "refining" the data - as in this case adding a "parent" column.)
3) Add relevant indexes on the materialized view
That way when the external source gives us new data, it is just a matter of putting the file in the right directory and refreshing the materialized view - presto, we're done :-)
I actually have one setup, where I use UTL_FTP package (by Timothy Hall -
http://www.oracle-base.com/articles/misc/FTPFromPLSQL.php ) to monthly read new data from an ftp server and then dmbs_refresh the materialized view.
So in a setup like that it makes it much easier, if I can do all my "refining" of the data in the single sql statement that defines the materialized view.
Anyway - thanks again for your pointers. I'll have to test if my model clause performs well on larger datasets :-)
Query on the sys_connect_by_path for the original quertion query
Declan Wilson, April 13, 2010 - 6:32 am UTC
Tom,
(Oracle 10 database)
Am interested to know if for each portion of the 'sys_connect_by_path( job, '/' ) scbp' column - can I can query another table (lets say a table 'jobdata' concerning other job related data), and return some of that table's data into new columns, within the same query.
I don't want to join the emp table to the jobdata table as an initial dataset on which to query with the connection by clauses ... I'd like to only perform the lookup into 'jobdata' as I treewalk the emp table.
Is this possible?
April 13, 2010 - 9:24 am UTC
you can use scalar subqueries, yes.
but what does that have to do with sys_connect_by_path - you obviously have something in mind, but I'm not sure what you want to do.
you need a create/inserts and explanation of what you are trying to accomplish to get a more concrete answer.
Att: Declan Wilson
Kim Berg Hansen, April 14, 2010 - 1:06 am UTC
Hi, Declan
You ask:
for each portion of the 'sys_connect_by_path( job, '/' ) scbp' column - can I can query another tableI believe you're looking for this:
SQL> select
2 sys_connect_by_path(
3 (select d.dname from scott.dept d where d.deptno = e.deptno),
4 '/'
5 ) scbp
6 from scott.emp e
7 start with e.mgr is null
8 connect by e.mgr = prior e.empno;
SCBP
---------------------------------------------------------------------
/ACCOUNTING
/ACCOUNTING/RESEARCH
/ACCOUNTING/RESEARCH/RESEARCH
/ACCOUNTING/RESEARCH/RESEARCH/RESEARCH
/ACCOUNTING/RESEARCH/RESEARCH
/ACCOUNTING/RESEARCH/RESEARCH/RESEARCH
/ACCOUNTING/SALES
/ACCOUNTING/SALES/SALES
/ACCOUNTING/SALES/SALES
/ACCOUNTING/SALES/SALES
/ACCOUNTING/SALES/SALES
/ACCOUNTING/SALES/SALES
/ACCOUNTING/ACCOUNTING
/ACCOUNTING/ACCOUNTING/ACCOUNTING
14 rows selected.
It works in 11g - I don't know about 10g :-)