Re: Variable Order By clause

I may have spoken too soon... either that or I'm missing something obvious.

If I run the query Jan sent with ord1 = 1, and ord2 = 2 everything works fine. If I switch, and set ord1 = 2, and ord2 = 1 I get an error:

declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 13

I tried simplifying, and using only a single variable and a single decode. It seems that if I pass a value causing the decode to choose anything other than the first conditional I get the error.

So my new code based on Jan's suggestion produces the error: declare

   ord1 integer := 2;

   CURSOR c_Known_Issues IS

       SELECT
         seq_no,
         issue_type
      FROM   known_issues
      WHERE  system_id = 99
      ORDER BY 
       DECODE(ord1,1,seq_no,2,issue_type);
       

begin

   FOR known_Issue IN c_Known_Issues LOOP

      dbms_output.put_line(known_Issue.seq_No || '...'||known_Issue.issue_type);

   END LOOP; end;
.

What is going on?! And I thought this would be simple!

~ ~ David

Jan H Malmberg wrote:
>
> Try something like
>
> SELECT
> seq_no,
> issue_type
> FROM known_issues
> WHERE system_id = 99
> ORDER BY DECODE(ord1,1,seq_no,issue_type),
> DECODE(ord2,2,issue_type,seq_no);
>
> It can of course only handle predefined situations but it avoids dynamic
> sql.
>
> /JhMa

> Received on Wed Aug 11 1999 - 12:56:12 CDT