Re: Hierarchical Query revisited

From: Graham Miller <lgmiller_at_elmrd.u-net.com>
Date: Wed, 08 Apr 1998 18:49:55 GMT
Message-ID: <352bc5cd.3491362@news.u-net.com>

"Wolfgang Hann" <W.Hann_at_netway.at> wrote (in <01bd62ce$9b4c5d20$0601030a_at_ENTWICKLUNG.NETWAY.AT>)...

|

[snip]
| In addition it would be very nice to be able to
| use a subquery beneath the start with clause for example
|
| start with job=(select job from job_list).
|
| Currently this can not be done because Oracle expects not
| have a multi row query.
|

Hello,
  Err, no

You cannot test one value to be equal to a potential list of values.

try:
SCOTT>l
  1 SELECT SUBSTR(LPAD(' ',2*(LEVEL-1))||ename,1,15) AS org_chart,   2 mgr, job, deptno FROM emp
  3 START WITH job IN

  4          (SELECT
  5             e2.job
  6           FROM
  7             emp e2
  8           WHERE
  9             e2.job like '%E%'
 10           )

 11* CONNECT BY PRIOR mgr = empno

graham Received on Wed Apr 08 1998 - 13:49:55 CDT