Re: ORDER BY

[Chuck Harkness <c.a.harkness@LARC.NASA.GOV>]

Hello,
  First of all, I want to thank everyone for your help.  I have received a
lot of excellent advice from everyone and I appreciate it.
  The data is a VARCHAR(2) type in Oracle and alphanumeric in MAXIMO.  The
data has both numbers and alpha numeric characters.  Here are examples of
the data format:
  1,2,F,F2,F3,101,102,1SL,1SM,1UP,LR1,LR2,MAT,108U04UCLH,40090, etc.

  Thanks,
    Chuck
>Chuck,
>
>Can you clarify the nature of the data in the column.
>Are ALL values of the form "digits, optionally followed by
>alphas"?  Or can there be multiple sequences of alphas
>interspersed with digits?  Are there any values that do
>NOT start with with digits?
>
>If the alphas are simply optional qualifiers for the digits,
>then I'm confident that we can come up something that will
>work.  Something like this:
>
>order by lpad(trim(translate(part_no,'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
>                                     '                          ')
>                  ,' '),10,' ')||
>              trim(translate(part_no,'0123456789','          '))
>
>Ray
>
>> Hello,
>>   Thanks for the info.  I have tried lpad,rpad,trim plus I tried using
>> translate and decode through Oracle, but nothing seems to work.  I believe
>> it has to do with the way Oracle does the order by because I get the same
>> results when executing sql statements in Oracle.  The table has about 1500
>> items in it now and will continue to grow.  Any suggestions?
>>
>> Chuck
>> At 03:43 PM 3/14/01 -0500, you wrote:
>> >Chuck,
>> >
>> >Try something similar to this:
>> >
>> >  ORDER BY lpad(part_no,w,' ')
>> >
>> >where part_no is the name of the column, and w is the maximum
>> >width of the part_no field.
>> >
>> >Ray
>> >
>> >> Hello!
>> >>   I am trying to use an order by clause on a char field, but the order
>> >> doesn't come out quite like the customer wanted.  For example, I have
the
>> >> values: 1,1SL,1SM,1UP,100,197,198,199 and 1000.  The order by clause
in SQR
>> >> produces the following order:
>> >>
>> >> 1
>> >> 100
>> >> 197
>> >> 198
>> >> 199
>> >> 1SL
>> >> 1SM
>> >> 1UP
>> >> 1000
>> >>
>> >> I would like the order to be 1,1SL,1UP,100,197,198,199,100.  Does anyone
>> >> have any suggestions?
>> >>
>> >> Thank you,
>> >>   Chuck
>> >>
>> >
>> >----------------------------------------------------------------------
>> >Ray Ontko   rayo@ontko.com   Phone 1.765.935.4283   Fax 1.765.962.9788
>> >Ray Ontko & Co.   Software Consulting Services   http://www.ontko.com/
>> >
>> >
>>
>
>----------------------------------------------------------------------
>Ray Ontko   rayo@ontko.com   Phone 1.765.935.4283   Fax 1.765.962.9788
>Ray Ontko & Co.   Software Consulting Services   http://www.ontko.com/
>
>