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

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"