Re: Variable Order By clause

From: Jan H Malmberg <jhma_at_gbg.ifsab.se>
Date: Wed, 11 Aug 1999 10:38:03 +0200
Message-ID: <7orcqa$sea$1@vega.lejonet.se>

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

David Gilbert <nepherim42_at_hotmail.com> wrote in message news:37B0973D.81B55B95_at_hotmail.com...
> Question:
> ---------
> 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
Received on Wed Aug 11 1999 - 03:38:03 CDT