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

You cannot use an order by with a union statement. Try enclosing your statement in brackets.

(
  SELECT * FROM logfile_h WHERE (ssn = '123456789') UNION SELECT * FROM   logfile WHERE (ssn = '123456789')
)
ORDER BY login_timestamp

Even using this method, the column you are ordering by needs to be in your sql statement.

e.g

SQL> l
  1 (select * from emp where empno=7934   2 union
  3 select * from emp where empno=7902)   4* order by empno
SQL> /
order by empno

         *
ERROR at line 4:
ORA-00904: invalid column name

But this works

SQL> (select empno, ename from emp where empno=7934   2 union
  3 select empno, ename from emp where empno=7902)   4 order by empno;

    EMPNO ENAME
--------- ----------

     7902 FORD
     7934 MILLER

HTH, Mark

David wrote in message <376e51a5_at_news2.one.net>...
>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.
>
>
>
Received on Mon Jun 21 1999 - 10:16:52 CDT