hierarchical - Why does oracle SYS_CONNECT_BY_PATH return null values? -

why path column full of null values? cycles in hierarchical data cause problems sys_connect_by_path function?

sql fiddle

oracle 11g r2 schema setup:

create table modules (     mod_id integer not null,     name varchar2(20) not null,     prnt_mod_id integer  );  insert  modules values(1, 'base module', 1); insert  modules values(2, 'sub module 1-2', 1); insert  modules values(3, 'sub module 1-3', 1); insert  modules values(4, 'sub module 1-4', 1); insert  modules values(5, 'sub module 1-2-5', 2); insert  modules values(6, 'sub module 1-2-5-6', 5); 

query 1:

select t.mod_id,        t.name,        t.prnt_mod_id,        max(level) mx_lvl,        sys_connect_by_path(t.name, '>') path modules t start t.mod_id = 1 connect nocycle prior t.mod_id = t.prnt_mod_id group t.mod_id, t.name, t.prnt_mod_id order mx_lvl, t.name 

results:

| mod_id |               name | prnt_mod_id | mx_lvl |   path | |--------|--------------------|-------------|--------|--------| |      1 |        base module |           1 |      1 | (null) | |      2 |     sub module 1-2 |           1 |      2 | (null) | |      3 |     sub module 1-3 |           1 |      2 | (null) | |      4 |     sub module 1-4 |           1 |      2 | (null) | |      5 |   sub module 1-2-5 |           2 |      3 | (null) | |      6 | sub module 1-2-5-6 |           5 |      4 | (null) | 

i'm trying use sys_connect_by_path demonstrated in oracle docs here:

http://docs.oracle.com/cd/b28359_01/server.111/b28286/queries003.htm#sqlrf52318 http://docs.oracle.com/cd/b28359_01/server.111/b28286/functions171.htm#sqlrf06116

i want display delimited path each of modules.

from oracle documentation:

if specify [a hierarchical query] clause, not specify either order or group by, because destroy hierarchical order of connect results. if want order rows of siblings of same parent, use order siblings clause.

(if need group in preceding or succeeding sub-query not in same sub-query.)

to output non-null paths, can this:

sql fiddle

query 1:

select t.mod_id,        t.name,        t.prnt_mod_id,        level mx_lvl,        sys_connect_by_path(t.name, '>') path modules t start t.mod_id = 1 connect nocycle prior t.mod_id = t.prnt_mod_id order mx_lvl, t.name 

results:

| mod_id |               name | prnt_mod_id | mx_lvl |                                                            path | |--------|--------------------|-------------|--------|-----------------------------------------------------------------| |      1 |        base module |           1 |      1 |                                                    >base module | |      2 |     sub module 1-2 |           1 |      2 |                                     >base module>sub module 1-2 | |      3 |     sub module 1-3 |           1 |      2 |                                     >base module>sub module 1-3 | |      4 |     sub module 1-4 |           1 |      2 |                                     >base module>sub module 1-4 | |      5 |   sub module 1-2-5 |           2 |      3 |                    >base module>sub module 1-2>sub module 1-2-5 | |      6 | sub module 1-2-5-6 |           5 |      4 | >base module>sub module 1-2>sub module 1-2-5>sub module 1-2-5-6 | 

however, might want use ordering clause:

order siblings t.name;