0

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.

Alessandro Cucina
  • 148
  • 1
  • 12
  • 2
    Seems to work for me, dummying up the inner query results from what you posted - I get two rows back. Just to be clear, where the inner query shows NULL that is actually null, not the string 'NULL', and not a `char` or other padded string value? – Alex Poole Apr 05 '17 at 16:15
  • 1
    You need to post sufficient code for us to understand your problem. If your whole query is too complicated to post here you need to work up a small test case which reproduces the problem. – APC Apr 05 '17 at 17:06
  • @Alex Poole: yes, those are NULL RAW values. I put the word "NULL" just to make clear it is not blank. I could add that I've checked they are recognized as NULL using UTL_RAW.compare. – Alessandro Cucina Apr 06 '17 at 07:21

1 Answers1

1

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;
B Samedi
  • 380
  • 3
  • 11