"connect by prior" requires a strict single parent, one or multiple child relationships.
I have some double parent, single child relationships in a table I'm querying. I've excluded those rows in my criteria but I get an error because it doesn't like the idea that they exist (even though they're outside the criteria).
Without the data, query, and error, it's a little difficult to diagnose.
But my guess would be that you are making assumptions about the order of execution that are not true. In other words, you are assuming that the WHERE clause will be processed before the CONNECT BY, which is most likely turning out not to be the case.
If I subquery (select from select) the dataset with valid relationships, everything's fine. If I drop the subquery and depend on the criteria to limit the returned data, it errors saying that there is a relationship integrity problem (2 parents for 1 child).
It doesn't make any sense to check for relationship integrity across the entire table first.
Reply With Quote
Bookmarks