Right...CONCAT is MySQL specific. For SQL Server you'd do: WHEN "thousand" THEN dsp_amount + '000'
But then you'd have to cast it as an INT: WHEN "thousand" THEN CAST(dsp_amount + '000' AS int) -----Original Message----- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2008 11:48 PM To: CF-Talk Subject: Re: Finding a number in a range - sort of - problem Les Mizzell wrote: >> Here's the final version of what I came up with (MySQL specific): >> >> SELECT * >> FROM ( >> SELECT 1, >> CASE dsp_millthou >> WHEN "thousand" THEN CONCAT(dsp_amount, '000') >> WHEN "million" THEN CONCAT(dsp_amount,'000000') >> WHEN "billion" THEN CONCAT(dsp_amount,'000000000') >> END AS myValue >> FROM temp >> ) t >> WHERE myValue < 99123 SQL Server doesn't like "CONCAT" - but I think I've got my head around it enough to find the SQL Server code that will work for this now. Will post the result once I've got it working (unless somebody has a better idea first!) Thanks, Les ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:308504 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
