Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Hierarchical Query returns some wrong results

RanagalJul 8 2019 — edited Jul 12 2019

Hello experts,

DB Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

I have run into some weird issue on Oracle. The below is the dataset setup:

with

nodes(node_id, parent_node_id, object_id) as

(

    select 1, null, 100 from dual union all

    select 2, 1, 101 from dual union all

    select 3, 2, 102 from dual

),

attributes(object_id, at_name, value) as

(

    select 100, 'user_name', 'User1'  from dual union all

    select 100, 'User_id', '01'  from dual union all

    select 100, 'created_date', '20190708120212'  from dual union all

    select 101, 'user_name', 'User1'  from dual union all

    select 101, 'User_id', '01'  from dual union all

    select 101, 'created_date', '20190708120212'  from dual union all

    select 102, 'user_name', 'User1'  from dual union all

    select 102, 'User_id', '01'  from dual union all

    select 103, 'created_date', '20190708120212'  from dual union all

    select 103, 'user_name', 'User1'  from dual union all

    select 103, 'User_id', '01'  from dual

)

select * from nodes n join attributes at

on      n.object_id = at.object_id and

        at.at_name = 'User_id'

start with n.node_id = 1

connect by prior node_id = parent_node_id;

The above gives the correct result (Note: I get some random column names like QCSJ_C000000002700000, QCSJ_C000000002700001. I dont know what they really are. But result is correct).

But instead of the above if I put like

select *

from nodes n, attributes at

where n.object_id = at.object_id and at.at_name = 'User_id'

start with node_id = 1

connect by prior node_id = parent_node_id

order by 1;

I get some duplicate values. I dont know why I get them. Can someone please explain ? Thanks in advance.

Regards,

Ranagal

This post has been answered by Zlatko Sirotic on Jul 8 2019
Jump to Answer
Comments
Post Details
Added on Jul 8 2019
12 comments
1,007 views