Home » SQL & PL/SQL » SQL & PL/SQL » Order By
Order By [message #299906] Wed, 13 February 2008 10:38 Go to next message
abis123
Messages: 31
Registered: February 2007
Member
I have created a package and package body to return a search. When displaying the search results I want the table headers to sort my results, to do this I am passing the column name in a variable but when I do this the order by does not work. Can anyone help?

as
  PROCEDURE GET_SEARCH_RESULTS (
    i_sort_by IN VARCHAR2,
    o_cursor OUT T_CURSOR
  )
    
  IS 

  BEGIN    
  OPEN o_cursor FOR 
   	       
    SELECT M.REFERENCE,
      M.CALLER_NAME,
      FROM LADO.MAIN M
      ORDER BY i_sort_by
      ;
     
  END GET_SEARCH_RESULTS;

END;


Passing M.CALLER_NAME into i_sort_by.
Re: Order By [message #299910 is a reply to message #299906] Wed, 13 February 2008 11:18 Go to previous messageGo to next message
moshea
Messages: 51
Registered: February 2008
Location: Dublin, Ireland
Member
Dynamic SQL is likely your friend.

Michael
Re: Order By [message #299911 is a reply to message #299910] Wed, 13 February 2008 11:31 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Dynamic SQL also has disadvantages. There is no syntax check on compile time, so when it breaks it breaks on run time, the built-in way to track dependencies doesn't work, plus it opens you up for SQL injections.

You could re-write the SQL to :

SELECT M.REFERENCE,
       M.CALLER_NAME,
  FROM LADO.MAIN M
 ORDER BY decode(i_sort_by,
        'M.REFERENCE'   ,M.REFERENCE,
        'M.CALLER_NAME' ,M.CALLER_NAME,
         M.REFERENCE)
      ;


if it's a manageable number of columns.
Re: Order By [message #301376 is a reply to message #299911] Wed, 20 February 2008 06:31 Go to previous messageGo to next message
abis123
Messages: 31
Registered: February 2007
Member
When I used
SELECT M.REFERENCE,
       M.CALLER_NAME,
  FROM LADO.MAIN M
 ORDER BY decode(i_sort_by,
        'M.REFERENCE'   ,M.REFERENCE,
        'M.CALLER_NAME' ,M.CALLER_NAME,
         M.REFERENCE)
      ;

I often got the error ORA-01722: invalid number, is there any reason for this?
Re: Order By [message #301384 is a reply to message #301376] Wed, 20 February 2008 06:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to cast all returned values of decode to the same type (here string).

Regards
Michel
Re: Order By [message #301397 is a reply to message #301384] Wed, 20 February 2008 08:02 Go to previous messageGo to next message
abis123
Messages: 31
Registered: February 2007
Member
If I am to do this, I will convert them to strings, but is there any way of correctly ordering by datetimes? Many thanks.
Re: Order By [message #301400 is a reply to message #301397] Wed, 20 February 2008 08:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
yyyymmdd hh24:mi:ss will yield an order-able string

[Updated on: Wed, 20 February 2008 08:11]

Report message to a moderator

Re: Order By [message #301406 is a reply to message #301400] Wed, 20 February 2008 08:32 Go to previous message
abis123
Messages: 31
Registered: February 2007
Member
Absolutely fantastic! Many thanks!
Previous Topic: sql query beteem two times (merged)
Next Topic: materialized view hlp
Goto Forum:
  


Current Time: Sun Dec 04 04:41:23 CST 2016

Total time taken to generate the page: 0.05464 seconds