Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > Oracle
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 1st, 2004, 11:34 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,111
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.


  #2 (permalink)  
Old December 1st, 2004, 11:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,111
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.

 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 11:27 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.