Let me follow up to my last post, with more info from Joe Celko's "SQL
for Smarties":
Here's an example of why an ORDER BY in a UNION query is problematic:
SELECT a,b,c FROM TableA WHERE city = 'Boston'
UNION
SELECT x,y,z FROM TableB WHERE city = 'New York'
This query is fine (assuming that the datatype of "a" matches that of
"x", etc.), but what would the ORDER BY look like? The columns in the
result set are not named (a,b,c) nor (x,y,z); they have no names at all.
One solution has been to use column position:
ORDER BY 1
SQL-92 deprecates this, but allows the naming of the result set and its
columns via the AS operator:
SELECT a,b,c FROM TableA WHERE city = 'Boston'
UNION
SELECT x,y,z FROM TableB WHERE city = 'New York'
AS Cities (tom, dick, harry)
Now you can add an ORDER BY:
ORDER BY tom
If you're working with a database which doesn't allow the user of the
column position and doesn't allow use of the AS operator as shown above,
you can make a view from the plain UNION query and use the view's column
names.
-David
On Sun, 17 Sep 2000 00:06:08 -0700 David Shadovitz <[EMAIL PROTECTED]>
writes:
> Here's why:
>
> Joe Celko writes in "SQL For Smarties" (2nd Ed., p230) that refering
> to a
> column by its positional number is a deprecated feature in SQL-92,
> meaning that it will be unavailable in the next SQL standard.
>
> -David
>
> On Wed, 19 Jul 2000 16:48:13 +0200 Paul Wakefield <[EMAIL PROTECTED]>
> writes:
> > I was working in Oracle 7.3 - but I can't see why they'd disabled
> > that
> > facility on a later version.
> >
> > --
> > Paul Wakefield
> >
> >
> > > -----Original Message-----
> > > From: Olive, Christopher M Mr USACHPPM
> > > [mailto:[EMAIL PROTECTED]]
> > > Sent: Wednesday, July 19, 2000 4:46 PM
> > > To: '[EMAIL PROTECTED]'
> > > Subject: RE: ORDER BY in a UNION Query
> > >
> > >
> > > odd. last time i ported from informix (where i used the
> > > column position) to
> > > Oracle 8i, it barfed. strange. i'll have to try it again.
> > >
> > > Chris Olive,
> > > DOEHRS Website Administrator
> > >
> > > -----Original Message-----
> > > From: Paul Wakefield [mailto:[EMAIL PROTECTED]]
> > > Sent: Wednesday, July 19, 2000 10:14 AM
> > > To: '[EMAIL PROTECTED]'
> > > Subject: RE: ORDER BY in a UNION Query
> > >
> > >
> > > Um, I've written many union queries on Oracle, and that's how
> > > I've always
> > > handled the Order By. It also seems to work on SQL Server.
> > > Dunno about the
> > > rest of the DB world.
> > >
> > > --
> > > Paul Wakefield
> > >
> > >
> > > > -----Original Message-----
> > > > From: Olive, Christopher M Mr USACHPPM
> > > > [mailto:[EMAIL PROTECTED]]
> > > > Sent: Wednesday, July 19, 2000 4:10 PM
> > > > To: '[EMAIL PROTECTED]'
> > > > Subject: RE: ORDER BY in a UNION Query
> > > >
> > > >
> > > > as a side note, this will not work in all DBs (eg, oracle)
> > > >
> > > > Chris Olive,
> > > > DOEHRS Website Administrator
> > > >
> > > > -----Original Message-----
> > > > From: Paul Wakefield [mailto:[EMAIL PROTECTED]]
> > > > Sent: Wednesday, July 19, 2000 8:40 AM
> > > > To: '[EMAIL PROTECTED]'
> > > > Subject: RE: ORDER BY in a UNION Query
> > > >
> > > >
> > > > Try using column positions, rather than names, eg.
> > > >
> > > > (You'll need to substitute appropriate column positions)
> > > >
> > > >
> > > > <CFIF IsDefined('ordervalue')>
> > > > <CFIF ordervalue EQ "Name">
> > > > ORDER BY 2
> > > > <CFELSEIF ordervalue EQ "Absence">
> > > > ORDER BY 4
> > > > </CFIF>
> > > > </CFIF>
> > > >
> > > > --
> > > > Paul Wakefield
> > > >
> > > > > -----Original Message-----
> > > > > From: Angél Stewart [mailto:[EMAIL PROTECTED]]
> > > > > Sent: Wednesday, July 19, 2000 2:30 PM
> > > > > To: [EMAIL PROTECTED]
> > > > > Subject: SQL: ORDER BY in a UNION Query
> > > > >
> > > > >
> > > > > Hey folks :)
> > > > >
> > > > > I'm trying to write a simple UNION query, between two
> > > > > dynamically created
> > > > > select queries.
> > > > > Based on the feilds that the user entered in the previous
> > > > > form, the SQL
> > > > > statement's WHERE clauses are created.
> > > > >
> > > > > Basically, I want to pull data from the current Employee
> Info
> > > > > table, and the
> > > > > Employee History table, for a report.
> > > > >
> > > > > However, the user has the option of Ordering the report
> > > > > either by name, or
> > > > > by Absence Type.
> > > > >
> > > > > When I stick the Order By statement at the end of the second
>
> > > > > Select clause,
> > > > > Oracle returns the error:
> > > > > "[Microsoft][ODBC driver for Oracle][Oracle]ORA-00904:
> > > > > invalid column name "
> > > > >
> > > > > The column names Name and Reason *are* present in both
> > > > > tables. I tried to
> > > > > qualify these columns by using empinfo.name and
> > > > > empinfo.reason, but this has
> > > > > not helped.
> > > > >
> > > > > Do any of you have a sample SQL statement that includes
> UNION
> > > > > and an ORDER
> > > > > BY clause?
> > > > > :-)
> > > > >
> > > > > <CFIF IsDefined('ordervalue')>
> > > > > <CFIF ordervalue EQ "Name">
> > > > > ORDER BY empinfo.Name
> > > > > <CFELSEIF ordervalue EQ "Absence">
> > > > > ORDER BY empinfo.Reason
> > > > > </CFIF>
> > > > > </CFIF>
> > > > >
> > > > > (If order value is not selected, then the resulting
> recordset
> > > > > will be sorted
> > > > > according to Employee ID.)
> > > > >
> > > > > -Gel
________________________________________________________________
YOU'RE PAYING TOO MUCH FOR THE INTERNET!
Juno now offers FREE Internet Access!
Try it today - there's no risk! For your FREE software, visit:
http://dl.www.juno.com/get/tagj.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
