|
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.
Marc
|
|
|
|
|
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.
|
|
|
|
|
Oracle is the perfect tool for when a fully functioning RDBMS would be too affordable.
Slogans aren't solutions.
|
|
|
|
|
|
As already said, you're most likely experiencing a bug in which case the arguments of ora-600 would be interesting.
But as a quick fix, if you need recursion, why not use CTE (WITH clause)?
|
|
|
|
|
Unfortunately, our Oracle version is too old, 10g 
|
|
|
|
|
|
Mika Wendelius wrote: You do know that regular support has ended 2010 and even extended support ended 2013
Nope, but I'm not the one making those decisions. I'm just being told "you need to work for that customer now and that's their database".
I usually work in SQL Server 
|
|
|
|
|
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.
|
|
|
|
|
Jörgen Andersson wrote: A very simple workaround is usually to put the join inside a CTE.
That's my default as I'm really a SQL Server guy which only knows that syntax. Unfortunately, our Oracle version is old and doesn't support it yet.
The deepest hierarchy we have is about five levels deep. On a small data set, so performance is not much of a problem.
Jörgen Andersson wrote: Luckily I don't work with hierarchies anymore, as SQLServer sucks really badly at it.
So what do you do when you have hierarchies?
|
|
|
|
|
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.
|
|
|
|
|
Jörgen Andersson wrote: I mean that you should put the joined tables inside a CTE, and run the CONNECT BY PRIOR using the CTE as source
That's what I'm doing now. Use a CTE for the CONNECT BY PRIOR, get additional data by joining on the CTE and then looping through the records.
If it looks stupid and it works then it ain't stupid 
Jörgen Andersson wrote: I'll expand the answer tonight if you want me to, but first I need to get back from town.
Thanks, but don't trouble yourself. My vacation starts tomorrow and I won't be doing a lot of Oracle development in the new year anyway
|
|
|
|
|
Sander Rossel wrote: If it looks stupid and it works then it ain't stupid
Sander Rossel wrote: Thanks, but don't trouble yourself. My vacation starts tomorrow and I won't be doing a lot of Oracle development in the new year anyway
My answer actually wouldn't be Oracle specific.
|
|
|
|
|
Jörgen Andersson wrote: My answer actually wouldn't be Oracle specific.
In that case I'm interested
|
|
|
|
|
Sorry for not replying yet, but the scope grew out of proportion.
This will either turn into an article, or I'll need to start over from the beginning.
|
|
|
|
|
No problem, looking forward to it
|
|
|
|
|
Sporting gear, flyer perches on crude sphere (8, 3)
Slogans aren't solutions.
|
|
|
|
|
Sporting gear,
flyer BAT
perches on
crude BASE
sphere BALL
BASEBALL BAT
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
Ah The Beloved Weapon of Millwall fans visiting West Ham 
We can’t stop here, this is bat country - Hunter S Thompson RIP
|
|
|
|
|
Yes, isn't it curious how baseball bat sales in the UK are so much larger than the number of baseball players?
Slogans aren't solutions.
|
|
|
|
|
Isn't it amazing how many Lutonites own the bats, but don't have any balls?
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
That's the one. Well done.
Slogans aren't solutions.
|
|
|
|
|
Movie Quote Of The Day
Quote: I'm Virgil and I'm guidin' you through the gates of Hell. We are now in the Ninth Circle, the Circle of Traitors. Traitors to country! Traitors to fellow man! Traitors to GOD! You, sir, are charged with betrayin' the principles of all three! Quote for me the American Bar Association's Rules of Professional Conduct, Canon Seven.
Which movie?
Note that tomorrow will be the last MQOTD for this year and also the last one on a very regular basis. Ye be warned .
|
|
|
|
|
|
We had that one on September 2d 2014 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
