Re: Problem sorting results of a Union query passed to Oracle

A copy of this was sent to "David" <davidbr93_at_hotmail.com> (if that email address didn't require changing) On Mon, 21 Jun 1999 10:54:43 -0400, you wrote:

>I'm having a problem sorting the result of a UNION query. I'm using Oracle'
>s built in OLE controls within Visual Basic to pass the following statement
>when creating a dynaset.
>
>SELECT * FROM logfile_h WHERE (ssn = '123456789') UNION SELECT * FROM
>logfile WHERE (ssn = '123456789') ORDER BY login_timestamp
>
>When I run this query, Oracle returns "Invalid Column Name". The query
>runs fine without the ORDER BY clause. It also runs fine if I use the ORDER
>BY with only part of the select as in "SELECT * FROM logfile_h WHERE (ssn =
>'123456789') ORDER BY login_timestamp".
>
>Any input appreciated.
>
>Dave B.
>
>

use positional order by's with unions. for example, if login_timestamp is the 1'st column in the "*" (btw: it can be deadly to do a select "*" in a program -- you really should select c1, c2, c3, ... and so on) then you would "order by 1"

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jun 21 1999 - 10:10:09 CDT