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
[EMAIL PROTECTED]
-----Original Message-----
[EMAIL PROTECTED]
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
