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> > ><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 Get the mailserver that powers this list at http://www.coolfusion.com
