Skip to main content
A
Anonymous
January 3, 2012
Question

Querying value from a range of two values

  • January 3, 2012
  • 3 replies
  • 2454 views

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?

    This topic has been closed for replies.

    3 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    January 22, 2012

    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>

    F
    FyremanTom
    Participating Frequently
    January 21, 2012

    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.

    ilssac
    ilssac
    Inspiring
    January 3, 2012

    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.

    A
    Anonymous
    January 3, 2012

    No,

    the line is:

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

    ilssac
    ilssac
    Inspiring
    January 3, 2012

    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?

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices