RE: connect by processing?
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <adonahue@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 12 Nov 2004 10:12:57 +0100
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Adam Donahue
Sent: Thursday, November 11, 2004 21:08
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
