Re: Variable Order By clause

I suspect it's the decode() type trap.

the type of the decode() column is the
type of the result in the

    value, result
pair.

i.e. the type of the first column in
your order by IS the type of seq_no
irrespective of whether you ord1 is
set to 1 or 2.

Consequently Oracle is trying to
do a numeric sort on this column,
but when you switch the value of
ord1 to 2 then values of issue_type
which are (presumably) not numeric
in form appear, and oracle produces
an 01722 as it tries to convert them
to numeric.

Change the decode to something like:

    decode(ord1,

            0,null, -- force the type to character
            1,to_char(seq_no,'0000000000'),   -- or similar trick for
pushing
                                    --a numeric to an alpha which will sort
correctly
            2, issue_type

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

David Gilbert wrote in message <37B1B93C.42CAFA78_at_hotmail.com>...
>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);
>
Received on Wed Aug 11 1999 - 13:35:39 CDT