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
