From oracle-l-bounce@freelists.org Thu Nov 11 22:10:44 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id iAC4AiL25245 for ; Thu, 11 Nov 2004 22:10:44 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id iAC4Aha25240 for ; Thu, 11 Nov 2004 22:10:43 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0DC5072CC57; Thu, 11 Nov 2004 23:13:24 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 02780-11; Thu, 11 Nov 2004 23:13:23 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 99BD272F5CC; Thu, 11 Nov 2004 22:55:49 -0500 (EST) Message-ID: <419418C0.3080707@opsware.com> Date: Thu, 11 Nov 2004 17:58:24 -0800 From: Adam Donahue User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.1) Gecko/20040707 X-Accept-Language: en-us, en MIME-Version: 1.0 To: oracle-l@freelists.org Subject: Confirmed problem? Content-type: text/plain Content-Transfer-Encoding: 8bit X-archive-position: 12154 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: adonahue@opsware.com Precedence: normal Reply-To: adonahue@opsware.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Take a look at this. Examples: 1 SELECT cr.role_class_id, substr( lpad( ' ', 2 * level )||substr( role_class_short_name, 1, 20 ), 1, 40), dvc_id FROM role_classes cr, device_role_classes drc 2 WHERE cr.role_class_id = drc.role_class_id (+) 3 START WITH cr.role_class_id = 3 4* CONNECT BY cr.parent_role_class_id = prior cr.role_class_id SQL> ROLE_CLASS_ID SUBSTR(LPAD('',2*LEVEL)||SUBSTR(ROLE_CLA DVC_ID ------------- ---------------------------------------- ------------- 3 Hardware 1350003 SERVER 440003 1420003 IBM 1430003 7028-6C1 20003 1430003 7028-6C1 780003 1360003 SUN MICROSYSTEMS 1370003 ENTERPRISE 420R 10003 1470003 NETRA X1 150004 1470003 NETRA X1 170003 1470003 NETRA X1 180003 1670003 NETRA T1 8210003 COMPAQ 8220003 PROLIANT DL360 1630003 HEWLETT PACKARD 1640003 HP NETSERVER LPR 430003 1420004 DELL COMPUTER CORPOR 1430004 POWEREDGE 650 20004 1430004 POWEREDGE 650 30004 8130003 PRECISION WORKSTATIO 100007 1680003 XPST700 460003 8130003 PRECISION WORKSTATIO 620007 8130003 PRECISION WORKSTATIO 820003 8620003 XPS-Z 840003 8700003 OPTIPLEX GX110 870003 24 rows selected. 1 SELECT cr.role_class_id, substr( lpad( ' ', 2 * level )||substr( role_class_short_name, 1, 20 ), 1, 40), dvc_id FROM role_classes cr, device_role_classes drc 2 WHERE cr.role_class_id = drc.role_class_id (+) 3 AND drc.dvc_id is not null 4 START WITH cr.role_class_id = 3 5* CONNECT BY cr.parent_role_class_id = prior cr.role_class_id SQL> / no rows selected 1 SELECT cr.role_class_id, substr( lpad( ' ', 2 * level )||substr( role_class_short_name, 1, 20 ), 1, 40), dvc_id FROM role_classes cr, device_role_classes drc 2 WHERE cr.role_class_id = drc.role_class_id (+) 3 AND drc.dvc_id is null 4 START WITH cr.role_class_id = 3 5* CONNECT BY cr.parent_role_class_id = prior cr.role_class_id SQL> / ROLE_CLASS_ID SUBSTR(LPAD('',2*LEVEL)||SUBSTR(ROLE_CLA DVC_ID ------------- ---------------------------------------- ------------- 3 Hardware 1420003 IBM 1360003 SUN MICROSYSTEMS 1670003 NETRA T1 8210003 COMPAQ 8220003 PROLIANT DL360 1630003 HEWLETT PACKARD 1420004 DELL COMPUTER CORPOR 8 rows selected. 1 SELECT cr.role_class_id, substr( lpad( ' ', 2 * level )||substr( role_class_short_name, 1, 20 ), 1, 40), dvc_id FROM role_classes cr, device_role_classes drc 2 WHERE cr.role_class_id = drc.role_class_id (+) 3 AND drc.dvc_id = 430003 4 START WITH cr.role_class_id = 3 5* CONNECT BY cr.parent_role_class_id = prior cr.role_class_id SQL> / no rows selected 1 SELECT cr.role_class_id, substr( lpad( ' ', 2 * level )||substr( role_class_short_name, 1, 20 ), 1, 40), dvc_id FROM role_classes cr, device_role_classes drc 2 WHERE cr.role_class_id = drc.role_class_id (+) 3 AND drc.dvc_id != 430003 4 START WITH cr.role_class_id = 3 5* CONNECT BY cr.parent_role_class_id = prior cr.role_class_id SQL> / no rows selected 1 SELECT cr.role_class_id, substr( lpad( ' ', 2 * level )||substr( role_class_short_name, 1, 20 ), 1, 40), dvc_id FROM role_classes cr, device_role_classes drc 2 WHERE cr.role_class_id = drc.role_class_id (+) 3 AND drc.dvc_id in ( 430003 ) 4 START WITH cr.role_class_id = 3 5* CONNECT BY cr.parent_role_class_id = prior cr.role_class_id SQL> / no rows selected 1 SELECT cr.role_class_id, substr( lpad( ' ', 2 * level )||substr( role_class_short_name, 1, 20 ), 1, 40), dvc_id FROM role_classes cr, device_role_classes drc 2 WHERE cr.role_class_id = drc.role_class_id (+) 3 AND drc.dvc_id in ( 430003, 192018291 ) 4 START WITH cr.role_class_id = 3 5* CONNECT BY cr.parent_role_class_id = prior cr.role_class_id SQL> / ROLE_CLASS_ID SUBSTR(LPAD('',2*LEVEL)||SUBSTR(ROLE_CLA DVC_ID ------------- ---------------------------------------- ------------- 1640003 HP NETSERVER LPR 430003 (Note 192018291 above is just a random number -- it is /not/ a dvc_id.) 1 SELECT cr.role_class_id, substr( lpad( ' ', 2 * level )||substr( role_class_short_name, 1, 20 ), 1, 40), dvc_id FROM role_classes cr, device_role_classes drc 2 WHERE cr.role_class_id = drc.role_class_id (+) 3 AND nvl(drc.dvc_id,0) = 430003 4 START WITH cr.role_class_id = 3 5* CONNECT BY cr.parent_role_class_id = prior cr.role_class_id SQL> / ROLE_CLASS_ID SUBSTR(LPAD('',2*LEVEL)||SUBSTR(ROLE_CLA DVC_ID ------------- ---------------------------------------- ------------- 1640003 HP NETSERVER LPR 430003 The interesting thing is that this same example using a pair of dummy tables (foo, bar) seems to work fine -- including the direct = clause above and the is not null! (In case one suspected this was related to nvl.) 1 select f.id, lpad( ' ', 2 * level )||f.name name, b.id FROM foo f, bar b 2 where f.id = b.foo_id (+) 3 start with f.id = 1 4* connect by f.parent_id = prior f.id SQL> / ID NAME ID ------------- -------------------- ------------- 1 1 2 1.1 1 3 1.2 8 1.2.1 4 1.3 7 1.3.1 2 7 1.3.1 4 7 rows selected. 1 select f.id, lpad( ' ', 2 * level )||f.name name, b.id FROM foo f, bar b 2 where f.id = b.foo_id (+) 3 and b.id = 2 4 start with f.id = 1 5* connect by f.parent_id = prior f.id SQL> / ID NAME ID ------------- -------------------- ------------- 7 1.3.1 2 1 select f.id, lpad( ' ', 2 * level )||f.name name, b.id FROM foo f, bar b 2 where f.id = b.foo_id (+) 3 and b.id in ( 1, 2 ) 4 start with f.id = 1 5* connect by f.parent_id = prior f.id SQL> / ID NAME ID ------------- -------------------- ------------- 2 1.1 1 7 1.3.1 2 1 select f.id, lpad( ' ', 2 * level )||f.name name, b.id FROM foo f, bar b 2 where f.id = b.foo_id (+) 3 and b.id is not null 4 start with f.id = 1 5* connect by f.parent_id = prior f.id SQL> / ID NAME ID ------------- -------------------- ------------- 2 1.1 1 7 1.3.1 2 7 1.3.1 4 The only difference between the two cases, so far as I can see, if that the device_role_classes PK in the first example is a composite key. But I tried that on foo/bar and it worked as well. Anyhow, before I open a TAR, is there any obvious flaw in the above? It's been a long day. Thanks! Adam -- http://www.freelists.org/webpage/oracle-l