Why does oracle SYS_CONNECT_BY_PATH return null values?

Question

Why is my PATH column full of null values? Do cycles in hierarchical data cause problems with the 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 INTO  MODULES VALUES(1, 'BASE MODULE', 1);
INSERT INTO  MODULES VALUES(2, 'SUB MODULE 1-2', 1);
INSERT INTO  MODULES VALUES(3, 'SUB MODULE 1-3', 1);
INSERT INTO  MODULES VALUES(4, 'SUB MODULE 1-4', 1);
INSERT INTO  MODULES VALUES(5, 'SUB MODULE 1-2-5', 2);
INSERT INTO  MODULES VALUES(6, 'SUB MODULE 1-2-5-6', 5);

Query 1:

select t.mod_id,
       t.name,
       t.prnt_mod_id,
       max(LEVEL) as mx_lvl,
       SYS_CONNECT_BY_PATH(t.name, '>') as PATH
from MODULES t
start with t.mod_id = 1
connect by NOCYCLE prior t.mod_id = t.prnt_mod_id
group by t.mod_id, t.name, t.prnt_mod_id
order by 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 to use SYS_CONNECT_BY_PATH as demonstrated in the 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 really want to display the delimited path for each of my modules.

Answer 1

From the oracle documentation:

If you specify [a hierarchical query] clause, then do not specify either ORDER BY or GROUP BY, because they will destroy the hierarchical order of the CONNECT BY results. If you want to order rows of siblings of the same parent, then use the ORDER SIBLINGS BY clause.

(If you do need to group then do it in a preceding or succeeding sub-query but not in the same sub-query.)

To get your output with non-null paths, you can do this:

SQL Fiddle

Query 1:

select t.mod_id,
       t.name,
       t.prnt_mod_id,
       LEVEL as mx_lvl,
       SYS_CONNECT_BY_PATH(t.name, '>') as PATH
from MODULES t
start with t.mod_id = 1
connect by NOCYCLE prior t.mod_id = t.prnt_mod_id
order by 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, you might want to use this as the ordering clause:

ORDER SIBLINGS BY t.name;