Re: How to order strings as numbers

A copy of this was sent to Guenther Ohlhausen <Guenther.Ohlhausen_at_iao.fhg.de> (if that email address didn't require changing) On Tue, 03 Aug 1999 17:39:00 +0200, you wrote:

>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;
>
>

much better to use NULL here I think. that way you do not have to convert anything to char and convert it again into a number.

select dummy1, dummy_2, ...., dummy_n from table_b UNION ALL
select dummy1, dummy_2, ...., to_number(null) from table_a;

I used UNION ALL instead of UNION. If you can, you might consider that as well. UNION ALL simply concatenates the first select with the second. UNION runs the first select and then adds to it anything from the second select NOT ALREADY in the first select (eg: lots of sorts and compares with UNION but not with UNION ALL). Here is the big difference between union and union all:

SQL> select 1 from dual
  2 union
  3 select 1 from dual;

         1

         1

SQL> l
  1 select 1 from dual
  2 union all
  3* select 1 from dual
SQL> /          1

         1
         1

>Now I need an ORDER BY dummy_n which orders like numbers, which means:
>
>asc: 'INFINITY', '-1', '-.4', '0', '.2', '1' and viceversa desc.
>

if you create the view as I did above, you can:

select * from theView order by nvl(dummy_n,-99999999999999999999999999999)

>thanks in advance.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Aug 03 1999 - 11:24:50 CDT