Skip to main content
C
coylo
Known Participant
March 29, 2010
Question

cfscript

  • March 29, 2010
  • 1 reply
  • 1534 views
ChartDirector Support
Forum HomeForum Home SearchSearch

Message ListMessage List     Post MessagePost Message    Subscribe to forumSubscribe

  cfscript
Posted by kevin on Mar-29-2010 11:00
| Reply | Monitor
I am trying to generate a multi series line graph using 3rd party software. when i loop through the bidders i want data0y and data0x to increment each time i.e. when it goes to bidder 1 it should generate:
<CFSCRIPT>
data0Y = Array(AllBids);
data0X = Array(AllBidDates);
</CFSCRIPT>

bidder 2:

<CFSCRIPT>
data1Y = Array(AllBids);
data1X = Array(AllBidDates);
</CFSCRIPT>

bidder 3:

<CFSCRIPT>
data2Y = Array(AllBids);
data2X = Array(AllBidDates);
</CFSCRIPT>

i have tried things like:
<!--- Find Bidder Position --->
<CFSET Bidderpostemp=ListFind(#AllBidders#, "#GetChartData.BidderID#")>
<CFSET Bidderpos=Bidderpostemp-1>
<CFSCRIPT>
data#Bidderpos#Y = Array(AllBids);
</CFSCRIPT>

but to no avail.

the relevant part of the code is....

<!--- Query to find all the Bidders participating in this Lot --->
<CFQUERY NAME="GetAllBidders" DATASOURCE="#Application.Datasource#">
SELECT BidderID
FROM Bidders
WHERE BidderID IN (<CFQUERYPARAM VALUE="#GetBidders.Bidders#" LIST="YES" CFSQLTYPE="CF_SQL_VARCHAR">) AND (Activated=<CFQUERYPARAM VALUE="Yes" CFSQLTYPE="CF_SQL_CHAR" MAXLENGTH="3">)
</CFQUERY>

<CFSCRIPT>

// ChartDirector for ColdFusion API Access Point
cd = CreateObject("java", "ChartDirector.CFChart");

// A utility to allow us to create arrays with data in one line of code
function Array() {
     var result = ArrayNew(1);
     var i = 0;
     for (i = 1; i LTE ArrayLen(arguments); i = i + 1)
         result = arguments;
     return result;
}

</CFSCRIPT>

<!--- Create a list of all the Bidders --->
<CFSET AllBidders=ValueList(GetAllBidders.BidderID)>

<!--- Loop through the Bidders --->
<CFLOOP INDEX="ListBidders" LIST="#AllBidders#">

<!--- Query to find Data for the Graph --->
<CFQUERY NAME="GetChartData" DATASOURCE="#Application.Datasource#">
SELECT Bids.BidID, Bids.BidderID, Bids.LotID, Bids.Bid, Bids.Bid_Date, Bidders.Company
FROM Bids INNER JOIN Bidders ON Bids.BidderID = Bidders.BidderID
WHERE (Bids.LotID=<CFQUERYPARAM VALUE="#URL.LotID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">) AND (Bids.BidderID=<CFQUERYPARAM VALUE="#ListBidders#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">)
ORDER BY Bids.Bid_Date ASC;
</CFQUERY>

<!--- Create a list of all the Bids --->
<CFSET AllBids=ValueList(GetChartData.Bid)>
<!--- Create a list of all the Bid Date/Times --->
<CFSET AllBidDates=ValueList(GetChartData.Bid_Date)>

<CFSCRIPT>
data0Y = Array(AllBids);
data0X = Array(AllBidDates);
</CFSCRIPT>

</CFLOOP>
This topic has been closed for replies.

1 reply

D
Dan_Bracuk
Inspiring
March 29, 2010

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.

C
coyloAuthor
Known Participant
April 15, 2010

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.

A
Adam Cameron.
Inspiring
April 15, 2010

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:

http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff0.html

--

Adam

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices