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=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.
