Dear Tom,

That was indeed very helpful. But my problem is little different.

I am developing an application in D2k and using the hierarchical tree control. It accepts a single query for populating the data. My problem is that I have two queries that has to be joined. My table structures are as follows: -

Create Table T1
(Code Varchar2(10) Constraint pk_T1_Code Primary Key,
T1_Name Varchar2(100),
Parent_Code Varchar2(10) Constraint fk_T1_PCode References T1(Code)
)
/
Create Table T2
(Code Varchar2(10) Constraint pk_T2_Code Primary Key,
T2_Name Varchar2(100),
T1_Code Varchar2(10) Constraint fk_T2_T1Code References T1(Code)
)
/

Please note that these are just sample tables. My design is such that I should have both these tables separate. With only T1, my hierarchical query is very simple. But my problem is that the table T2 will have rows that refers to one (and only one) of the rows in table T1, and when I show the tree it should appear like that.

Expecting your valuable help.

Regards,

Sri

Hi Tom,

I am afraid Tom, I think I didn't put my requirement properly. I have the tables t1 and t2 as above. Now,

insert into t1 values ('1', 'First', Null);
insert into t1 values ('2', 'A', '1');
insert into t1 values ('3', 'B', '1');
insert into t1 values ('4', 'C', '1');
insert into t1 values ('5', 'AA', '2');
insert into t1 values ('6', 'AB', '2');
insert into t1 values ('7', 'CA', '4');
insert into t1 values ('8', 'CB', '4');
insert into t1 values ('9', 'CAA', '7');
insert into t1 values ('10', 'ABA', '6');

insert into t2 values ('1', 'Child of AA', '5');
insert into t2 values ('2', 'Child of CAA', '9');

I gave the select statement as you adviced: -

select level, substr(t1_name, 1, 15) Name,
(select substr(t2_name, 1, 15) from t2 where t2.t1_code = t1.code) t2Name
from t1
start with parent_code is Null
Connect by prior code = parent_code;

I am getting the following output: -

LEVEL NAME T2NAME
---------- ---------- ---------------
1 First
2 A
3 AA Child of AA
3 AB
4 ABA
2 B
2 C
3 CA
4 CAA Child of CAA
3 CB

Actually, my requirement is that I need the output as follows: -

LEVEL NAME
---------- -----------
1 First
2 A
3 AA
4 Child of AA
3 AB
4 ABA
2 B
2 C
3 CA
4 CAA
5 Child of CAA
3 CB

Hope you got me. Waiting for your comments.

Sri

The solution is very useful But i have slightly diffrent requirement. I want to stop at the Highest level for a particular Manager. For ex: For a particular hierarchy the output should stop at higest level. The sample output is given below::

1 **KING
2 ****JONES
3 ******SCOTT
4 ********ADAMS

Becaase i will be processing the employee with Highest LEVEL. Can you please suggest me a solution for this.

Sunil.

The Following is a TREE view of the EMP Table.

SELECT Empno,Ename,Sal,LEVEL
FROM EMP
START WITH Empno In (7839,1234)
CONNECT BY mgr = PRIOR empno;

EMPNO ENAME SAL LEVEL
---------- ---------- ---------- ----------
7839 KING 5000 1
7566 JONES 2975 2
7788 SCOTT 3000 3
7876 ADAMS 1100 4
7902 FORD 3000 3
7369 SMITH 800 4
7698 BLAKE 2850 2
7499 ALLEN 1600 3
7521 WARD 1250 3
7654 MARTIN 1250 3
7844 TURNER 1500 3

EMPNO ENAME SAL LEVEL
---------- ---------- ---------- ----------
7900 JAMES 950 3
7782 CLARK 2450 2
7934 MILLER 1300 3
1234 SHAUN 8000 1
1235 MARK 4000 2

Now I want to sort this TREE on the basis of SAL for
PARENT row only. If I sort by SAL DESC then I would like to have the SHAUN (with empno 1234) and its tree to come first.
If I do sort by SAL ASC, the above should be the output.

I did it using a stored procedure using PLSQL table type. But I am unable to return it to JAVA as a OUT Parameter.
The other way is to use TEMP TABLES, but I do not want to use it.

Please suggest me how this can be achieved. Is it possible using a single QUERY.

Regards
Siba

Tom,
What about a situation where I want to do a search in the hierarchy for a bit of text. I'm going to use contains and index it using the ctxsys index. I want to return this in the hierarchy as above but Oracle won't allow me to place this subquery in the the connect by condition.
Eg

Data returned as we have it now ....

1A
2AA
3AAA
4AAAA
5AAAAA
1A
2AB
3AB2
4AAA
5ABCD
1 C
2 CC

Perform a search for AAA, want the below returned...
1A
2AA
3AAA
1A
2AB
3AB2
4AAA

Is this possible in a statement?

Hi Tom,
Which of the either queries is better?
sql>select max(sal) from (select sal from emp
where rownum <= 5);
or
sql>select sal from (select sal from emp where rownum <= 5
order by sal desc) where rownum = 1;
Do you have other ways to put this query in a far better
way? Please do reply.
Bye!

both are wrong.  both return random data.  neither is a good query.

both get 5 random rows more or less from emp, then return the max sal from this set of 5.

so, both are meaningless, unpredicable.


ops$tkyte@ORA920PC> select max(sal) from ( select sal from emp1 where rownum <= 5 );
 
  MAX(SAL)
----------
      5000
 
ops$tkyte@ORA920PC> select sal from ( select sal from emp2 where rownum <= 5 order by sal desc) where rownum=1;
 
       SAL
----------
      1250
 
ops$tkyte@ORA920PC> select * from emp1 minus select * from emp2;
 
no rows selected
 
ops$tkyte@ORA920PC> select * from emp2 minus select * from emp1;
 
no rows selected
 
ops$tkyte@ORA920PC>



<b>See, same data in both, different answers....</b>


 

Dear tom,
I need a query that puts employees hierarchically sorted
departmentwise.I mean
dept Hierarchy
---- --------
Accounts King <<-- Head
Jones -- Subordinate
Marketing ..
Projects ..
Is that possible using sql?
Thanks in advance.

Hello Tom.

Referring back to Siba's note from 12/27/2002:
The result set is -

EMPNO ENAME SAL LVL
--------------- ---------- ---------- ----------
**7566 JONES 2975 1
****7788 SCOTT 3000 2
******7876 ADAMS 1100 3
****7902 FORD 3000 2
******7369 SMITH 800 3
**7698 BLAKE 2850 1
****7499 ALLEN 1600 2
****7521 WARD 1250 2
****7654 MARTIN 1250 2
****7844 TURNER 1500 2
****7900 JAMES 950 2

Is there a way to add a column with dot notion such as:

EMPNO ENAME SAL LVL LVL2
--------------- ---------- ---------- ---------- ----------
**7566 JONES 2975 1 1
****7788 SCOTT 3000 2 1.1
******7876 ADAMS 1100 3 1.1.1
****7902 FORD 3000 2 1.2
******7369 SMITH 800 3 1.2.1
**7698 BLAKE 2850 1 2
****7499 ALLEN 1600 2 2.1
****7521 WARD 1250 2 2.2
****7654 MARTIN 1250 2 2.3
****7844 TURNER 1500 2 2.4
****7900 JAMES 950 2 2.5

If so, can you please illustrate with an example or a reference?

Many, many thanks!
- J

ops$tkyte@ORA9IR2> select ename,
  2             substr(
  3             case when lvl >= 1 then substr( max(l1) over (order by r), 6 ) end ||
  4             case when lvl >= 2 then substr( max(l2) over (order by r), 6 ) end ||
  5             case when lvl >= 3 then substr( max(l3) over (order by r), 6 ) end ||
  6             case when lvl >= 4 then substr( max(l4) over (order by r), 6 ) end, 2 ) lvl2
  7    from (
  8  select ename, r, lvl, rn,
  9         decode( lvl, 1, to_char(r,'fm00000')||'.'|| rn ) l1,
 10         decode( lvl, 2, to_char(r,'fm00000')||'.'|| rn ) l2,
 11         decode( lvl, 3, to_char(r,'fm00000')||'.'|| rn ) l3,
 12         decode( lvl, 4, to_char(r,'fm00000')||'.'|| rn ) l4
 13    from (
 14  select ename, r, row_number() over (partition by scbp order by r) rn, lvl, scbp
 15    from (
 16  select ename,
 17         r,
 18             substr( scbp, 1, instr(scbp,'.',-1,1) ) scbp,
 19             lvl
 20    from (
 21  select rpad( '*', 2*level, '*' ) || ename ename,
 22             rownum r,
 23             sys_connect_by_path( empno, '.' ) scbp,
 24             level lvl
 25    from emp
 26    start with mgr = (select empno from emp where ename='KING')
 27    connect by prior empno = mgr
 28          )
 29          )
 30                  )
 31                  )
 32    order by r
 33  /
 
ENAME                LVL2
-------------------- ----------
**JONES              1
****SCOTT            1.1
******ADAMS          1.1.1
****FORD             1.2
******SMITH          1.2.1
**BLAKE              2
****ALLEN            2.1
****WARD             2.2
****MARTIN           2.3
****TURNER           2.4
****JAMES            2.5
**CLARK              3
****MILLER           3.1
 
13 rows selected.
 

that would be workable for reasonable sized hierarchies -- couple of sorts going on there. 

Gotta start drinking your brand of coffee...
Thanks a million, Tom. Whew...

Hi Tom,

I could have never visualized the sql you wrote for "Levels with dot/outline notation?".
It is simply astonishing to know the way your mind works.

Got any tips to share which can help visualizing solutions to a problem like this one?

Regards
Deepak

One for any depth? 

Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> COLUMN ename FORMAT A20;
SQL> COLUMN nde FORMAT A20;
SQL> SET PAGESIZE 100;
SQL> INSERT INTO emp VALUES (9001, 'KYTE', 'JEDI', 7369, SYSDATE, 10000, NULL, 20);

1 row created.

SQL> INSERT INTO emp VALUES (9002, 'PADFIELD', 'PADAWAN', 9001, SYSDATE, 10, NULL, 20);

1 row created.

SQL> INSERT INTO emp VALUES (9003, 'ROBERTSON', 'PADAWAN', 9001, SYSDATE, 10, NULL, 20);

1 row created.

SQL> INSERT INTO emp VALUES (9004, 'BILLINGTON', 'PADAWAN', 9001, SYSDATE, 10, NULL, 20);

1 row created.

SQL> SELECT LPAD (' ', 2 * (LEVEL - 1)) || ename ename, job,
  2           LTRIM (SYS_CONNECT_BY_PATH (nde, '.'), '.') nde
  3  FROM  (SELECT ename, empno, mgr, job, lvl,
  4                COUNT (*) OVER (
  5                  PARTITION BY SUBSTR (pth, 1, INSTR (pth, '.', -1))
  6                  ORDER BY ord) nde
  7         FROM  (SELECT ename, empno, mgr, job, LEVEL lvl,
  8                       SYS_CONNECT_BY_PATH (ename, '.') pth,
  9                       ROWNUM ord
 10                FROM   emp
 11                START WITH mgr = 7839
 12                CONNECT BY PRIOR empno = mgr))
 13  START WITH lvl = 1
 14  CONNECT BY PRIOR empno = mgr;

ENAME                JOB       NDE
-------------------- --------- --------------------
JONES                MANAGER   1
  SCOTT              ANALYST   1.1
    ADAMS            CLERK     1.1.1
  FORD               ANALYST   1.2
    SMITH            CLERK     1.2.1
      KYTE           JEDI      1.2.1.1
        PADFIELD     PADAWAN   1.2.1.1.1
        ROBERTSON    PADAWAN   1.2.1.1.2
        BILLINGTON   PADAWAN   1.2.1.1.3
BLAKE                MANAGER   2
  ALLEN              SALESMAN  2.1
  WARD               SALESMAN  2.2
  TURNER             SALESMAN  2.4
  MARTIN             SALESMAN  2.3
  JAMES              CLERK     2.5
CLARK                MANAGER   3
  MILLER             CLERK     3.1

17 rows selected.

SQL>

(Any relationship between names used herein and any real person is purely coincidental)
 

Hadn't yet encountered the SYS_CONNECT_BY_PATH function--pretty neat. How about this as a simpler alternative to the above queries:


SQL*Plus: Release 9.2.0.1.0 - Production on Wed Aug 4 15:08:21 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

>column ename format a20
>column epath format a10
>column base_epath format a10
>column rel_epath format a10
>set pagesize 100
> select empno,
2 lpad( '_', 2 * ( level - 1 ), '_' ) || ename ename,
3 sys_connect_by_path( pos, '.' ) epath,
4 sal
5 from ( select empno,
6 ename,
7 mgr,
8 sal,
9 row_number() over
10 ( partition by mgr
11 order by sal desc,
12 empno ) pos
13 from scott.emp )
14 start with
15 mgr is null
16 connect by
17 prior empno = mgr
18 /

EMPNO ENAME EPATH SAL
---------- -------------------- ---------- ----------
7839 KING .1 5000
7566 __JONES .1.1 2975
7788 ____SCOTT .1.1.1 3000
7876 ______ADAMS .1.1.1.1 1100
7902 ____FORD .1.1.2 3000
7369 ______SMITH .1.1.2.1 800
7698 __BLAKE .1.2 2850
7499 ____ALLEN .1.2.1 1600
7844 ____TURNER .1.2.2 1500
7521 ____WARD .1.2.3 1250
7654 ____MARTIN .1.2.4 1250
7900 ____JAMES .1.2.5 950
7782 __CLARK .1.3 2450
7934 ____MILLER .1.3.1 1300

14 rows selected.

From this, you can easily SUBSTR out the common prefix. Or, how about a generic "relative path" query for any subtree:

>variable test_empno number
>exec :test_empno := 7566;

PL/SQL procedure successfully completed.

> select empno,
2 lpad( '_', 2 * ( level - 1 ), '_' ) || ename ename,
3 substr( sys_connect_by_path( pos, '.' ), 4 ) rel_epath,
4 sal
5 from ( select empno,
6 ename,
7 mgr,
8 sal,
9 row_number() over
10 ( partition by mgr
11 order by sal desc,
12 empno ) pos
13 from scott.emp )
14 start with
15 empno = :test_empno
16 connect by
17 prior empno = mgr
18 /

EMPNO ENAME REL_EPATH SAL
---------- -------------------- ---------- ----------
7566 JONES 2975
7788 __SCOTT 1 3000
7876 ____ADAMS 1.1 1100
7902 __FORD 2 3000
7369 ____SMITH 2.1 800

On this final example, I confess I'm just having fun with the analytics. It shows both the "base path" and "relative path" for any subtree:

>exec :test_empno := 7698;

PL/SQL procedure successfully completed.

> select empno,
2 ename,
3 epath,
4 base_epath,
5 substr( epath,
6 2 + length( base_epath )
7 ) rel_epath,
8 sal
9 from ( select empno,
10 ename,
11 epath,
12 sal,
13 depth_first_seq,
14 max( case
15 when empno = :test_empno
16 then epath
17 else null
18 end
19 ) over
20 ( order by 0 ) base_epath
21 from ( select empno,
22 lpad( '_', 2 * ( level - 1 ), '_' ) || ename ename,
23 sys_connect_by_path( pos, '.' ) epath,
24 sal,
25 rownum depth_first_seq
26 from ( select empno,
27 ename,
28 mgr,
29 sal,
30 row_number() over
31 ( partition by mgr
32 order by sal desc,
33 empno ) pos
34 from scott.emp
35 )
36 start with
37 mgr is null
38 connect by
39 prior empno = mgr
40 )
41 )
42 where instr( epath, base_epath ) = 1
43 order by
44 depth_first_seq
45 /

EMPNO ENAME EPATH BASE_EPATH REL_EPATH SAL
---------- -------------------- ---------- ---------- ---------- ----------
7698 __BLAKE .1.2 .1.2 2850
7499 ____ALLEN .1.2.1 .1.2 1 1600
7844 ____TURNER .1.2.2 .1.2 2 1500
7521 ____WARD .1.2.3 .1.2 3 1250
7654 ____MARTIN .1.2.4 .1.2 4 1250
7900 ____JAMES .1.2.5 .1.2 5 950

6 rows selected.

Suppose we have an hierarchical data a follows and have to select records between two levels below the root and
two level above the leaf. The condition "level NOT IN (1,2)" will exclude top two levels. Is it possible to exclude
last two levels similarly in the same query?


L0..................................................Root Level
|
+-->L1..............................................Level 2
| |
| +-->L2.........................................Level 3
| |
| +-->L3.....................................Level 4
| |
| +-->L4.................................Level 5
| |
| +-->L5.............................Level 6
| |
| +-->P1.........................Leaf
| |
| +-->P2.........................Leaf
| |
| +-->P3.........................Leaf
|
|
+-->L6..............................................Level 2
|
+-->L7..........................................Level 3
| |
| +-->L8......................................Level 4
| |
| +-->L9..................................Level 5
| |
| +-->P4..............................Leaf
| |
| +-->P5..............................Leaf
|
|
+-->L10.........................................Level 3
|
+-->L11.....................................Level 4
|
+-->P6..................................Leaf
|
+-->P7..................................Leaf


create table employees (emp varchar2(10) primary key, mgr varchar2(10) references employees(emp));

insert into employees values('L0', NULL);

insert into employees values('L1', 'L0');

insert into employees values('L2', 'L1');

insert into employees values('L3', 'L2');

insert into employees values('L4', 'L3');

insert into employees values('L5', 'L4');

insert into employees values('P1', 'L5');

insert into employees values('P2', 'L5');

insert into employees values('P3', 'L5');

insert into employees values('L6', 'L0');

insert into employees values('L7', 'L6');

insert into employees values('L8', 'L7');

insert into employees values('L9', 'L8');

insert into employees values('P4', 'L9');

insert into employees values('P5', 'L9');

insert into employees values('L10', 'L6');

insert into employees values('L11', 'L10');

insert into employees values('P6', 'L11');

insert into employees values('P7', 'L11');

COMMIT;

The following SQL gives me results ommitting the root and second level records.

select * from (select emp, mgr, level from employees
where level not in (1,2)
connect by mgr = prior emp
start with emp='L0');

But I want to ommit records at level leaf and 1 level above leaf also. In the end the result set should NOT contain
(L0, L1, L6)-top two levels, (P1, P2, P3, P4, P5, P6, P7)-leaf levels Or (L5, L9, L11)- one level above the leaf.

Is this possible using a single hierarchical query? Or do we need to write a procedure?

Thanks and regards

Praveen

INGENIOUS!!!

But feel like a procedure can gain in performance.
What do u think sir?

Thanks Tom,

Praveen

Tom,

need your help here.

I was able to run a query till last week, suddenly this has stopped running giving error.

SELECT
*
ERROR at line 1:
ORA-00928: missing SELECT keyword


select
emp_typ1.emp_sso_id,
emp_typ1.first_nam,
emp_typ1.last_nam,
level,deleted_flg,deleted_dt,supervisor_sso_id
from
emp_typ1
where emp_sso_id>0 connect by prior emp_sso_id = supervisor_sso_id
start with emp_sso_id in
(
select a.emp_sso_id from emp_hier a,emp_typ1 b
where a.tier2_mgr_sso_id=-2
and a.emp_sso_id in(select supervisor_sso_id from emp_typ1)
and a.emp_sso_id=b.emp_sso_id and b.supervisor_sso_id not in(select emp_sso_id from emp_typ1)
and b.supervisor_sso_id!=-1
)


I tried rebuilding index - just in case any problem. also analyzed the table.

I could not go ahead with this. Please help,the version is Oracle 9.0.1. i was able run the the same query in 9.2

Thanks for your consideration

Tom,

It was working in 9ir1. now its not running. doesnt that sql run in 9ir1 - any flaw in that?

Thanks

Tom,

Sorry about that - i didnt get you, until you said.

please see for the table structure.
create table emp_typ1
( seh_emp_id number(10,0) not null enable,
emp_sso_id number(10,0) not null enable,
prefix_nam varchar2(40),
first_nam varchar2(40) not null enable,
middle_nam varchar2(40),
last_nam varchar2(40) not null enable,
supervisor_sso_id number(10,0) not null enable,
deleted_flg char(1) default 'n' not null enable,
deleted_dt date,
primary key (seh_emp_id)
using index pctfree 0 initrans 2 maxtrans 255
tablespace users enable
) pctfree 10 pctused 40 initrans 1 maxtrans 255 nocompress logging
tablespace users



create table emp_hier
( tier2_mgr_sso_id number(10,0),
emp_sso_id number(10,0),
emp_first_nam varchar2(40),
emp_last_nam varchar2(40),
cksum number(20,0),
hierarchy_last_level_num number(2,0),
hierarchy_create_dt date,
hierarchy_last_update_dt date,
deleted_flg char(1) default 'n',
deleted_dt date
) pctfree 10 pctused 40 initrans 1 maxtrans 255 nocompress nologging
tablespace users

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.1.0 - 64bit Production
PL/SQL Release 9.0.1.1.0 - Production
CORE 9.0.1.1.0 Production
TNS for Solaris: Version 9.0.1.1.0 - Production
NLSRTL Version 9.0.1.1.0 - Production


Thanks for your consideration ....

ops$tkyte@ORA9IR1> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production
PL/SQL Release 9.0.1.0.0 - Production
CORE    9.0.1.0.0       Production
TNS for Linux: Version 9.0.1.0.0 - Production
NLSRTL Version 9.0.1.0.0 - Production
 
ops$tkyte@ORA9IR1> select
  2  emp_typ1.emp_sso_id,
  3  emp_typ1.first_nam,
  4  emp_typ1.last_nam,
  5  level,deleted_flg,deleted_dt,supervisor_sso_id
  6  from
  7   emp_typ1
  8   where emp_sso_id>0 connect by prior emp_sso_id = supervisor_sso_id
  9   start with emp_sso_id in
 10      (
 11      select a.emp_sso_id from emp_hier a,emp_typ1 b
 12      where a.tier2_mgr_sso_id=-2
 13      and a.emp_sso_id in(select supervisor_sso_id from emp_typ1)
 14      and a.emp_sso_id=b.emp_sso_id and b.supervisor_sso_id not in(select
 15  emp_sso_id from emp_typ1)
 16      and b.supervisor_sso_id!=-1
 17      )
 18  /
 
no rows selected


<b>it works fine for me, so show is an EXACT cut and paste (funny you say 9.0.1 but the DDL comes from 9.2.0 and above only...)</b>

 

Hi Tom,
VERSION : 8i

I have following table. one for city and another for travel information.
I want to select the information about my travel history.

query 1. If I give fromcity id it should show names of the cities i have travelled from the given id.( i assumed this as chilren)

query 2. Names of the cities visited before reach this city. ( i assumed this as fathers)



create table city
(
cityid number,
cityname varchar2(20)
)
/

create table travel
(
travelid number,
fromcity number,
tocity number
)
/

--- city table data

insert into city values(1,'city1')
/
insert into city values(2,'city2')
/
insert into city values(3,'city3')
/
insert into city values(4,'city4')
/
insert into city values(5,'city5')
/
insert into city values(6,'city6')
/
insert into city values(7,'city7')
/
insert into city values(8,'city8')
/
insert into city values(9,'city9')
/
insert into city values(10,'city10')
/

---- travel table data

insert into travel values(1,1,2)
/
insert into travel values(2,2,3)
/
insert into travel values(3,3,4)
/
insert into travel values(4,4,5)
/
insert into travel values(5,5,6)
/
insert into travel values(6,6,7)
/
insert into travel values(7,7,8)
/


select level,lpad('*',level,'*')||fromcity as fromcity,tocity from travel
connect by prior tocity=fromcity
start with
fromcity=1

level fromcity tocity
---- -------- ------
1 *1 2
2 **2 3
3 ***3 4
4 ****4 5
5 *****5 6
6 ******6 7
7 *******7 8

-----------------------

--- if my further travel will be

insert into travel values(8,8,5)
/
insert into travel values(9,5,8)
/
insert into travel values(10,8,10)
/
insert into travel values(11,10,1)
/

---- if i try to get the information

select level,lpad('*',level,'*')||fromcity as fromcity,tocity from travel
connect by prior tocity=fromcity
start with
fromcity=1

ERROR:
ORA-01436: CONNECT BY loop in user data


How i get the required information.
I don't have 9i, so i can't try sys_connect_by_path.
Kindly show me how to get it?

Hi Tom,
Thank you for your response.
I am still in dark room.
This design is not for flight trips. If this is flight trip then i can set this is the only way to reach. But this is for TRAIN or BUS trips. I can not say this is the route. Our sales man will reach one place to attend the call. He will dial from there for his next call ( some times he has plan trips). So he is free to go anywhere. But our city limited ( about 100).
He can take either bus or train as he desires( some place is not connected by train).
If this is my situation, how to design it. How to achive my goal.
Kindly advice me.

Hi Tom,

I have a table with hierarchical information like

Empid, Desig, MgriD

The lowest level employees(not manadatory though) would normally do some transactions, which will be stored in a table which would have structure like

(TransactionID(PK), Transaction_Amount, Transaction_Date, Empid).

I would like to generate a performance mis for a given date range , for each employee based on the transaction.

--A tranasaction made by employee reporting to a manager should also reflect in manager's performance mis.
--Hierarchy can change every now on then (people resign, get promoted etc).


The problem that I am facing as of now is how to store the hierarchy information with each transaction.

Employee A reporting to Manager M1 did 50 transactions from 01-OCt-05 to 10-OCt-05. From 11-OCt-05, he starts reporting to M2 and does 75 transactions from 11-OCt-05 to 20-Oct-05.

How to store the hierarchy information along with the transactions.
Some one suggested store the complete hierarchy string along with the transaction.

I personally think that we should create a separate table (TransactionID (FK referring to Transaction table's TransactionID) , Empid) and should store the hierarchy details as on date in this tables

i.e some thing like

TransactionID, EmpiD
10 A
10 M1
20 A
20 M2.


Considering the fact that transaction table is going to be big (may be a billion records in a year) and will be simultaneously hit by 200 employees, can we use nested tables here?
Is there any other alternative?

Thanks,

Hi Tom,

Following are my concerns pertaining to versioning the hierarchy (I scurried through the versioning examples, not sure whether my understanding is correct or not.

1. Performance reports are generated between a given date range. If I slightly change the example "Employee A reporting to Manager M1 did 50 transactions from 01-OCt-05 to
10-OCt-05. From 11-OCt-05 afternoon, he starts reporting to M2 and does 75 transactions
from 11-Oct-05 to 20-Oct-05". problem here is that EMP_HIST view will contain two rows for 11-OCt-05, one each for M1 and M2. How to distinguish between the transactions, which Employee did when he was reporting to M1 and subsequently when he started reporting to M2?

2. Even if we consider time also along with datepart, its quite possible that Retireddatetime of old record and createddatetime of new record are same.

3. If we version enable the Employee master, then we will have to version enable the Transaction table and many other tables as well, which may not be required.

Thanks,
Shachi

Thanks Tom.

I think Shachi has a point.
What if retired datetime record of where employee is reporting to M1 and created date time of record where employee is reporting to M2 is exactly same i.e 11-Oct-2005 10:10:50, and coincidentally a transaction is made by the employee A at the same very time. In this case, which manager(M1 or M2) will be credited with the transaction?

I know its a one in a million case, but still there is a possibility, isn't it?.

I see a lot of this rown() over syntax... wondering if it would boost my performance over the use of the connect by query as exampled below :

SELECT LEVEL
, e.person_id
, e.hisl_id
, e.title
, e.first_name
, e.middle_names
, e.last_name
, e.suffix
, e.supervisor_person_id
, e.supervisor_hisl_id
, e.supervisor_title
, e.supervisor_first
, e.supervisor_middle
, e.supervisor_last
, e.supervisor_suffix
FROM emp e
WHERE 1 = 1
AND EXISTS (
SELECT 'x'
FROM emp x
WHERE 1 = 1
AND UPPER (x.last_name) LIKE 'CHASE%'
AND x.person_id = e.person_id)
AND LEVEL >= 1
AND LEVEL <= 4
CONNECT BY PRIOR e.person_id = e.supervisor_person_id
START WITH e.person_id = 12073

At the highest level of the tree it takes about 20 seconds to retrieve a result with 283 records returning for the 4 levels and searching by name. If I search by number, it returns in about 5 seconds. I need to the name search to be around 5 seconds as well.

Does anyone have an idea on how to rewrite my above query so that it leverages the ROW_NUMBER () OVER ... syntax. The total records without level restriction is about 16k records. Additionally, I would like to be able to see the level of the individual as well as be able to restrict the level as needed.

I tried myself, and couldn't seem to get the jist of how it was supposed to work.


Thank you in advance.

well, think about it this way.... 

the connect by conceptually does this:

a) runs the "start with" to find the rows to start
b) does the connect by predidate to build the hierarchy, the ENTIRE hirearchy
c) runs the where clause against that.

Now, much of your where clause can be pushed into the connect by - the level part in paricular, you want levels 1..4 - sooooo don't build 5, 6, 7, 8, 9, 10,..... and so on.

Consider:

ops$tkyte@ORA9IR2> select level, rpad('*',2*level,'*')||ename name
  2    from emp
  3  start with mgr is null
  4  connect by prior empno = mgr;
 
     LEVEL NAME
---------- --------------------
         1 **KING
         2 ****JONES
         3 ******SCOTT
         4 ********ADAMS
         3 ******FORD
         4 ********SMITH
         2 ****BLAKE
         3 ******ALLEN
         3 ******WARD
         3 ******MARTIN
         3 ******TURNER
         3 ******JAMES
         2 ****CLARK
         3 ******MILLER
 
14 rows selected.
 
<b>let's say we wanted to get just 1 and 2 - and had and exists type of query (I'll use not exists for my own ease this time...)</b>

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace on statistics
ops$tkyte@ORA9IR2> select level, rpad('*',2*level,'*')||ename name
  2    from emp
  3   where not exists (select null from all_objects a where subobject_name = emp.ename )<b>
  4     and level >= 1
  5     and level <= 2</b>
  6   start with mgr is null
  7  connect by prior empno = mgr;
 
     LEVEL NAME
---------- --------------------
         1 **KING
         2 ****JONES
         2 ****BLAKE
         2 ****CLARK
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4373  consistent gets
          0  physical reads
          0  redo size
        510  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
          4  rows processed
 
ops$tkyte@ORA9IR2> select level, rpad('*',2*level,'*')||ename name
  2    from emp
  3   where not exists (select null from all_objects a where subobject_name = emp.ename )
  4   start with mgr is null
  5  connect by prior empno = mgr <b>AND level <= 2;</b>
 
     LEVEL NAME
---------- --------------------
         1 **KING
         2 ****JONES
         2 ****BLAKE
         2 ****CLARK
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1244  consistent gets
          0  physical reads
          0  redo size
        510  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          4  rows processed
 
ops$tkyte@ORA9IR2> set autotrace off



Your issue doesn't look like "something to be done with analytics" in as much as "push what you can into the connect by!"


This looks suspicious:

       AND EXISTS (
              SELECT 'x'
                FROM emp x
               WHERE 1 = 1
                 AND UPPER (x.last_name) LIKE 'CHASE%'
                 AND x.person_id = e.person_id)

if person_id is the primary key (as one would assume...) why not just

and emp.last_name like 'CHASE%'

what is the point of the exists subquery at all? 

Person_id is not a primary key. Its a non-unique index but is unique to the individual. Users want to be able to search by name however. Numbers have always worked faster for me so I translate back to the numbers. It improved things by 75% when I did that.

let me try the push to the connect by method though.

Did just that. Only thing that did not work was pushing my name search ... but it appears to be working when I leave that in the main WHERE conditions.

Performance was radically different. Went from 20 seconds down to milliseconds. Very very impressed.

Thank you very much.

Hi Tom,

My requirement is to build a hierarchy for all employees in a horizontal tree fashion i.e. to say that for every employee build all his direct and indirect reports where each level will be a column in a table. SO there will be columns L0, L1, L2, L3, L4 ..... and so on.

Then bring in each employee into L0 ( just a left shift of the levels from the above structure).

The final step is to have L0, L1, L2 and then rollup all levels from L2 down into L2.

Ex:
l1 L2 L3 L4
e1 e2 e3
e1 e2 e4 e5
e1 e6

And then have
l0 l1 l2 l3
e1 e2 e3
e2 e3
e2 e4 e5
e6
e2 e3
e3
e4 e5
e5

And finally,
e1 e2 e3
e1 e2 e4
e1 e2 e5
e1 e6
How can this be achieved? Can this be done using connect by?
Or a procedure would be better? There may be upto 20 levels in the hierarchy
Please advise
Thanks

Tom,
Please can you answer my question above?

Thanks,

Hi Tom,
How to put this query in Oracle 8i or lower versions??

select sys_connect_by_path(ename,'/') as path
from emp
start with mgr is null
connect by prior empno = mgr

Hi Tom,

I've a similar requirement as that of Shachi
---------------------------------
"Performance reports are generated between a given date range. If I slightly
change the example "Employee A reporting to Manager M1 did 50 transactions from
01-OCt-05 to
10-OCt-05. From 11-OCt-05 afternoon, he starts reporting to M2 and does 75
transactions
from 11-Oct-05 to 20-Oct-05". problem here is that EMP_HIST view will contain
two rows for 11-OCt-05, one each for M1 and M2. How to distinguish between the
transactions, which Employee did when he was reporting to M1 and subsequently
when he started reporting to M2?"
-------------------
Could you pls provide an example of the query..i.e lets say we have to show count of transactions done for each M1 and M2 from 01-Oct to 30-Oct. I am not able to form the query using emp_hist view

Yes, I am using workspace manager.

Thanks

Thanks Tom, but I guess it is slightly more complicated.
I want the values across a range. i.e transaction done by a manager (transactions done by child units get added to managers list) between 01October to 30October.

NOw using dbms_wm.gotodate means using it for each date between 01Oct to 30Oct.
How can I achieve it in a SQL?

Hi Tom,

I have an interesting Requirement,
Let us consider the employee hierarchy

SQL> select level,
2 rpad( '*', 2*level, '*' )|| name name
3 from my_emp
4 start with name = 'KING'
5 connect by prior emp_id = mgr_id
6 ;

LEVEL NAME
---------- --------------------------------------------------------------------------------
1 **KING
2 ****SMITH
3 ******HOBBS
4 ********SMART
3 ******BLACK
2 ****JONES
3 ******WHITE
3 ******MARSH
3 ******CHANG
2 ****BALL
3 ******STEWART
4 ********BELL
4 ********HARRIS
3 ******DAILY
3 ******GREEN

Now, Let us say some managers are terminated but the manager (mgr) information is not updated for the employees under these terminated managers.


How to build a hierarchy which assumes that employees who have terminated managers are assigned to the non terminated manager navigating top in the hierarchy?

So If `STEWART¿, `BELL¿, `HOBBS¿ and `SMITH¿ are terminated the hierarchy would look like

LEVEL NAME
---------- --------------------------------------------------------------------------------
1 **KING
2 ****SMART
2 ****BLACK
2 ****JONES
3 ******WHITE
3 ******MARSH
3 ******CHANG
2 ****BALL
3 ******HARRIS
3 ******DAILY
3 ******GREEN



The inherent question in this is How do I navigate the tree from bottom to top and find the manager who is not terminated for an employee.

SET autotrace off
SET pagesize 40

drop table my_emp;

create table my_emp
(
emp_id varchar2(2),
name varchar2(15),
mgr_id varchar2(2),
terminated char(1)
);


insert into my_emp (emp_id,name,mgr_id,terminated) values ('1','KING',' ','N');
insert into my_emp (emp_id,name,mgr_id,terminated) values ('2','SMITH','1','N');
insert into my_emp (emp_id,name,mgr_id,terminated) values ('3','JONES','1','N');
insert into my_emp (emp_id,name,mgr_id,terminated) values ('4','BALL','1','N');
insert into my_emp (emp_id,name,mgr_id,terminated) values ('5','HOBBS','2','N');
insert into my_emp (emp_id,name,mgr_id,terminated) values ('15','SMART','5','N');
insert into my_emp (emp_id,name,mgr_id,terminated) values ('6','BLACK','2','N');
insert into my_emp (emp_id,name,mgr_id,terminated) values ('7','WHITE','3','N');
insert into my_emp (emp_id,name,mgr_id,terminated) values ('8','MARSH','3','N');
insert into my_emp (emp_id,name,mgr_id,terminated) values ('9','CHANG','3','N');
insert into my_emp (emp_id,name,mgr_id,terminated) values ('10','STEWART','4','N');
insert into my_emp (emp_id,name,mgr_id,terminated) values ('11','DAILY','4','N');
insert into my_emp (emp_id,name,mgr_id,terminated) values ('12','GREEN','4','N');
insert into my_emp (emp_id,name,mgr_id,terminated) values ('13','BELL','10','N');
insert into my_emp (emp_id,name,mgr_id,terminated) values ('14','HARRIS','10','N');


commit;


update my_emp set terminated = 'Y' where name in ('STEWART', 'BELL','HOBBS','SMITH');

commit;
/


Your help is highly appreciated.

Thank you,
Balu.

Thanks for your response.

I guess I choose wrong set for example.

Since I have to explain my whole problem, I am posting two questions in this review. Please excuse me if I am asking too many in one review.

My requirement was not for actual emp table, instead it is a event handler.

APEX version : 2.0 apex db : 9.2.0.7.0
Date DB version: 9.2.0.7.0


Basically we have Events and Sub-events (An Event can have n number of sub-events). Sub-Events are related to each other same as emp and manager relationship (two columns, subevent_id{emp_id} and prev_subevent_id{mgr_id} ). i.e sub-event can have a previous sub-event, If a sub-event has no prev sub-event then they are related directly to the Event.
SET autotrace off
SET pagesize 40

drop table subevents;

create table subevents
(
subevent_id varchar2(2),
name varchar2(15),
prev_subevent_id varchar2(2),
inactive_dt date
);


insert into subevents (subevent_id,name,prev_subevent_id,inactive_dt) values ('1','KING',null,null);
insert into subevents (subevent_id,name,prev_subevent_id,inactive_dt) values ('2','SMITH','1',null);
insert into subevents (subevent_id,name,prev_subevent_id,inactive_dt) values ('3','JONES','1',null);
insert into subevents (subevent_id,name,prev_subevent_id,inactive_dt) values ('4','BALL','1',null);
insert into subevents (subevent_id,name,prev_subevent_id,inactive_dt) values ('5','HOBBS','2',null);
insert into subevents (subevent_id,name,prev_subevent_id,inactive_dt) values ('15','SMART','5',null);
insert into subevents (subevent_id,name,prev_subevent_id,inactive_dt) values ('6','BLACK','2',null);
insert into subevents (subevent_id,name,prev_subevent_id,inactive_dt) values ('7','WHITE','3',null);
insert into subevents (subevent_id,name,prev_subevent_id,inactive_dt) values ('8','MARSH','3',null);
insert into subevents (subevent_id,name,prev_subevent_id,inactive_dt) values ('9','CHANG','3',null);
insert into subevents (subevent_id,name,prev_subevent_id,inactive_dt) values ('10','STEWART','4',null);
insert into subevents (subevent_id,name,prev_subevent_id,inactive_dt) values ('11','DAILY','4',null);
insert into subevents (subevent_id,name,prev_subevent_id,inactive_dt) values ('12','GREEN','4',null);
insert into subevents (subevent_id,name,prev_subevent_id,inactive_dt) values ('13','BELL','10',null);
insert into subevents (subevent_id,name,prev_subevent_id,inactive_dt) values ('14','HARRIS','10',null);


commit;


select level , rpad( '*', 2*(level-1), '*' )|| name name from subevents
start with prev_subevent_id is null
connect by prior subevent_id = prev_subevent_id

LEVEL NAME
---------- ----------
1 KING
2 **SMITH
3 ****HOBBS
4 ******SMART
3 ****BLACK
2 **JONES
3 ****WHITE
3 ****MARSH
3 ****CHANG
2 **BALL
3 ****STEWART
4 ******BELL
4 ******HARRIS
3 ****DAILY
3 ****GREEN

Users can inactivate a Sub-event by setting inactive date (But they can always reactivate this sub-event if they want to).

update subevents set inactive_dt = trunc(sysdate) where name in ('STEWART', 'BELL','HOBBS', 'SMITH');

commit;
/

SQL> select * from subevents;

SUBEVENT_ID NAME PREV_SUBEVENT_ID INACTIVE_DT
----------- --------------- ---------------- -----------
1 KING
2 SMITH 1 01/12/2007
3 JONES 1
4 BALL 1
5 HOBBS 2 01/12/2007
15 SMART 5
6 BLACK 2
7 WHITE 3
8 MARSH 3
9 CHANG 3
10 STEWART 4 01/12/2007
11 DAILY 4
12 GREEN 4
13 BELL 10 01/12/2007
14 HARRIS 10

15 rows selected

Because this application is still in the design phase, I have two options to choose from
1) If a sub-event is inactive, inactivate all sub-events that are below this sub-event in the hierarchy.

select level , rpad( '*', 2*(level-1), '*' )|| name name from subevents
start with prev_subevent_id is null
connect by prior subevent_id = prev_subevent_id and (inactive_dt > sysdate or inactive_dt is null)

LEVEL NAME
---------- ------------------
1 KING
2 **JONES
3 ****WHITE
3 ****MARSH
3 ****CHANG
2 **BALL
3 ****DAILY
3 ****GREEN
So. I pull only sub-events active according to this above option.
Now, the problem is it returns no data found when I run this query in APEX (workshop and application).

APEX version: 2.0, apex DB: 9.2.0.7.0
Data DB version: 9.2.0.7.0

So Apex is hosted on different database than the one that holds application tables. Views are created in APEX workspace schema over dblink.
What I think the problem either APEX or dblink is acting weird when ever the sql gets complex like conditions in connect by clause,

Since the tree cannot be built in APEX using the criteria shown
So I am considering this second option

2) They inactivate subevents they want and I will automatically adjust the tree without inactivating the sub-events as discussed in (1).

Now, can we get the hierarchy like the one I showed below where it links the sub-Events that have inactive prev_sub-event_id to the active sub-event navigating from bottom to top in the hierarchy and if no active sub-event is found then this sub-event would link to the event (prev_subevent_id --> null)

LEVEL NAME
---------- -----------
1 KING
2 **SMART
2 **BLACK
2 **JONES
3 ****WHITE
3 ****MARSH
3 ****CHANG
2 **BALL
3 ****HARRIS
3 ****DAILY
3 ****GREEN

I would highly appreciate if you could solve problems in my option (1) (maybe an alternative query to achieve the same) which would work in APEX or option (2).

Thank you,
Balu.

when I Looked into problem in option2 more closely
I see that my queries are acting differently over dblink
and think that the problem I faced due to outer joins and not because of connect by

Let us consider this example

I have a table "subevents" which link subevents to event by relation 'Child'

case 1: relates subevents to subevents by relation 'Prev Event' to form a heirarchy.

SET autotrace off
SET pagesize 40

drop table subevents;

create table subevents
(
event_id number(4),
name varchar2(15),
member_id number(4),
relation_cd VArchar2(10),
inactive_dt date
);


insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (141,'KING',1,'Child',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (141,'SMITH',2,'Child',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (141,'JONES',3,'Child',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (141,'BALL',4,'Child',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (141,'HOBBS',5,'Child',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (141,'SMART',15,'Child',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (141,'BLACK',6,'Child',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (141,'WHITE',7,'Child',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (141,'MARSH',8,'Child',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (141,'CHANG',9,'Child',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (141,'STEWART',10,'Child',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (141,'DAILY',11,'Child',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (141,'GREEN',12,'Child',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (141,'BELL',13,'Child',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (141,'HARRIS',14,'Child',null);


insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (2,'SMITH',1,'Prev Event',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (3,'JONES',1,'Prev Event',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (4,'BALL',1,'Prev Event',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (5,'HOBBS',2,'Prev Event',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (15,'SMART',5,'Prev Event',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (6,'BLACK',2,'Prev Event',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (7,'WHITE',3,'Prev Event',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (8,'MARSH',3,'Prev Event',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (9,'CHANG',3,'Prev Event',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (10,'STEWART',4,'Prev Event',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (11,'DAILY',4,'Prev Event',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (12,'GREEN',4,'Prev Event',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (13,'BELL',10,'Prev Event',null);
insert into subevents (event_id,name,member_id,relation_cd,inactive_dt) values (14,'HARRIS',10,'Prev Event',null);


commit;


update subevents set inactive_dt = trunc(sysdate) where name in ('STEWART', 'BELL','HOBBS', 'SMITH') and relation_cd = 'Child';

commit;

NOTE: name field doesnt have a meaning when relation_cd not 'Child'


from sqlplus connect to the same database

SELECT LEVEL, rpad('*', 2 * (LEVEL - 1), '*') || NAME NAME
FROM (SELECT s1.inactive_dt, s1.member_id event_id, s2.member_id prev_event, s1.NAME
FROM subevents s1, subevents s2
WHERE s1.member_id = s2.event_id(+)
AND s2.relation_cd(+) = 'Prev Event'
AND s1.relation_cd = 'Child') subevents
START WITH prev_event IS NULL
CONNECT BY PRIOR event_id = prev_event
AND (inactive_dt > SYSDATE OR inactive_dt IS NULL)

LEVEL NAME
---------- ---------
1 KING
2 **JONES
3 ****WHITE
3 ****MARSH
3 ****CHANG
2 **BALL
3 ****DAILY
3 ****GREEN


from sqlplus connect to a different database and over dblink

SELECT LEVEL, rpad('*', 2 * (LEVEL - 1), '*') || NAME NAME
FROM (SELECT s1.inactive_dt, s1.member_id event_id, s2.member_id prev_event, s1.NAME
FROM subevents@dev s1, subevents@dev s2
WHERE s1.member_id = s2.event_id(+)
AND s2.relation_cd(+) = 'Prev Event'
AND s1.relation_cd = 'Child') subevents
START WITH prev_event IS NULL
CONNECT BY PRIOR event_id = prev_event
AND (inactive_dt > SYSDATE OR inactive_dt IS NULL)

LEVEL NAME
---------- -----------
1 KING
2 **JONES
3 ****WHITE
3 ****MARSH
3 ****CHANG
2 **BALL
3 ****DAILY
3 ****GREEN

Works like a charm and no problem



case 2: relates subevents to subevents by relation 'Next Event' to form a heirarchy.
basically reversing the data in columns event_id and member_id from the case 1.



SET autotrace off
SET pagesize 40

drop table subevents_2;

create table subevents_2
(
event_id number(4),
name varchar2(15),
member_id number(4),
relation_cd VArchar2(10),
inactive_dt date
);


insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (141,'KING',1,'Child',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (141,'SMITH',2,'Child',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (141,'JONES',3,'Child',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (141,'BALL',4,'Child',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (141,'HOBBS',5,'Child',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (141,'SMART',15,'Child',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (141,'BLACK',6,'Child',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (141,'WHITE',7,'Child',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (141,'MARSH',8,'Child',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (141,'CHANG',9,'Child',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (141,'STEWART',10,'Child',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (141,'DAILY',11,'Child',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (141,'GREEN',12,'Child',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (141,'BELL',13,'Child',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (141,'HARRIS',14,'Child',null);


insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (1,'SMITH',2,'Next Event',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (1,'JONES',3,'Next Event',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (1,'BALL',4,'Next Event',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (2,'HOBBS',5,'Next Event',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (5,'SMART',15,'Next Event',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (2,'BLACK',6,'Next Event',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (3,'WHITE',7,'Next Event',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (3,'MARSH',8,'Next Event',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (3,'CHANG',9,'Next Event',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (4,'STEWART',10,'Next Event',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (4,'DAILY',11,'Next Event',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (4,'GREEN',12,'Next Event',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (10,'BELL',13,'Next Event',null);
insert into subevents_2 (event_id,name,member_id,relation_cd,inactive_dt) values (10,'HARRIS',14,'Next Event',null);


commit;


update subevents_2 set inactive_dt = trunc(sysdate) where name in ('STEWART', 'BELL','HOBBS', 'SMITH') and relation_cd = 'Child';

commit;


NOTE: name field doesnt have a meaning when relation_cd not 'Child'


from sqlplus connect to the same database

SQL> SELECT LEVEL, rpad('*', 2 * (LEVEL - 1), '*') || NAME NAME
2 FROM (SELECT s1.inactive_dt, s1.member_id event_id, s2.event_id prev_event, s1.NAME
3 FROM subevents_2 s1, subevents_2 s2
4 WHERE s1.member_id = s2.member_id(+)
5 AND s2.relation_cd(+) = 'Next Event'
6 AND s1.relation_cd = 'Child') subevents
7 START WITH prev_event IS NULL
8 CONNECT BY PRIOR event_id = prev_event
9 AND (inactive_dt > SYSDATE OR inactive_dt IS NULL)
10 ;

LEVEL NAME
---------- --------------------------------------------------------------------------------
1 KING
2 **JONES
3 ****WHITE
3 ****MARSH
3 ****CHANG
2 **BALL
3 ****DAILY
3 ****GREEN

8 rows selected

from sqlplus connect to a different database and over dblink

SQL> SELECT LEVEL, rpad('*', 2 * (LEVEL - 1), '*') || NAME NAME
2 FROM (SELECT s1.inactive_dt,
3 s1.member_id event_id,
4 s2.event_id prev_event,
5 s1.NAME
6 FROM subevents_2@dev s1,
7 subevents_2@dev s2
8 WHERE s1.member_id = s2.member_id(+)
9 AND s2.relation_cd(+) = 'Next Event'
10 AND s1.relation_cd = 'Child') subevents
11 START WITH prev_event IS NULL
12 CONNECT BY PRIOR event_id = prev_event
13 AND (inactive_dt > SYSDATE OR inactive_dt IS NULL)
14 ;

LEVEL NAME
---------- -----------


Why is the same query run though dblink and directly gives two different results??

why does the change in implementation like this is making difference over dblink? is anything wrong in the case 2??

Thanks is advance

Thank you,
Balu.

Sorry missed the DB versions.

SQL> select version from v$instance;

VERSION
-----------------
9.2.0.7.0


For both remote and current databases.


"when I Looked into problem in option2 more closely "
sorry, it is actually option 1 from the previous post.

Thank you
-Balu.

Some great use of SQL here - I like the epath particularly.

I have looked at extending this functionality for a HTML tree application, where elements are selected and pages expand and contract.

The rules for which nodes are displayed are simple (in theory) - for the selected nodes, show all parents/aunts/grandparents/grandaunts .. but no cousins!

As I say simple analogy, and theory - but trying to produce the SQL from a selected node is proving quite tricky. Are there any good examples where we can start at the bottom of a structure and walk up it - but include peers (aunts + uncles) at the same time?

Maybe analytics to get the grandparent and walking down from there each time we move up?

is its possible to add new row having sub total for each level, for examble the total of salaries for each level?

is its possible to add new row having sub total for each level, for examble the total of salaries
for each level?
please can you help? its urgent.

iam sorry as my message was not cleare, i will explane by example,
suppose we have a multi division levels company, starting from head under it we have divisions, under divisions we have sup divisions, under sub divisions we have departments

head
------------------------------------------------
| | | |
division1 division2 division3 division4
|
--------------------------------
| |
sub-subdivision1 sub-subdivision2
|
---------------
| |
dept1 dept2

i need to generate a report to view total salories for each individual department. and sup totals for the salaries for each sub-subdivisions and subtotals for each division.
tree query can do that but what i need is to represent thos totals in separate rows like this

head
division1
supdivision1
dept1 100
dept2 200
total of salaris in sudivison1 300
subdivision2
dept1 100
dept2 200
total of salaris in sudivison2 300

total sal. in divison1 600

ets...

so is it possible to have new row in side the tree to present the totals you see in my example?

  CREATE TABLE "SCOTT"."EMP_DEPARTMENT" 
   ( "DEPT_NO" NUMBER(6,0), 
 "DEPT_NAME" VARCHAR2(200 BYTE), 
 "DEPT_PARENT" NUMBER(6,0)
   );
-----------------
CREATE TABLE "SCOTT"."EMP_INFO" 
   ( "EMP_NO" NUMBER(6,0), 
 "EMP_NAME" VARCHAR2(60 BYTE), 
 "EMP_DEPT" NUMBER(6,0), 
 "EMP_SALE" NUMBER(6,0)
   );
--------------------
-- INSERTING into EMP_DEPARTMENT
Insert into EMP_DEPARTMENT (DEPT_NO,DEPT_NAME,DEPT_PARENT) values (1,'Head',null);
Insert into EMP_DEPARTMENT (DEPT_NO,DEPT_NAME,DEPT_PARENT) values (12,'HR',1);
Insert into EMP_DEPARTMENT (DEPT_NO,DEPT_NAME,DEPT_PARENT) values (13,'ACC',1);
Insert into EMP_DEPARTMENT (DEPT_NO,DEPT_NAME,DEPT_PARENT) values (14,'sec',1);
Insert into EMP_DEPARTMENT (DEPT_NO,DEPT_NAME,DEPT_PARENT) values (15,'fin',1);
Insert into EMP_DEPARTMENT (DEPT_NO,DEPT_NAME,DEPT_PARENT) values (2,'Jedda',null);
Insert into EMP_DEPARTMENT (DEPT_NO,DEPT_NAME,DEPT_PARENT) values (21,'PRANCH1',2);
Insert into EMP_DEPARTMENT (DEPT_NO,DEPT_NAME,DEPT_PARENT) values (22,'PRANCH2',2);
Insert into EMP_DEPARTMENT (DEPT_NO,DEPT_NAME,DEPT_PARENT) values (24,'PRANCH4',2);
Insert into EMP_DEPARTMENT (DEPT_NO,DEPT_NAME,DEPT_PARENT) values (25,'PRANCH5',2);
Insert into EMP_DEPARTMENT (DEPT_NO,DEPT_NAME,DEPT_PARENT) values (241,'PRANCH4_H',24);
Insert into EMP_DEPARTMENT (DEPT_NO,DEPT_NAME,DEPT_PARENT) values (242,'PRANCH4_D',24);
Insert into EMP_DEPARTMENT (DEPT_NO,DEPT_NAME,DEPT_PARENT) values (221,'PRANCH2_H',22);

--------------------
-- INSERTING into EMP_INFO
Insert into EMP_INFO (EMP_NO,EMP_NAME,EMP_DEPT,EMP_SALE) values (20,'SCOTT',13,250);
Insert into EMP_INFO (EMP_NO,EMP_NAME,EMP_DEPT,EMP_SALE) values (30,'TIGER1',221,2652);
Insert into EMP_INFO (EMP_NO,EMP_NAME,EMP_DEPT,EMP_SALE) values (40,'FORD',25,2525);
Insert into EMP_INFO (EMP_NO,EMP_NAME,EMP_DEPT,EMP_SALE) values (50,'SAHE',241,555);
Insert into EMP_INFO (EMP_NO,EMP_NAME,EMP_DEPT,EMP_SALE) values (10,'ALI',12,10000);
--------------------
 SELECT LPAD(' ',   2 *(LEVEL -1)) || TO_CHAR(DEPT_NAME)  DEPT_NAME,
 dept_no,dept_parent
 FROM  EMP_DEPARTMENT
 START WITH DEPT_PARENT IS NULL
 CONNECT BY PRIOR DEPT_NO = DEPT_PARENT;
-----
DEPT_NAME                                   DEPT_NO DEPT_PARENT
---------------------------------------- ---------- -----------
Head                                              1
  HR                                             12           1
  ACC                                            13           1
  sec                                            14           1
  fin                                            15           1
Jedda                                             2
  PRANCH1                                        21           2
  PRANCH2                                        22           2
    PRANCH2_H                                   221          22
  PRANCH4                                        24           2
    PRANCH4_H                                   241          24
    PRANCH4_D                                   242          24
  PRANCH5                                        25           2

-------------------------------------------------------------------
create or replace function get_tot_sal (vn_dept_no in number) return number is
cursor c_get_total is
select sum(emp_salE)
from   EMP_INFO
where  EMP_DEPT = VN_DEPT_NO;
VN_TOTAL NUMBER(6);
BEGIN
 OPEN C_GET_TOTAL;
 FETCH C_GET_TOTAL
 INTO VN_TOTAL;
 CLOSE C_GET_TOTAL;
RETURN VN_TOTAL;
END;
----------------------------------------

  1   SELECT LPAD(' ',   2 *(LEVEL -1)) || TO_CHAR(DEPT_NAME)  DEPT_NAME,
  2    SUM(GET_TOT_SAL(EMP_DEPARTMENT.DEPT_NO))
  3   FROM  EMP_DEPARTMENT
  4   START WITH DEPT_PARENT IS NULL
  5   CONNECT BY PRIOR DEPT_NO = DEPT_PARENT
  6*  GROUP BY ROLLUP (LPAD(' ',   2 *(LEVEL -1)) || TO_CHAR(DEPT_NAME))
SQL> /

DEPT_NAME                                SUM(GET_TOT_SAL(EMP_DEPARTMENT.DEPT_NO))
---------------------------------------- ----------------------------------------
    PRANCH2_H                                                                2652
    PRANCH4_D
    PRANCH4_H                                                                 555
  ACC                                                                         250
  HR                                                                        10000
  PRANCH1
  PRANCH2
  PRANCH4
  PRANCH5                                                                    2525
  fin
  sec

DEPT_NAME                                SUM(GET_TOT_SAL(EMP_DEPARTMENT.DEPT_NO))
---------------------------------------- ----------------------------------------
Head
Jedda
                                                                            15982
-----------------------------------------
  1   SELECT LPAD(' ',   2 *(LEVEL -1)) || TO_CHAR(DEPT_NAME)  DEPT_NAME,
  2    SUM(GET_TOT_SAL(EMP_DEPARTMENT.DEPT_NO))
  3   FROM  EMP_DEPARTMENT
  4   START WITH DEPT_PARENT IS NULL
  5   CONNECT BY PRIOR DEPT_NO = DEPT_PARENT
  6*  GROUP BY CUBE (LPAD(' ',   2 *(LEVEL -1)) || TO_CHAR(DEPT_NAME))
SQL> /

DEPT_NAME                                SUM(GET_TOT_SAL(EMP_DEPARTMENT.DEPT_NO))
---------------------------------------- ----------------------------------------
                                                                            15982
  HR                                                                        10000
Head
  ACC                                                                         250
  fin
  sec
Jedda
  PRANCH1
  PRANCH2
  PRANCH4
  PRANCH5                                                                    2525

DEPT_NAME                                SUM(GET_TOT_SAL(EMP_DEPARTMENT.DEPT_NO))
---------------------------------------- ----------------------------------------
    PRANCH2_H                                                                2652
    PRANCH4_D
    PRANCH4_H                                                                 555

14 rows selected.
---------------------------------------
STILL I COULD NOT HAVE WHAT I NEED. TO HAVE NEW ROW OF TOTAL (WITH LABEL) INDICATED EACH LEVEL TOTAL. I NEED THE RESULTS LIKE THIS


DEPT_NAME                                GET_TOT_SAL(DEPT_NO)   
---------------------------------------- -------------------- -
Head                                                            
  HR                                                    10000   
  ACC                                                     250   
  sec                                                           
  fin                                                           
  TOTL HEAD                                             10250
Jedda                                                           
  PRANCH1                                                       
  PRANCH2                                                       
    PRANCH2_H                                            2652
    TOTAL BRANCH2                                        2652   
  PRANCH4                                                       
    PRANCH4_H                                             555   
    PRANCH4_D                                                   
    TOTAL PRACH4                                          555
  PRANCH5                                                2525
  TOTAL BRANCH5                                          2525   
TOTAL JEDDA                                              5177 

ops$tkyte%ORA11GR1> SELECT LPAD(' ',   2 *(LEVEL -1)) || DEPT_NAME  DEPT_NAME,
  2             dept_no,
  3             dept_parent,
  4         (select sum(emp_sale)
  5                from emp_info
  6                   where emp_dept in (select dept_no
  7                                from emp_department e2
  8                               start with e2.rowid = e1.rowid
  9                             connect by prior dept_no = dept_parent)
 10         ) sum_sale
 11   FROM  EMP_DEPARTMENT e1
 12  START WITH DEPT_PARENT IS NULL
 13  CONNECT BY PRIOR DEPT_NO = DEPT_PARENT
 14  /

DEPT_NAME               DEPT_NO DEPT_PARENT   SUM_SALE
-------------------- ---------- ----------- ----------
Head                          1                  10250
  HR                         12           1      10000
  ACC                        13           1        250
  sec                        14           1
  fin                        15           1
Jedda                         2                   5732
  PRANCH1                    21           2
  PRANCH2                    22           2       2652
    PRANCH2_H               221          22       2652
  PRANCH4                    24           2        555
    PRANCH4_H               241          24        555
    PRANCH4_D               242          24
  PRANCH5                    25           2       2525

13 rows selected.

Tom, I have a list of records which are related [logically] by their Code values but there is no column that shows the relationship. Now I am planning to create one. But I don't have what it takes to create a Connect by [parent column].
Can you help me get the query i need?

Logic:
First 2 letters of the CODE shows a group / higher level code [33 in 33-0000 or 35 in 35-0000]
1st letter after hyphen [-] indicates this code is the child for the above group.
eg:- 33-1000 is child of 33-0000 code.
33-2000 is child of 33-0000 code. [this code is not in the sample below]
3rd letter after hyphen [-] indicates this code is child for the above group
eg:- 33-1010 is child of 33-1000 code
33-1011 is child of 33-1000 code
33-1012 is child of 33-1000 code


Now I want to create a third column in my table and indicate the parent_code in it.

here is what I have:
Code Description
33-0000 Some description
33-1000 Some description
33-1010 Some description

35-0000 Some description
35-1010 Some description
35-1011 Some description

What I am wanting:
Code Description Parent_code
33-0000 Some description
33-1000 Some description 33-0000
33-1010 Some description 33-1000

35-0000 Some description
35-1010 Some description 35-0000
35-1011 Some description 35-1000


Here are the table creation scripts:

CREATE TABLE MYTEST_CODES
(
CODE VARCHAR2(10 BYTE) NOT NULL,
DESCRIPTION VARCHAR2(66 BYTE)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;



SET DEFINE OFF;
Insert into MYTEST_CODES
(CODE, DESCRIPTION)
Values
('33-0000', 'description for this code 1');
Insert into MYTEST_CODES
(CODE, DESCRIPTION)
Values
('33-1000', 'description for this code 2');
Insert into MYTEST_CODES
(CODE, DESCRIPTION)
Values
('33-1010', 'description for this code 3');
Insert into MYTEST_CODES
(CODE, DESCRIPTION)
Values
('33-1011', 'description for this code 4');
Insert into MYTEST_CODES
(CODE, DESCRIPTION)
Values
('33-1012', 'description for this code 5');
Insert into MYTEST_CODES
(CODE, DESCRIPTION)
Values
('35-0000', 'description for this another code 1');
Insert into MYTEST_CODES
(CODE, DESCRIPTION)
Values
('35-1000', 'description for this another code 2');
Insert into MYTEST_CODES
(CODE, DESCRIPTION)
Values
('35-1010', 'description for this another code 3');
Insert into MYTEST_CODES
(CODE, DESCRIPTION)
Values
('35-1011', 'description for this another code 4');
Insert into MYTEST_CODES
(CODE, DESCRIPTION)
Values
('35-1012', 'description for this another code 5');
COMMIT;


Can this be done? if so, how should I approach this?
Thanks,

ops$tkyte%ORA10GR2> select code,
  2         case when to_number(substr(code,4)) = 0 then null
  3              when to_number(substr(code,6)) = 0 then substr(code,1,2) || '-0000'
  4              else substr( code, 1, 5 ) || '00'
  5          end parent_code
  6    from mytest_codes
  7  /

CODE       PARENT_
---------- -------
33-0000
33-1000    33-0000
33-1010    33-1000
33-1011    33-1000
33-1012    33-1000
35-0000
35-1000    35-0000
35-1010    35-1000
35-1011    35-1000
35-1012    35-1000

10 rows selected.

Hi Tom,

We have a requirement similar to this.
Actually we have a hierarchy table like emp, and we need to update a column in this table based on the levels in the ascending order, i.e.., we need to update the level 1 nodes first..then level 2 nodes..then level 3 nodes and so on...

I am able to do it using pl/sql for-loop and update the table level by level. Just wondering if we can achieve this in a single sql instead of plsql for loop.

Could you please comment on this.

I tried the following,

 alter table emp add (seq integer);
  drop sequence emp_seq;
  create sequence emp_seq;
  
  update emp a
    set  a.seq = emp_seq.nextval
  where exists (select 1
                  from (
                    select empno,mgr,level lvl
                      from emp
                      start with mgr is null
                    connect by prior empno = mgr
                  ) q
                  where q.empno = a.empno
        );

but it is updating based on the order of rows in the table emp..

SQL> select empno,mgr,level lvl,seq
  2                        from emp
  3                        start with mgr is null
  4                      connect by prior empno = mgr;

     EMPNO        MGR        LVL        SEQ
---------- ---------- ---------- ----------
      7839                     1          9
      7566       7839          2          4
      7788       7566          3          8
      7876       7788          4         11
      7902       7566          3         13
      7369       7902          4          1
      7698       7839          2          6
      7499       7698          3          2
      7521       7698          3          3
      7654       7698          3          5
      7844       7698          3         10
      7900       7698          3         12
      7782       7839          2          7
      7934       7782          3         14

14 rows selected.

Thanks
Krish