6 Replies Latest reply: Mar 8, 2011 1:49 AM by Nattu RSS

    hierarchical query - Error: ORA-30929: ORDER SIBLINGS BY clause not allowed

    Nattu
      Hi,

      I have a hierarchical query in which I generate the unordered list automatically. I needed to get the children ordered in the same level, so I used the order siblings by clause, but procedure did not compile and threw an error Ora-30929 - Order Siblings By Clause not allowed here.

      Here is the query.

      SELECT CASE WHEN LAG(LEVEL,1,0) OVER (ORDER BY ROWNUM) >= LEVEL THEN '<li>'
      ELSE CASE lead(level) OVER (ORDER BY ROWNUM) WHEN LEVEL THEN
      CASE WHEN ROWNUM = 1 THEN '<ul id="sidebarmenu1" ' ELSE '<ul' END ||'><li>'
      ELSE CASE WHEN ROWNUM = 1 THEN '<ul id="sidebarmenu1"' ELSE '<ul ' END ||' ><li>' END END||
      '<span>'||daevmt.short_menu_item||'</span>'||
      CASE LEVEL - LEAD(LEVEL,1,1) OVER (ORDER BY ROWNUM) WHEN -1 THEN NULL WHEN 0 THEN '</li>'
      ELSE REPLACE(LPAD('*', LEVEL-LEAD(LEVEL,1,1) OVER (ORDER BY ROWNUM),'*'), '*','</li></ul></li>')
      END ||CASE WHEN LEAD(LEVEL,1, 0) OVER (ORDER BY ROWNUM) = 0 THEN '</ul>' else null END unordered_list, daevmt.menu_item, daevmt.link_url, daevmt.menu_id,
      daevmt.above_menu_id
      FROM dae_vs_my_tasks daevmt
      START WITH daevmt.above_menu_id = 'TOPMENU'
      CONNECT BY PRIOR daevmt.menu_id = daevmt.above_menu_id
      ORDER SIBLINGS BY daevmt.display_order;

      Can any one please help what is wrong in my query?

      Thanks in Advance,

      Natarajan

      Edited by: Nattu on Mar 7, 2011 11:26 PM

      Edited by: Nattu on Mar 7, 2011 11:28 PM

      Edited by: Nattu on Mar 7, 2011 11:28 PM
        • 1. Re: hierarchical query - Error: ORA-30929: ORDER SIBLINGS BY clause not allowed
          odie_63
          Hi,

          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?
          • 2. Re: hierarchical query - Error: ORA-30929: ORDER SIBLINGS BY clause not allowed
            kendenny
            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.
            • 3. Re: hierarchical query - Error: ORA-30929: ORDER SIBLINGS BY clause not allowed
              Nattu
              Thanks Kendenny for your solution. I will work on as per your suggestion.
              • 4. Re: hierarchical query - Error: ORA-30929: ORDER SIBLINGS BY clause not allowed
                Boneist
                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.
                • 5. Re: hierarchical query - Error: ORA-30929: ORDER SIBLINGS BY clause not allowed
                  odie_63
                  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;
                  • 6. Re: hierarchical query - Error: ORA-30929: ORDER SIBLINGS BY clause not allowed
                    Nattu
                    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.

                    SELECT
                    CASE WHEN LAG(mylevel,1,0) OVER (ORDER BY myrownum) >= mylevel THEN '<li>'
                    ELSE
                    CASE LEAD(mylevel) OVER (ORDER BY myrownum)
                    WHEN mylevel THEN
                    CASE WHEN myrownum = 1 THEN '<ul id="sidebarmenu1" '
                    ELSE '<ul'
                    END ||'><li>'
                    ELSE
                    CASE WHEN myrownum =1 THEN '<ul id="sidebarmenu1"'
                    ELSE '<ul '
                    END ||' ><li>'
                    END
                    END ||'<a href="'||
                       CASE WHEN link_url IS NOT NULL THEN
                          link_url||'title="'||menu_item||'"'
                    ELSE '#"' END ||
                    '><span>'||short_menu_item||'</span></a>'||
                    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>')
                    END ||
                    CASE WHEN LEAD(mylevel,1,0) OVER (ORDER BY myrownum) = 0 THEN '</ul>'
                    ELSE NULL END unordered_List,
                    menu_item, menu_id,
                    above_menu_id
                    FROM (
                    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
                    ) t;


                    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.