Re: OT: Order By Position and UNION

From: <JRicard982_at_aol.com>
Date: Thu, 12 Jul 2001 15:47:19 -0700
Message-ID: <F001.003494E6.20010712155123@fatcity.com>

Larry,

You can use column name if you only have two statements with your set operators. However, any more than that you must user positional. See below.

1 select empno, ename from emp
2 union
3 select deptno, dname from dept
4 union
5 select locid, room from location
6* order by empno

SQL> /
order by empno
*

ERROR at line 6:
ORA-00904: invalid column name

SQL> select empno, ename from emp
2 union
3 select deptno, dname from dept
4 union
5 select locid, room from location
6 order by 1
7 /

    EMPNO ENAME

---------- --------------
       10 ACCOUNTING
       20 RESEARCH
       30 SALES
       40 OPERATIONS
       45 101
       46 202
       47 103
       48 105
       49 105
       50 404
       51 421
       52 211
       53 424
       54 402
       55 433
       56 217
       57 222
     7369 SMITH
     7499 ALLEN
     7521 WARD
     7566 JONES
     7654 MARTIN
     7698 BLAKE
     7782 CLARK
     7788 SCOTT
     7839 KING
     7844 TURNER
     7876 ADAMS
     7900 JAMES
     7902 FORD
     7934 MILLER
31 rows selected.

SQL> Received on Thu Jul 12 2001 - 17:47:19 CDT