Here's what finally worked (simplified a bit):
<cfswitch expression="#form.frommillthou#">
<cfcase value="thousand">
<cfset req.fromVALUE = form.fromAMNT * 10 />
</cfcase>
<cfcase value="million">
<cfset req.fromVALUE = form.fromAMNT * 10000 />
</cfcase>
<cfcase value="billion">
<cfset req.fromVALUE = form.fromAMNT * 10000000 />
</cfcase>
</cfswitch>
<cfswitch expression="#form.tomillthou#">
<cfcase value="thousand">
<cfset req.toVALUE = form.toAMNT * 10 /></cfcase>
<cfcase value="million">
<cfset req.toVALUE = form.toAMNT * 10000 />
</cfcase>
<cfcase value="billion">
<cfset req.toVALUE = form.toAMNT * 10000000 />
</cfcase>
</cfswitch>
<cfquery name="getCARDS">
SELECT * FROM
(
SELECT
tmb_id,
card_type,
dsp_amount,
dsp_millthou,
CASE
WHEN dsp_millthou = 'thousand' THEN dsp_amount * 10
WHEN dsp_millthou = 'million' THEN dsp_amount * 10000
WHEN dsp_millthou = 'billion' THEN dsp_amount * 10000000
END as thisVALUE
FROM tombstones
) AS t
WHERE
card_type = '#form.card_type#'
and thisVALUE > #req.fromVALUE#
and thisVALUE < #req.toVALUE#
and ( dsp_millthou = '#form.frommillthou#'
or dsp_millthou = '#form.tomillthou#')
</cfquery>
....plus insert all the appropriate error traps, cfquery param and all that..
Note my multiplier values - I'm not actually multiplying by a thousand,
million, or billion. I don't really need to and it keeps the integers
smaller.
Boy, this was an interesting exercise! Learned a lot on this one. Thanks
to all that chimed in. Guess I owe Andy and Jochem a drink...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:308570
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
