Re: Oracle Sql-plus question

In a previous article, awdorrin_at_kocrsv01.delcoelect.com (Albert W. Dorrington) says:

> I thought I had figured this out once before, but I can't
>seem to remember right now.
> Is there a way using an sqlplus query to have an order by
>clause actually order the numbers in numeric order,
>(1, 2, ... 10, 19, 20, etc) instead of its strange ordering
>(1, 10, 19, 2, 20, etc)
>
> Thanks in advance,
> Al
>
>--
>Al Dorrington
>awdorrin_at_ictest.delcoelect.com Database Admin
>Delco Electronics - IC CIM Unix Sysadmin
>Kokomo, Indiana, USA Phone: 317.451.9655

If you're ordering on this column and you're getting the results you describe above, then the column's datatype isn't a binary NUMBER type but rather a CHAR or VARCHAR2 type.

You can modify your SELECT statement from

"SELECT text_num_col, ...ORDER BY text_num_col"

to instead read

"SELECT TO_NUMBER(text_num_col) NUM_COL, ... ORDER BY NUM_COL"

to fix the problem. Note the use of the alias NUM_COL so you can specify what data field is to be ordered.

Karla Johnson

-- 
+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
Karla Johnson                       |      Internet: karjohn_at_kincyb.com
S/W Engr., Informax Data Systems    |                  or ab803_at_lafn.org
Los Angeles, California             |   Standard disclaimers, ad nauseam