Re: sql query help
Cool, yeah I never remember until I do it when an aggregate query is going to want HAVING vs WHERE. Glad it's working for you! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316379 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql query help
- Original Message - From: Jason Fisher [EMAIL PROTECTED] Cool, yeah I never remember until I do it when an aggregate query is going to want HAVING vs WHERE. Glad it's working for you! This bites me too when I'm not paying attention. Just remember that the WHERE applies to the records BEFORE the are grouped up and the HAVING applies to the grouped result set after the aggregates have been applied. ~Brad ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316384 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql query help
In MS SQL Server it's ISNULL(), but can't speak for other platforms. Can't recall what it is in Oracle, might just be NULL(). ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316319 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql query help
In MS SQL Server it's ISNULL(), but can't speak for other platforms. Can't recall what it is in Oracle, might just be NULL(). IIRC in Oracle it is NVL. There is also COALESCE, which is usually a safe bet with most databases. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316349 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: sql query help
didn't work, got an error. changed the isnull to ifnull, got a invalid use of a group function error... I don't even know how to fix that...=( Try this, I think it's what you're looking for: SELECT SUM(ISNULL(o.qty, 0)) as sold, p.sku, p.name, p.points, p. short_description, p.quantity, p.image FROM tblproducts as p LEFT JOIN tblorder_list as o ON p.sku = o.sku #can_afford# WHERE SUM(ISNULL(o.qty, 0)) p.quantity GROUP BY p.sku ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316372 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql query help
NM, got it... changed the where clause to having and moved it below the group by... seems to be working so far! Thanks! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316373 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
sql query help
I have a MySQL query to pull all products from the database, no problem. I am trying to get it to only display prodcuts that are in stock, the value should be sold p.quantity in the following query. however, when sold appears as null (very often) it removes the full record, which I don't want. there has to be an easy way to do this... Here is my current query: cfquery name=rsproducts datasource=#application.db# SELECT SUM(o.qty) as sold, p.sku, p.name, p.points, p.short_description, p.quantity, p.image FROM tblproducts as p LEFT JOIN tblorder_list as o ON p.sku=o.sku #can_afford# GROUP BY p.sku /cfquery Thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316295 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql query help
Try this, I think it's what you're looking for: SELECT SUM(ISNULL(o.qty, 0)) as sold, p.sku, p.name, p.points, p.short_description, p.quantity, p.image FROM tblproducts as p LEFT JOIN tblorder_list as o ON p.sku = o.sku #can_afford# WHERE SUM(ISNULL(o.qty, 0)) p.quantity GROUP BY p.sku ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316300 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql query help
i believe the correct function to use is IFNULL(), not ISNULL()... Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Jason Fisher wrote: Try this, I think it's what you're looking for: SELECT SUM(ISNULL(o.qty, 0)) as sold, p.sku, p.name, p.points, p.short_description, p.quantity, p.image FROM tblproducts as p LEFT JOIN tblorder_list as o ON p.sku = o.sku #can_afford# WHERE SUM(ISNULL(o.qty, 0)) p.quantity GROUP BY p.sku ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316311 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Query Help
thanks to everyone who replied, problem solved. Jenny -Original Message- From: Rafael Marquez [mailto:[EMAIL PROTECTED] Sent: 04 April 2007 21:06 To: CF-Talk Subject: RE: SQL Query Help Well, it's not a brain fart. That query is kinda kinky. Use this as an example, replacing your tables and fields, it should work BASED ON DATE! SELECT DISTINCT T.CustomerID, T.TransactionDate, T.ID AS InvoiceID FROM dbo.Transactions T INNER JOIN (SELECT MAX(TransactionDate) AS maxdate, CustomerID FROM Transactions WHERE deleted = 0 GROUP BY CustomerID) maxresults ON T.CustomerID = maxresults.CustomerID AND T.TransactionDate = maxresults.maxdate WHERE (T.Deleted = 0) AND (T.Status 'Pending') AND (T.AccountType = 'Customer') GROUP BY T.CustomerID, T.TransactionDate, T.ID Hope this helps. -Original Message- From: Gaulin, Mark [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 04, 2007 3:39 PM To: CF-Talk Subject: RE: SQL Query Help Need more info: What would the query be to return the last invoice for a single customer? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 04, 2007 8:58 AM To: CF-Talk Subject: SQL Query Help having brain fart here ... ughh scenario: customer table, invoice table I'd like to do a query to list the last invoice for each customer. Can someone point me in the right direction please? TIA, Jenny ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274660 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
SQL Query Help
having brain fart here ... ughh scenario: customer table, invoice table I'd like to do a query to list the last invoice for each customer. Can someone point me in the right direction please? TIA, Jenny ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274504 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Query Help
Maybe something like this (which I cannot test): SELECT c.id, c.name, i.* FROM customer c LEFT OUTER JOIN ( -- Get max (most recent) id (assuming pkey) of -- each invoice as grouped by client. SELECT i2.customer_id MAX( i2.id ) AS invoice_id FROM invoice i2 GROUP BY i2.customer_id ) AS inv ON c.id = inv.customer_Id LEFT OUTER JOIN invoice i ON inv.invoice_id = i.id We create an interim table that has JUST the customer id and MAX invoice Id. Then we join that to customers, and then join that to invoices (only were the max id = the invoice id). .. Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 04, 2007 8:58 AM To: CF-Talk Subject: SQL Query Help having brain fart here ... ughh scenario: customer table, invoice table I'd like to do a query to list the last invoice for each customer. Can someone point me in the right direction please? TIA, Jenny ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274517 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Query Help
Assuming the invoice numbers increase each time, you can use MAX() and GROUP BY to get what you need. SELECT customer, MAX(invoiceNum) AS lastNum FROM table GROUP BY customer If invoices are not numbered sequentially, hopefully you have a date field that you can add to the grouping. M!ke -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 04, 2007 7:58 AM To: CF-Talk Subject: SQL Query Help having brain fart here ... ughh scenario: customer table, invoice table I'd like to do a query to list the last invoice for each customer. Can someone point me in the right direction please? TIA, Jenny ~| Create Web Applications With ColdFusion MX7 Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274520 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Query Help
Need more info: What would the query be to return the last invoice for a single customer? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 04, 2007 8:58 AM To: CF-Talk Subject: SQL Query Help having brain fart here ... ughh scenario: customer table, invoice table I'd like to do a query to list the last invoice for each customer. Can someone point me in the right direction please? TIA, Jenny ~| Create Web Applications With ColdFusion MX7 Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274519 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Query Help
Well, it's not a brain fart. That query is kinda kinky. Use this as an example, replacing your tables and fields, it should work BASED ON DATE! SELECT DISTINCT T.CustomerID, T.TransactionDate, T.ID AS InvoiceID FROM dbo.Transactions T INNER JOIN (SELECT MAX(TransactionDate) AS maxdate, CustomerID FROM Transactions WHERE deleted = 0 GROUP BY CustomerID) maxresults ON T.CustomerID = maxresults.CustomerID AND T.TransactionDate = maxresults.maxdate WHERE (T.Deleted = 0) AND (T.Status 'Pending') AND (T.AccountType = 'Customer') GROUP BY T.CustomerID, T.TransactionDate, T.ID Hope this helps. -Original Message- From: Gaulin, Mark [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 04, 2007 3:39 PM To: CF-Talk Subject: RE: SQL Query Help Need more info: What would the query be to return the last invoice for a single customer? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 04, 2007 8:58 AM To: CF-Talk Subject: SQL Query Help having brain fart here ... ughh scenario: customer table, invoice table I'd like to do a query to list the last invoice for each customer. Can someone point me in the right direction please? TIA, Jenny ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274523 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
sql query help
Hey all, I'm having trouble trying to write a sql server query grouped on a datetime field. The problem is that since the time is included in the field, the date isn't really grouping well. I know of a datepart function but get an error when I try it. Any help? This sql statement groups all pages by the second. How do I split off just the date part? left(datetime,11)? select count(datetime) as dt, [datetime] from sitelog where datetime = '2007-02-03 00:00' group by [datetime] order by [datetime] desc Ahah! There it is. Nevermind. select left(datetime,11), count(*) as dt from sitelog where datetime = '2007-02-03 00:00' group by left(datetime,11) order by left(datetime,11) desc Well, now that I look at it, the order by is ordering by jan 1 2006 etc in desc alphabetical order. How do I get this field to be sorted by -mm-dd? Do I have to go through all this... http://www.databasejournal.com/features/mssql/article.php/10894_2197931_2 Michael Michael Muller Admin, MontagueMA.net Website work (413) 863-0030 cell (413) 320-5336 skype: michaelBmuller http://www.MontagueMA.net Eschew Obfuscation ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268769 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: sql query help
Mik Muller wrote: select left(datetime,11), count(*) as dt from sitelog where datetime = '2007-02-03 00:00' group by left(datetime,11) order by left(datetime,11) desc Well, now that I look at it, the order by is ordering by jan 1 2006 etc in desc alphabetical order. How do I get this field to be sorted by -mm-dd? Do I have to go through all this... http://www.databasejournal.com/features/mssql/article.php/10894_2197931_2 Read up on the CONVERT formats in books online... select CONVERT(char(8),datetime,112), count(*) as dt from sitelog where datetime = '2007-02-03 00:00' group by CONVERT(char(8),datetime,112) order by CONVERT(char(8),datetime,112) desc Or use the Day, Month and Year functions. When you use the LEFT function as you did above, it is actually doing a CONVERT...just to the default format. ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268773 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql query help
This is how I'm doing it for now... select count(*) as dt cfif url.stats eq bar-day, left(datetime,11) as datetime/cfif from sitelog where datepart(year,datetime) = '#theyear#' and datepart(month,datetime) = '#themonth#' cfif listLen(url.datestat,/) eq 2 and datepart(year,datetime) = '#listFirst(url.datestat,/)#' and datepart(month,datetime) = '#listLast(url.datestat,/)#' /cfif cfif not url.blankuserAND userID 0/cfif cfif url.uAND userID = #url.u#/cfif cfif url.gAND groupID = #url.g#/cfif cfif url.IDAND ID = #url.id#/cfif cfif isDefined(url.script_name)AND script_name = '#url.script_name#'/cfif cfif url.stats eq bar-day group by left(datetime,11) order by left(datetime,11) desc /cfif Michael At 09:50 PM 2/5/2007, you wrote: Mik Muller wrote: select left(datetime,11), count(*) as dt from sitelog where datetime = '2007-02-03 00:00' group by left(datetime,11) order by left(datetime,11) desc Well, now that I look at it, the order by is ordering by jan 1 2006 etc in desc alphabetical order. How do I get this field to be sorted by -mm-dd? Do I have to go through all this... http://www.databasejournal.com/features/mssql/article.php/10894_2197931_2 Read up on the CONVERT formats in books online... select CONVERT(char(8),datetime,112), count(*) as dt from sitelog where datetime = '2007-02-03 00:00' group by CONVERT(char(8),datetime,112) order by CONVERT(char(8),datetime,112) desc Or use the Day, Month and Year functions. When you use the LEFT function as you did above, it is actually doing a CONVERT...just to the default format. ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268774 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
SQL query help needed
Hi, i know that this must be simple, but i cant seem to figure it out... Please advise: I have the Database with the following Data: id Date Qty Dollars 16 05/30/2003 4 890.00 27 07/28/2004 1 300.00 29 01/17/2003 4 108.00 55 01/21/2002 1 105.00 56 09/20/2003 7 700.00 56 09/16/2003 0 0.00 56 09/10/2003 39 1,735.00 56 03/16/2001 2 1,118.00 56 03/05/2001 1 450.00 56 03/05/2001 13 2,270.00 123 12/29/2001 1 70.00 175 08/28/2003 3 567.00 175 07/16/2003 2 192.00 I need to display one line for each cust_num, with the latest date for that cust_num, and the sum of each the qty and dollars. The above should be displayed like this: id Date Qty Dollars 16 05/30/2003 4 890.00 27 07/28/2004 1 300.00 29 01/17/2003 4 108.00 55 01/21/2002 1 105.00 56 09/20/2003 62 6273.00 123 12/29/2001 1 70.00 175 08/28/2003 5 759.00 Any suggestions are appreaciated on how to do this. Thanks ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:209692 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
Re: SQL query help needed
Elena Aminova wrote: id DateQty Dollars 1605/30/2003 4 890.00 2707/28/2004 1 300.00 2901/17/2003 4 108.00 5501/21/2002 1 105.00 5609/20/2003 7 700.00 5609/16/2003 0 0.00 5609/10/2003 39 1,735.00 5603/16/2001 2 1,118.00 5603/05/2001 1 450.00 5603/05/2001 13 2,270.00 123 12/29/2001 1 70.00 175 08/28/2003 3 567.00 175 07/16/2003 2 192.00 I need to display one line for each cust_num, with the latest date for that cust_num, and the sum of each the qty and dollars. SELECT id, MAX(Date), SUM(Qty), Sum(Dollars) FROM table GROUP BY id Jochem ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:209694 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Mambo SQL query help.... Please
This looks like a job for OLAP. On Tue, 23 Nov 2004 15:31:07 -0600, Mark W. Breneman [EMAIL PROTECTED] wrote: John, I thought about that and also just caching the results, but that will not work in my case. I fear that I did not explain this very well. This is a dynamic query that can get the results for a different years, different ages and different school districts. The user can pick several items from several pull down list in a form. Any other ideas? Thanks. Mark W. Breneman Here is the full CFquery tag: cfquery name=getData datasource=#database# SELECT count(*) as totalRecords, (SELECT count(*) FROM CheckListData WHERE review_no = 1 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND TWO_ATTEND = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R2Yes,
Mambo SQL query help.... Please
I just inherited a project that has a very very large SQL count query. Now when I say very large I mean very large. What I have posted here is only 4 blocks of the 35 total blocks of SQL code in this one query. The total query takes about 120 seconds to run and often takes down the CF server. This query is made up of 203 in line sub queries and only returns a single row of values. Currently this query is not a stored procedure it is just a standard cfquery. So my question is where do I get started rewriting this query. This report page is on an administrative website where the traffic is very low. But never the less 2 mins is far too long to wait for a simple report. First off I can see that the yes, No and NA should be converted to a number. The DISTRICT also needs to be converted to a number. Then the whole thing needs to be converted into a stored procedure. Is there an EZ way to write this as a stored procedure. Currently the query is made by a Cfloop list that changes the query based on what options the users pick. What do I do next? Is there an EZer way to get this data then in line queries? What can I do first to get the biggest bang for my $. IOW is there something I can do quickly to get 40% shorter query run time? THANKS! Here is a small sample of the Query: SELECT count(*) as totalRecords, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'yes' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'no'AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'yes' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'no'AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'yes'AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'no' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND TWO_ATTEND = 'yes'AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND TWO_ATTEND = 'no' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q3R1,
RE: Mambo SQL query help.... Please
Maybe make it into some kind of scheduled job that runs every X hours or something and populates a table with the new values rather than doing real-time queries. Then the 2 minutes isn't as big of a deal. It's probably still a good idea to make it a stored procedure and then just execute that however often you want to populate the temporary table with the newest values. Then your code just does a select * from temporaryTable. John -Original Message- From: Mark W. Breneman [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 4:02 PM To: CF-Talk Subject: Mambo SQL query help Please I just inherited a project that has a very very large SQL count query. Now when I say very large I mean very large. What I have posted here is only 4 blocks of the 35 total blocks of SQL code in this one query. The total query takes about 120 seconds to run and often takes down the CF server. This query is made up of 203 in line sub queries and only returns a single row of values. Currently this query is not a stored procedure it is just a standard cfquery. So my question is where do I get started rewriting this query. This report page is on an administrative website where the traffic is very low. But never the less 2 mins is far too long to wait for a simple report. First off I can see that the yes, No and NA should be converted to a number. The DISTRICT also needs to be converted to a number. Then the whole thing needs to be converted into a stored procedure. Is there an EZ way to write this as a stored procedure. Currently the query is made by a Cfloop list that changes the query based on what options the users pick. What do I do next? Is there an EZer way to get this data then in line queries? What can I do first to get the biggest bang for my $. IOW is there something I can do quickly to get 40% shorter query run time? THANKS! Here is a small sample of the Query: SELECT count(*) as totalRecords, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'yes' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'no' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'yes' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'no' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'yes' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'no' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND TWO_ATTEND = 'yes' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R2Yes
RE: Mambo SQL query help.... Please
John, I thought about that and also just caching the results, but that will not work in my case. I fear that I did not explain this very well. This is a dynamic query that can get the results for a different years, different ages and different school districts. The user can pick several items from several pull down list in a form. Any other ideas? Thanks. Mark W. Breneman Here is the full CFquery tag: cfquery name=getData datasource=#database# SELECT count(*) as totalRecords, (SELECT count(*) FROM CheckListData WHERE review_no = 1 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND TWO_ATTEND = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND TWO_ATTEND = 'no' cfif form.schoolyear is not allAND schoolyear =
RE: Mambo SQL query help.... Please
Not really, unless you did stored procedures or something. I guess you could also use a view for each type of data you want to look up. I'm not really sure what that would do for you performance-wise but it may help organize things some. I think that you're going to take a while because of all the data you're checking against and all of the queries you're doing. I don't really know that there is a way around it besides caching. cfqueryparam should improve performance some without having to go to a stored procedure. Out of curiosity, have you tried making each query separate? Maybe that would help by eliminating the subqueries? I'm not a SQL guru so that could be dead wrong, but it may be worth a try. At least that way, you could use CF to cache some of the queries and then if someone changes the search criteria, only the query with new criteria will need to access the database. John -Original Message- From: Mark W. Breneman [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 4:31 PM To: CF-Talk Subject: RE: Mambo SQL query help Please John, I thought about that and also just caching the results, but that will not work in my case. I fear that I did not explain this very well. This is a dynamic query that can get the results for a different years, different ages and different school districts. The user can pick several items from several pull down list in a form. Any other ideas? Thanks. Mark W. Breneman Here is the full CFquery tag: cfquery name=getData datasource=#database# SELECT count(*) as totalRecords, (SELECT count(*) FROM CheckListData WHERE review_no = 1 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision
Re: Mambo SQL query help.... Please
Mark W. Breneman wrote: So my question is where do I get started rewriting this query. If that is an option, start with optimizing the data model. First off I can see that the yes, No and NA should be converted to a number. To a BOOLEAN. The DISTRICT also needs to be converted to a number. In general, you need to normalize the data. Obviously the DISTRICT should be a foreign key to a district table, but for instance, I am wondering if STUDENT_AGE_AT_IEP isn't redundant with for instance some table with records from students which has a birthday. Then the whole thing needs to be converted into a stored procedure. Why? For performance? A stored procedure has a precompiled execution plan so it saves you the time to parse and plan the query. From the looks of it you are running multiple indexscans and possibly even seqscans on the table so that won't help you. It saves you 99.99% of the 1 second it takes to compile, and doesn't help with the 119 seconds it takes to run. Profile the query. What is the execution plan? Is your system I/O bound or CPU bound? What DBMS are you using? Jochem ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185260 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
RE: Mambo SQL query help.... Please
Jochem, Thank you for your reply. You have answered one of my questions I have never asked but, always wondered about. How a stored procedure improves performance. Is there a better way to get the end result then using the inline queries? I am running a MSSQL 2000 database. The execution plan in SQL Query Analyzer for this query is as large or larger then the query itself. Now how do I tell if the query is I/O bound or CPU bound? Thanks for your help. Mark W. Breneman -Cold Fusion Developer -Network Administrator Vivid Media [EMAIL PROTECTED] www.vividmedia.com 608.270.9770 -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 3:47 PM To: CF-Talk Subject: Re: Mambo SQL query help Please Mark W. Breneman wrote: So my question is where do I get started rewriting this query. If that is an option, start with optimizing the data model. First off I can see that the yes, No and NA should be converted to a number. To a BOOLEAN. The DISTRICT also needs to be converted to a number. In general, you need to normalize the data. Obviously the DISTRICT should be a foreign key to a district table, but for instance, I am wondering if STUDENT_AGE_AT_IEP isn't redundant with for instance some table with records from students which has a birthday. Then the whole thing needs to be converted into a stored procedure. Why? For performance? A stored procedure has a precompiled execution plan so it saves you the time to parse and plan the query. From the looks of it you are running multiple indexscans and possibly even seqscans on the table so that won't help you. It saves you 99.99% of the 1 second it takes to compile, and doesn't help with the 119 seconds it takes to run. Profile the query. What is the execution plan? Is your system I/O bound or CPU bound? What DBMS are you using? Jochem ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185263 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Mambo SQL query help.... Please
John, I never thought of running separate SQL statements VS the inlin. I'm not sure if that would be faster or not. I may mock up a quick test of that. Mark W. Breneman -Cold Fusion Developer -Network Administrator Vivid Media [EMAIL PROTECTED] www.vividmedia.com 608.270.9770 -Original Message- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 3:36 PM To: CF-Talk Subject: RE: Mambo SQL query help Please Not really, unless you did stored procedures or something. I guess you could also use a view for each type of data you want to look up. I'm not really sure what that would do for you performance-wise but it may help organize things some. I think that you're going to take a while because of all the data you're checking against and all of the queries you're doing. I don't really know that there is a way around it besides caching. cfqueryparam should improve performance some without having to go to a stored procedure. Out of curiosity, have you tried making each query separate? Maybe that would help by eliminating the subqueries? I'm not a SQL guru so that could be dead wrong, but it may be worth a try. At least that way, you could use CF to cache some of the queries and then if someone changes the search criteria, only the query with new criteria will need to access the database. John -Original Message- From: Mark W. Breneman [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 4:31 PM To: CF-Talk Subject: RE: Mambo SQL query help Please John, I thought about that and also just caching the results, but that will not work in my case. I fear that I did not explain this very well. This is a dynamic query that can get the results for a different years, different ages and different school districts. The user can pick several items from several pull down list in a form. Any other ideas? Thanks. Mark W. Breneman Here is the full CFquery tag: cfquery name=getData datasource=#database# SELECT count(*) as totalRecords, (SELECT count(*) FROM CheckListData WHERE review_no = 1 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear
Re: Mambo SQL query help.... Please
Mark W. Breneman wrote: Is there a better way to get the end result then using the inline queries? With a helicopter view of the problem I can think of various execution plans that could make sense in your situation. But it all depends on the schema, cardinality and distribution of the data. All of which would express themselves in the execution plan. Possible options I can think of on the SQL front are: - replace your COUNT queries by SUM + CASE SELECT SUM(CASE WHEN review_no = 1 THEN 1 ELSE 0 END) as tot_Q1R1, SUM(CASE WHEN review_no = 2 THEN 1 ELSE 0 END) as tot_Q1R2, SUM(CASE WHEN review_no = 1 AND ONE_INVITED = 'no' THEN 1 ELSE 0 END) as tot_Q1R1No, SUM(CASE WHEN review_no = 2 AND ONE_INVITED = 'no' THEN 1 ELSE 0 END) as tot_Q1R2No, etc. FROM CheckListData WHERE schoolyear = 2003 AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP 15 AND DISTRICT = 'Black River Falls' (The idea behind this query is to force the database to run just one scan of the table and do the rest in RAM. This should help a lot if you are running many scans (one for each subquery) and are I/O bound.) Or: - create a temp table with all the data that matches the primary predicates: schoolyear = 2003 AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP 15 AND DISTRICT = 'Black River Falls' - run your query on just that table - drop the temp table (Here we want to do away with possible seqscans due to low cardinality on the primary predicates.) But all of these are just (imperfect) means to an end: getting a good execution plan. I'll see if I can dig up some high level literature on database internals. Also, keep looking at the schema of your table. If you can get your fields in your schema to be BOOLEAN NOT NULL, you might be able to do away with half of the subqueries, because total = true + false. (In your case with MS SQL Server that would be BIT fields.) The execution plan in SQL Query Analyzer for this query is as large or larger then the query itself. Isn't it largely repetetive? (I would expect so.) Else put it up on a website. Now how do I tell if the query is I/O bound or CPU bound? Task Manager: is your CPU at 100% ? If not, you need the Windows equivalent of IOstat to determine if the disks are running at their maximum capacity. If you are CPU bound, indexes often help. If you are I/O bound, normalize more so you store the data more efficiently. Jochem ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185265 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Mambo SQL query help.... Please
Jochem van Dieten wrote: But all of these are just (imperfect) means to an end: getting a good execution plan. I'll see if I can dig up some high level literature on database internals. While Googling for an easy howto I had little success: most general reading material is database specific and doesn't really grow an understanding of execution plans and most scientific literature assumes you already know too much about them. Then I came across SQL Tuning by Dan Tow: http://www.oreilly.com/catalog/sqltuning/ It looks like the book I wanted to write :-) Jochem ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185269 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query Help Please.
2.) Can someone please suggest a good book on SQL syntax that will clear thing up for me. Other beginner resources: 1) http://www.sqlcourse.com/ http://www.sqlcourse.com/ 2) http://sqlcourse2.com/ http://sqlcourse2.com/ 3) http://www.freeprogrammingresources.com/sql.html http://www.freeprogrammingresources.com/sql.html(links to many others) [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query Help Please.
Thanks Umer Others, You guys have been of great help. Will try the query today.. Regards, Mark - Original Message - From: Umer Farooq [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, October 11, 2004 8:17 PM Subject: Re: SQL Query Help Please. Here you.. go.. returns.. orderId, date, customerId, customer name, paymentReceived, amount due (per order)... , SELECT orders.orderId, order.orderDate, orders.customerID, customers.customerName, customerPayment.PaymentReceived (SELECT sum(unitPrice * quantity) FROM orderDetails WHERE orderDetails.orderID = orders.orderID ) AS amountDue, FROM (orders LEFT JOIN customerPayment ON customerPayment.orderID = orders.orderID) LEFT JOIN customer ON customer.customerID = orders.customerID Nomad wrote: The Database tables and fields are: Orders (orderid,customerid,Orderdate) OrderDetails(Orderid,UnitPrice,productid,quantity) Customer(Customerid, customername, address) CustomerPayments(orderid,PaymentReceived) My purpose is to track the account status of the customer for each order they have placed. The desired format of result obtained from the query is like this: Customername, Orderid, Orderdate,AmountReceived, AmountDue(this is a calculated column). -- Umer Farooq Octadyne Systems [EMAIL PROTECTED] +1 (519) 772-5424 voice +1 (519) 635-2795 mobile +1 (208) 275-3824 fax LOOKING FOR A USED CAR IN IOWA VISIT: http://www.IowaMotors.com WARNING: --- The information contained in this document and attachments is confidential and intended only for the person(s) named above. If you are not theintended recipient you are hereby notified that any disclosure, copying, distribution, or any other use of the information is strictly prohibited.If you have received this document by mistake, please notify the sender immediately and destroy this document and attachments without making any copy of any kind. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Query Help Please.
2.) Can someone please suggest a good book on SQL syntax Teach Yourself SQL in 10 Minutes by Ben Forta. ISBN 0-672-32128-9 ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
SQL Query Help Please.
Hello! I am trying to create a join of four tables to get data from a db in the format I want. The Database tables and fields are: Orders (orderid,customerid,Orderdate) OrderDetails(Orderid,UnitPrice,productid,quantity) Customer(Customerid, customername, address) CustomerPayments(orderid,PaymentReceived) My purpose is to track the account status of the customer for each order they have placed. The desired format of result obtained from the query is like this: Customername, Orderid, Orderdate,AmountReceived, AmountDue(this is a calculated column). I am using Access 2000 with CFusion. 1.)Can someonehelp me with the SQL syntax please. I have tried several permutations and combinations of sql (to the best of my knowledge) but without success. 2.) Can someone please suggest a good book on SQL syntax that will clear thing up for me. Many Thanks in Advance, Mark Taylor Admin [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query Help Please.
Hi, http://www.techonthenet.com/access/queries/joins1.htm For beginner I would suggest.. SAMS SQL in 21 Days.. and Google.. :-) Nomad wrote: Hello! I am trying to create a join of four tables to get data from a db in the format I want. The Database tables and fields are: Orders (orderid,customerid,Orderdate) OrderDetails(Orderid,UnitPrice,productid,quantity) Customer(Customerid, customername, address) CustomerPayments(orderid,PaymentReceived) My purpose is to track the account status of the customer for each order they have placed. The desired format of result obtained from the query is like this: Customername, Orderid, Orderdate,AmountReceived, AmountDue(this is a calculated column). I am using Access 2000 with CFusion. 1.)Can someonehelp me with the SQL syntax please. I have tried several permutations and combinations of sql (to the best of my knowledge) but without success. 2.) Can someone please suggest a good book on SQL syntax that will clear thing up for me. Many Thanks in Advance, Mark Taylor Admin [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query Help Please.
Here's a start, though you'll have to explain your tables and your calculations further. SELECT c.Customername, a.Orderid, a.Orderdate, ( where is this being stored? AmountReceived maybe this could be d.PaymentReceivedAS AmountReceived ? ), ( put your formula for calculating AmountDue here, possibly a subquery ) FROMOrders a, OrderDetails b, Customer c, CustomerPayments d WHERE a.orderid = b.orderid AND a.orderid = d.orderid AND c.customerid = a.customerid Hope that helps some... I would recomend the SQL book by forta, or just look online for a good tutorial. -Josh -- Exciteworks, Inc Expert Hosting for less! *Ask for a free 30 day trial!* http://exciteworks.com Plans starting at -$12.95- including MS SQL Server! Nomad wrote: Hello! I am trying to create a join of four tables to get data from a db in the format I want. The Database tables and fields are: Orders (orderid,customerid,Orderdate) OrderDetails(Orderid,UnitPrice,productid,quantity) Customer(Customerid, customername, address) CustomerPayments(orderid,PaymentReceived) My purpose is to track the account status of the customer for each order they have placed. The desired format of result obtained from the query is like this: Customername, Orderid, Orderdate,AmountReceived, AmountDue(this is a calculated column). I am using Access 2000 with CFusion. 1.)Can someonehelp me with the SQL syntax please. I have tried several permutations and combinations of sql (to the best of my knowledge) but without success. 2.) Can someone please suggest a good book on SQL syntax that will clear thing up for me. Many Thanks in Advance, Mark Taylor Admin [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query Help Please.
Hi Josh, Thanks for your reply. Your query is similar to what I have been trying. My query is: SELECT customer.Customercompanyname, orders.Orderid, orders.Orderdate, (Orderdetails.unitprice * Orderdetails.quantity) As OrderValue, CustomerPayments.PRIG As PaymentsReceived FROMOrders , OrderDetails, Customer, CustomerPayments WHERE 0=0 AND Orderdetails.orderid=Orders.orderid AND CustomerPayments.orderid=Orders.orderid AND Orders.customerid=Customer.customerid The result is something like this which is wrong: Notice the repetition of the OrderId field. Ideally there should be one row for each order. Customer Name OrderID, OrderDate, OrderValuePayments Received. Company /10/2004 55 500 Company /10/2004 31898 500 Company /10/20045678 500 Company5 411/10/20042320 400 Company5 411/10/20044740 400 Company5 411/10/2004 26400 400 Company5 411/10/2004544600 400 - Original Message - From: Josh [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, October 11, 2004 6:07 PM Subject: Re: SQL Query Help Please. Here's a start, though you'll have to explain your tables and your calculations further. SELECT c.Customername, a.Orderid, a.Orderdate, ( where is this being stored? AmountReceived maybe this could be d.PaymentReceivedAS AmountReceived ? ), ( put your formula for calculating AmountDue here, possibly a subquery ) FROMOrders a, OrderDetails b, Customer c, CustomerPayments d WHERE a.orderid = b.orderid AND a.orderid = d.orderid AND c.customerid = a.customerid Hope that helps some... I would recomend the SQL book by forta, or just look online for a good tutorial. -Josh -- Exciteworks, Inc Expert Hosting for less! *Ask for a free 30 day trial!* http://exciteworks.com Plans starting at -$12.95- including MS SQL Server! Nomad wrote: Hello! I am trying to create a join of four tables to get data from a db in the format I want. The Database tables and fields are: Orders (orderid,customerid,Orderdate) OrderDetails(Orderid,UnitPrice,productid,quantity) Customer(Customerid, customername, address) CustomerPayments(orderid,PaymentReceived) My purpose is to track the account status of the customer for each order they have placed. The desired format of result obtained from the query is like this: Customername, Orderid, Orderdate,AmountReceived, AmountDue(this is a calculated column). I am using Access 2000 with CFusion. 1.)Can someonehelp me with the SQL syntax please. I have tried several permutations and combinations of sql (to the best of my knowledge) but without success. 2.) Can someone please suggest a good book on SQL syntax that will clear thing up for me. Many Thanks in Advance, Mark Taylor Admin [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query Help Please.
Hi, Here you.. go.. returns.. orderId, date, customerId, customer name, paymentReceived, amount due per order... , SELECT orders.orderId, order.orderDate, orders.customerID, customers.customerName, customerPayment.PaymentReceived (SELECT sum(unitPrice * quantity) FROM orderDetails WHERE orderDetails.orderID = orders.orderID ) AS amountDue, FROM (orders LEFT JOIN customerPayment ON customerPayment.orderID = orders.orderID) LEFT JOIN customer ON customer.customerID = orders.customerID Nomad wrote: Hi Josh, Thanks for your reply. Your query is similar to what I have been trying. My query is: SELECT customer.Customercompanyname, orders.Orderid, orders.Orderdate, (Orderdetails.unitprice * Orderdetails.quantity) As OrderValue, CustomerPayments.PRIG As PaymentsReceived FROMOrders , OrderDetails, Customer, CustomerPayments WHERE 0=0 AND Orderdetails.orderid=Orders.orderid AND CustomerPayments.orderid=Orders.orderid AND Orders.customerid=Customer.customerid The result is something like this which is wrong: Notice the repetition of the OrderId field. Ideally there should be one row for each order. Customer Name OrderID, OrderDate, OrderValuePayments Received. Company /10/2004 55 500 Company /10/2004 31898 500 Company /10/20045678 500 Company5 411/10/20042320 400 Company5 411/10/20044740 400 Company5 411/10/2004 26400 400 Company5 411/10/2004544600 400 - Original Message - From: Josh [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, October 11, 2004 6:07 PM Subject: Re: SQL Query Help Please. Here's a start, though you'll have to explain your tables and your calculations further. SELECT c.Customername, a.Orderid, a.Orderdate, ( where is this being stored? AmountReceived maybe this could be d.PaymentReceivedAS AmountReceived ? ), ( put your formula for calculating AmountDue here, possibly a subquery ) FROMOrders a, OrderDetails b, Customer c, CustomerPayments d WHERE a.orderid = b.orderid AND a.orderid = d.orderid AND c.customerid = a.customerid Hope that helps some... I would recomend the SQL book by forta, or just look online for a good tutorial. -Josh -- Exciteworks, Inc Expert Hosting for less! *Ask for a free 30 day trial!* http://exciteworks.com Plans starting at -$12.95- including MS SQL Server! Nomad wrote: Hello! I am trying to create a join of four tables to get data from a db in the format I want. The Database tables and fields are: Orders (orderid,customerid,Orderdate) OrderDetails(Orderid,UnitPrice,productid,quantity) Customer(Customerid, customername, address) CustomerPayments(orderid,PaymentReceived) My purpose is to track the account status of the customer for each order they have placed. The desired format of result obtained from the query is like this: Customername, Orderid, Orderdate,AmountReceived, AmountDue(this is a calculated column). I am using Access 2000 with CFusion. 1.)Can someonehelp me with the SQL syntax please. I have tried several permutations and combinations of sql (to the best of my knowledge) but without success. 2.) Can someone please suggest a good book on SQL syntax that will clear thing up for me. Many Thanks in Advance, Mark Taylor Admin [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query Help Please.
Here you.. go.. returns.. orderId, date, customerId, customer name, paymentReceived, amount due (per order)... , SELECT orders.orderId, order.orderDate, orders.customerID, customers.customerName, customerPayment.PaymentReceived (SELECT sum(unitPrice * quantity) FROM orderDetails WHERE orderDetails.orderID = orders.orderID ) AS amountDue, FROM (orders LEFT JOIN customerPayment ON customerPayment.orderID = orders.orderID) LEFT JOIN customer ON customer.customerID = orders.customerID Nomad wrote: The Database tables and fields are: Orders (orderid,customerid,Orderdate) OrderDetails(Orderid,UnitPrice,productid,quantity) Customer(Customerid, customername, address) CustomerPayments(orderid,PaymentReceived) My purpose is to track the account status of the customer for each order they have placed. The desired format of result obtained from the query is like this: Customername, Orderid, Orderdate,AmountReceived, AmountDue(this is a calculated column). -- Umer Farooq Octadyne Systems [EMAIL PROTECTED] +1 (519) 772-5424 voice +1 (519) 635-2795 mobile +1 (208) 275-3824 fax LOOKING FOR A USED CAR IN IOWA VISIT: http://www.IowaMotors.com WARNING: --- The information contained in this document and attachments is confidential and intended only for the person(s) named above. If you are not theintended recipient you are hereby notified that any disclosure, copying, distribution, or any other use of the information is strictly prohibited.If you have received this document by mistake, please notify the sender immediately and destroy this document and attachments without making any copy of any kind. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Query Help (I'm stuck again)
this should work SELECT ai.Product_ID, ai.Status FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID GROUP BY ai.Product_ID, ai.Status HAVING ai.Reserve_Price MAX(ab.Bid_Amount) -Oorspronkelijk bericht- Van: Michael C.Grove [mailto:[EMAIL PROTECTED] Verzonden: wo 10/09/2003 7:26 Aan: CF-Talk CC: Onderwerp: SQL Query Help (I'm stuck again) SAMPLE 2 UPDATE Auction_Items SET Status = 'CLOSED' WHERE EXISTS(SELECT ai.Product_ID, ai.Status FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID AND ai.Reserve_Price ab.Bid_Amount GROUP BY ai.Product_ID, ai.Status); ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: SQL Query Help (I'm stuck again)
Using the following code, I get cfquery name=Auction_GetAuctions datasource=#DatasourceName# dbtype=ODBC SELECT ai.Product_ID, ai.Status FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID GROUP BY ai.Product_ID, ai.Status HAVING ai.Reserve_Price MAX(ab.Bid_Amount) /cfquery pNon-Sale Report/p cfoutput query=Auction_GetAuctions #Product_ID# #status#br /cfoutput ERROR --- ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'ai.Reserve_PriceMAX(ab.Bid_Amount)' as part of an aggregate function. I think we are close, but still having problems. Original Message: From: Pascal Peters [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Subject: RE: SQL Query Help (I'm stuck again) Date: Wed, 10 Sep 2003 08:36:33 +0200 this should work SELECT ai.Product_ID, ai.Status FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID GROUP BY ai.Product_ID, ai.Status HAVING ai.Reserve_Price MAX(ab.Bid_Amount) -Oorspronkelijk bericht- Van: Michael C.Grove [mailto:[EMAIL PROTECTED] Verzonden: wo 10/09/2003 7:26 Aan: CF-Talk CC: Onderwerp: SQL Query Help (I'm stuck again) SAMPLE 2 UPDATE Auction_Items SET Status = 'CLOSED' WHERE EXISTS(SELECT ai.Product_ID, ai.Status FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID AND ai.Reserve_Price ab.Bid_Amount GROUP BY ai.Product_ID, ai.Status); ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: SQL Query Help (I'm stuck again)
You still need the 'MAX(ab.Bid_Amount)' in the SELECT part of the query. The HAVING clause does a secondary filter on this Michael Traher Systems Manager ICLP (London) Tel: UK +44 (0) 20 8256 9072 Fax: UK +44 (0) 20 8681 0234 This e-mail may contain privileged and confidential information and/or copyright material and is intended for the use of the addressee only. If you receive this e-mail by mistake please advise the sender immediately by using the reply facility in your e-mail software and delete this e-mail from your computer system. You may not deliver, copy or disclose its contents to anyone else. Any unauthorised use may be unlawful. Any views expressed in this e-mail are those of the individual sender and may not necessarily reflect the views of ICLP. -Original Message- From: grovem [mailto:[EMAIL PROTECTED] Sent: 10 September 2003 15:16 To: CF-Talk Subject: RE: SQL Query Help (I'm stuck again) Using the following code, I get cfquery name=Auction_GetAuctions datasource=#DatasourceName# dbtype=ODBC SELECT ai.Product_ID, ai.Status FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID GROUP BY ai.Product_ID, ai.Status HAVING ai.Reserve_Price MAX(ab.Bid_Amount) /cfquery pNon-Sale Report/p cfoutput query=Auction_GetAuctions #Product_ID# #status#br /cfoutput ERROR --- ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'ai.Reserve_PriceMAX(ab.Bid_Amount)' as part of an aggregate function. I think we are close, but still having problems. Original Message: From: Pascal Peters [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Subject: RE: SQL Query Help (I'm stuck again) Date: Wed, 10 Sep 2003 08:36:33 +0200 this should work SELECT ai.Product_ID, ai.Status FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID GROUP BY ai.Product_ID, ai.Status HAVING ai.Reserve_Price MAX(ab.Bid_Amount) -Oorspronkelijk bericht- Van: Michael C.Grove [mailto:[EMAIL PROTECTED] Verzonden: wo 10/09/2003 7:26 Aan: CF-Talk CC: Onderwerp: SQL Query Help (I'm stuck again) SAMPLE 2 UPDATE Auction_Items SET Status = 'CLOSED' WHERE EXISTS(SELECT ai.Product_ID, ai.Status FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID AND ai.Reserve_Price ab.Bid_Amount GROUP BY ai.Product_ID, ai.Status); ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: SQL Query Help (I'm stuck again)
If I ad the Max(ab.Bid_Amount) to the select statment and remove the HAVING line, I do not get any errors, but I also return all records that have had a bid. When I ad the HAVING line [below] I receve the error ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'MAX(ab.Bid_Amount)ai.Reserve_Price' as part of an aggregate function. cfquery name=Auction_GetAuctions datasource=#DatasourceName# dbtype=ODBC SELECT ai.Product_ID, ai.Status, MAX(ab.Bid_Amount) AS HighBid FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID GROUP BY ai.Product_ID, ai.Status HAVING ai.Reserve_Price MAX(ab.Bid_Amount) /cfquery basically this needs to pull all of the records that did not receive a bid meeting or exceeding the reserve price. Thanks Again Original Message: From: Michael Traher [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Subject: RE: SQL Query Help (I'm stuck again) Date: Wed, 10 Sep 2003 15:28:19 +0100 You still need the 'MAX(ab.Bid_Amount)' in the SELECT part of the query. The HAVING clause does a secondary filter on this Michael Traher Systems Manager ICLP (London) Tel: UK +44 (0) 20 8256 9072 Fax: UK +44 (0) 20 8681 0234 This e-mail may contain privileged and confidential information and/or copyright material and is intended for the use of the addressee only. If you receive this e-mail by mistake please advise the sender immediately by using the reply facility in your e-mail software and delete this e-mail from your computer system. You may not deliver, copy or disclose its contents to anyone else. Any unauthorised use may be unlawful. Any views expressed in this e-mail are those of the individual sender and may not necessarily reflect the views of ICLP. -Original Message- From: grovem [mailto:[EMAIL PROTECTED] Sent: 10 September 2003 15:16 To: CF-Talk Subject: RE: SQL Query Help (I'm stuck again) Using the following code, I get cfquery name=Auction_GetAuctions datasource=#DatasourceName# dbtype=ODBC SELECT ai.Product_ID, ai.Status FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID GROUP BY ai.Product_ID, ai.Status HAVING ai.Reserve_Price MAX(ab.Bid_Amount) /cfquery pNon-Sale Report/p cfoutput query=Auction_GetAuctions #Product_ID# #status#br /cfoutput ERROR --- ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'ai.Reserve_PriceMAX(ab.Bid_Amount)' as part of an aggregate function. I think we are close, but still having problems. Original Message: From: Pascal Peters [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Subject: RE: SQL Query Help (I'm stuck again) Date: Wed, 10 Sep 2003 08:36:33 +0200 this should work SELECT ai.Product_ID, ai.Status FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID GROUP BY ai.Product_ID, ai.Status HAVING ai.Reserve_Price MAX(ab.Bid_Amount) -Oorspronkelijk bericht- Van: Michael C.Grove [mailto:[EMAIL PROTECTED] Verzonden: wo 10/09/2003 7:26 Aan: CF-Talk CC: Onderwerp: SQL Query Help (I'm stuck again) SAMPLE 2 UPDATE Auction_Items SET Status = 'CLOSED' WHERE EXISTS(SELECT ai.Product_ID, ai.Status FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID AND ai.Reserve_Price ab.Bid_Amount GROUP BY ai.Product_ID, ai.Status); ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Get the mailserver that powers this list at http://www.coolfusion.com
RE: SQL Query Help (I'm stuck again)
You need ai.Reserve_Price in the select (NOT the aggregate function) AND the HAVING clause. Because of the inner join, you will NOT get the records that did not receive a bid. -Oorspronkelijk bericht- Van: grovem [mailto:[EMAIL PROTECTED] Verzonden: wo 10/09/2003 16:43 Aan: CF-Talk CC: Onderwerp: RE: SQL Query Help (I'm stuck again) If I ad the Max(ab.Bid_Amount) to the select statment and remove the HAVING line, I do not get any errors, but I also return all records that have had a bid. When I ad the HAVING line [below] I receve the error ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'MAX(ab.Bid_Amount)ai.Reserve_Price' as part of an aggregate function. cfquery name=Auction_GetAuctions datasource=#DatasourceName# dbtype=ODBC SELECT ai.Product_ID, ai.Status, MAX(ab.Bid_Amount) AS HighBid FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID GROUP BY ai.Product_ID, ai.Status HAVING ai.Reserve_Price MAX(ab.Bid_Amount) /cfquery basically this needs to pull all of the records that did not receive a bid meeting or exceeding the reserve price. ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: SQL Query Help (I'm stuck again)
that worked, thank you very much. So far out of all the resources I have used in the past, this one is by far the best. Original Message: From: Pascal Peters [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Subject: RE: SQL Query Help (I'm stuck again) Date: Wed, 10 Sep 2003 16:51:14 +0200 You need ai.Reserve_Price in the select (NOT the aggregate function) AND the HAVING clause. Because of the inner join, you will NOT get the records that did not receive a bid. -Oorspronkelijk bericht- Van: grovem [mailto:[EMAIL PROTECTED] Verzonden: wo 10/09/2003 16:43 Aan: CF-Talk CC: Onderwerp: RE: SQL Query Help (I'm stuck again) If I ad the Max(ab.Bid_Amount) to the select statment and remove the HAVING line, I do not get any errors, but I also return all records that have had a bid. When I ad the HAVING line [below] I receve the error ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'MAX(ab.Bid_Amount)ai.Reserve_Price' as part of an aggregate function. cfquery name=Auction_GetAuctions datasource=#DatasourceName# dbtype=ODBC SELECT ai.Product_ID, ai.Status, MAX(ab.Bid_Amount) AS HighBid FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID GROUP BY ai.Product_ID, ai.Status HAVING ai.Reserve_Price MAX(ab.Bid_Amount) /cfquery basically this needs to pull all of the records that did not receive a bid meeting or exceeding the reserve price. ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Get the mailserver that powers this list at http://www.coolfusion.com
RE: SQL Query Help (I'm stuck again)
Ok, hopefully this is my last question regarding this After doing some testing, the query seems to pull the data I am looking for. The next thing I want to do is, make this query change the status of its results from ACTIVE to CLOSED. Using the statement below, it is closing all of the records that are ACTIVE and have an expired end date. It does not seem to even care about the bid amounts. UPDATE auction_items SET Status = 'CLOSED' WHERE EXISTS( SELECT ai.Product_ID, ai.Status, ai.Reserve_Price FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID GROUP BY ai.Product_ID, ai.Status, ai.Reserve_Price HAVING ai.Reserve_Price MAX(ab.Bid_Amount) ); This statment does seem to pull the correct data. Original Message: From: Pascal Peters [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Subject: RE: SQL Query Help (I'm stuck again) Date: Wed, 10 Sep 2003 16:51:14 +0200 You need ai.Reserve_Price in the select (NOT the aggregate function) AND the HAVING clause. Because of the inner join, you will NOT get the records that did not receive a bid. -Oorspronkelijk bericht- Van: grovem [mailto:[EMAIL PROTECTED] Verzonden: wo 10/09/2003 16:43 Aan: CF-Talk CC: Onderwerp: RE: SQL Query Help (I'm stuck again) If I ad the Max(ab.Bid_Amount) to the select statment and remove the HAVING line, I do not get any errors, but I also return all records that have had a bid. When I ad the HAVING line [below] I receve the error ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'MAX(ab.Bid_Amount)ai.Reserve_Price' as part of an aggregate function. cfquery name=Auction_GetAuctions datasource=#DatasourceName# dbtype=ODBC SELECT ai.Product_ID, ai.Status, MAX(ab.Bid_Amount) AS HighBid FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID GROUP BY ai.Product_ID, ai.Status HAVING ai.Reserve_Price MAX(ab.Bid_Amount) /cfquery basically this needs to pull all of the records that did not receive a bid meeting or exceeding the reserve price. ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
SQL Query Help (I'm stuck again)
I am working on another SQL query for an auction project in CF and I am very stuck. I think the answer should be very simple, but I have tried manipulating the query every way I can and cannot receive the correct data. For this query I am working with two tables using a Microsoft Access 2000 database.. Table 1. Auction_Items (Table Name) Product_ID (auto number/ Key) Date_EndDate (date/time) Reserve_Price (Currency) Status (Text) [ options are Active and Closed ] Table 2. Auction_Bids (Table Name) Product_ID (auto number/ Key) Member_ID (number) [ this is the person that placed the bid.] Bid (Currency) In a nutshell I am trying to build two queries (the first, which someone here already helped me resolve) The purpose of the first query is to select all of the products that that have a status of active, an EndDate less than today and a bid that has exceeded the reserve price. The query (SAMPLE 1) seems to work fine for this. SAMPLE 1 SELECT ai.Product_ID, ai.Status, ai.reserve_price, ai.date_enddate, MAX(ab.Bid_Amount) AS HighBid, Max(Bid_Amount*00.015) As Charge FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID AND ai.Reserve_Price = ab.Bid_Amount GROUP BY ai.Product_ID, ai.Status, ai.reserve_price, ai.date_enddate THIS IS THE ONE I CANT GET TO WORK The second query that I am having trouble (SAMPLE 2) with should do the exact same thing except it should take the records that do not have bids meeting and/or exceeding the reserve price and automatically setting their status to closed. I would think that you would simply take the ai.Reserve_Price = ab.bid_amount and change it to , but that returns too many records. It returns all the records that have bids less then the reserve rather than returning records that only have bids less then the reserve. SAMPLE 2 UPDATE Auction_Items SET Status = 'CLOSED' WHERE EXISTS(SELECT ai.Product_ID, ai.Status FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID AND ai.Reserve_Price ab.Bid_Amount GROUP BY ai.Product_ID, ai.Status); SAMPLE DATA Auction_Items Product_IDDate_EndDate Reserve_Price Status 1 9/8/03 $1000 ACTIVE 2 9/8/03 $500 ACTIVE 3 9/8/03 $200 ACTIVE Auction_Bids Product_IDBid_Amount 1 $200 1 $1100 2 $400 2 $450 3 $200 Using the sample data, Query two should basically automatically set Product_ID 2's status to CLOSED since there is no bid that meets or exceeds the reserve price. With my current query, it closes them all because they each have had a bid under the reserve price. I hope that makes sense.Thanks :-) ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
FW: 2nd try: SQL query help
Anyone have any ideas? -Original Message- From: Jeff Beer [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 30, 2003 11:00 AM To: CF-Talk Subject: OT: SQL query help Using SQL Server, how would I define a query to return ranges of ID's (primary key) where a tinyint field was not true? I'm looking for any ranges (eg; 12202 - 12249 and 14477 - 15225) where that tinyint field is 0. I'd like to specify a minimum number of records in that range, but it's not absolutely necessary. Something like: show me sequential ranges where myTinyint = 0 and total records in each range 100 Any help is greatly appreciated! ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: 2nd try: SQL query help
Sounds like a job for a stored procI don't think a simple query will do it for you. Get a good T-SQL book! Good luck. -- Josh Trefethen .:[ Exciteworks, Inc ]::[ http://exciteworks.com ]:. .::[ cf hosting on linux ]::[ consulting ]::[ expertise ]::. -Original Message- From: Jeff Beer [mailto:[EMAIL PROTECTED]] Sent: Sunday, February 02, 2003 2:22 AM To: CF-Talk Subject: FW: 2nd try: SQL query help Anyone have any ideas? -Original Message- From: Jeff Beer [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 30, 2003 11:00 AM To: CF-Talk Subject: OT: SQL query help Using SQL Server, how would I define a query to return ranges of ID's (primary key) where a tinyint field was not true? I'm looking for any ranges (eg; 12202 - 12249 and 14477 - 15225) where that tinyint field is 0. I'd like to specify a minimum number of records in that range, but it's not absolutely necessary. Something like: show me sequential ranges where myTinyint = 0 and total records in each range 100 Any help is greatly appreciated! ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: 2nd try: SQL query help
Quoting Josh Trefethen [EMAIL PROTECTED]: Sounds like a job for a stored procI don't think a simple query will do it for you. Simple was not a requirement. I just hope performance is not a requirement either ;-) SELECT MIN(t1id) AS minimum, t2id AS maximum, MAX(occurences) AS occurences FROM( SELECT t1id, MAX(t2id) AS t2id, MAX(occurences) AS occurences FROM( SELECT t1.id AS t1id, t2.id as t2id, count(t3.id) + 2 as occurences FROM test t1, test t2, test t3 WHERE t1.id t3.id AND t2.id t3.id AND t1.myTinyint = 0 AND t2.myTinyint = 0 AND t3.myTinyint = 0 GROUP BY t1.id, t2.id HAVING COUNT(t3.id) 5 -- Fill out (minimum count - 2) AND COUNT(t3.id) = t2.id -1 -t1.id ) AS sub GROUP BY t1id ) AS sup GROUP BY t2id Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: 2nd try: SQL query help
Jochem - you are a rock star - thanks for this! Performance is not an issue - I can run this once or twice a month late, late at night :) -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] Sent: Sunday, February 02, 2003 12:04 PM To: CF-Talk Subject: RE: 2nd try: SQL query help Quoting Josh Trefethen [EMAIL PROTECTED]: Sounds like a job for a stored procI don't think a simple query will do it for you. Simple was not a requirement. I just hope performance is not a requirement either ;-) SELECT MIN(t1id) AS minimum, t2id AS maximum, MAX(occurences) AS occurences FROM( SELECT t1id, MAX(t2id) AS t2id, MAX(occurences) AS occurences FROM( SELECT t1.id AS t1id, t2.id as t2id, count(t3.id) + 2 as occurences FROM test t1, test t2, test t3 WHERE t1.id t3.id AND t2.id t3.id AND t1.myTinyint = 0 AND t2.myTinyint = 0 AND t3.myTinyint = 0 GROUP BY t1.id, t2.id HAVING COUNT(t3.id) 5 -- Fill out (minimum count - 2) AND COUNT(t3.id) = t2.id -1 -t1.id ) AS sub GROUP BY t1id ) AS sup GROUP BY t2id Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
OT: SQL query help
Using SQL Server, how would I define a query to return ranges of ID's (primary key) where a tinyint field was not true? I'm looking for any ranges (eg; 12202 - 12249 and 14477 - 15225) where that tinyint field is 0. I'd like to specify a minimum number of records in that range, but it's not absolutely necessary. Something like: show me sequential ranges where myTinyint = 0 and total records in each range 100 Any help is greatly appreciated! ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL query help
Use the cast function WG -Original Message- From: Jeff Beer [mailto:[EMAIL PROTECTED]] Sent: 30 January 2003 16:00 To: CF-Talk Subject: OT: SQL query help Using SQL Server, how would I define a query to return ranges of ID's (primary key) where a tinyint field was not true? I'm looking for any ranges (eg; 12202 - 12249 and 14477 - 15225) where that tinyint field is 0. I'd like to specify a minimum number of records in that range, but it's not absolutely necessary. Something like: show me sequential ranges where myTinyint = 0 and total records in each range 100 Any help is greatly appreciated! ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL query help
That was a bit short !! [sic] E.g. select * Where cast(column as bit) = 1 Wg -Original Message- From: webguy [mailto:[EMAIL PROTECTED]] Sent: 30 January 2003 16:14 To: CF-Talk Subject: RE: SQL query help Use the cast function WG -Original Message- From: Jeff Beer [mailto:[EMAIL PROTECTED]] Sent: 30 January 2003 16:00 To: CF-Talk Subject: OT: SQL query help Using SQL Server, how would I define a query to return ranges of ID's (primary key) where a tinyint field was not true? I'm looking for any ranges (eg; 12202 - 12249 and 14477 - 15225) where that tinyint field is 0. I'd like to specify a minimum number of records in that range, but it's not absolutely necessary. Something like: show me sequential ranges where myTinyint = 0 and total records in each range 100 Any help is greatly appreciated! ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
SQL Query Help - Access Lookup tables and query output not accurate - not working
Hello List - I can not get this query down for the life of me. After a wasted afternoon - I am hoping ( I know you can) someone will assist me. I have an access table I am grabbing my information from - table - ModuleFeatures ModuleFeatures contains Column iModule -WHICH is a lookup to a ApplicationModules table SELECT [ApplicationModules].[iApplicationModuleID], [ApplicationModules].[sModuleName] FROM ApplicationModules; and it has an iApplication colum - which again is a lookup to a Application table - SELECT [ApplicationTypes].[iApplicationID], [ApplicationTypes].[sApplicationName] FROM ApplicationTypes; That is the code WITHIN access. Now on coldusion - I run a simple query to grab these features - cfquery name=GetModuleFeatures datasource=#dsndata# SELECT iModule, iApplication, sModuleFeature FROM ModuleFeatures ORDER BY iModule, iApplication /cfquery And the output is equally as simple - cfoutput query=GetModuleFeatures group=iModule #iModule# : within #iApplication#br cfoutputnbsp; #sModuleFeature#br/cfoutput /cfoutput This outputs perfectly. And the statement above - #iModule# : within #iApplication# was for a test. And it outputs correctly. So what it ends up with is 8: within 2 - 8 in Table ApplicationModules = Medical Records 2 in Table ApplicationTypes = Financial Applications. So instead of 8: within 2 - I need it to say Medical Records: within Financial Applications When I modified my simple query and alter the query to grab from both tables - it threw everything off. The ONLY working solution is that I can do this... modify the output query (shown below )-which does the trick - but is a headache. Not to mention - I have to do that with iModule - and there are upwards of 30 modules. Setting those by hand defeats the purpose of pulling from a database. I know I there is a way to modify my simple query - but going on 3 hours of tinkering and can not get it to work right or if I get it working - it's not accurate. Sorry this is long - but I figured the more info - the better someone can help me! Thanks !--- reassign numbers with names --- cfparam name=ThisApp default= cfif iApplication EQ 1 cfset ThisApp =Financial Systems cfelseif iApplication EQ 2 cfset ThisApp =Management Systems cfelseif iApplication EQ 3 cfset ThisApp =Clinical Applications /cfif ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Query Help - Access Lookup tables and query output not accurate - not working
Short Version - I realize that is VERY long - but I jsut supplied alot of information - Temporary link online - http://64.234.202.15/dbindex.cfm LookUp existing tables - need to be altered on cfquery end efficiently - 8: within 2 - 8 in Table ApplicationModules = Medical Records 2 in Table ApplicationTypes = Financial Applications. Simple question is - I am looking for an alternative to having to do this !--- reassign numbers with names --- cfparam name=ThisApp default= cfif iApplication EQ 1 cfset ThisApp =Financial Systems cfelseif iApplication EQ 2 cfset ThisApp =Management Systems cfelseif iApplication EQ 3 cfset ThisApp =Clinical Applications /cfif Jason Miller wrote: In Coldusion - I run a simple query to grab these features - cfquery name=GetModuleFeatures datasource=#dsndata# SELECT iModule, iApplication, sModuleFeature FROM ModuleFeatures ORDER BY iModule, iApplication /cfquery And the output is equally as simple - cfoutput query=GetModuleFeatures group=iModule #iModule# : within #iApplication#br cfoutputnbsp; #sModuleFeature#br/cfoutput /cfoutput This outputs perfectly. And the statement above - #iModule# : within #iApplication# was for a test. And it outputs correctly. So what it ends up with is 8: within 2 - 8 in Table ApplicationModules = Medical Records 2 in Table ApplicationTypes = Financial Applications. So instead of 8: within 2 - I need it to say Medical Records: within Financial Applications ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Query Help - Access Lookup tables and query output not accurate - not working
I don't fully undertand what you're doing. The first two queries, are those examples of the data inside those tables, or are they views/queries of some other table. It seems like all you need is a simple table join. Perhaps you could send me the database and cfml off list and I could look at it for you. You'll have to email me at [EMAIL PROTECTED] Fregas - Original Message - From: Jason Miller [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Friday, January 10, 2003 7:03 PM Subject: SQL Query Help - Access Lookup tables and query output not accurate - not working Hello List - I can not get this query down for the life of me. After a wasted afternoon - I am hoping ( I know you can) someone will assist me. I have an access table I am grabbing my information from - table - ModuleFeatures ModuleFeatures contains Column iModule -WHICH is a lookup to a ApplicationModules table SELECT [ApplicationModules].[iApplicationModuleID], [ApplicationModules].[sModuleName] FROM ApplicationModules; and it has an iApplication colum - which again is a lookup to a Application table - SELECT [ApplicationTypes].[iApplicationID], [ApplicationTypes].[sApplicationName] FROM ApplicationTypes; That is the code WITHIN access. Now on coldusion - I run a simple query to grab these features - cfquery name=GetModuleFeatures datasource=#dsndata# SELECT iModule, iApplication, sModuleFeature FROM ModuleFeatures ORDER BY iModule, iApplication /cfquery And the output is equally as simple - cfoutput query=GetModuleFeatures group=iModule #iModule# : within #iApplication#br cfoutputnbsp; #sModuleFeature#br/cfoutput /cfoutput This outputs perfectly. And the statement above - #iModule# : within #iApplication# was for a test. And it outputs correctly. So what it ends up with is 8: within 2 - 8 in Table ApplicationModules = Medical Records 2 in Table ApplicationTypes = Financial Applications. So instead of 8: within 2 - I need it to say Medical Records: within Financial Applications When I modified my simple query and alter the query to grab from both tables - it threw everything off. The ONLY working solution is that I can do this... modify the output query (shown below )-which does the trick - but is a headache. Not to mention - I have to do that with iModule - and there are upwards of 30 modules. Setting those by hand defeats the purpose of pulling from a database. I know I there is a way to modify my simple query - but going on 3 hours of tinkering and can not get it to work right or if I get it working - it's not accurate. Sorry this is long - but I figured the more info - the better someone can help me! Thanks !--- reassign numbers with names --- cfparam name=ThisApp default= cfif iApplication EQ 1 cfset ThisApp =Financial Systems cfelseif iApplication EQ 2 cfset ThisApp =Management Systems cfelseif iApplication EQ 3 cfset ThisApp =Clinical Applications /cfif ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL query help
cfquery name=browse datasource=marketing dbtype=ODBC select * from main_table where datestamp #dateformat(date,dd/mm/)# /cfquery This is really all I want to do. The Access field is in date format. This does not seem to work and I know it isnt exactly complex so there must be something really obviously wrong. -Original Message- From: Chris Sinkwitz [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 17:25 To: CF-Talk Subject: RE: SQL query help Darren, since you are using an Access db go into your Access program. Start to create a query to return the value you want. Then look at the SQL of this query you created in Access. Access doesn't use standard SQL and has a lot of different keywords. So this will get you on the right track of what your SQL statement should look like. -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 9:56 AM To: CF-Talk Subject: RE: SQL query help I think it was just that id didnt like the value in quotes !! -Original Message- From: Michael Blair [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:54 To: CF-Talk Subject: RE: SQL query help Try reversing the mm/dd/yy into the db and see. -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 10:44 AM To: CF-Talk Subject: RE: SQL query help Even though the datestamp field is a date/time field in access I still get this error message. ODBC Error Code = 22005 (Error in assignment) [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. Data Source = marketing SQL = select * from main_table where datestamp '28/01/02' The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:60) in the template file E:\Inetpub\inetsu\menutest\marketing\browse.cfm -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:44 To: CF-Talk Subject: Re: SQL query help What error do you get? There are two major products that come out of Berkeley: LSD and [Unix] BSD. We don't believe this to be a coincidence. Doug Brown - Original Message - From: Darren Adams [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, January 28, 2002 7:40 AM Subject: SQL query help Hi guys Can anyone help, I just need to check that the date of a record in a database is within a specific range. cfset today = dateformat(now(),dd/mm/) cfoutput#today#/cfoutput cfquery name=browse datasource=marketing dbtype=ODBC select * from main_table where 1=1 cfif #date# NEQ DD/MM/ and datestamp BETWEEN '#date#' AND #today# /cfif cfif region NEQ --- Select --- and region = '#region#'/cfifcfifmaterial NEQ --- Select --- and mater='#material#'/cfif cfif vertical NEQ --- Select --- and verti= '#vertical#'/cfif cfif target NEQ --- Select --- and target = '#target#'/cfifcfifproduct NEQ --- Select --- and product_serv= '#product#'/cfif /cfquery This query give me an error in assignment. I know that this should be easy but I cant think of it!!! Cheers, Darren Adams Web Developer Marketing Department Systems Union mail: [EMAIL PROTECTED] __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
Well since your using ODBC, just use CreateODBCDate cfquery name=browse datasource=marketing dbtype=ODBC select * from main_table where datestamp #CreateODBCDate(date,dd/mm/)# /cfquery __ steve oliver cresco technologies, inc. http://www.crescotech.com -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 29, 2002 7:11 AM To: CF-Talk Subject: RE: SQL query help cfquery name=browse datasource=marketing dbtype=ODBC select * from main_table where datestamp #dateformat(date,dd/mm/)# /cfquery This is really all I want to do. The Access field is in date format. This does not seem to work and I know it isnt exactly complex so there must be something really obviously wrong. -Original Message- From: Chris Sinkwitz [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 17:25 To: CF-Talk Subject: RE: SQL query help Darren, since you are using an Access db go into your Access program. Start to create a query to return the value you want. Then look at the SQL of this query you created in Access. Access doesn't use standard SQL and has a lot of different keywords. So this will get you on the right track of what your SQL statement should look like. -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 9:56 AM To: CF-Talk Subject: RE: SQL query help I think it was just that id didnt like the value in quotes !! -Original Message- From: Michael Blair [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:54 To: CF-Talk Subject: RE: SQL query help Try reversing the mm/dd/yy into the db and see. -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 10:44 AM To: CF-Talk Subject: RE: SQL query help Even though the datestamp field is a date/time field in access I still get this error message. ODBC Error Code = 22005 (Error in assignment) [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. Data Source = marketing SQL = select * from main_table where datestamp '28/01/02' The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:60) in the template file E:\Inetpub\inetsu\menutest\marketing\browse.cfm -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:44 To: CF-Talk Subject: Re: SQL query help What error do you get? There are two major products that come out of Berkeley: LSD and [Unix] BSD. We don't believe this to be a coincidence. Doug Brown - Original Message - From: Darren Adams [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, January 28, 2002 7:40 AM Subject: SQL query help Hi guys Can anyone help, I just need to check that the date of a record in a database is within a specific range. cfset today = dateformat(now(),dd/mm/) cfoutput#today#/cfoutput cfquery name=browse datasource=marketing dbtype=ODBC select * from main_table where 1=1 cfif #date# NEQ DD/MM/ and datestamp BETWEEN '#date#' AND #today# /cfif cfif region NEQ --- Select --- and region = '#region#'/cfifcfifmaterial NEQ --- Select --- and mater='#material#'/cfif cfif vertical NEQ --- Select --- and verti= '#vertical#'/cfif cfif target NEQ --- Select --- and target = '#target#'/cfifcfifproduct NEQ --- Select --- and product_serv= '#product#'/cfif /cfquery This query give me an error in assignment. I know that this should be easy but I cant think of it!!! Cheers, Darren Adams Web Developer Marketing Department Systems Union mail: [EMAIL PROTECTED] __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
SQL query help
Hi guys Can anyone help, I just need to check that the date of a record in a database is within a specific range. cfset today = dateformat(now(),dd/mm/) cfoutput#today#/cfoutput cfquery name=browse datasource=marketing dbtype=ODBC select * from main_table where 1=1 cfif #date# NEQ DD/MM/ and datestamp BETWEEN '#date#' AND #today# /cfif cfif region NEQ --- Select --- and region = '#region#'/cfif cfif material NEQ --- Select --- and material = '#material#'/cfif cfif vertical NEQ --- Select --- and vertical = '#vertical#'/cfif cfif target NEQ --- Select --- and target = '#target#'/cfif cfif product NEQ --- Select --- and product_service = '#product#'/cfif /cfquery This query give me an error in assignment. I know that this should be easy but I cant think of it!!! Cheers, Darren Adams Web Developer Marketing Department Systems Union mail: [EMAIL PROTECTED] __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
Well there are a number of options which users can search by. All the other ones work but, when they enter a date I cant compare the dates and get it too work. I tried the simple datestamp date but, that just didnt do anything so I tried to do a between comparison. Really all I want to do is get out all the records that are later that the date the user specifys. -Original Message- From: Chris Sinkwitz [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:12 To: CF-Talk Subject: RE: SQL query help What are you trying to do with your where clause? -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 8:41 AM To: CF-Talk Subject: SQL query help Hi guys Can anyone help, I just need to check that the date of a record in a database is within a specific range. cfset today = dateformat(now(),dd/mm/) cfoutput#today#/cfoutput cfquery name=browse datasource=marketing dbtype=ODBC select * from main_table where 1=1 cfif #date# NEQ DD/MM/ and datestamp BETWEEN '#date#' AND #today# /cfif cfif region NEQ --- Select --- and region = '#region#'/cfif cfif material NEQ --- Select --- and material = '#material#'/cfif cfif vertical NEQ --- Select --- and vertical = '#vertical#'/cfif cfif target NEQ --- Select --- and target = '#target#'/cfif cfif product NEQ --- Select --- and product_service = '#product#'/cfif /cfquery This query give me an error in assignment. I know that this should be easy but I cant think of it!!! Cheers, Darren Adams Web Developer Marketing Department Systems Union mail: [EMAIL PROTECTED] __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL query help
What error do you get? There are two major products that come out of Berkeley: LSD and [Unix] BSD. We don't believe this to be a coincidence. Doug Brown - Original Message - From: Darren Adams [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, January 28, 2002 7:40 AM Subject: SQL query help Hi guys Can anyone help, I just need to check that the date of a record in a database is within a specific range. cfset today = dateformat(now(),dd/mm/) cfoutput#today#/cfoutput cfquery name=browse datasource=marketing dbtype=ODBC select * from main_table where 1=1 cfif #date# NEQ DD/MM/ and datestamp BETWEEN '#date#' AND #today# /cfif cfif region NEQ --- Select --- and region = '#region#'/cfif cfif material NEQ --- Select --- and material = '#material#'/cfif cfif vertical NEQ --- Select --- and vertical = '#vertical#'/cfif cfif target NEQ --- Select --- and target = '#target#'/cfif cfif product NEQ --- Select --- and product_service = '#product#'/cfif /cfquery This query give me an error in assignment. I know that this should be easy but I cant think of it!!! Cheers, Darren Adams Web Developer Marketing Department Systems Union mail: [EMAIL PROTECTED] __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL query help
P.S have you tried just putting it into query analyzer? select * from main_table where datestamp '12/01/2000' If so did it work? There are two major products that come out of Berkeley: LSD and [Unix] BSD. We don't believe this to be a coincidence. Doug Brown - Original Message - From: Darren Adams [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, January 28, 2002 8:16 AM Subject: RE: SQL query help Well there are a number of options which users can search by. All the other ones work but, when they enter a date I cant compare the dates and get it too work. I tried the simple datestamp date but, that just didnt do anything so I tried to do a between comparison. Really all I want to do is get out all the records that are later that the date the user specifys. -Original Message- From: Chris Sinkwitz [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:12 To: CF-Talk Subject: RE: SQL query help What are you trying to do with your where clause? -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 8:41 AM To: CF-Talk Subject: SQL query help Hi guys Can anyone help, I just need to check that the date of a record in a database is within a specific range. cfset today = dateformat(now(),dd/mm/) cfoutput#today#/cfoutput cfquery name=browse datasource=marketing dbtype=ODBC select * from main_table where 1=1 cfif #date# NEQ DD/MM/ and datestamp BETWEEN '#date#' AND #today# /cfif cfif region NEQ --- Select --- and region = '#region#'/cfif cfif material NEQ --- Select --- and material = '#material#'/cfif cfif vertical NEQ --- Select --- and vertical = '#vertical#'/cfif cfif target NEQ --- Select --- and target = '#target#'/cfif cfif product NEQ --- Select --- and product_service = '#product#'/cfif /cfquery This query give me an error in assignment. I know that this should be easy but I cant think of it!!! Cheers, Darren Adams Web Developer Marketing Department Systems Union mail: [EMAIL PROTECTED] __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
Even though the datestamp field is a date/time field in access I still get this error message. ODBC Error Code = 22005 (Error in assignment) [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. Data Source = marketing SQL = select * from main_table where datestamp '28/01/02' The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:60) in the template file E:\Inetpub\inetsu\menutest\marketing\browse.cfm -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:44 To: CF-Talk Subject: Re: SQL query help What error do you get? There are two major products that come out of Berkeley: LSD and [Unix] BSD. We don't believe this to be a coincidence. Doug Brown - Original Message - From: Darren Adams [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, January 28, 2002 7:40 AM Subject: SQL query help Hi guys Can anyone help, I just need to check that the date of a record in a database is within a specific range. cfset today = dateformat(now(),dd/mm/) cfoutput#today#/cfoutput cfquery name=browse datasource=marketing dbtype=ODBC select * from main_table where 1=1 cfif #date# NEQ DD/MM/ and datestamp BETWEEN '#date#' AND #today# /cfif cfif region NEQ --- Select --- and region = '#region#'/cfif cfif material NEQ --- Select --- and material = '#material#'/cfif cfif vertical NEQ --- Select --- and vertical = '#vertical#'/cfif cfif target NEQ --- Select --- and target = '#target#'/cfif cfif product NEQ --- Select --- and product_service = '#product#'/cfif /cfquery This query give me an error in assignment. I know that this should be easy but I cant think of it!!! Cheers, Darren Adams Web Developer Marketing Department Systems Union mail: [EMAIL PROTECTED] __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
Try reversing the mm/dd/yy into the db and see. -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 10:44 AM To: CF-Talk Subject: RE: SQL query help Even though the datestamp field is a date/time field in access I still get this error message. ODBC Error Code = 22005 (Error in assignment) [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. Data Source = marketing SQL = select * from main_table where datestamp '28/01/02' The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:60) in the template file E:\Inetpub\inetsu\menutest\marketing\browse.cfm -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:44 To: CF-Talk Subject: Re: SQL query help What error do you get? There are two major products that come out of Berkeley: LSD and [Unix] BSD. We don't believe this to be a coincidence. Doug Brown - Original Message - From: Darren Adams [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, January 28, 2002 7:40 AM Subject: SQL query help Hi guys Can anyone help, I just need to check that the date of a record in a database is within a specific range. cfset today = dateformat(now(),dd/mm/) cfoutput#today#/cfoutput cfquery name=browse datasource=marketing dbtype=ODBC select * from main_table where 1=1 cfif #date# NEQ DD/MM/ and datestamp BETWEEN '#date#' AND #today# /cfif cfif region NEQ --- Select --- and region = '#region#'/cfif cfif material NEQ --- Select --- and material= '#material#'/cfif cfif vertical NEQ --- Select --- and vertical= '#vertical#'/cfif cfif target NEQ --- Select --- and target = '#target#'/cfif cfif product NEQ --- Select --- and product_service= '#product#'/cfif /cfquery This query give me an error in assignment. I know that this should be easy but I cant think of it!!! Cheers, Darren Adams Web Developer Marketing Department Systems Union mail: [EMAIL PROTECTED] __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
I think it was just that id didnt like the value in quotes !! -Original Message- From: Michael Blair [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:54 To: CF-Talk Subject: RE: SQL query help Try reversing the mm/dd/yy into the db and see. -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 10:44 AM To: CF-Talk Subject: RE: SQL query help Even though the datestamp field is a date/time field in access I still get this error message. ODBC Error Code = 22005 (Error in assignment) [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. Data Source = marketing SQL = select * from main_table where datestamp '28/01/02' The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:60) in the template file E:\Inetpub\inetsu\menutest\marketing\browse.cfm -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:44 To: CF-Talk Subject: Re: SQL query help What error do you get? There are two major products that come out of Berkeley: LSD and [Unix] BSD. We don't believe this to be a coincidence. Doug Brown - Original Message - From: Darren Adams [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, January 28, 2002 7:40 AM Subject: SQL query help Hi guys Can anyone help, I just need to check that the date of a record in a database is within a specific range. cfset today = dateformat(now(),dd/mm/) cfoutput#today#/cfoutput cfquery name=browse datasource=marketing dbtype=ODBC select * from main_table where 1=1 cfif #date# NEQ DD/MM/ and datestamp BETWEEN '#date#' AND #today# /cfif cfif region NEQ --- Select --- and region = '#region#'/cfif cfifmaterial NEQ --- Select --- and materia= '#material#'/cfif cfif vertical NEQ --- Select --- and vertica= '#vertical#'/cfif cfif target NEQ --- Select --- and target = '#target#'/cfif cfifproduct NEQ --- Select --- and product_servic= '#product#'/cfif /cfquery This query give me an error in assignment. I know that this should be easy but I cant think of it!!! Cheers, Darren Adams Web Developer Marketing Department Systems Union mail: [EMAIL PROTECTED] __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
Darren, since you are using an Access db go into your Access program. Start to create a query to return the value you want. Then look at the SQL of this query you created in Access. Access doesn't use standard SQL and has a lot of different keywords. So this will get you on the right track of what your SQL statement should look like. -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 9:56 AM To: CF-Talk Subject: RE: SQL query help I think it was just that id didnt like the value in quotes !! -Original Message- From: Michael Blair [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:54 To: CF-Talk Subject: RE: SQL query help Try reversing the mm/dd/yy into the db and see. -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 10:44 AM To: CF-Talk Subject: RE: SQL query help Even though the datestamp field is a date/time field in access I still get this error message. ODBC Error Code = 22005 (Error in assignment) [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. Data Source = marketing SQL = select * from main_table where datestamp '28/01/02' The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:60) in the template file E:\Inetpub\inetsu\menutest\marketing\browse.cfm -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:44 To: CF-Talk Subject: Re: SQL query help What error do you get? There are two major products that come out of Berkeley: LSD and [Unix] BSD. We don't believe this to be a coincidence. Doug Brown - Original Message - From: Darren Adams [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, January 28, 2002 7:40 AM Subject: SQL query help Hi guys Can anyone help, I just need to check that the date of a record in a database is within a specific range. cfset today = dateformat(now(),dd/mm/) cfoutput#today#/cfoutput cfquery name=browse datasource=marketing dbtype=ODBC select * from main_table where 1=1 cfif #date# NEQ DD/MM/ and datestamp BETWEEN '#date#' AND #today# /cfif cfif region NEQ --- Select --- and region = '#region#'/cfif cfifmaterial NEQ --- Select --- and materi= '#material#'/cfif cfif vertical NEQ --- Select --- and vertic= '#vertical#'/cfif cfif target NEQ --- Select --- and target = '#target#'/cfif cfifproduct NEQ --- Select --- and product_servi= '#product#'/cfif /cfquery This query give me an error in assignment. I know that this should be easy but I cant think of it!!! Cheers, Darren Adams Web Developer Marketing Department Systems Union mail: [EMAIL PROTECTED] __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
SQL query help
I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12 2 bar blue6 3 bar blue5 4 bar red 12 5 bar red 6 6 bar red 5 I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
Try SELECT DISTINCT color WHERE NAME = 'bar' Steven Semrau SRA International, Inc. Senior Member, Professional Staff [EMAIL PROTECTED] [EMAIL PROTECTED] Com: (703) 805-1095 DSN: (703) 655-1095 -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 4:16 PM To: CF-Talk Subject: SQL query help I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12 2 bar blue6 3 bar blue5 4 bar red 12 5 bar red 6 6 bar red 5 I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
Chad, Use the DISTINCT keyword: SELECT DISTINCT COLOR FROM Yourtable .. -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 4:16 PM To: CF-Talk Subject: SQL query help I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12 2 bar blue6 3 bar blue5 4 bar red 12 5 bar red 6 6 bar red 5 I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
You can either use the GROUP BY statement or do a SELECT DISTINCT(Color) FROM WHEREEVER WHERE WHATEVER hth, Bill -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 1:16 PM To: CF-Talk Subject: SQL query help I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12 2 bar blue6 3 bar blue5 4 bar red 12 5 bar red 6 6 bar red 5 I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
do a group by query SELECT * FROM Bars GROUP BY BarColor hth kevin ~ Kevin Mansel Web Developer Fox Communications [EMAIL PROTECTED] DL : 425-649-1321 C : 425-346-7221 -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 1:16 PM To: CF-Talk Subject: SQL query help I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12 2 bar blue6 3 bar blue5 4 bar red 12 5 bar red 6 6 bar red 5 I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
Chad, SELECT DISTINCT NAME ... Ben -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 1:16 PM To: CF-Talk Subject: SQL query help I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12 2 bar blue6 3 bar blue5 4 bar red 12 5 bar red 6 6 bar red 5 I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
Chad, OOPS! fumble-fingered today, meant SELECT DISTINCT COLOR ... sigh Ben -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 1:16 PM To: CF-Talk Subject: SQL query help I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12 2 bar blue6 3 bar blue5 4 bar red 12 5 bar red 6 6 bar red 5 I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
Chad, you'd want to do this: SELECT DISTINCT COLOR FROM tblWhatever WHERE NAME = 'bar' david -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 1:16 PM To: CF-Talk Subject: SQL query help I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12 2 bar blue6 3 bar blue5 4 bar red 12 5 bar red 6 6 bar red 5 I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
SELECT DISTINCT color from tableName where name='bar' -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 1:16 PM To: CF-Talk Subject: SQL query help I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12 2 bar blue6 3 bar blue5 4 bar red 12 5 bar red 6 6 bar red 5 I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
select DISTINCT color from... Tony -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 1:16 PM To: CF-Talk Subject: SQL query help I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12 2 bar blue6 3 bar blue5 4 bar red 12 5 bar red 6 6 bar red 5 I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
SELECT distinct(color) as color WHERE name = 'bar' chris olive, cio cresco technologies [EMAIL PROTECTED] http://www.crescotech.com -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 4:16 PM To: CF-Talk Subject: SQL query help I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12 2 bar blue6 3 bar blue5 4 bar red 12 5 bar red 6 6 bar red 5 I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
you should try to avoid using SELECT * in a production environment. it's unnecessary process utilization. it's more efficient for SQL to look up specified row names. might take longer to type, but it's the better way to go. ;) -Original Message- From: Kevin Mansel [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 2:05 PM To: CF-Talk Subject: RE: SQL query help do a group by query SELECT * FROM Bars GROUP BY BarColor hth kevin ~ Kevin Mansel Web Developer Fox Communications [EMAIL PROTECTED] DL : 425-649-1321 C : 425-346-7221 -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 1:16 PM To: CF-Talk Subject: SQL query help I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12 2 bar blue6 3 bar blue5 4 bar red 12 5 bar red 6 6 bar red 5 I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
OT: SQL Query Help
I don't know if any of you know how to do it but I'm trying to get the SQL query pasted below to include the two dates, at the moment it doesn't return data from the two dates specified. ie It returns queries starting Jan 02 and ending 19 Feb :-( SELECT DateAdded, EmailA FROMeShot WHERE ( DateAdded BETWEEN '2001/Jan/01' AND '2001/Feb/20' ) AND EmailA LIKE '%@%' AND ConfCode LIKE '%cc0243%' ORDER BYDateAdded All I can say is: HELP Any and all comments would be most gratefully appreciated. -= Ed ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Query Help
Change the dates to a day earlier and a day later ? Russell Brown Internet Application Developer Freeserve.com Plc, PO Box 452, Leeds LS2 7EY Telephone: 0113 207 1203 -Original Message- From: Edward Chanter [mailto:[EMAIL PROTECTED]] Sent: 19 February 2001 11:48 To: CF-Talk Subject: OT: SQL Query Help I don't know if any of you know how to do it but I'm trying to get the SQL query pasted below to include the two dates, at the moment it doesn't return data from the two dates specified. ie It returns queries starting Jan 02 and ending 19 Feb :-( SELECT DateAdded, EmailA FROMeShot WHERE ( DateAdded BETWEEN '2001/Jan/01' AND '2001/Feb/20' ) AND EmailA LIKE '%@%' AND ConfCode LIKE '%cc0243%' ORDER BYDateAdded All I can say is: HELP Any and all comments would be most gratefully appreciated. -= Ed ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Query Help
I would do but the dates are input into a form by our client. They are under the assumption that selecting the two dates will include them I'm trying to find a clean way to get the system to do that without having to spend untold time explaining it to the client on the web form.. -= Ed -Original Message- From: Russell Brown [mailto:[EMAIL PROTECTED]] Sent: Monday, February 19, 2001 11:53 AM To: CF-Talk Subject: RE: SQL Query Help Change the dates to a day earlier and a day later ? Russell Brown Internet Application Developer Freeserve.com Plc, PO Box 452, Leeds LS2 7EY Telephone: 0113 207 1203 -Original Message- From: Edward Chanter [mailto:[EMAIL PROTECTED]] Sent: 19 February 2001 11:48 To: CF-Talk Subject: OT: SQL Query Help I don't know if any of you know how to do it but I'm trying to get the SQL query pasted below to include the two dates, at the moment it doesn't return data from the two dates specified. ie It returns queries starting Jan 02 and ending 19 Feb :-( SELECTDateAdded, EmailA FROM eShot WHERE ( DateAdded BETWEEN '2001/Jan/01' AND '2001/Feb/20' ) AND EmailA LIKE '%@%' AND ConfCode LIKE '%cc0243%' ORDER BY DateAdded All I can say is: HELP Any and all comments would be most gratefully appreciated. -= Ed ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Query Help
I don't know if any of you know how to do it but I'm trying to get the SQL query pasted below to include the two dates, at the moment it doesn't return data from the two dates specified. ie It returns queries starting Jan 02 and ending 19 Feb SELECTDateAdded, EmailA FROM eShot WHERE ( DateAdded BETWEEN '2001/Jan/01' AND '2001/Feb/20' ) AND EmailA LIKE '%@%' AND ConfCode LIKE '%cc0243%' ORDER BY DateAdded What you've got to remember is that a straight date is effectively midnight at that date and because a DateTime field is exactly that, you have to consider a BETWEEN to return the results you specify If you want to get the results between 1 Jan 2001 and 19 Feb 2001, you can either do BETWEEN '1 Jan 2001' AND '20 Feb 2001' or BETWEEN '1 Jan 2001 00:00:00' AND '19 Feb 2001 23:59:59' It should always return the whole day of the Low Date in a BETWEEN, if it's not, you should check your data and ensure there is actually data there in the first place Philip Arnold Director Certified ColdFusion Developer ASP Multimedia Limited T: +44 (0)20 8680 1133 "Websites for the real world" ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Query Help
You could always use the DATEADD function to add a day to the later date and take a day off the earlier date. -- 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: Edward Chanter [mailto:[EMAIL PROTECTED]] Sent: 19 February 2001 12:04 To: CF-Talk Subject: RE: SQL Query Help I would do but the dates are input into a form by our client. They are under the assumption that selecting the two dates will include them I'm trying to find a clean way to get the system to do that without having to spend untold time explaining it to the client on the web form.. -= Ed -Original Message- From: Russell Brown [mailto:[EMAIL PROTECTED]] Sent: Monday, February 19, 2001 11:53 AM To: CF-Talk Subject: RE: SQL Query Help Change the dates to a day earlier and a day later ? Russell Brown Internet Application Developer Freeserve.com Plc, PO Box 452, Leeds LS2 7EY Telephone: 0113 207 1203 -Original Message- From: Edward Chanter [mailto:[EMAIL PROTECTED]] Sent: 19 February 2001 11:48 To: CF-Talk Subject: OT: SQL Query Help I don't know if any of you know how to do it but I'm trying to get the SQL query pasted below to include the two dates, at the moment it doesn't return data from the two dates specified. ie It returns queries starting Jan 02 and ending 19 Feb :-( SELECTDateAdded, EmailA FROM eShot WHERE ( DateAdded BETWEEN '2001/Jan/01' AND '2001/Feb/20' ) AND EmailA LIKE '%@%' AND ConfCode LIKE '%cc0243%' ORDER BY DateAdded All I can say is: HELP Any and all comments would be most gratefully appreciated. -= Ed ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Query Help
Thanks very much Philip Putting the times in worked like a treat! -= Ed -Original Message- From: Philip Arnold - ASP [mailto:[EMAIL PROTECTED]] Sent: Monday, February 19, 2001 12:06 PM To: CF-Talk Subject: RE: SQL Query Help I don't know if any of you know how to do it but I'm trying to get the SQL query pasted below to include the two dates, at the moment it doesn't return data from the two dates specified. ie It returns queries starting Jan 02 and ending 19 Feb SELECT DateAdded, EmailA FROMeShot WHERE ( DateAdded BETWEEN '2001/Jan/01' AND '2001/Feb/20' ) AND EmailA LIKE '%@%' AND ConfCode LIKE '%cc0243%' ORDER BYDateAdded What you've got to remember is that a straight date is effectively midnight at that date and because a DateTime field is exactly that, you have to consider a BETWEEN to return the results you specify If you want to get the results between 1 Jan 2001 and 19 Feb 2001, you can either do BETWEEN '1 Jan 2001' AND '20 Feb 2001' or BETWEEN '1 Jan 2001 00:00:00' AND '19 Feb 2001 23:59:59' It should always return the whole day of the Low Date in a BETWEEN, if it's not, you should check your data and ensure there is actually data there in the first place Philip Arnold Director Certified ColdFusion Developer ASP Multimedia Limited T: +44 (0)20 8680 1133 "Websites for the real world" ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
