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> <p>Non-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_Price>MAX(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
