obrienjimmy
asked on
Connect By statement returning duplicate rows
I'm trying to use a connect by statement to order a table in a hierarchical format.
Each row has an EntityKey and a ParentEK. Below is just example data to explain what I'm trying to do.
TABLE:
Objkey ObjId ParKey
1 Object1 0
2 Object2 1
3 Object3 1
4 Object4 1
5 Object5 2
6 Object6 2
7 Object7 2
8 Object8 3
9 Object9 3
E.g. query:
SELECT * FROM objTable
START WITH ParKey = 0
CONNECT BY PRIOR ObjKey= ParKey
RESULT:
Objkey ObjId ParKey
1 Object1 0
2 Object2 1
5 Object5 2
5 Object5 2
5 Object5 2
6 Object6 2
6 Object6 2
6 Object6 2
7 Object7 2
7 Object7 2
7 Object7 2
3 Object3 1
8 Object8 3
8 Object8 3
8 Object8 3
9 Object9 3
9 Object9 3
9 Object9 3
4 Object4 1
These extra rows are not in the database. I've no idea why the query is repeating them. I have used a distinct in the query to get the correct results:
SELECT DISTINCT ObjKey, ObjId, Parkey FROM objTable
START WITH ParKey = 0
CONNECT BY PRIOR ObjKey= ParKey
Objkey ObjId ParKey
1 Object1 0
2 Object2 1
5 Object5 2
6 Object6 2
7 Object7 2
3 Object3 1
8 Object8 3
9 Object9 3
4 Object4 1
But this is not the way I want to approach it. Mainly because I have a CLOB column that I need to return occasionally and DISTINCT doesnt work with CLOBS, but also because I would like to know what is the right way to do this.
Obviously my table and query are not as simple as above. The actual query is:
Really appreciate help on this, has been bugging me for ages.
Each row has an EntityKey and a ParentEK. Below is just example data to explain what I'm trying to do.
TABLE:
Objkey ObjId ParKey
1 Object1 0
2 Object2 1
3 Object3 1
4 Object4 1
5 Object5 2
6 Object6 2
7 Object7 2
8 Object8 3
9 Object9 3
E.g. query:
SELECT * FROM objTable
START WITH ParKey = 0
CONNECT BY PRIOR ObjKey= ParKey
RESULT:
Objkey ObjId ParKey
1 Object1 0
2 Object2 1
5 Object5 2
5 Object5 2
5 Object5 2
6 Object6 2
6 Object6 2
6 Object6 2
7 Object7 2
7 Object7 2
7 Object7 2
3 Object3 1
8 Object8 3
8 Object8 3
8 Object8 3
9 Object9 3
9 Object9 3
9 Object9 3
4 Object4 1
These extra rows are not in the database. I've no idea why the query is repeating them. I have used a distinct in the query to get the correct results:
SELECT DISTINCT ObjKey, ObjId, Parkey FROM objTable
START WITH ParKey = 0
CONNECT BY PRIOR ObjKey= ParKey
Objkey ObjId ParKey
1 Object1 0
2 Object2 1
5 Object5 2
6 Object6 2
7 Object7 2
3 Object3 1
8 Object8 3
9 Object9 3
4 Object4 1
But this is not the way I want to approach it. Mainly because I have a CLOB column that I need to return occasionally and DISTINCT doesnt work with CLOBS, but also because I would like to know what is the right way to do this.
Obviously my table and query are not as simple as above. The actual query is:
select * from (
SELECT Fullwf, Bftype, fType, Bflevel,EntityKey,ParentEK,Customid,Description
FROM mbrcalcbreakdown
START WITH ParentEK = 0
CONNECT BY PRIOR EntityKey = ParentEK
ORDER BY fullWF
) where bftype IN ('Calculation','Reference')
Really appreciate help on this, has been bugging me for ages.
ASKER
Wow, that's a really handy tool. Must remember to use that.
Sorry, I should have clarified..... I didnt think there would be any problem with the example I gave. That was just to show what I was trying to do and the type or result I was getting.
The table in question is a global temporary table that has about 15 columns... I didnt want to post up all that info.
I was hoping there may be some glaringly obvious issue with the way I was querying that could be fixed.
I'll see if I can get a working model into the sqlfiddle site with the problem occuring
Sorry, I should have clarified..... I didnt think there would be any problem with the example I gave. That was just to show what I was trying to do and the type or result I was getting.
The table in question is a global temporary table that has about 15 columns... I didnt want to post up all that info.
I was hoping there may be some glaringly obvious issue with the way I was querying that could be fixed.
I'll see if I can get a working model into the sqlfiddle site with the problem occuring
ASKER
Okay, this is very difficult. That site only allows very small samples unfortunately.
yes, 8000 chars only for the 'schema' I'm afraid, but shouldn't need all 15 columns perhaps.
ASKER
And I can't repeat the problem with small sample size. I removed a load of columns and only brought in a small subset of the data. But when I do this, the query works fine.
These duplicate rows have to be coming from somewhere though. I know 100% that they are not in the database as duplicate rows
Is there any other way to ensure the rows are distinct, but will allow me to work with CLOB fields too?
These duplicate rows have to be coming from somewhere though. I know 100% that they are not in the database as duplicate rows
Is there any other way to ensure the rows are distinct, but will allow me to work with CLOB fields too?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It sounds to me like one way or another, your global temporary table has some "duplicate" rows that are not expected. Because this is a global temporary table, it is a bit harder to verify than with a permanent table, since the contents of it change every time it is loaded. I suspect if you carefully review the rows in the global temporary table, you will find the duplicates there.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all the replies. I wont get back to this issue till the morning, but I'll look into doing the distinct as a sub query, then joining. It will solve the problem for now. When I've more time I'll take a closer look to see if there are issues in the table itself somewhere.
e.g. see you sample in Ora 11g at: http://sqlfiddle.com/#!4/02ae1/3
what is your Oracle version?