RE: connect by processing?

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Fri, 12 Nov 2004 10:12:57 +0100
Message-Id: <20041112091304.25B0838BB95@ha-smtp2.tiscali.nl>

Hi Adam,
When I see:

WHERE cr.role_class_id = drc.role_class_id(+) AND drc.dvc_id is not null

I immediately get a little nervous. When using this outdated outer join syntax, you typically apply the (+) operator to "all or none" references of the same table -- drc in your case -- otherwise you mess up the outer join.

I would like to see your attempts to achieve the same, using the new ANSI/ISO outer join syntax instead -- that one is much cleaner and easier to interpret...

Lex.  

---

visit http://www.naturaljoin.nl

---

skype me  

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Adam Donahue
Sent: Thursday, November 11, 2004 21:08
To: oracle-l_at_freelists.org
Subject: connect by processing?

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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Nov 12 2004 - 03:09:34 CST