heirarchy relationship
arunima, February 01, 2002 - 10:51 am UTC
Tom,
If the table looks like this then how will we do it. If in the hierarchy 'CLERK' has a parent as 'ANALYST' then
result should display 'ANALYST' or else 'MANAGER'.
Since ANALSYT or MANAGER is not a immediate parent of 'CLERK' I was wondering how to do that.
PADDED_NAME JOB LEVEL
-------------------- --------- ----------
KING PRESIDENT 1
JONES MANAGER 2
SCOTT ANALYST 3
ADAMS1 SALESMAN 4
ADAMS CLERK 5
FORD ANALYST 3
SMITH1 SALESMAN 4
SMITH CLERK 5
BLAKE MANAGER 2
ALLEN SALESMAN 3
WARD SALESMAN 3
MARTIN SALESMAN 3
TURNER SALESMAN 3
JAMES CLERK 4
CLARK MANAGER 2
MILLER1 SALESMAN 3
MILLER CLERK 4
Thanks for your time.
February 01, 2002 - 1:00 pm UTC
you are not making sense to me. show an example of what you expect like you did above (and I then supplied the answer for)
heirarchy relationship
arunima, February 01, 2002 - 10:52 am UTC
Tom,
If the table looks like this then how will we do it. If in the hierarchy 'CLERK' has a parent as 'ANALYST' then
result should display 'ANALYST' or else 'MANAGER'.
Since ANALSYT or MANAGER is not a immediate parent of 'CLERK' I was wondering how to do that.
PADDED_NAME JOB LEVEL
-------------------- --------- ----------
KING PRESIDENT 1
JONES MANAGER 2
SCOTT ANALYST 3
ADAMS1 SALESMAN 4
ADAMS CLERK 5
FORD ANALYST 3
SMITH1 SALESMAN 4
SMITH CLERK 5
BLAKE MANAGER 2
ALLEN SALESMAN 3
WARD SALESMAN 3
MARTIN SALESMAN 3
TURNER SALESMAN 3
JAMES CLERK 4
CLARK MANAGER 2
MILLER1 SALESMAN 3
MILLER CLERK 4
Thanks for your time.
sql for heirarchical relationship
arunima, February 01, 2002 - 1:44 pm UTC
Sorry Tom, If I was not detailed enough with my question. In this table structure mentioned below I need to know the parents of CLERKS.
In the heirarchy of each CLERK if there is an ANALYST then my result should display the ANALYST. If in the heirarchy of CLERK there are no ANALYST then the result should be MANAGER.
PADDED_NAME JOB LEVEL
-------------------- --------- ----------
KING PRESIDENT 1
JONES MANAGER 2
SCOTT ANALYST 3
ADAMS1 SALESMAN 4
ADAMS CLERK 5
FORD ANALYST 3
SMITH1 SALESMAN 4
SMITH CLERK 5
BLAKE MANAGER 2
ALLEN SALESMAN 3
WARD SALESMAN 3
MARTIN SALESMAN 3
TURNER SALESMAN 3
JAMES CLERK 4
CLARK MANAGER 2
MILLER1 SALESMAN 3
MILLER CLERK 4
This the the result I need.
MILLER CLARK
JAMES BLAKE
SMITH FORD
ADAMS SCOTT
I am not sure if it is possible at all with sql. Hope I could expalin it better this time.
Thanks a lot for your time.
February 01, 2002 - 2:12 pm UTC
scott@ORA817DEV.US.ORACLE.COM> column padded_name format a20
scott@ORA817DEV.US.ORACLE.COM>
scott@ORA817DEV.US.ORACLE.COM> select lpad(' ', (level - 1) * 2) || ename as padded_name, job, level
2 from emp
3 connect by prior empno = mgr
4 start with mgr IS null;
PADDED_NAME JOB LEVEL
-------------------- --------- ----------
KING PRESIDENT 1
JONES MANAGER 2
SCOTT ANALYST 3
ADAMS CLERK 4
FORD ANALYST 3
SMITH CLERK 4
BLAKE MANAGER 2
ALLEN SALESMAN 3
WARD SALESMAN 3
MARTIN SALESMAN 3
TURNER SALESMAN 3
JAMES CLERK 3
CLARK MANAGER 2
MILLER CLERK 3
14 rows selected.
scott@ORA817DEV.US.ORACLE.COM>
scott@ORA817DEV.US.ORACLE.COM>
scott@ORA817DEV.US.ORACLE.COM>
scott@ORA817DEV.US.ORACLE.COM> select ename,
2 substr( (select min( to_char( level, 'fm0009' ) || ename )
3 from emp e2
4 where job in ( 'ANALYST', 'MANAGER' )
5 start with e2.empno = e1.empno
6 connect by prior mgr = empno ), 5 )
7 from emp e1
8 where job = 'CLERK'
9 /
ENAME SUBSTR((SEL
---------- -----------
SMITH FORD
ADAMS SCOTT
JAMES BLAKE
MILLER CLARK
scott@ORA817DEV.US.ORACLE.COM>
should get that for you.
excellent
arunima, February 01, 2002 - 2:45 pm UTC
That was awesome. thanks a lot
Don't know how to do it
Neelam, March 29, 2004 - 4:39 pm UTC
I have a table eg:
create table temp(a number,b date);
Values are
select * from temp order by b desc,a;
A B
---------- ---------
1 01-FEB-04
2 02-FEB-04
1 14-JAN-04
2 15-JAN-04
1 01-JAN-04
2 01-JAN-04
Now I want to create another table temp1(a number,b date, c date);
TEMP1 table should have the values based on TEMP as
A B C
---------- --------- ---------
1 01-FEB-04 14-JAN-04
2 02-FEB-04 15-JAN-04
1 14-JAN-04 01-JAN-04
2 15-JAN-04 01-JAN-04
1 01-JAN-04
2 01-JAN-04
Means for Class 1 C should be updated as the previous date for Class 1
14-JAN-04 is the next lower date for Class 1, so C should be updated with this value
Same for Class 2. Hope I'm able to make it clear. If not please let me know.
I have done this using PL/SQL by opening a cursor.
I want to know whether this is possible in SQL statement?
I'm using Oracle9i Enterprise Edition Release 9.2.0.4.0
Thanks
March 29, 2004 - 5:01 pm UTC
ops$tkyte@ORA9IR2> select a, b, lead(b) over (partition by a order by b desc) c
2 from t
3 order by b desc, a;
A B C
---------- --------- ---------
2 02-FEB-04 15-JAN-04
1 01-FEB-04 14-JAN-04
2 15-JAN-04 01-JAN-04
1 14-JAN-04 01-JAN-04
1 01-JAN-04
2 01-JAN-04
6 rows selected.
Excellent
Neelam, March 29, 2004 - 6:00 pm UTC
Thanks a lot.
Once again learned something new.
Great!!!!!!
Excellent answer. How would I get the immediate parent listed next
A reader, April 13, 2004 - 4:18 pm UTC
PADDED_NAME JOB LEVEL
-------------------- --------- ----------
KING PRESIDENT 1
JONES MANAGER 2
SCOTT ANALYST 3
ADAMS CLERK 4
FORD ANALYST 3
SMITH CLERK 4
BLAKE MANAGER 2
ALLEN SALESMAN 3
WARD SALESMAN 3
MARTIN SALESMAN 3
TURNER SALESMAN 3
JAMES CLERK 3
CLARK MANAGER 2
MILLER CLERK 3
In this example, I want the result to be displayed (jut the empname)
Par Child
--------- ---------
KING JONES
JONES SCOTT
SCOTT ADAMS
..
FORD SMITH
...
ie, I want the immediate parent to be displayed next to the child?
Oracle Ver 9.2
Thanks
April 13, 2004 - 6:46 pm UTC
join.
You have the MGR column in there, just join emp to itself.
scott@ORA9IR2> select lpad(' ', (level - 1) * 2) || a.ename as padded_name, a.job, level,
2 b.ename
3 from emp A left join emp B on ( a.mgr = b.empno )
4 connect by prior a.empno = a.mgr
5 start with a.mgr IS null;
PADDED_NAME JOB LEVEL ENAME
--------------- --------- ---------- ----------
KING PRESIDENT 1
CLARK MANAGER 2 KING
MILLER CLERK 3 CLARK
BLAKE MANAGER 2 KING
JAMES CLERK 3 BLAKE
TURNER SALESMAN 3 BLAKE
MARTIN SALESMAN 3 BLAKE
WARD SALESMAN 3 BLAKE
ALLEN SALESMAN 3 BLAKE
JONES MANAGER 2 KING
FORD ANALYST 3 JONES
SMITH CLERK 4 FORD
SCOTT ANALYST 3 JONES
ADAMS CLERK 4 SCOTT
14 rows selected.
Or perhaps...
Padders, April 14, 2004 - 5:01 am UTC
Can't you just use PRIOR ename?
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
SQL> SELECT PRIOR ename parent, ename child
2 FROM emp
3 CONNECT BY PRIOR empno = mgr
4 START WITH mgr IS NULL;
PARENT CHILD
---------- ----------
KING
KING JONES
JONES SCOTT
SCOTT ADAMS
JONES FORD
FORD SMITH
KING BLAKE
BLAKE ALLEN
BLAKE WARD
BLAKE MARTIN
BLAKE TURNER
BLAKE JAMES
KING CLARK
CLARK MILLER
14 rows selected.
SQL>
April 14, 2004 - 8:41 am UTC
(sheepishly) yes....
Thanks Tom.
A reader, April 14, 2004 - 1:18 pm UTC
Can I extend the above query to, say to get the grand parent?. I tried the sys_connect_by_path, instr, substr. But I am not sure whether my approach is correct.
Also,
I have the hierarchical with nested join (the IN portion). Is there any way, I can change the nested loop to some other kind? When testing with lot of data, performance is really slow. I have the following query joining with the same table, as a view. Is there any way to change that IN clause to a join or something like that?
create table dep (pk number,
typ char(1),
par number,
constraint pk_l primary key (pk))
/
insert into dep values ( 1,'X', NULL );
insert into dep values ( 2,'X', NULL );
insert into dep values ( 3,'X', NULL );
insert into dep values ( 4,'X', NULL );
insert into dep values ( 5,'X', NULL );
insert into dep values ( 6,'X', NULL );
insert into dep values ( 7,'X', NULL );
insert into dep values ( 8,'X', NULL );
insert into dep values ( 9,'X', NULL );
insert into dep values ( 10,'X', NULL );
insert into dep values ( 11,'X', NULL );
insert into dep values ( 12,'X', NULL );
insert into dep values ( 13,'X', NULL );
insert into dep values ( 14,'X', NULL );
insert into dep values ( 15,'C', 9 );
insert into dep values ( 16,'C', 9 );
insert into dep values ( 17,'C', 9 );
insert into dep values ( 18,'C', 9 );
insert into dep values ( 19,'C', 9 );
insert into dep values ( 20,'C', 9 );
insert into dep values ( 21,'X', NULL );
select l1.pk,l2.pk from dep l1, dep l2
where l1.typ in ('A','B','C','X') and
l2.typ = 'X' and
l2.pk in (
select l3.pk
from dep l3
where l3.typ = 'X'
start with l3.pk = l1.pk
connect by l3.pk = prior l3.par)
Thank you.
April 14, 2004 - 2:22 pm UTC
i'd be using sys_connect_by_path to go higher up the food chain.
not sure what your query is after exactly there. are you missing a join or something?
else you have a wicked sort of cartesian semi join going on.
take all the typ in (a,b,c,x) and join them to each of the typ in (x)
once you have that cartesian join, keep the rows such that l2.pk is in a hierarchy we must build for each row.
is that what you intended?
I aggree, its somewhat messy
A reader, April 14, 2004 - 5:17 pm UTC
A friend through this query at me. I have removed most of the columns from the original and posted the complex part with few columns keeping the original rewuirement.
Originally, it was something like the following query (many columns are removed for clarity).
SELECT d1.*, d2.*
FROM dep d1,
(
select l1.pk as key,l2.pk from dep l1, dep l2
where l1.typ in ('A','B','C','X') and
l2.typ = 'X' and
l2.pk in (
select l3.pk
from dep l3
where l3.typ = 'X'
start with l3.pk = l1.pk
connect by l3.pk = prior l3.par
)
) d2
WHERE d1.pk = d2.key (+)
The above was created as a view and the realy query would be like, "select * from view where pk = ?"
I will try your suggetion.
Thanks so much.
Heirarchy relations
Sanjeev, August 31, 2004 - 5:19 am UTC
hi Tom,
i have table called 'EMP' where i have 3 columns.
empno,deptno,sal.
i want to know the sql where i can get the following result
deptno-10
empno-7010
empno-7020
empno-7030
deptno-20
empno-7030
empno-7040
that means i want to display first the deptno then the employee's in that department in a single sql query.
Thanks
August 31, 2004 - 9:05 am UTC
ops$tkyte@ORA9IR2> select deptno, empno, grouping(deptno), grouping(empno)
2 from emp
3 group by grouping sets( (deptno), (deptno,empno) )
4 order by deptno, empno NULLS FIRST
5 /
DEPTNO EMPNO GROUPING(DEPTNO) GROUPING(EMPNO)
---------- ---------- ---------------- ---------------
10 0 1
10 7782 0 0
10 7839 0 0
10 7934 0 0
20 0 1
20 7369 0 0
20 7566 0 0
20 7788 0 0
20 7876 0 0
20 7902 0 0
30 0 1
30 7499 0 0
30 7521 0 0
30 7654 0 0
30 7698 0 0
30 7844 0 0
30 7900 0 0
17 rows selected.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select decode( grouping(empno), 1, 'deptno-' || deptno, 'empno-' || empno )
2 from emp
3 group by grouping sets( (deptno), (deptno,empno) )
4 order by deptno, empno NULLS FIRST
5 /
DECODE(GROUPING(EMPNO),1,'DEPT
------------------------------------------------------------------------
deptno-10
empno-7782
empno-7839
empno-7934
deptno-20
empno-7369
empno-7566
empno-7788
empno-7876
empno-7902
deptno-30
empno-7499
empno-7521
empno-7654
empno-7698
empno-7844
empno-7900
17 rows selected.
A reader, August 31, 2004 - 12:06 pm UTC
Hierarchy Relation
Sanjeev.H, September 09, 2004 - 3:22 am UTC
Hi Tom,
Thank You for your response,but the problem is i am having
oracle 8.1.7 version.So the function "sets" doesn't work.
Can you please give the solution compatable for
this version.
Thank you
September 09, 2004 - 8:25 am UTC
well, thats what happens when you *don't specify a release* and are doing new development in really old stuff I guess. Not much I can say otherwise. I sort of have to make some assumptions when presented with "not much detail"
Less efficient method for 8i:
ops$tkyte@ORA817DEV> select deptno, empno, grouping(deptno), grouping(empno)
2 from emp
3 group by rollup( deptno, empno )
4 having grouping(deptno) = 0
5 /
DEPTNO EMPNO GROUPING(DEPTNO) GROUPING(EMPNO)
---------- ---------- ---------------- ---------------
10 7782 0 0
10 7839 0 0
10 7934 0 0
10 0 1
20 7369 0 0
20 7566 0 0
20 7788 0 0
20 7876 0 0
20 7902 0 0
20 0 1
30 7499 0 0
30 7521 0 0
30 7654 0 0
30 7698 0 0
30 7844 0 0
30 7900 0 0
30 0 1
17 rows selected.
ops$tkyte@ORA817DEV> select decode( grouping(empno), 1, 'deptno-' || deptno, 'empno-' || empno )
2 from emp
3 group by rollup(deptno,empno)
4 having grouping(deptno) = 0
5 order by deptno, empno NULLS FIRST
6 /
DECODE(GROUPING(EMPNO),1,'DEPTNO-'||DEPTNO,'EMP
-----------------------------------------------
deptno-10
empno-7782
empno-7839
empno-7934
deptno-20
empno-7369
empno-7566
empno-7788
empno-7876
empno-7902
deptno-30
empno-7499
empno-7521
empno-7654
empno-7698
empno-7844
empno-7900
17 rows selected.
Heirarchy
Sanjeev H, September 10, 2004 - 6:54 am UTC
Hi Tom,
Sorry for not mensioning the Version in the previous mail.
But the solution which you have given is having separate
coulmns for "dept" and "Empno".
But my issue was i want the deptno and the corresponding empno's in that department in a single column.
E.g
10
7010
7020
7030
20
7040
7050
7060
-
-
-
-
Thank you,
Sanjeev
September 10, 2004 - 9:21 am UTC
you said "column" but show "rows"
my query produces the output you keep drawing so -- confusion reigns.
Heirarchy
Sanjeev H, September 13, 2004 - 1:50 am UTC
Hi Tom,
Thanks a Ton Tom.
Sanjeev
seems close to what i want to do
steve, October 12, 2004 - 7:49 pm UTC
I have the same as the manager, staff situation, but the data is more in the form of an org chart.
I have something similar to the following "key data ( simplified)".
where the "key" ( both parent&child can be keys) is used to point to a person record in another table ( not shown):
parent,child
a,b
b,z
a,p
p,m
m,y
m,x
z,w
j,x
what i want to do is to pass in a "key" and a nest value
and get all related data back to that nest level.
I.E
"key"= a, level =2
should return the following values
a,b
b,z ( via a,b)
but not z,w (via a,b+b,z) because it is at level3
a,p
p,m
but not m,y ( level=3)
but not m,x (level=3)
but not j,x (because it is not connected, in a downward fasion)
October 12, 2004 - 7:59 pm UTC
just add a predicate on LEVEL in the connect by step.
LEVEL is set for you, you can use that to stop at a certain level and not build the hierarchy any deeper.
to steve (china)
Marcio Portes, May 24, 2005 - 11:30 am UTC
Tom, correct me if I am wrong. But he could.
ops$mportes@FCCUAT9I> select * from t;
P C
- -
a b
b z
a p
p m
m y
m x
z w
j x
8 rows selected.
ops$mportes@FCCUAT9I>
ops$mportes@FCCUAT9I> select parent, child, level
2 from t
3 start with parent = 'a'
4 connect by prior child = parent and level <= 2
5 /
P C LEVEL
- - -------------
a b 1
b z 2
a p 1
p m 2
4 rows selected.
Regards,
May 24, 2005 - 1:34 pm UTC
lots of questions in questions here -- not sure which one you mean. but if you meant the first one -- i think so. just one level up.
lots of question
Marcio Portes, May 24, 2005 - 1:59 pm UTC
Yes exactly as I pointed it out on title "to steve (china)", I've made a find in this thread and just one steve has came up so far. Anyway, yes it was the first one before my post.
Thanks.