RE: OT: Order By Position and UNION

From: Larry Elkins <elkinsl_at_flash.net>
Date: Thu, 12 Jul 2001 17:05:22 -0700
Message-ID: <F001.003495D0.20010712170106@fatcity.com>

Thanks for the response. I'm wondering if it has always been like that or if it changed with a recent version -- don't know since I have always used positional notation going back to V5 when I started using Oracle (and yes, I know people go back further than that -- that's not the point I was trying to make). Taking your example further, if I alias each column (in your case alias deptno and locid as empno), it will still work if I refer to empno in the ORDER BY. Not that our queries make much sense ;-)

Anyway, I think it's obvious why I used OT in the subject line -- we are talking about some trivial things here ;-) But, there is still the curiosity factor that made me want to know if it has always been this way or if this behavior was introduced in a particular version or point release. For example, in-line views were "there" in 7.1 but not official until 7.2 (I think those are the correct versions, it's been a while).

OK, I did a deja search since this is bugging me and at one point, based on posts I pulled up and the examples they provided, you were *required* to use positional. And as we see PL/SQL sometimes lagging SQL, there also were mentions of where referring to a column name was supported in SQL but not in PL/SQL, and references to in earlier versions where positional was required in both. But, none of the posts happened to mention what version of the DB.

So I now know that I'm not crazy, that positional was mandatory at some point. It's interesting what the manuals say and your examples showing the difference between unioning 2 or 3 selects, and then what happens with aliasing all to be the same. I'll stick with positional to be safe.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
-----Original Message-----
JRicard982_at_aol.com
Sent: Thursday, July 12, 2001 6:51 PM
To: Multiple recipients of list ORACLE-L

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>
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Jul 12 2001 - 19:05:22 CDT