Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
|
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 1st, 2004, 11:34 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,110
Thanks: 0
Thanked 3 Times in 3 Posts
Default Dynamic Order By

If I'm UNIONing 2 queries can I use a dynamic ORDER BY like this?

order by decode(sortby,'1',substr(last_name||', '||first_name||' '||mi,1,24),
                              '2',styp_code,
                              '3',fund_code);

**2 columns on the 2nd query are different than the 1st. and it just so happens to be what I'm trying to sort by.

I get this error "order by item must be the number SELECT-list expression".
And tried:
Cause: A column alias was used in the ORDER BY clause of a SELECT statement that uses a UNION, INTERSECT, or MINUS set operator. This is not allowed. In such cases, expressions in the ORDER BY clause must be unsigned integers that designate the ordinal positions of select-list items.

Action: Change the alias in the ORDER BY clause to an unsigned integer that designates the ordinal position of the select item in question.

I tried:
order by decode(sortby,'1',3,
                              '2',4,
                              '3',8);
but it gives the same error message.


 
Old December 1st, 2004, 11:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,110
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Also, without the UNION and the 2nd query the ORDER BY works fine. And if I remove the decode while still using the UNION command and just do ORDER BY 3,4,8 it works fine too.






Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamic Columns & Dynamic Grouping ??? nileshgambhava BOOK: Professional SQL Server 2005 Reporting Services ISBN: 0-7645-8497-9 1 March 24th, 2008 07:58 AM
Dynamic sort order or sort datatype kapy_kal XSLT 2 September 18th, 2007 02:10 PM
help writing dynamic form data to dynamic table ublend SQL Server ASP 1 June 1st, 2007 08:09 AM
help writing dynamic form data to dynamic table ublend Classic ASP Professional 1 June 1st, 2007 08:08 AM
document node order vs sort node order. ladyslipper98201 XSLT 2 June 5th, 2003 11:06 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.