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/[email protected]/
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.
