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

From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1997/07/17
Message-ID: <33CDAD06.2EAD@postoffice.worldnet.att.net>#1/1

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

---

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,
  Received on Thu Jul 17 1997 - 00:00:00 CDT