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
