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
