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

Poon Chak Yau

unread,

Jul 17, 1997, 3:00:00 AM7/17/97

to

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,

Michael Serbanescu

unread,

Jul 17, 1997, 3:00:00 AM7/17/97

to Poon Chak Yau

According to the SQL92 standard (and SQL89), the ORDER BY clause in
UNION, UNION ALL, INTERSECT and MINUS type of queries, is used to sort
the output of the query. One can specify both column names and integers
to indicate columns. However, if the output columns are unnamed, you
will have to use numbers. ORACLE does not have a way to name the output
columns, hence the requirement to use numbers.

For more information about the SQL92 standard, have a look at the book
"SQL Instant Reference" by Martin Gruber, published (in U.S.A.) by
Sybex.

Hope this helps.

Michael Serbanescu
---------------------------------------------------------------------

Chrysalis

unread,

Jul 17, 1997, 3:00:00 AM7/17/97

to

Poon Chak Yau wrote:
>
> 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,

Consider:
select a,b,c from X
UNION
select e,f,g from Y
order by ?

It is clear that a column name is not appropriate in the order by
clause. You *must* use position numbers, e.g. order by 3,1.

HTH
--
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"

Jim Gregory

unread,

Jul 17, 1997, 3:00:00 AM7/17/97

to

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