OT: Order By Position and UNION

From: Larry Elkins <elkinsl_at_flash.net>
Date: Thu, 12 Jul 2001 14:40:17 -0700
Message-ID: <F001.003493C3.20010712144027@fatcity.com>

Listers,

<<<Start 8.1.7 SQL Manual from tahiti>>>

For compound queries (containing set operators UNION, INTERSECT, MINUS, or UNION ALL), the ORDER BY clause must use positions, rather than explicit expressions.

<<<End 8.1.7 SQL Manual from tahiti>>>

Now, against an 8.1.7 DB on WIN2K:

SQL> select deptno, loc from dept
  2 union
  3 select empno, ename from emp
  4 order by deptno --<<<<<< DEPTNO, not 1   5 /

    DEPTNO LOC
---------- -------------

        10 NEW YORK
        20 DALLAS
        30 CHICAGO
        40 BOSTON
      7369 SMITH
      7499 ALLEN

<snip>

I've always used positional notation in the ORDER BY on my queries using set operators, and, the snippet from the SQL manual still says you have to. The 9i manual at tahiti contains the same statement regarding positional notation. Now I've got someone telling me they *think* they were using expressions even back in 7.x. Now I'm going to play it safe and continue to use positional notation, but, I was wondering if anyone has any insight into this. I hadn't tried expressions in V7 and was curious if it was accepted even back then. Is this simply a documentation bug? There have been lots of enhancements to Oracle's SQL over the last few years so I could see them missing something.

Regards,

Larry G. Elkins
elkinsl_at_flash.net

-- 
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 - 16:40:17 CDT