• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

cfscript

Community Beginner ,
Mar 29, 2010 Mar 29, 2010

Copy link to clipboard

Copied

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
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>
TOPICS
Advanced techniques

Views

1.4K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Mar 29, 2010 Mar 29, 2010

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Apr 15, 2010 Apr 15, 2010

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 15, 2010 Apr 15, 2010

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 15, 2010 Apr 15, 2010

Copy link to clipboard

Copied

LATEST

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation