Skip navigation
Currently Being Moderated

Many line by line calculations

Apr 1, 2013 9:34 AM

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,

 
Replies
  • Currently Being Moderated
    Apr 1, 2013 9:51 AM   in reply to jfb00

    Q of Q seems as good a way as any.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 1, 2013 7:27 PM   in reply to jfb00

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 2, 2013 5:13 AM   in reply to jfb00

    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

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points