| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Inner Joins and Hierarchical Queries
I think it shows the correct results.
At each hierarchy level you get the full tree underneath.
so king 1a has both sets of children (jones 2a AND jones 2b, for example)
and king 1b also has (jones 2a AND jones 2b).
I think you want to prune the tree such that you only get the "a" children of the "a" parent and the "b" children of the "b" parent.
Off the top of my head I'm not sure of the syntax to state that to sql.
I suppose you could project the original connect by as an inline view and then join it to whatever, and you should get one hierarchy per row joined to.
So I guess that would be:
select h.empno, h.ename, h.level, dd.v
from
(select empno, ename, level from emp
connect by prior empno = mgr
start with empno = 7839) h,
(SELECT 'A' AS V FROM DUAL
UNION SELECT 'B' FROM DUAL) dd;
Okay, so that's my guess. Now maybe I'll test it.
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On
Behalf Of MacGregor, Ian A.
Sent: Thursday, December 14, 2006 2:04 PM
To: oracle-l_at_freelists.org
Subject: Inner Joins and Hierarchical Queries
For many of us the first hierarchichal query we ever wrote was on the lines of
1 SELECT EMPNO, ENAME, LEVEL FROM EMP
2 CONNECT BY PRIOR EMPNO = MGR
3* START WITH EMPNO = 7839
SQL> /
EMPNO ENAME LEVEL
---------- ---------- ----------
7839 KING 1
7566 JONES 2
7788 SCOTT 3
7876 ADAMS 4
7902 FORD 3
7369 SMITH 4
7698 BLAKE 2
7499 ALLEN 3
7521 WARD 3
7654 MARTIN 3
7844 TURNER 3
7900 JAMES 3
7782 CLARK 2
7934 MILLER 3
----------------------------------------------------------------------------
--------------------
SELECT EMPNO, ENAME, LEVEL, V
FROM EMP, (SELECT 'A' AS V FROM DUAL)
CONNECT BY PRIOR EMPNO = MGR
START WITH EMPNO = 7839
EMPNO ENAME LEVEL V
---------- ---------- ---------- -
7839 KING 1 A
7566 JONES 2 A
7788 SCOTT 3 A
7876 ADAMS 4 A
7902 FORD 3 A
7369 SMITH 4 A
7698 BLAKE 2 A
7499 ALLEN 3 A
7521 WARD 3 A
7654 MARTIN 3 A
7844 TURNER 3 A
7900 JAMES 3 A
7782 CLARK 2 A
7934 MILLER 3 A
The results are as expected.
Now if I run
SELECT EMPNO, ENAME, LEVEL, V
FROM EMP, (SELECT 'A' AS V FROM DUAL
UNION SELECT 'B' FROM DUAL)
CONNECT BY PRIOR EMPNO = MGR
START WITH EMPNO = 7839
Order by LEVEL, ENAME
/
EMPNO ENAME LEVEL V
---------- ---------- ---------- -
7839 KING 1 A
7839 KING 1 B
7698 BLAKE 2 A
7698 BLAKE 2 B
7698 BLAKE 2 A
7698 BLAKE 2 B
7782 CLARK 2 B
7782 CLARK 2 A
7782 CLARK 2 B
7782 CLARK 2 A
7566 JONES 2 B
7566 JONES 2 A
7566 JONES 2 B
7566 JONES 2 A
7499 ALLEN 3 A
7499 ALLEN 3 B
7499 ALLEN 3 A
7499 ALLEN 3 B
7499 ALLEN 3 B
7499 ALLEN 3 A
7499 ALLEN 3 A
7499 ALLEN 3 B
7902 FORD 3 B
7902 FORD 3 A
7902 FORD 3 B
7902 FORD 3 A
7902 FORD 3 B
7902 FORD 3 B
7902 FORD 3 A
7902 FORD 3 A
7900 JAMES 3 A
7900 JAMES 3 B
7900 JAMES 3 B
7900 JAMES 3 A
7900 JAMES 3 A
7900 JAMES 3 B
7900 JAMES 3 B
7900 JAMES 3 A
7654 MARTIN 3 A
7654 MARTIN 3 B
7654 MARTIN 3 B
7654 MARTIN 3 A
7654 MARTIN 3 A
7654 MARTIN 3 B
7654 MARTIN 3 A
7654 MARTIN 3 B
7934 MILLER 3 B
7934 MILLER 3 A
7934 MILLER 3 B
7934 MILLER 3 A
7934 MILLER 3 B
7934 MILLER 3 A
7934 MILLER 3 B
7934 MILLER 3 A
7788 SCOTT 3 B
7788 SCOTT 3 A
7788 SCOTT 3 B
7788 SCOTT 3 A
7788 SCOTT 3 B
7788 SCOTT 3 A
7788 SCOTT 3 A
7788 SCOTT 3 B
7844 TURNER 3 A
7844 TURNER 3 B
7844 TURNER 3 B
7844 TURNER 3 A
7844 TURNER 3 B
7844 TURNER 3 A
7844 TURNER 3 B
7844 TURNER 3 A
7521 WARD 3 B
7521 WARD 3 A
7521 WARD 3 B
7521 WARD 3 A
7521 WARD 3 B
7521 WARD 3 A
7521 WARD 3 A
7521 WARD 3 B
7876 ADAMS 4 A
7876 ADAMS 4 B
7876 ADAMS 4 A
7876 ADAMS 4 B
7876 ADAMS 4 A
7876 ADAMS 4 B
7876 ADAMS 4 A
7876 ADAMS 4 B
7876 ADAMS 4 A
7876 ADAMS 4 B
7876 ADAMS 4 A
7876 ADAMS 4 B
7876 ADAMS 4 A
7876 ADAMS 4 B
7876 ADAMS 4 A
7876 ADAMS 4 B
7369 SMITH 4 A
7369 SMITH 4 B
7369 SMITH 4 A
7369 SMITH 4 B
7369 SMITH 4 A
7369 SMITH 4 B
7369 SMITH 4 A
7369 SMITH 4 B
7369 SMITH 4 A
7369 SMITH 4 B
7369 SMITH 4 A
7369 SMITH 4 B
7369 SMITH 4 A
7369 SMITH 4 B
7369 SMITH 4 A
7369 SMITH 4 B
110 rows selected.
Not what I was expecting. I thought the query would return 28 rows 14 rows from emp * 2 rows from the inline view.
Instead for each row in emp it is returning power(2,level). Is this expected?
The database version is 10.2.0.2.0 .
Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Dec 14 2006 - 16:28:33 CST
 |
 |