This content has been marked as final. Show 6 replies
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 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.