You have to put the case part in a subquery. You can't query against
something that doesn't exist yet, as "thisValue" doesn't. Give this a try:
SELECT *
FROM
(
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
) AS t
WHERE thisVALUE > #req.fromVALUE#
Basically "thisVALUE" doesn't exist in your original query as real column,
it's a computed column based on your CASE statement. So to do it this way
you'd have to throw the whole initial query inside a subquery to get at the
"thisVALUE".
Try that and see what happens.
-----Original Message-----
From: Les Mizzell [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 03, 2008 1:20 AM
To: CF-Talk
Subject: Re: Finding a number in a range - sort of - problem
OK, query looks like:
<cfquery name="getDATA">
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 ...
I also tried:
CASE = 'thisVALUE'
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
But that generates an error...
Incorrect syntax near '='.
I feel I'm close though. Suggestions?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:308514
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
