Skip to Main Content
  • Questions
  • How to Rollup Hierarchy from CONNECT BY into Singe Row?

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Richard.

Asked: July 30, 2003 - 9:44 pm UTC

Last updated: April 13, 2010 - 9:24 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

We have an ORGANIZATIONS table that contains the rollup hierarchy for financial reporting throughout the company. It's teh typical structure of ORG_ID,PARENT_ORD_ID plus a few additional attributes to select the particular rollup structure to report (financial reporting, budget reporting, etc.) Generating the Connect By query to obtain the hierarchy itself is fairly straightforward, and I've taken it to the point where I generate a view with each level of the hierarchy in a separate column.
The difficulty I'm running into now is how to cascade the hierarchy values from parent nodes down to the current row?
For Example:
COMPANY
--DIVISION
----REGION
------DISTRICT
Row 2 would have columns COMPANY,DIVISION; row 3 would have COMPANY,DIVISION,REGION; etc.
I considered using LAG() or FIRST_VALUE() analytic functions, but they didn't conceptually seem to meet the need. Would I have to resort to a pl/sql procedure to loop thru the result set?


and Tom said...

Oracle 9i makes this trival and efficient -- but sys_connect_by_path is not available in 8i:

ops$tkyte@ORA920> select rpad('*',2*level,'*')|| ename emp_name, job,
2 sys_connect_by_path( 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.

In 8i, you can use an object type like this

ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace type myArrayType as table of varchar2(30)
2 /

Type created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> column scbp format a60
ops$tkyte@ORA920> select rpad('*',2*level,'*')|| ename emp_name, job,
2 cast( multiset( select job
3 from emp e2
4 start with empno = emp.empno
5 connect by prior mgr = empno ) as myArrayType ) scbp
6 from emp
7 start with mgr is null
8 connect by prior empno = mgr
9 /

EMP_NAME JOB SCBP
------------------------------ --------- ------------------------------------------------------------
**KING PRESIDENT MYARRAYTYPE('PRESIDENT')
****JONES MANAGER MYARRAYTYPE('MANAGER', 'PRESIDENT')
******SCOTT ANALYST MYARRAYTYPE('ANALYST', 'MANAGER', 'PRESIDENT')
********ADAMS CLERK MYARRAYTYPE('CLERK', 'ANALYST', 'MANAGER', 'PRESIDENT')
******FORD ANALYST MYARRAYTYPE('ANALYST', 'MANAGER', 'PRESIDENT')
********SMITH CLERK MYARRAYTYPE('CLERK', 'ANALYST', 'MANAGER', 'PRESIDENT')
****BLAKE MANAGER MYARRAYTYPE('MANAGER', 'PRESIDENT')
******ALLEN SALESMAN MYARRAYTYPE('SALESMAN', 'MANAGER', 'PRESIDENT')
******WARD SALESMAN MYARRAYTYPE('SALESMAN', 'MANAGER', 'PRESIDENT')
******MARTIN SALESMAN MYARRAYTYPE('SALESMAN', 'MANAGER', 'PRESIDENT')
******TURNER SALESMAN MYARRAYTYPE('SALESMAN', 'MANAGER', 'PRESIDENT')
******JAMES CLERK MYARRAYTYPE('CLERK', 'MANAGER', 'PRESIDENT')
****CLARK MANAGER MYARRAYTYPE('MANAGER', 'PRESIDENT')
******MILLER CLERK MYARRAYTYPE('CLERK', 'MANAGER', 'PRESIDENT')

14 rows selected.


or a cursor variable like this:


ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> select rpad('*',2*level,'*')|| ename emp_name, job,
2 CURSOR( select job
3 from emp e2
4 start with empno = emp.empno
5 connect by prior mgr = empno ) scbp
6 from emp
7 start with mgr is null
8 connect by prior empno = mgr
9 /

EMP_NAME JOB SCBP
------------------------------ --------- ------------------------------------------------------------
**KING PRESIDENT CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

JOB
---------
PRESIDENT

****JONES MANAGER CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

JOB
---------
MANAGER
PRESIDENT

******SCOTT ANALYST CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

JOB
---------
ANALYST
MANAGER
PRESIDENT


Rating

  (74 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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.


Tom Kyte
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

Tom Kyte
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 

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

Tom Kyte
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

Tom Kyte
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

Tom Kyte
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;
/
 

Tom Kyte
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.

Tom Kyte
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

Tom Kyte
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

Tom Kyte
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

Tom Kyte
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...

Tom Kyte
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...:)

Tom Kyte
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....

Tom Kyte
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?

Tom Kyte
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.

Tom Kyte
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.

Tom Kyte
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.



Tom Kyte
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 ..




Tom Kyte
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

Tom Kyte
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,

Tom Kyte
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,

Tom Kyte
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.

Tom Kyte
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,



Tom Kyte
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,



Tom Kyte
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


Tom Kyte
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,


Tom Kyte
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,

Tom Kyte
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,

Tom Kyte
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

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




 

Tom Kyte
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>  

Tom Kyte
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

Tom Kyte
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

Tom Kyte
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
Tom Kyte
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
Tom Kyte
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
Tom Kyte
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
Tom Kyte
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?
Tom Kyte
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.

Tom Kyte
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.



Tom Kyte
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

Tom Kyte
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

Tom Kyte
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
Tom Kyte
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
Tom Kyte
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.

Tom Kyte
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

Tom Kyte
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?





Tom Kyte
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 table

I 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 :-)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library