Confirmed problem?
- From: Adam Donahue <adonahue@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 11 Nov 2004 17:58:24 -0800
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
