Home » SQL & PL/SQL » SQL & PL/SQL » connect by - too many rows?
connect by - too many rows? [message #199143] Fri, 20 October 2006 09:39 Go to next message
SaraC
Messages: 81
Registered: August 2005
Member
Hi
I have created a query which I need to sort in a hierarchical order.
The query has an inner query comprising of a union - this inner query returns the results I want but not yet in the correct order.

I then added CONNECT BY PRIOR and ORDER BY SIBLINGS clauses and the query now returns too many rows - the rows I want are displayed in the correct order however there are another 16 rows returned after those - these have already appeared in the list of records but this time have a higher hierarchy level value (i.e. if the original row was level 2 it appears again with level1)

SELECT ..
FROM (
SELECT..
FROM
WHERE

UNION

SELECT
FROM
WHERE
)
CONNECT BY PRIOR..
ORDER SIBLINGS BY..

Cant help thinking that Ive missed a condition e.g. and after the connect by but Ive played about with this and still didnt get the expected results
Can anyone help. Please....
Sorry I was not able to add my actual query as an example but it is quite specific and complex and no good for discussion as an example

Thx



Re: connect by - too many rows? [message #199171 is a reply to message #199143] Fri, 20 October 2006 14:04 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Sara, there is no START WITH clause in your example. I'm also confused when you say the inner query has the results you want - with a hierarchical query you normally start with a small set of data (one row or a few rows) and then the hierarchical logic builds a tree from there.

Could you at least give an example with the sample EMP table showing what you are trying to do?
Re: connect by - too many rows? [message #199280 is a reply to message #199143] Mon, 23 October 2006 03:13 Go to previous messageGo to next message
SaraC
Messages: 81
Registered: August 2005
Member
I constructed the inner query first as it consists of a UNION of 2 queries. I was then trying to sort the results of the query using a connect by. It did sort the data into the correct order but then extra rows were returned after the expected rows in the expected order.

I will try starting the query from scratch, doing the connect by part first, not last - and see what happens.

Sara
Re: connect by - too many rows? [message #199283 is a reply to message #199143] Mon, 23 October 2006 03:34 Go to previous message
SaraC
Messages: 81
Registered: August 2005
Member
Thanks for the tip Todd, it works now - I added a start with clause and reconstructed the query from the bottom up as it were. And it worked!

Thought I had tried that but obviously hadnt set the starts with to the right value. Reading up on it today I got the correct value and now the query works and in the correct order!!!

thanks very much - spent hours on it on Friday and have now sorted it within half an hour today!
Previous Topic: oracle 10g purge all entries of a single table
Next Topic: constraint deferred
Goto Forum:
  


Current Time: Thu Dec 08 10:39:45 CST 2016

Total time taken to generate the page: 0.08432 seconds