Skip navigation
rickaclark54
rickaclark54
Currently Being Moderated

Querying value from a range of two values

Jan 3, 2012 12:27 PM

I have patched together a shopping cart, operating on CF8,  and am now trying to add shipping.  The client wants the shipping charges based on the cost of item, ie:

0-$499 is $20

$500 – $999 is $25.00

$999 – up $45

 

I built an access table (simplified) with the following rows

Price_min, Price_max,ship_cost

 

The idea is to query the subtotal from the price_min and Price_max to determine the shipping cost:

 

<cfquery name="getInfo" datasource="#application.databasePRD#">

select ship_cost, price_min,price_max

from shipping

where price_min >= #form.subtotal# and price_max <= #form.subtotal#>

</cfquery>

</cfif>

 

<cfoutput query="getInfo">

#ship_cost#

</cfoutput>

 

Alas, I get the following error:

Syntax error (missing operator) in query expression ‘price_min  >= 29 and  price_max <= 29'.

 

What am I doing wrong?

1247 Views   10 Replies   Latest reply: BKBK, Jan 22, 2012 4:26 AM
 
Replies
  • ilssac
    Currently Being Moderated
    1. ilssac,
    Jan 3, 2012 12:40 PM   in reply to rickaclark54
    Report

    select ship_cost, price_min,price_max

    from shipping

    where price_min >= #form.subtotal# and price_max <= #form.subtotal#>

     

    Is that an exact copy of your SQL syntax?  If so, there is an extra character at the end of it that would throw the SQL parser for a loop.

     
    |
    Mark as:
  • rickaclark54
    Currently Being Moderated
    2. rickaclark54,
    Jan 3, 2012 1:11 PM   in reply to ilssac
    Report

    No,

    the line is:

    where price_min >= #form.subtotal# and price_max <= #form.subtotal#

     
    |
    Mark as:
  • ilssac
    Currently Being Moderated
    3. ilssac,
    Jan 3, 2012 1:18 PM   in reply to rickaclark54
    Report

    Did you try the comparison operators in a different order?  Sometimes databases care about this and different databases sometimes want them in a different way.

     

    I.E.

    where price_min => #form.subtotal# and price_max =< #form.subtotal#

     

    Also, have you investidated the values of the form.subtotal and confirmed that it is just the number you expect it to be and that it does not contain any other characters?

     
    |
    Mark as:
  • rickaclark54
    Currently Being Moderated
    4. rickaclark54,
    Jan 3, 2012 1:37 PM   in reply to ilssac
    Report

    Yes, I tried moving the comparison operators and verified the that the number was actually a number by itself. I even broke the code out to a new form and simplified the script. I created a new form and sent the price to the script and still got the "Syntax error (missing operator) in query expression" error

     
    |
    Mark as:
  • ilssac
    Currently Being Moderated
    5. ilssac,
    Jan 3, 2012 2:01 PM   in reply to rickaclark54
    Report

    I don't see anything actually wrong with the query.

     

    I ran this SQL in my access databas and it produces the expected results.

     

    SELECT ship_cost, price_min, price_max
FROM test
WHERE test.price_min<=55 AND test.price_max>=55;
     
    |
    Mark as:
  • rickaclark54
    Currently Being Moderated
    6. rickaclark54,
    Jan 3, 2012 2:19 PM   in reply to ilssac
    Report

    In your test.price_min<=55 isn't this saying if 55 is less than or equal to price_min? If  price_min is 0 and price_max is 499, then the operator should be price_min>=55 and price_min<=55. What happens if 55 is a dynamic variable passed from a form? 

     
    |
    Mark as:
  • Endboss_ZA
    Currently Being Moderated
    7. Endboss_ZA,
    Jan 4, 2012 4:08 AM   in reply to rickaclark54
    Report

    I think ilssac is correct here... your initial WHERE clause is wrong.

     

    Consider a record where price_min is 0 and price_max is 499, and the subtotal is 250.

     

    Your query is [where price_min >= #form.subtotal# and price_max <= #form.subtotal#]

     

    Which is [where 0 >= 250 and 499 <=250]

     

    Clearly this is never going to return anything.

     

    Reverse the operators like ilssac has done and you should be ok.

     
    |
    Mark as:
  • rickaclark54
    Currently Being Moderated
    8. rickaclark54,
    Jan 4, 2012 5:07 AM   in reply to Endboss_ZA
    Report

    I do not know what I am doing wrong. I tried the suggestion the two of you made and I still get an error. However I did try the following line and it is working fine: where #form.price# between price_min and price_max

     
    |
    Mark as:
  • FyremanTom
    Currently Being Moderated
    9. FyremanTom,
    Jan 21, 2012 3:14 PM   in reply to rickaclark54
    Report

    Replace #form.subtotal# with 200, then run the page.  I am not getting an error with my qry with exact syntax.  Also, check that price_min and price_max are number fields.

     
    |
    Mark as:
  • BKBK
    Currently Being Moderated
    10. BKBK,
    Jan 22, 2012 4:26 AM   in reply to rickaclark54
    Report

    Rickaclark54,

     

    I expected your query to work. However, from your explanation, I expected the operators to be the other way round! That is, this query:

     

    <cfquery name="getInfo" datasource="#application.databasePRD#">

    select ship_cost, price_min, price_max

    from shipping

    where shipping.price_min <= #form.subtotal#

    and    shipping.price_max >= #form.subtotal#

    </cfquery>

     

    If you continue to have problems, then verify that the database columns ship_cost, price_min and price_max have numeric datatypes. In any case, an even better query statement is:

     

    <cfquery name="getInfo" datasource="#application.databasePRD#">

    select ship_cost, price_min, price_max

    from shipping

    where shipping.price_min <= <cfqueryparam cfsqltype="cf_sql_numeric" value="#form.subtotal#">

    and    shipping.price_max >= <cfqueryparam cfsqltype="cf_sql_numeric" value="#form.subtotal#">

    </cfquery>

     
    |
    Mark as:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points