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...

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...

> 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.

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.

----- 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

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

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

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...

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

case? Les Mizzell wrote: ----- Excess quoted text cut - see Original Post for more -----

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?

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

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

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!

> 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?

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?

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!

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!

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...

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?