------------------------------------------------------------------
Andrew Ewings
Project Manager
Thoughtbubble Ltd
http://www.thoughtbubble.net
------------------------------------------------------------------
United Kingdom
http://www.thoughtbubble.co.uk/
Tel: +44 (0) 20 7387 8890
------------------------------------------------------------------
New Zealand
http://www.thoughtbubble.co.nz/
Tel: +64 (0) 9 488 9131
------------------------------------------------------------------
The information in this email and in any attachments is confidential and
intended solely for the attention and use of the named addressee(s). Any
views or opinions presented are solely those of the author and do not
necessarily represent those of Thoughtbubble. This information may be
subject to legal, professional or other privilege and further distribution
of it is strictly prohibited without our authority. If you are not the
intended recipient, you are not authorised to disclose, copy, distribute, or
retain this message. Please notify us on +44 (0)207 387 8890.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 15 May 2001 11:40
To: CF-Talk
Subject: RE: Query Help!! Problems???
Not quite sure of the problem. Would you not expect mutiple records. Your
where clause doesn't look like a unique identifier.
-----Original Message-----
From: Christopher Olive, CIO [mailto:[EMAIL PROTECTED]]
Sent: 13 May 2001 04:14
To: CF-Talk
Subject: RE: Query Help!! Problems???
are shipfrom and shipto string? if so, you'll get that error on the second
query, as you're comparing strings to numbers.
chris olive, cio
cresco technologies
[EMAIL PROTECTED]
http://www.crescotech.com
-----Original Message-----
From: ibtoad [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 11, 2001 8:16 PM
To: CF-Talk
Subject: RE: Query Help!! Problems???
OK when I use:
<cfquery name="get_shipratedollars" datasource="#config.datasource#"
dbtype="ODBC">
select shiprate from shipratesdollars
where '#total#' >=shipfrom
and '#total#' <= shipto
</cfquery>
I get multiple records returned.
When I get rid of the tick marks:
<cfquery name="get_shipratedollars" datasource="#config.datasource#"
dbtype="ODBC">
select shiprate from shipratesdollars
where #total# >=shipfrom
and #total# <= shipto
</cfquery>
I get Data type mismatch in criteria expression.
WHAT AM I DOING WRONG??
HELP,
Rich
-----Original Message-----
From: ibtoad [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 11, 2001 7:07 PM
To: CF-Talk
Subject: RE: Query Help!!
These queries give me:
ODBC Error Code = S1000 (General error)
[Microsoft][ODBC Microsoft Access Driver] Circular reference caused by alias
'shipRate' in query definition's SELECT list.
Rich
-----Original Message-----
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 11, 2001 5:35 PM
To: CF-Talk
Subject: RE: Query Help!!
it's impossible to know what's wrong without knowing the values of the
session variables at any given time
You'll notice that you don't need to do a comparison on both numbers:
<cfquery name="myQuery"...
SELECT max(shipRate) AS shipRate
FROM shipRatesDollars
WHERE shipFrom <= #val(session.total)#
OR
SELECT min(shipRate) AS shipRate
FROM shipRatesDollars
WHERE shipTo >= #val(session.total)#
Either of these will return the one value you need
Also if you're sure the columns in the DB will always be sorted in ascending
order then just do this:
<cfquery name="myQuery" maxrows="1"...
SELECT shipRate
FROM shipRatesDollars
WHERE shipTo >= #val(session.total)#
This time the first record in the DB where shipTo is greater than your
session variable is the one you want and will be the only one returned.
You may have noticed by now that you only need to have shipFrom OR shipTo in
the DB table, you do not need both :)
Bryan Love ACP
Internet Application Developer
[EMAIL PROTECTED]
-----Original Message-----
From: ibtoad [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 11, 2001 1:01 PM
To: CF-Talk
Subject: Query Help!!
Does anyone see any problems with the following query:
<cfquery name="get_shipratedollars" datasource="#config.datasource#">
Select shiprate from shipratesdollars
WHERE '#session.total#' >= shipfrom
and '#session.total#' <= shipto
</cfquery>
I can not figure out why I am getting incorrect results. Sometimes it will
pull more than one record and sometimes it will pull the max record only.
Here is a copy of the table I am using named shipratedollars.
ID shiprate shipfrom shipto
1 4.00 0.00 50.00
2 6.00 50.01 100.00
3 8.00 100.01 200.00
4 12.00 200.01 300.00
5 18.00 300.01 500.00
6 25.00 500.01 750.00
7 40.00 750.01 1000.00
8 50.00 1000.01 1500.00
9 70.00 1500.01 2000.00
10 100.00 2000.00 99999.00
Thansk,
Rich
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
