ORDER BY With Set Operators

In 9iR2, when we use ORDER BY with SET operators(UNION/UNION ALL/INTERSECT/MINUS), the naming rules is very confusing and appreciate if anyone can clarify:

According to SQL Reference:

1. Must use position notation i.e. ORDER BY 1. We cannot use 'ORDER BY colname'.

By personal testing with 9iR2:

1. If there is >=2 SET operators, the above rule is true e.g. select col1,col2,col3 from a UNION ALL select a,b,c from b MINUS select i,j,k from c order by 1

 and 'order by colname' => error

2. if there is only 1 SET operator, we can use names instead of position so long as the number and type of the 2 select list columns are matching: select col1, col2 from a UNION ALL
 select a,b,c from b
order by col1 okay

So, why the difference? Which rule should we follow? Received on Mon Feb 28 2005 - 06:18:52 CST