This seems to work as long as it doesn't encounter the 01DEMO account number.
ORDER BY (CASE WHEN isNumeric(a.ClCo) = 1 THEN CONVERT(numeric(8), a.ClCo) ELSE a.ClCo END) Then I get this error: The value "01DEMO" cannot be converted to a number But, I think, logically, it should work anyway. It seems to see 01DEMO as numeric in the first statement, but then can't convert it to a number. Thanks, Lewis -----Original Message----- From: Dave Phillips <[EMAIL PROTECTED]> To: cf-newbie <[email protected]> Sent: Wed, 29 Oct 2008 2:04 pm Subject: RE: Numerical order of query results Yes, your parenthesis were correct. You have to remember though...using sNumeric() in the SQL statement means you are using a COMPLETELY DIFFERENT UNCTION than the IsNumeric you use in an IF statement in ColdFusion. It's SQL Server function versus a ColdFusion function. In any case, try the option I told you for Isnumeric() = 1. Also, I did NOT eceive the example of the data. Don't know why, but it didn't come hrough. Dave -----Original Message----- rom: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] ent: Wednesday, October 29, 2008 12:31 PM o: cf-newbie ubject: Re: Numerical order of query results Hi David, I thought that the data you mentioned was an example of how the data was eing displayed and then how it should be displayed. I sent that. And, I hink Option 1 included adding zeros, which will not always work, as I xplained. I know that I can get IsNumbric() to work in an If statement, ut so far not in a query. Did my query look like it was right? Were the arenthisis in the right places? Thanks, ewis ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:4114 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
