Les Mizzell wrote: > SELECT id, > resp_atty, > dsp_name, > CASE > WHEN dsp_millthou='thousand' THEN CAST(dsp_amount + '000' AS int) > WHEN dsp_millthou='million' THEN CAST(dsp_amount + '000000' AS int) > WHEN dsp_millthou='billion' THEN CAST(dsp_amount + '000000000' AS int) > END as thisVALUE > FROM myTABLE > WHERE thisVALUE > #req.fromVALUE# > > > No error, but "thisVALUE" isn't getting set ...
This won't work because the WHERE will get processed before the SELECT. The workaround for that is to use a subselect like Andy describes. There are 2 other problems with this code: - the combination dsp_amount >= 5 and dsp_millthou = 'billion' will throw an error because an integer has a max value of 4 billion (in most databases); - this is not indexable. You should bite the bullet and write the full set of conditions: SELECT id, resp_atty, dsp_name, CASE WHEN dsp_millthou='thousand' THEN dsp_amount * 1000 WHEN dsp_millthou='million' THEN dsp_amount * 1000000 WHEN dsp_millthou='billion' THEN dsp_amount * 1000000000 END as thisVALUE FROM myTABLE WHERE (dsp_millthou='thousand' AND dsp_amount>#Int(req.fromVALUE/1000)#) OR (dsp_millthou='million' AND dsp_amount>#Int(req.fromVALUE/1000000)#) OR (dsp_millthou='billion' AND dsp_amount>#Int(req.fromVALUE/1000000000)#) If you are dealing with negative numbers for dsp_amount use Ceiling() instead of Int(). Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:308513 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4