| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_slac.stanford.edu
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Dec 14 2006 - 13:04:10 CST
 |
 |