-
1. Re: cfscript
Dan Bracuk Mar 29, 2010 9:34 AM (in response to coylo)You are overwriting your variables each time you go through your loop.
Not related to your question, but, going back to the database inside a loop is very inefficient and could cause problems. A better strategy would be to make one trip to the db and get all the data you need. Then you can use Q of Q and other things to build your graph.
-
2. Re: cfscript
coylo Apr 15, 2010 8:20 AM (in response to Dan Bracuk)thanks for the Q of Q tip.
however i have a subquery that is also inside a cfloop:
<!--- Query to find the latest/current bid of each of the other Bidders --->
<CFQUERY NAME="GetLastBid" DATASOURCE="#Application.Datasource#">
SELECT DISTINCT Scores.Non_Price_Score, Scores.Price_Score, Bids.BidderID, Bids.Bid
FROM (Bids INNER JOIN Bidders ON Bids.BidderID = Bidders.BidderID) INNER JOIN Scores ON (Bids.LotID = Scores.LotID) AND (Bids.BidderID = Scores.BidderID) JOIN
(Select BidderID, MAX(Bid_Date) as Max_Bid_Date
FROM Bids
WHERE (Bids.LotID=<CFQUERYPARAM VALUE="#Form.LotID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">) AND (Bids.BidderID=<CFQUERYPARAM VALUE="#ListBidders#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">)
GROUP BY BidderID) x ON Bids.BidderID = x.BidderID and Bids.Bid_Date = Max_Bid_Date
WHERE (Bids.LotID=<CFQUERYPARAM VALUE="#Form.LotID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">) AND (Bids.BidderID=<CFQUERYPARAM VALUE="#ListBidders#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">)
</CFQUERY>For more efficiency is it possible to do above in Q of Q as in the following:
<CFQUERY NAME="GetBidsScores" DATASOURCE="#Application.Datasource#">
SELECT Bids.BidID, Bids.BidderID, Bids.LotID, Bids.Bid, Bids.Bid_Date, Scores.Non_Price_Score, Scores.Price_Score
FROM (Bids INNER JOIN Bidders ON Bids.BidderID = Bidders.BidderID) INNER JOIN Scores ON (Bids.LotID = Scores.LotID) AND (Bids.BidderID = Scores.BidderID)WHERE Bids.LotID=<CFQUERYPARAM VALUE="#Form.LotID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">
</CFQUERY>
<!--- Loop through the other Bidders --->
<CFLOOP INDEX="ListBidders" LIST="#OtherBidders#"><!--- Query to find the latest/current bid of each of the Bidders --->
<CFQUERY NAME="GetLastBid" DBTYPE="QUERY">
SELECT Non_Price_Score, Price_Score, LotID, BidderID, Bid
FROM GetBidsScores JOIN
(Select GetBidsScores.BidderID, MAX(Bid_Date) as Max_Bid_Date
FROM GetBidsScores
WHERE GetBidsScores.BidderID=<CFQUERYPARAM VALUE="#ListBidders#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">
GROUP BY BidderID) x ON GetBidsScores.BidderID = x.BidderID and GetBidsScores.Bid_Date = Max_Bid_Date
WHERE GetBidsScores.BidderID=<CFQUERYPARAM VALUE="#ListBidders#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">
</CFQUERY>............
</CFLOOP>
I tried above and got following error.
Query Of Queries syntax error.
Encountered "JOIN. -
3. Re: cfscript
Adam Cameron. Apr 15, 2010 8:29 AM (in response to coylo)One cannot use JOIN syntax or have subqueries in a QoQ. QoQ supports only a very limited subset of SQL constructs. It might be an idea to give the docs a bit of a read:
--
Adam
-
4. Re: cfscript
Dan Bracuk Apr 15, 2010 10:41 AM (in response to coylo)You don't need a loop. The general syntax for getting the most recent or highest, or whatever set of data is as follows. I'll use most recent as an example.
select datefield, some_other_fields
from some_tables
join (
select some_other_fields, max(datefield) maxdatefield
from some_tables
where whatever
group by some_other_fields
) sq on some_tables.some_other_fields = sq.some_other_fields
and datefield = maxdatefield
where whatever