Lewis,
I just thought of a few things. First, try this:
ORDER BY REPLICATE( '0', 10 - LEN(a.Clco))+ a.Clco
Replace 10 with a number that would be greater than your LONGEST string in
a.Clco. This way, all your values will have leading zeroes and should sort
appropriately.
If that doesn't work, Try this:
SELECT * FROM (
SELECT ....your columns here, and add this to your select list:
(CASE WHEN isNumeric(a.ClCo) THEN
convert(varchar(50),convert(numeric(8), a.ClCo)) ELSE a.ClCo END) as
my_string (or whatever you want to call it)
FROM ...
WHERE ....
)
ORDER BY my_string
Okay, so here's what this does....The SELECT query inside the parenthesis
will be your regular query along with this extra column. The case statement
will look at a.ClCo and if it is numeric, then it will convert it to numeric
(which will get rid of leading zeroes) and then convert it back to varchar
(which will make it a string). If it's NOT numeric (01DEMO), it will just
provide that. So you either get a string of an account number without
leading zeroes or the actual account number, if it's a string, in your new
column (I called it my_string). Now, all of this has to be in your regular
query, and you need to wrap that entire query with
SELECT * FROM ( ...your query here... ) ORDER BY my_string
This will allow you to order by that new column you created which should
work because all values will now be a string. Now you should get the 01DEMO
at the top since it has a leading zero, but everything else will NOT have
the leading zero, so they should sort normally.
Dave
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4104
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
