Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
Finding a number in a range - sort of - problem
Author: Les Mizzell
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56912#308570
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...
Author: Andy Matthews
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56912#308531
Well there we go. That's even easier, and yes...that would be the best as
well.
Andy Matthews wrote:
> Suppose it might. But that forces the user to make one extra choice
> when you could just do it in the query.
Not really...
The way the search form *has* to be built is the same way the database is
put together. You enter a base amount like "2", and then there are radio
buttons for "thousand", "million", and "billion".
No way around it - that's the requirement! But, these folks have
*endless* pockets, so whatever it takes is whatever it takes!
Author: Les Mizzell
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56912#308525
Andy Matthews wrote:
> Suppose it might. But that forces the user to make one extra choice when you
> could just do it in the query.
Not really...
The way the search form *has* to be built is the same way the database
is put together. You enter a base amount like "2", and then there are
radio buttons for "thousand", "million", and "billion".
No way around it - that's the requirement! But, these folks have
*endless* pockets, so whatever it takes is whatever it takes!
Author: Andy Matthews
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56912#308524
Suppose it might. But that forces the user to make one extra choice when you
could just do it in the query.
And you shouldn't need to change any of the actual columns in the database,
just cast as bigint rather than int.
----- Excess quoted text cut - see Original Post for more -----
If I use a varient of this, I'm not even sure I need the CASE statement at
all...
In theory then...
Assuming
form.fromVALUE
form.toVALUE
form.fromMILLTHOU
form.toMILLTHOU
req.frommultiplier (set depending on fromMILLTHOU choice...)
req.tomultiplier (set depending on toMILLTHOU choice...)
in the Query
WHERE
(dsp_millthou = '#form.fromMILLTHOU#'
and dsp_amount > #Int(req.fromVALUE/req.frommultiplier)#)
AND
(dsp_millthou = '#form.toMILLTHOU#'
and dsp_amount > #Int(req.toVALUE/req.tomultiplier)#)
That outta work, shouldn't it?
Author: Les Mizzell
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56912#308522
> WHERE
----- Excess quoted text cut - see Original Post for more -----
If I use a varient of this, I'm not even sure I need the CASE statement
at all...
In theory then...
Assuming
form.fromVALUE
form.toVALUE
form.fromMILLTHOU
form.toMILLTHOU
req.frommultiplier (set depending on fromMILLTHOU choice...)
req.tomultiplier (set depending on toMILLTHOU choice...)
in the Query
WHERE
(dsp_millthou = '#form.fromMILLTHOU#'
and dsp_amount > #Int(req.fromVALUE/req.frommultiplier)#)
AND
(dsp_millthou = '#form.toMILLTHOU#'
and dsp_amount > #Int(req.toVALUE/req.tomultiplier)#)
That outta work, shouldn't it?
Author: Les Mizzell
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56912#308520
Andy Matthews wrote:
> Good pont Jochem...I hadn't even considered a max column size. Does SQL
> Server have a largeint char type like MySQL?
Yup!
bigint
-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
...and I'm glad Jochem chimed in there, because I would have spent all
day trying to figure out where the error was coming from!
Unfortunately, I'm only allowed to work on the system and make changes
while they're *not* using it - so I'm locked out until this evening to
try and implement the latest round of changes and see if I can finally
get it to work.
Thanks for all the pointers!
Author: Andy Matthews
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56912#308517
Good pont Jochem...I hadn't even considered a max column size. Does SQL
Server have a largeint char type like MySQL?
andy
Les Mizzell wrote:
----- Excess quoted text cut - see Original Post for more -----
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
Author: Andy Matthews
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56912#308514
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.
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?
Author: Jochem van Dieten
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56912#308513
Les Mizzell wrote:
----- Excess quoted text cut - see Original Post for more -----
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
Author: Les Mizzell
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56912#308507
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?
Author: Loathe
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56912#308506
case?
Les Mizzell wrote:
----- Excess quoted text cut - see Original Post for more -----
Author: Les Mizzell
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56912#308505
OK, looks like the SQL format is sorta:
SELECT column1, column2 ,
CASE dsp_millthou
WHEN 'thousand' THEN CAST(dsp_amount + '000' AS int)
WHEN 'million' THEN CAST(dsp_amount + '000000' AS int)
WHEN 'billion' THEN CAST(dsp_amount + '000000000' AS int)
END
FROM myTABLE
but that's not *quite* it. I'm getting an error:
"Syntax error converting the varchar value 'Million' to a column of data
type int."
Still working on it...
Author: Andy Matthews
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56912#308504
Right...CONCAT is MySQL specific. For SQL Server you'd do:
WHEN "thousand" THEN dsp_amount + '000'
But then you'd have to cast it as an INT:
WHEN "thousand" THEN CAST(dsp_amount + '000' AS int)
Les Mizzell wrote:
----- Excess quoted text cut - see Original Post for more -----
SQL Server doesn't like "CONCAT" - but I think I've got my head around it
enough to find the SQL Server code that will work for this now.
Will post the result once I've got it working (unless somebody has a better
idea first!)
Thanks,
Les
Author: Andy Matthews
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56912#308503
I created a table on my db to test...temp is it's name. Change that to
whatever your table name is which contains the dsp_millthou column.
----- Excess quoted text cut - see Original Post for more -----
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
Author: Les Mizzell
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56912#308502
Les Mizzell wrote:
----- Excess quoted text cut - see Original Post for more -----
SQL Server doesn't like "CONCAT" - but I think I've got my head around
it enough to find the SQL Server code that will work for this now.
Will post the result once I've got it working (unless somebody has a
better idea first!)
Thanks,
Les
Author: Les Mizzell
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56912#308501
----- Excess quoted text cut - see Original Post for more -----
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
Author: Andy Matthews
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56912#308500
Actually, I just tested the inner query and it works perfectly in MySQL, no
reason it wouldn't work in SQL Server as well.
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
andy
> SELECT blah, blah
> FROM myTABLE
> WHERE dsp_amount > 10
> and dsp_amount < 99
> and dsp_millthou = 'thousand'
Is it possible to do a calculation on a field in the WHERE statement like:
<cfif dsp_millthou EQ "thousand">
<cfset multiplier = 1000>
</cfif>
WHERE (dsp_amount * #multiplier#) > #form.someAMOUNT#
If there's a way to do that - I think I can make this work.
Author: Andy Matthews
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56912#308498
Sounds like you might need a query of queries, or a subquery. A subquery
might look like this:
SELECT *
FROM (
SELECT 1,
CASE dsp_millthou
WHEN "thousand" THEN dsp_amount + '000'
WHEN "million" THEN dsp_amount + '000000'
WHEN "billion" THEN dsp_amount + '000000000'
END AS myValue
FROM yourTable
) t
WHERE myValue < 99123
I've not written many subqueries, but I think you get the idea. You might
also have to cast the case statement as an integer or something, but I'll
bet that should get you to where you want to be.
andy
I'm trying to build a search interface for an existing database. Part of the
search is to find records of transactions between entered amounts.
Sounds easy enough - but the database is build with 2 fields for the
amounts.
dsp_amount - integer - holds the base amount dsp_millthou - varchar - holds
"thousand", "million", or "billion"
so instead of 2,000 or 1,999,000 in a single field, you've got "2" in the
dsp_amount and "thousand" in the dsp_millthou field.
I can't change the database.
So searching for a transaction between 10 to 999 thousand is no big deal:
SELECT blah, blah
FROM myTABLE
WHERE dsp_amount > 10
and dsp_amount < 99
and dsp_millthou = 'thousand'
But, how would I locate amounts between 99 thousand and 4 million, for
example?
I've been stumped for a couple of hours now...
Author: Les Mizzell
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56912#308499
> SELECT blah, blah
> FROM myTABLE
> WHERE dsp_amount > 10
> and dsp_amount < 99
> and dsp_millthou = 'thousand'
Is it possible to do a calculation on a field in the WHERE statement like:
<cfif dsp_millthou EQ "thousand">
<cfset multiplier = 1000>
</cfif>
WHERE (dsp_amount * #multiplier#) > #form.someAMOUNT#
If there's a way to do that - I think I can make this work.
Author: Les Mizzell
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56912#308497
I'm trying to build a search interface for an existing database. Part of
the search is to find records of transactions between entered amounts.
Sounds easy enough - but the database is build with 2 fields for the
amounts.
dsp_amount - integer - holds the base amount
dsp_millthou - varchar - holds "thousand", "million", or "billion"
so instead of 2,000 or 1,999,000 in a single field, you've got
"2" in the dsp_amount and "thousand" in the dsp_millthou field.
I can't change the database.
So searching for a transaction between 10 to 999 thousand is no big deal:
SELECT blah, blah
FROM myTABLE
WHERE dsp_amount > 10
and dsp_amount < 99
and dsp_millthou = 'thousand'
But, how would I locate amounts between 99 thousand and 4 million, for
example?
I've been stumped for a couple of hours now...
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
