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 + '00' AS int) WHEN dsp_millthou='billion' THEN CAST(dsp_amount + '0' 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 + '00' AS int) WHEN dsp_millthou= 'billion' THEN CAST(dsp_amount + '0' 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:308507 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Finding a number in a range - sort of - problem
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 + '00' AS int) WHEN dsp_millthou='billion' THEN CAST(dsp_amount + '0' 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 * 100 WHEN dsp_millthou='billion' THEN dsp_amount * 10 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/100)#) OR (dsp_millthou='billion' AND dsp_amount#Int(req.fromVALUE/10)#) 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
RE: Finding a number in a range - sort of - problem
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 + '00' AS int) WHEN dsp_millthou='billion' THEN CAST(dsp_amount + '0' 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 + '00' AS int) WHEN dsp_millthou='billion' THEN CAST(dsp_amount + '0' 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 + '00' AS int) WHEN dsp_millthou= 'billion' THEN CAST(dsp_amount + '0' 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
RE: Finding a number in a range - sort of - problem
Good pont Jochem...I hadn't even considered a max column size. Does SQL Server have a largeint char type like MySQL? andy -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Thursday, July 03, 2008 8:15 AM To: CF-Talk Subject: Re: Finding a number in a range - sort of - problem 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 + '00' AS int) WHEN dsp_millthou='billion' THEN CAST(dsp_amount + '0' 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 * 100 WHEN dsp_millthou='billion' THEN dsp_amount * 10 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/100)#) OR (dsp_millthou='billion' AND dsp_amount#Int(req.fromVALUE/10)#) 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:308517 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Finding a number in a range - sort of - problem
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! ~| 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:308520 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Finding a number in a range - sort of - problem
WHERE (dsp_millthou='thousand' AND dsp_amount#Int(req.fromVALUE/1000)#) OR (dsp_millthou='million' AND dsp_amount#Int(req.fromVALUE/100)#) OR (dsp_millthou='billion' AND dsp_amount#Int(req.fromVALUE/10)#) 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? ~| 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:308522 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Finding a number in a range - sort of - problem
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. -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Thursday, July 03, 2008 9:37 AM To: CF-Talk Subject: Re: Finding a number in a range - sort of - problem WHERE (dsp_millthou='thousand' AND dsp_amount#Int(req.fromVALUE/1000)#) OR (dsp_millthou='million' AND dsp_amount#Int(req.fromVALUE/100)#) OR (dsp_millthou='billion' AND dsp_amount#Int(req.fromVALUE/10)#) 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? ~| 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:308524 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Finding a number in a range - sort of - problem
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! ~| 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:308525 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Finding a number in a range - sort of - problem
Well there we go. That's even easier, and yes...that would be the best as well. -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Thursday, July 03, 2008 9:57 AM To: CF-Talk Subject: Re: Finding a number in a range - sort of - problem 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! ~| 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:308531 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Finding a number in a range - sort of - problem
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 * 1 / /cfcase cfcase value=billion cfset req.fromVALUE = form.fromAMNT * 1000 / /cfcase /cfswitch cfswitch expression=#form.tomillthou# cfcase value=thousand cfset req.toVALUE = form.toAMNT * 10 //cfcase cfcase value=million cfset req.toVALUE = form.toAMNT * 1 / /cfcase cfcase value=billion cfset req.toVALUE = form.toAMNT * 1000 / /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 * 1 WHEN dsp_millthou = 'billion' THEN dsp_amount * 1000 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
Finding a number in a range - sort of - problem
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... ~| 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:308497 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Finding a number in a range - sort of - problem
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 + '00' WHEN billion THEN dsp_amount + '0' 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 -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2008 10:58 PM To: CF-Talk Subject: Finding a number in a range - sort of - problem 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... ~| 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:308498 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Finding a number in a range - sort of - problem
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. ~| 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:308499 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Finding a number in a range - sort of - problem
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,'00') WHEN billion THEN CONCAT(dsp_amount,'0') END AS myValue FROM temp ) t WHERE myValue 99123 andy -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2008 11:12 PM To: CF-Talk Subject: Re: Finding a number in a range - sort of - problem 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. ~| 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:308500 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Finding a number in a range - sort of - problem
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,'00') WHEN billion THEN CONCAT(dsp_amount,'0') END AS myValue FROM temp ) t WHERE myValue 99123 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 ~| 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:308501 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Finding a number in a range - sort of - problem
Les Mizzell wrote: 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,'00') WHEN billion THEN CONCAT(dsp_amount,'0') END AS myValue FROM temp ) t WHERE myValue 99123 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 ~| 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:308502 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Finding a number in a range - sort of - problem
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. -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2008 11:33 PM To: CF-Talk Subject: Re: Finding a number in a range - sort of - problem 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,'00') WHEN billion THEN CONCAT(dsp_amount,'0') END AS myValue FROM temp ) t WHERE myValue 99123 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 ~| 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:308503 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Finding a number in a range - sort of - problem
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) -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2008 11:48 PM To: CF-Talk Subject: Re: Finding a number in a range - sort of - problem Les Mizzell wrote: 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,'00') WHEN billion THEN CONCAT(dsp_amount,'0') END AS myValue FROM temp ) t WHERE myValue 99123 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 ~| 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:308504 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Finding a number in a range - sort of - problem
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 + '00' AS int) WHEN 'billion' THEN CAST(dsp_amount + '0' 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... ~| 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:308505 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Finding a number in a range - sort of - problem
case? Les Mizzell wrote: 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... ~| 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:308506 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
