Hierarchical query returning duplicate rows
514663Jun 13 2006 — edited Jun 16 2006Hi
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)