Re: connect by processing?
- From: Mark Richard <mrichard@xxxxxxxxxxxxxxxxx>
- To: adonahue@xxxxxxxxxxx
- Date: Fri, 12 Nov 2004 11:43:49 +1100
Adam,
I suspect your comment about the documentation "implying" that the non-join
is applied after the connect by is probably your problem. The "is null"
probably works because the record in the "start with" clause has a null
dvc_id column - allowing the query to start and continue down the null
branches. Have you tried placing the "is not null" in an outer query? I
know it's not particularly nice visually but it might work... And if
Oracle doesn't specifically state that the non-join clause is applied last
then it might be your only option.
Regards,
Mark.
|---------+----------------------------->
| | Adam Donahue |
| | <adonahue@opsware.|
| | com> |
| | Sent by: |
| | oracle-l-bounce@fr|
| | eelists.org |
| | |
| | |
| | 12/11/2004 07:07 |
| | Please respond to |
| | adonahue |
|---------+----------------------------->
>------------------------------------------------------------------------------------------------------------------------------|
|
|
| To: oracle-l@xxxxxxxxxxxxx
|
| cc:
|
| 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
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for
delivery of the message to such person), you may not copy or deliver this
message to anyone.
In such a case, you should destroy this message and kindly notify the sender by
reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999.
Please advise immediately if you or your employer does not consent to Internet
e-mail for messages of this kind.
Opinions, conclusions and other information in this message that do not relate
to the official business of Transurban Infrastructure Developments Limited and
CityLink Melbourne Limited shall be understood as neither given nor endorsed by
them.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
