Ang�l,
On a union query, the order by clause can only specify a field in the first
select query, not the second. I tried specifying a field in the second
select query and got an 'invalid column name' error too.
Worth a try.
Neil
> -----Original Message-----
> From: Ang�l Stewart [mailto:[EMAIL PROTECTED]]
> Sent: 19 July 2000 13:30
> 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?sidebarRsts&bodyRsts/cf_talk or send a message
to [EMAIL PROTECTED] with 'unsubscribe' in the body.
