Re: Does Decode work in a REF CURSOR?

Hi Sean,

You may use negative value of TICKET.TKT_ID in asc order by mode to provide desc one.
For example, a sort of

ORDER BY  decode(v_OrderBy,     'A', TICKET.TKT_ID, null),
          decode(v_OrderBy,     'D', -TICKET.TKT_ID, null);

I dont know exactly both datatype and contents of TICKET.TKT_ID column, so in example I have assumed it as number.

I hope this idea may solve your problem regardless of other questions.

Regards Alexander Medvedev

Sean wrote:

> Platform : Oracle 8.1.5 on NT
>
> Scenario: Package body that returns a REF CURSOR. The sql statement
> has at the end of it :
> ORDER BY decode(v_OrderBy, 'A', TICKET.TKT_ID, null) asc,
> decode(v_OrderBy, 'D', TICKET.TKT_ID, null) desc;
>
> (where v_OrderBy is either A or D)
>
> More Info: If I run the package in SQL Plus passing in a parameter of
> D for descending, I ALWAYS get the order as the same which is ascending.
>
> So I took the SQL statement, changed the v_OrderBy by hand and ran THAT
> within SQL Plus - I got the results I was looking for.
>
> Question : Does a REF CURSOR see decode? Or is that an SQL PLus
> thing? Or is there a "bug" in the PL/SQL engine for version 8.1.5 ?
>
> Thank you.
> Sean
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
  Received on Sat Dec 09 2000 - 02:29:17 CST