its ugly because I couldn't get the loops to work inside the sql...suggestions
also welcomed. :)
<!---GET CUSTOMER INFO---->
<cfif form.area neq ''>
<cfset sql1 = "">
<cfset listnum = 0>
<cfloop list="#form.area#" index="m">
<cfset listnum = listnum + 1>
<cfset sql1 = '#sql1#' & "customer_contact.phone LIKE '%#trim(m)#%'">
<cfif listnum LT listLen(form.area)>
<cfset sql1 = '#sql1#' & ' OR '>
</cfif>
</cfloop>
</cfif>
<cfif form.zip NEQ ''>
<cfset sql2 = "">
<cfset listnum1 = 0>
<cfloop list="#form.zip#" index="p">
<cfset listnum1 = listnum1 + 1>
<cfset sql2 = '#sql2#' & "customer.zip LIKE '%#trim(p)#%'">
<cfif listnum1 LT listLen(form.zip)>
<cfset sql2 = '#sql2#' & ' OR '>
</cfif>
</cfloop>
</cfif>
<cfquery name="getcust#i#" datasource="#heinz.dsn#">
Select
DISTINCT customer.id as cid,
customer.business,
customer_orders.id,
SUM (customer_orders_prod.qty) as totunits
FROM customer_orders
JOIN customer on customer.id = customer_orders.customer_id
JOIN customer_contact on customer.id = customer_contact.customer_id
LEFT OUTER JOIN customer_orders_prod on customer_orders.id =
customer_orders_prod.order_id
WHERE customer_orders.orderdate BETWEEN #createODBCDate(DateAdd('D', -1,
form.start1))# AND #CreateODBCDate(DateAdd('D', 1, form.end1))#
AND customer_orders_prod.product_id = #i#
<cfif form.lowcap GT 0 AND form.highcap GT 0>
AND customer_orders_prod.qty BETWEEN #form.lowcap# AND #form.highcap#
</cfif>
<cfif form.lowcap GT 0 AND form.highcap EQ ''>
AND customer_orders_prod.qty >= #form.lowcap#
</cfif>
<cfif form.lowcap EQ '' AND form.highcap GT 0>
AND customer_orders_prod.qty <= #form.highcap#
</cfif>
<cfif form.state NEQ ''>
AND customer.state = '#form.state#'
</cfif>
<cfif form.area neq ''>
AND (#sql1#)
</cfif>
<cfif form.zip NEQ ''>
AND (#sql2#)
</cfif>
AND (business NOT LIKE '%HSR%' AND business NOT LIKE '%OSR%' AND business NOT
LIKE '%HAS%' AND business NOT LIKE '%HAM%')
AND customer.category <> 5
GROUP BY customer_orders.id, customer.id, business
</cfquery>
THANK YOU!!!
Eric
--------------------------------------------------------
Eric J. Hoffman
Market Connections
2081 Industrial Blvd.
Stillwater
MN 55082
http://www.marketconnections.us
T: 651.207.1526
F: 651.207.1536
M: 952.210.9060
E: [EMAIL PROTECTED]
This message contains confidential information and is intended only for the
individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately by e-mail if you have received this e-mail by mistake and delete
this e-mail from your system. E-mail transmission cannot be guaranteed to be
secure or error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender therefore
does not accept liability for any errors or omissions in the contents of this
message, which arise as a result of e-mail transmission. If verification is
required please request a hard-copy version.
________________________________
From: Adrian Lynch [mailto:[EMAIL PROTECTED]
Sent: Wed 3/23/2005 2:12 PM
To: CF-Talk
Subject: RE: divide by zero error
Is it a problem in the ColdFusion code rather than the SQL? I can't see
where you're doing any division in the code below.
Have you got the CF code for us to see?
Ade
-----Original Message-----
From: Eric J. Hoffman [mailto:[EMAIL PROTECTED]
Sent: 23 March 2005 20:05
To: CF-Talk
Subject: divide by zero error
Overview: run a cold fusion query against a datasource. Get a divide by
zero error. Take that query in SQL Query analyzer and type in the query
substituting values and it works fine. All values being input by cold
fusion appear correct as well.
Can anyone identify where to look in this instance?
IN case it helps, resultant query:
Select
DISTINCT customer.id as cid,
customer.business,
customer_orders.id,
SUM (customer_orders_prod.qty) as totunits
FROM customer_orders
JOIN customer on customer.id = customer_orders.customer_id
JOIN customer_contact on customer.id = customer_contact.customer_id
LEFT OUTER JOIN customer_orders_prod on customer_orders.id =
customer_orders_prod.order_id
JOIN inventory on customer_orders_prod.product_id = inventory.service_id
WHERE customer_orders.orderdate BETWEEN '1/1/05' AND '3/1/05'
AND customer_orders_prod.product_id = 101
AND customer_orders_prod.qty >= 1
AND customer.state = 'MN'
AND (phone LIKE '%952%' OR phone LIKE '%651%' or Phone like '%763%' or
Phone like '%612%')
AND (business NOT LIKE '%HSR%' AND business NOT LIKE '%OSR%' AND business
NOT LIKE '%HAS%' AND business NOT LIKE '%HAM%')
AND customer.category <> 5
GROUP BY customer_orders.id, customer.id,
business --------------------------------------------------------
Eric J. Hoffman
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.8.0 - Release Date: 21/03/2005
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:199801
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
