Re: Connect By & Join

You can also define a sql function and use it to do the join. Be careful though since the function won't be read consistent.    

On Thu, 02 Apr 1998 19:49:31 GMT, jurij.modic_at_mf.sigov.mail.si (Jurij Modic) wrote:

>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 Fri Apr 03 1998 - 00:00:00 CST