> 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
I believe I understand what you've got there. What's the name of the table in the above? Once I srap my head around that, I should be able to get it to work in SQL Server too... Mine is searching maybe 30 fields in the database, so the amount (which may or may not be an entered search criteria) could be just one of many fields searched ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:308501 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
