Re: How to order strings as numbers

You could do something like:

select column_1, ...., decode(dummy_n,null,'-Infinity',dummy_n) colum_n from
(

select column_1, ... , dummy_n from tableB union all
select column_1, ... , to_number(null) from tableA )
order by decode(dummy_n,null,1,2), dummy_n /

This leaves the dummy output column_n a character type, but sorted in numeric order, but with '-INFINITY' appearing before all other values.

--

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

    Guenther Ohlhausen wrote in message = <37A70D14.8F4581C9_at_iao.fhg.de>...

    Hi everyone,
    I've got two equal tables, except that the column 'dummy_n' is = missing in table_a.I need to select the string '-INFINITY' as a = pseudocolumn!

    In table_b, the datatype of dummy_n is number, and the values are in =
(-1, 1). So I used TO_CHAR to get the same datatypes for the UNION.

    (I Don't know who the hell designed these two tables.)

    So I get the following:

    select dummy1, dummy_2, ..., '-INFINITY' AS dummy_n from table_a     union
    select dummy1, dummy_2, ..., TO_CHAR(dummy_n) as dummy_n from = table_b;       

    Now I need an ORDER BY dummy_n which orders like numbers, which = means:

    asc: 'INFINITY', '-1', '-.4', '0', '.2', '1' and viceversa desc. Received on Tue Aug 03 1999 - 11:21:02 CDT