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
Hanlon's Razor - Never attribute to malice that which can be adequately
explained by stupidity.
> -----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
>
> Hanlon's Razor - Never attribute to malice that which can be
> adequately
> explained by stupidity.
>
>
> > -----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
> >
> >
> >
> >
> > --------------------------------------------------------------
> > ----------------
> > Archives: http://www.mail-archive.com/[email protected]/
> > To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=list
s/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
----------------------------------------------------------------------------
--
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.
----------------------------------------------------------------------------
--
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.
----------------------------------------------------------------------------
--
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.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebarRsts&bodyRsts/cf_talk or send a message
to [EMAIL PROTECTED] with 'unsubscribe' in the body.
