Re: Inner Joins and Hierarchical Queries

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

--
http://www.freelists.org/webpage/oracle-l