| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: connect by processing?
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_at_opsware.|
| | com> |
| | Sent by: |
| | oracle-l-bounce_at_fr|
| | eelists.org |
| | |
| | |
| | 12/11/2004 07:07 |
| | Please respond to |
| | adonahue |
|---------+-----------------------------> >------------------------------------------------------------------------------------------------------------------------------| | | | To: oracle-l_at_freelists.org | | 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
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 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 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. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 11 2004 - 23:01:23 CST
 |
 |