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