You are here: Home ยป Using UNION ALL together with ORDER SIBLINGS BY in hierarchical SQL queries

Using UNION ALL together with ORDER SIBLINGS BY in hierarchical SQL queries

Posted on Thursday, October 23 2014 at 14:10 (Age: 3 yrs) | Category: SQL & Databases

In Hierarchical queries using the Oracle RDBMS - Determine ordering within each level, I showed how to sort the rows of a hierarchical SQL query within each tree level by using ORDER SIBLINGS BY instead of ORDER BY.

There is one detail to keep in mind, though: even the ORDER SIBLINGS BY is done on the overall result of the query, which can cause problems when using UNION ALL to take additional rows into the result:

SELECT 0, 0, 'xyz' FROM DUAL
UNION ALL
SELECT LEVEL, Id, substr( LPAD (' ', 3 * (LEVEL - 1)) || Label, 0, 30) Node
FROM TreeNode
START WITH Parent = 0
CONNECT BY PRIOR Id = Parent
ORDER SIBLINGS BY Label;
ERROR at line 7:
ORA-30929: ORDER SIBLINGS BY clause not allowed here

Using parantheses to explicitly group the second select within the UNION ALL does not help, but there is a simple solution: just wrap the hierarchical query into a sub select, and then do a SELECT * from the sub select:

SELECT 0, 0, 'xyz' FROM DUAL
UNION ALL
SELECT * FROM (
	SELECT LEVEL, Id, substr( LPAD (' ', 3 * (LEVEL - 1)) || Label, 0, 30) Node
	FROM TreeNode
	START WITH Parent = 0
	CONNECT BY PRIOR Id = Parent
	ORDER SIBLINGS BY Label
);
                 0                  0 'XYZ'
------------------ ------------------ ---------
                 0                  0 xyz
                 1                  1 Root Node
                 2                  2    Node 1
                 3                  3       Node 1.1
                 3                  4       Node 1.2
...

No comments

Be the first to post a comment!