Re: Why ORA-01785 ORDER BY item must be the number of a SELECT-list expression?

In unions ( or other set operations ) ORACLE does not require that the columns in the various queries all have the same names.  If they don't, what name would you put in the order by? ORACLE solves this by allowing you to use the column position instead of the column name.

e.g.

         select name, home_phone from contacts;   union

         select customer_name, business_phone from customers  order by 2;

HTH
Jim Gregory
==========Poon Chak Yau, 7/16/97==========

Hello,

     I have an union statement that have an order-by clause. However, I get error ORA-01785. Do you know why it requires me to specify the order by column number instead of column name? In fact, why Oracle ask for that? Is there any situation that column name is not possible?

Regards,

Expressed views and opinions are mine
and do not reflect those of my employer or clients

Jim Gregory Received on Thu Jul 17 1997 - 00:00:00 CDT