RE: Sql Order by Question

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

This works:
SELECT case when f1<'0'
        then f1 end as Alpha,
       case when f1>='0'
        then cast(f1 as integer) end as Numeric
FROM tested/test1
ORDER BY Alpha, Numeric

Eric DeLong
Sally Beauty Company
MIS-Sr. Programmer/Analyst
940-898-7863 or ext. 1863



-----Original Message-----
From: DeLong, Eric [mailto:EDeLong@Sallybeauty.com]
Sent: Thursday, October 18, 2001 5:36 PM
To: 'midrange-l@midrange.com'
Subject: RE: Sql Order by Question


Tim,

How about (assumes EBCDIC):

Select case when substr(field1,1,1)<'0' then field1 else ' ' end as Alpha,
       case when substr(field1,1,1)>='0' then cast(field1) as integer else 0
end as Numeric
 Order by 1,2

The idea is to separate the alpha fields and the numeric fields into
separate columns, then sort over both columns.

Note: this is not tested, so be prepared to correct syntax problems.

Eric DeLong
Sally Beauty Company
MIS-Sr. Programmer/Analyst
940-898-7863 or ext. 1863



-----Original Message-----
From: Hatzenbeler, Tim [mailto:thatzenbeler@clinitech.net]
Sent: Thursday, October 18, 2001 4:40 PM
To: 'midrange-l@midrange.com'
Subject: Sql Order by Question


This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
--
[ Picked text/plain from multipart/alternative ]
Can this be done, via an sql call?


I have a field, it's 20 char long, left justified..

It has things like:
Accounting
Education
110
1101
220
2234


The problems is sorting... Because it's not a numeric field, and there are
are no '0's infront of the number to make them the same length or right
justified, their not sorted correctly...

Is there a way in a sql statement, to check the field to test if it's
numeric, and if its, a valid number, (cast) it as an integer, and sort it
that way?

Just looking for an idea...

thanks

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.