Xref: alice comp.databases.oracle.server:83068 Path: alice!news-feed.fnsi.net!newsfeed.icl.net!newsfeed.icl.net!bignews.mediaways.net!fu-berlin.de!news.uar.net!carrier.kiev.ua!news.lucky.net!not-for-mail From: "Alex P. Zotov" Newsgroups: comp.databases.oracle.server Subject: Re: Dynamic order by without dynamic sql ? Date: Wed, 2 Feb 2000 17:13:54 +0200 Organization: unknown Lines: 30 Sender: news-server@lucky.net Distribution: world Message-ID: References: <38973469.90A28B00@exchange.ml.com> Reply-To: alex@soft-review.kiev.ua Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: news.lucky.net 949507393 12907 193.193.193.102 (2 Feb 2000 16:03:13 GMT) X-Complaints-To: usenet@news.lucky.net NNTP-Posting-Date: 2 Feb 2000 16:03:13 GMT X-Return-Path: alex@soft-review.kiev.ua Sameer Utrankar wrote in message news:38973469.90A28B00@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