Re: Connect By & Join

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/04/02
Message-ID: <3523cb73.3476748@www.sigov.si>#1/1

On 2 Apr 1998 06:05:13 GMT, "Blinoff Dmitry" <dbprok_at_ropnet.ru> wrote:

>Hi !
>
>Does anybody find solution to use Connect By and Join
>in one Select ? I don't want to use temporary tables or snapshots to do
>this.
>May be it could be done with subquery from View witch performs join with
>/*+ NO_MERGE */ ?
>I had tried tens of combinations, but alas, nothing of them works.

You can do it if you put your "connect by" query of a single table in a view (can be in-line view) and join that view to the other table. Here is an example of a hierarchical query, joining tables SCOTT.EMP and SCOTT.DEPT:

SQL> SELECT x.org_chart, x.mgr, x.job, dept.dname FROM   2 (SELECT SUBSTR(LPAD(' ',2*(LEVEL-1))||ename,1,15) AS org_chart,

  3           mgr, job, deptno                -- get the hierarchy
  4    FROM emp START WITH job = 'PRESIDENT'  -- inside an in-line
  5         CONNECT BY PRIOR empno = mgr) x,  -- view
  6    dept                       -- get the department name by joinig

  7 WHERE x.deptno = dept.deptno -- hierarchical query to table DEPT   8 /

ORG_CHART MGR JOB DNAME --------------- --------- --------- --------------

KING                      PRESIDENT ACCOUNTING
  JONES              7839 MANAGER   RESEARCH
    SCOTT            7566 ANALYST   RESEARCH
      ADAMS          7788 CLERK     RESEARCH
    FORD             7566 ANALYST   RESEARCH
      SMITH          7902 CLERK     RESEARCH
  BLAKE              7839 MANAGER   SALES
    ALLEN            7698 SALESMAN  SALES
    WARD             7698 SALESMAN  SALES
    MARTIN           7698 SALESMAN  SALES
    TURNER           7698 SALESMAN  SALES
    JAMES            7698 CLERK     SALES
  CLARK              7839 MANAGER   ACCOUNTING
    MILLER           7782 CLERK     ACCOUNTING

14 rows selected.

SQL>
>--
>With respect,Dmitry, dbprok_at_ropnet.ru

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Thu Apr 02 1998 - 00:00:00 CST