This content has been marked as final. Show 6 replies
A few questions :
What's your database version? (select * from v$version)
What client tool are you using to run the query, and which version?
Does it work in SQL*Plus?
According to Oracle's documentation, that error is caused by having "ORDER SIBLINGS" clause in a query that doesn't have a "CONNECT BY" clause which is clearly not the case here. I suspect it's because you're using "LEAD" and "LAG" analytic functions. I took your statement, replaced the table and column names with some from a table I have which also has a hierarchical structure and got the same error. After removing all the "LEAD" and "LAG" functions it executed OK.
I don't suppose that helps you any.
It may take creating another table with the contents of this table adding 4 columns for rownum, level, lead(level), and lag(level). Then use that table to generate your unordered list html code.
Thanks Kendenny for your solution. I will work on as per your suggestion.
rather than putting it into a new table, I'd suggest doing the lag/lead in a subquery and then doing the connect by in the outer query.
I suspect it's because you're using "LEAD" and "LAG" analytic functions.Ken, you may be right about that.
There are some known bugs in 10g when mixing analytic functions and CONNECT BY clause.
The workaround (given by MOS) is :
alter session set "_optimizer_connect_by_cost_based" = false;
Thanks to Kendenny, Boneist and Odie.
Got the point that "Order Siblings by clause" cannot be used with connect by query with analytical function, Thanks Kendenny.
Yes, I now use main query and subquery, however the subquery be just "connect by" and have the all html tags added in the main query.
The below query is working now.
CASE WHEN LAG(mylevel,1,0) OVER (ORDER BY myrownum) >= mylevel THEN '<li>'
CASE LEAD(mylevel) OVER (ORDER BY myrownum)
WHEN mylevel THEN
CASE WHEN myrownum = 1 THEN '<ul id="sidebarmenu1" '
CASE WHEN myrownum =1 THEN '<ul id="sidebarmenu1"'
ELSE '<ul '
END ||' ><li>'
END ||'<a href="'||
CASE WHEN link_url IS NOT NULL THEN
ELSE '#"' END ||
CASE mylevel - LEAD(mylevel,1,1) OVER (ORDER BY myrownum)
WHEN -1 THEN NULL
WHEN 0 THEN '</li>'
ELSE REPLACE(LPAD('*', myleveL-LEAD(mylevel,1,1) OVER (ORDER BY myrownum),'*'), '*','</li></ul></li>')
CASE WHEN LEAD(mylevel,1,0) OVER (ORDER BY myrownum) = 0 THEN '</ul>'
ELSE NULL END unordered_List,
SELECT LEVEL mylevel, ROWNUM myrownum,daevmt.*
FROM dae_vs_my_tasks daevmt
CONNECT BY PRIOR daevmt.menu_id = daevmt.above_menu_id
START WITH daevmt.above_menu_id = 'TOPMENU'
ORDER SIBLINGS BY display_order
Odie, I tried altering the session for the flag, still the first query was not working.
Thanks again all for your great time in answering me.