Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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