Re: Parametizing "ORDER BY" clause of a cursor?
Date: Thu, 16 Jul 1998 22:18:40 GMT
Message-ID: <35b57be2.29206977_at_192.86.155.100>
A copy of this was sent to "michael t hudacko" <hudacko_at_bbdo.com> (if that email address didn't require changing) On 16 Jul 1998 17:22:33 GMT, you wrote:
>Hi Folks!
>
>I wrote an Oracle Webserver PL/SQL procedure that builds a four column
>table to return to a browser. Then I recognized the need to sort by each
>column within the browser. The table headers are hypertext links which
>call the procedure recursively, passing the column number as a parameter to
>the procedure. Thought this would work... it doesn't It appears that the
>ORDER BY clause is being ignored.
>
>
>procedure ais_report (ord IN NUMBER DEFAULT 1) as
>
>CURSOR rpt_cur (orderby number) is
>select one,two,three,four from table
>order by orderby;
>
>BEGIN
>
>OPEN rpt_cur(ord);
>.........
>
>I was thinking that the ORDER BY CLAUSE will be interpreted as either:
>ORDER BY 1
>ORDER BY 2
>ORDER BY 3
>ORDER BY 4
>representing the four different ordering choices: fields ONE, TWO,THREE, or
>FOUR as illustrated in the snipet above,
>
One way to do this is with decode, for example:
CURSOR rpt_cur (orderby number) is
select one,two,three,four from table
order by decode( orderby, 1, one, 2, two, 3, three, 4, four );
just make sure that one, two, three and four are 'type' compatible -- eg they are all numbers or all dates or all strings. if they are not the same, use to_date, to_number or to_char to make them the same.
>Thanks for any feedback
>hudacko_at_bbdo.com
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Jul 17 1998 - 00:18:40 CEST