CODEWITHSUNDEEP
oracle-databaseplsqlhierarchical-query
Alessandro Cucina
Alessandro Cucina

Reputation: 148

Oracle START WITH clause returns no root rows

I'm performing the following query in Oracle 10g to retrieve data in hierarchical form (the complete query is a bit more complex):

SELECT LEVEL AS lvl, a.*
 FROM (
      WITH temp
        AS (...)
    SELECT id_request, subj_type, id_subj, name, flag_e,
           person_code, assoc_type, nature, parent
      FROM temp,
           (...)
    ) a
START WITH a.parent IS NULL
CONNECT BY PRIOR a.id_subj = a.parent;

This is the set returned by the inner select:

ID_REQUEST  SUBJ_TYPE   ID_SUBJ                             NAME            FLAG_E  PERSON_CODE         ASSOC_TYPE  NATURE  PARENT
91948       F           4A4BE76C44D4003CE0530AA000A6003C    John Smith      0       xxxyyy123456zzzzz   Declarant   F       NULL
91948       C           4A4BE76C44D6003CE0530AA000A6003C    Rose Anderson   0       kkkkkk654321qqqqq   NULL        F       4A4BE76C44D4003CE0530AA000A6003C

If I run the whole query without the START WITH clause I correctly get the following result:

LVL ID_REQUEST  SUBJ_TYPE   ID_SUBJ                             NAME            FLAG_E  PERSON_CODE         ASSOC_TYPE  NATURE  PARENT                          
1   91948       C           4A4BE76C44D6003CE0530AA000A6003C    Rose Anderson   0       kkkkkk654321qqqqq   NULL        F       4A4BE76C44D4003CE0530AA000A6003C
1   91948       F           4A4BE76C44D4003CE0530AA000A6003C    John Smith      0       xxxyyy123456zzzzz   Declarant   F       NULL                                
2   91948       C           4A4BE76C44D6003CE0530AA000A6003C    Rose Anderson   0       kkkkkk654321qqqqq   NULL        F       4A4BE76C44D4003CE0530AA000A6003C

but if I run it with the START WITH clause the query returns no rows while I expect 2 rows:

LVL     ID_REQUEST  SUBJ_TYPE   ID_SUBJ                             NAME            FLAG_E  PERSON_CODE         ASSOC_TYPE  NATURE  PARENT
1       91948       F           4A4BE76C44D4003CE0530AA000A6003C    John Smith      0       xxxyyy123456zzzzz   Declarant   F       NULL
2       91948       C           4A4BE76C44D6003CE0530AA000A6003C    Rose Anderson   0       kkkkkk654321qqqqq   NULL        F       4A4BE76C44D4003CE0530AA000A6003C

The strangest things are:

  1. the problem occurs only in some cases while the query works for the most of the cases without big differences in terms of data values;
  2. if I create a table with the resulting set of the inner select, the same query works correctly even using START WITH clause.

This is the query using the table created:

  select LEVEL as lvl, a.*
    from (select * from test_tbl) a
   start with a.parent is null
 connect by PRIOR a.id_subj = a.parent;

It seems that the START WITH clause doesn't match the NULL value in PARENT field. Why does this happen?

Thanks in advance. Best regards.

Upvotes: 0

Views: 1335

Answers (1)

B Samedi
B Samedi

Reputation: 380

Please try the following. "Materialize" hint matters.

WITH
temp AS (...),
a as (
    SELECT /*+ materialize */ id_request, subj_type, id_subj, name, flag_e,
           person_code, assoc_type, nature, parent
      FROM temp,
           (...)
)
SELECT LEVEL AS lvl, a.*
 FROM a
START WITH a.parent IS NULL
CONNECT BY PRIOR a.id_subj = a.parent;

Upvotes: 1

Related Questions