Re: Variable Order By clause

>Why can I not use variables in the ORDER BY clause of a PL/SQL cursor?
>Is there any way to produce the same effect in PL/SQL, without resorting
>to dymanic SQL?
>
>Background:
>-----------
>I'm trying to create a cursor that has a variable ORDER BY clause. The
>sample code below runs, but does not perform the ORDER BY clause
>correctly (it produces the same results as the query without the ORDER
>BY clause).
>
>Any ideas welcome,
>
>Thanks,
>
>~ ~ David
>
>
>declare
> ord1 integer := 1;
> ord2 integer := 2;
>
> CURSOR c_Known_Issues IS
> SELECT
> seq_no,
> issue_type
> FROM known_issues
> WHERE system_id = 99
> ORDER BY ord1, ord2;
>
>begin
>
> FOR known_Issue IN c_Known_Issues LOOP
>
> dbms_output.put_line(known_Issue.seq_No);
>
> END LOOP;
>
>end;

>Produces output:
>2
>1
>16
>15
>14
>13
>12
>11
>10
>9
>8
>7
>6
>5
>4
>28
>30
>31
>36
>37
>
>
>
>
>
>

Try using the decode statement - your order by does not need to use a 'pure' column...

   CURSOR c_Known_Issues (in_order_by varchar2) IS

      SELECT
         seq_no,
         issue_type
      FROM   known_issues
      WHERE  system_id = 99
      ORDER BY decode(in_order_by,'ORDER1,ord1,'ORDER2', ord2);
	

This will work in some situations (gets tricky with 2-colum order-bys, group bys etc). but you can try it.
:)

Dan Hekimian-Williams Received on Wed Aug 11 1999 - 01:04:54 CEST