Many line by line calculations

[jfb00]

Hi All,

I have a component with couple methods, each method is the result of query from the database.

I want to do some calculations after i get the data from the server. I am lokking for a solution with good performance because I have many calculations to do.

Code:

<cfset myObj = CreateObject("component", "cfcs.myComponent") />

<cfset firstQry = myObj.getFirstResult(arguments.A, arguments.B) />

<cfset secondQry = myObj.getSecondResult(arguments.A, arguments.B) />

Both query results have the same structure myID, myDesc, year_1, year2, year3

firstQry have 10 rows, how can I sum the years row 3 (myID = 3) and years row 5 (myID = 5) ?

and add the result of the calculation into firstQry as line 11 (new myID = 11).

secondQry have one row result.

How can I multiple the firstQry years row 4 (myID=4) with the secondQry years row 1 (only row myID = 1)? 

and add the result into firstQry as line 12 (new myId =12).

I was thinking to use query of query but I wondering if it is a better way to do this.

Thanks in advance for your help.

Best,

[Dan_Bracuk]

Q of Q seems as good a way as any.

[jfb00]

Q of Q have many limitations. I have to generate a lot of code.

If I want to do the first calculation, i have to create another query because I cannot use query table alias like:

<cfquery dbtype="query" name="firstQry2">

select * from firstQry

</cfquery>

<cfquery dbtype="query" name="qryResult">

select 11 as myID, 'cal 12' as myDesc

<cfloop from="1" to="3" index="y">

,(firstQry.year_#y# + firstQry2.year_#y#) as year_#y#

</cfloop>

from firstQry, firstQry2

where firstQry.myID = 3

and firstQry2.myID = 5

</cfquery>

Is this correct, or I can do this in a better way?

Now, how can i insert the result into firstQuery?

I try to do another Q of Q with insert but I am getting an error.

Query Of Queries syntax error.
  Encountered "insert. 

<cfquery dbtype="query" name="insertQry">

insert into firstQuery (myID, myDesc

<cfloop from="1" to="3" index="y">

,year_#y#

</cfloop>

)

select  myID, myDesc

<cfloop from="1" to="3" index="y">

,year_#y#

</cfloop>

from qryResult

</cfquery>

[BreakawayPaul]

Have you considered putting the query inside the loop rather than the reverse?

<cfloop from="1" to="3" index="y">

<cfquery  name="insertQry" datasource="myDsn">

INSERT INTO firstQuery (myID, myDesc, year_#y#)

VALUES (...

</cfquery>

</cfloop>

I don't think you can use a QoQ to insert (or update) records.  AFAIK you're just working with a cached copy of the data, so updating that doesn't really make sense.

[jfb00]

Correct, I cannot do insert using Q of Q, but I can do it using QueryAddRow like:

        <cfset QueryAddRow(firstQuery, "1")>

        <cfset QuerySetCell(firstQuery, "myID", qryResult.myID)>

        <cfset QuerySetCell(firstQuery, "myDesc", qryResult.myDesc)>

        <cfloop from="1" to="#totalYears#" index="y">

            <cfset tempYear = qryResult["year_" & #y#] />

            <cfset QuerySetCell(firstQuery, "year_#y#", tempYear)>

        </cfloop>

But again, i wonder if it is a better way to do this.

[Dan_Bracuk]

To create a new query with more rows using Q of Q, make it a union query.

select field1, field2

from somequery

union

select Value1, Value2

from somequery

[jfb00]

Dan,

I am using the union query and adding another row with QueryAddRow.

After the additions I am sorting the data, error shows in the sort by line:

java.math.BigDecimal cannot be cast to java.lang.Long

Any ideas about this?

Thanks!

[jfb00]

I was able to cast the id as this example:

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=using_recordsets_8.html

SELECT SUM(CAST(qStockItems.LastTradedPrice as INTEGER))

Why am i loosing the data type?