connect by processing?

From: Adam Donahue <adonahue_at_opsware.com>
Date: Thu, 11 Nov 2004 12:07:56 -0800
Message-ID: <4193C69C.5090504@opsware.com>

Folks, let's say I run this query:
select drc.dvc_id FROM role_classes cr, device_role_classes drc WHERE cr.role_class_id = drc.role_class_id (+) START WITH cr.role_class_id = 5
CONNECT BY cr.parent_role_class_id = prior cr.role_class_id

it returns a bunch of dvc_id's and nulls (based on the outer join):

       DVC_ID

---

       460003

       150004
       170003
       180003

       780003

... etc.

Now, if I modify the query by adding an additional where condition:

select drc.dvc_id FROM role_classes cr, device_role_classes drc WHERE cr.role_class_id = drc.role_class_id (+) /AND drc.dvc_id is not null/
START WITH cr.role_class_id = 5
CONNECT BY cr.parent_role_class_id = prior cr.role_class_id

instead of returning the dvc_id's above that are not null, it returns NOTHING. But if I run the same query using "dvc_id IS null" then it returns the NULL rows.

SQL> select * from v$version;

BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.3.0 - Production NLSRTL Version 9.2.0.3.0 - Production

The documentation implies that the (non-join) where clause is evaluated after complete connect by processing, eliminating those rows not matching the condition.

I've tried rewriting this using ANSI join syntax, same problem.

I feel like I'm missing something obvious here... any ideas?

Adam

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

Received on Thu Nov 11 2004 - 16:31:36 CST