Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Dynamic order by without dynamic sql ?
Sameer Utrankar <sameeru_at_exchange.ml.com> wrote in message
news:38973469.90A28B00_at_exchange.ml.com...
> I have a huge select statement cursor that's a union. Sorting of the
> cursor resultset should be dynamic (ie order by column comes into the
> proc as input parameter). Usually I do this as decode. eg
>
> select
> customer_no, customer_name, address, phone etc
> from
> where
> order by
> decode(i_var, 'customer_no', 1,
> 'customer_name', 2)
>
> This works fine as long as statment is NOT UNION. When it's Union, plsql
> complains and expects order by to be ONLY COLUMN NUMBER (and doesn't
> allow decode, function etc).
>
You should move your query into subquery.
E.g:
select *
from
(Origin query with set operators) sub
order by col_name
--
Alex P. Zotov
Received on Wed Feb 02 2000 - 09:13:54 CST