The Lounge is rated PG. If you're about to post something you wouldn't want your
kid sister to read then don't post it. No flame wars, no abusive conduct, no programming
questions and please don't post ads.
Check the trace files for more info on what may be causing this error.
I've fixed it using a loop, much much much faster in my case. I can't access the server directly, I don't know how to get it using SQL Developer and so I'd have to ask around and then learn how to use/read trace files.
I only use Oracle for this specific customer, otherwise I use SQL Server. I've heard so much about Oracle, it was supposed to be the RDBMS heaven, justifying its expensive price. So far it's done nothing than disappoint
I've had errors like that in the past doing XML manipulation -- the SQL statement parser can get very lost, confused, and messy, and in particular, I experienced getting different (wrong) results simply by changing the order of select columns / where clauses -- keep in mind this was all related to XML queries.
Anyways, does this[^] help? See example lower down for CONNECT BY ROOT.
It works fine in the SELECT clause, apparently it works in ways that don't allow for it to be used in JOINS or WHERE clauses. I've tried it with other queries that did not crash and disconnect me, but I still got the error that I needed a CONNECT BY PRIOR clause while I most certainly had that.
Without seeing your actual query I can't say for sure, but connect by prior and joins doesn't cooperate well. That's quite well documented by the way. And fixed in later versions as well. Certain constructs are still not allowed, but then you'll get a proper error message.
A very simple workaround is usually to put the join inside a CTE.
Regarding the performance, you should always add two composite indexes on the table you're running CONNECT BY PRIOR on. One on (Id,ParentId) and one on (ParentId,Id).
Secondly, you should keep in mind that Connect by Prior does not behave like it's set based. You can compare it with a recursive CTE depth first instead of breadth first. So in this case the power comes at the cost of speed.
But what a power it is! Luckily I don't work with hierarchies anymore, as SQLServer sucks really badly at it.
Yes I know about the HierarchyID, and how someone could be allowed to implement a path enumeration model instead of an adjacency list or a nested set model, without getting flogged, is beyond my comprehension.
I'm not talking about recursive CTE's. I mean that you should put the joined tables inside a CTE, and run the CONNECT BY PRIOR using the CTE as source, alternately put the CONNECT BY PRIOR inside a CTE and then join using this CTE as a source table. Whatever fits the requirements the best.
Sander Rossel wrote:
So what do you do when you have hierarchies?
Depends on size, whether it's fairly static or have frequent insertions, what kind of queries you need to do (subtrees, paths, lowest common ancestor)...
I have even used the path enumeration model since it's fairly humanly readable. It's just so prone to f***ups.
I'll expand the answer tonight if you want me to, but first I need to get back from town.