Re: SQLPlus ordering of data

Albert W. Dorrington wrote:
>
> Hi All,
>
> I've been playing at this for a few hours, and have not
> been able to determine a solution to my problem.
> I have a VARCHAR2 column in a database that contains
> numbers, and the character string 'N/A'.
>
> I'd like to determine an order by statement which
> would allow the values to be ordered such as:
>
> N/A N/A
> 1 1
> 2 14
> 3 15
> 4 2
> 5 instead of: 20
> 6 22
> 14 3
> 15 4
> 20 5
> 22 6
>
> I've tried an order by to_number(column_name) but
> get an error when Oracle tries to convert the N/A to a number.
> I've been reading the manuals, and nothing seems like
> it will work for this problem. Other than piping the output of
> my sql to a external perl program to parse and reorder the
> output.
> Does anyone have any suggestions?
>
> Thanks in advance,
>
> - Al
>
> --
> Al Dorrington awdorrin_at_ictest.delcoelect.com
> Delco Electronics - IC CIM Database & Unix Administrator
> Kokomo, Indiana, USA Phone: 765.451.9655

Try:
order by decode(col_name,'N/A',-1,to_number(col_name))

Chrysalis Received on Sat Apr 19 1997 - 00:00:00 CDT