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
