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 returning duplicate rows

514663Jun 13 2006 — edited Jun 16 2006
Hi

I am running a hierarchical query and it is returning duplicate results and I cannot see why. I can of course use DISTINCT, but I would rather know where I am going wrong in my SQL so I don't need to use DISTINCT.

MyTable is:

ID PARENT_ID LOCALE_ID
---------------------- ---------------------- ----------------------
2            1            25
3            1            25
4            1            25
5            1            25
6            2            25
6            2            64
7            3            25
7            3            64
8            3            25
8            3            64
9            4            25
9            4            64
10            9            25
11            10            25

My Query is

SELECT LEVEL, ID, PARENT_ID, LOCALE_ID
FROM MyTable
WHERE LOCALE_ID = 25
START WITH PARENT_ID = 1
CONNECT BY PRIOR ID = PARENT_ID
ORDER BY ID;

And the results I get are:

LEVEL ID PARENT_ID LOCALE_ID
---------------------- ---------------------- ---------------------- ----------------------
1            2            1            25
1            3            1            25
1            4            1            25
1            5            1            25
2            6            2            25
2            7            3            25
2            8            3            25
2            9            4            25
3            10            9            25
3            10            9            25
4            11            10            25
4            11            10            25

All fine until it reaches IDs 10 + 11 where it duplicates the result.

As I say, DISTINCT works, but I dont want to use this as a hack as I want to know why this is happening and how I can fix it.

I am on 10.2.0.1.0

TIA

Message was edited by:
IngramIFP (added spaces to results)
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 14 2006
Added on Jun 13 2006
5 comments
2,203 views