RE: Inner Joins and Hierarchical Queries
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <mwf@xxxxxxxx>, <ian@xxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 14 Dec 2006 17:57:24 -0500
select h.empno,h.ename, h.hlevel, dd.v from (select empno,ename, level
hlevel from scott.emp connect by prior empno = mgr)h,
(select 'a' as v from dual union select 'b' as v from dual) dd
/
That worked, but I got some strange "connect by required in this block"
errors if I broke up the connect by inline view to multiple lines. Not sure
if that was fat fingers or some chars I couldn't see, but it worked like
this and I've grown tired of the exercise....
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of Mark W. Farnham
Sent: Thursday, December 14, 2006 5:29 PM
To: ian@xxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of MacGregor, Ian A.
Sent: Thursday, December 14, 2006 2:04 PM
To: oracle-l@xxxxxxxxxxxxx
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
----------------------------------------------------------------------------
--------------------
Now joining to an inline view
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@xxxxxxxxxxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
