Query Reference Data and CFCs

[jpcane]

I would to run a  query once that returns a record set and pass that as reference data to functions in a ColdFusion component. Inside those functions, I'd take the query and run a query of queries for aggregate results among other things. Can this be done? I am getting an error basically saying the passed-in query is not a simple value where I use it in the FROM clause of the query.

It works fine when both queries are within the same function, but it's needless overhead to continually get the same ref data.

Example:

template.cfm

-------------------

...

<cfobject name="lib" component="mycfc">

<cfinvoke component="#lib#" method="getQueryRefData" returnvariable="refData" />

<cfinvoke component="#lib#" method="getSum" returnvariable="sum" refdata="#refData#" />

...

mycfc.cfc

--------------

...

<cffunction name="getQueryRefData" access="public" returntype="query">
        <cfquery name="allData" datasource="myDatasource">
            SELECT * FROM answer
        </cfquery>

        <cfreturn allData>
</cffunction>

<cffunction name="getSum" access="public" returntype="numeric">

     <cfargument name="refData" type="query" required="yes">

     <cfquery name="deptSum" dbtype="query">
            SELECT COUNT(*) AS total, department
            FROM ARGUMENTS.refData <--- ERROR occurs here --->
            GROUP BY department
      </cfquery>

      <cfreturn Val(deptSum.total)>

</cffunction>

...

[Dan_Bracuk]

You forgot the octothorps.

[jpcane]

You're right, I left them out in my original post. But they are in the actual code. The question remains. Anyone have suggestions? Thank you.

[Dan_Bracuk]

This works when I run it.

<cffunction name="q_of_q" returntype="query">
<cfargument name="QueryIn" type="query">
<cfquery name="QueryOut" dbtype="query">
select count(*) thecount
from arguments.queryin
</cfquery>
<cfreturn QueryOut>
</cffunction>

<cfquery name="x" datasource="burns">
select 1 f1 from dual
</cfquery>

<cfset abc = q_of_q(Queryin = x)>
<cfdump var="#abc#" label="abc">

Try it and if it works, start modifying it until it either does what you want it to do, or crashes.

[BKBK · Accepted solution]

You're right, I left them out in my original post. But they are in the actual code.

No, that is indeed the problem. You have to leave the # signs out.  It has to be just ARGUMENTS.refData or refData, because it's the name of the query that follows "FROM", not the query object itself.

[jpcane]

Ahhh! I understand you now. Thank you for your help. Thanks to Dan Bracuk too.