Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> SQLPlus ordering of data
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 likeit 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 Dorrington awdorrin_at_ictest.delcoelect.com Delco Electronics - IC CIM Database & Unix Administrator Kokomo, Indiana, USA Phone: 765.451.9655Received on Fri Apr 18 1997 - 00:00:00 CDT