Re: Inner Joins and Hierarchical Queries
- From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- To: ian@xxxxxxxxxxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 14 Dec 2006 11:39:44 -0800 (PST)
Ian
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
/
Yes, it's expected, you're effectively doing a hierarchical query on the
cartesian join of 2 rows from dual with EMP
Logically equivalent to:
insert into emp select * from emp;
do the original hierarchical query on EMP
rollback;
(Try it - but you may have to lose any PKs and UKs on EMP)
What you thought you were getting would be:
SELECT *
FROM (
SELECT EMPNO, ENAME, LEVEL FROM EMP
CONNECT BY PRIOR EMPNO = MGR
START WITH EMPNO = 7839
),
(SELECT 'A' AS V FROM DUAL
UNION SELECT 'B' FROM DUAL)
ORDER BY LEVEL, ENAME
ie a cartesian join of the result of the hierarchical query. Cartesian join and
hierarchical query are not associative!
Regards Nigel
--
//www.freelists.org/webpage/oracle-l
Other related posts:
