CFC Query - Should be easy!

[RonKochanowski]

In MySQL we have the ability to process the following:

SELECT @n := COUNT(something) FROM sometable

and then use that result in another query to generate a percentage:

SELECT something, (COUNT(something)*100/@n AS percent FROM sometable GROUP BY
something

I know this is probably very easy to accomplish using a CFC. I would guess
that one function in the CFC would call another function in the CFC, each
containing the appropriate query, but I'm missing something...

<!--- GridFill() method --->
<cffunction name="gridFill" returntype="query" hint="retrieve data to
populate a grid">

<!--- make call to function within this component --->
<!--- <cfinvoke
component="cf.cfc.missionDb"
method="getRecords"
returnvariable="getRecordsRet">
</cfinvoke> --->

<!--- var scoped variables --->
<cfset var getList = "">

<!--- run the query --->
<cfquery name="getList" datasource="bag">
SELECT svcRegion, (COUNT(svcRegion)*100)/3 AS percent FROM m_name GROUP
BY svcRegion
</cfquery>
<!--- send the results --->
<cfreturn getList>
</cffunction>

<!---
function getRecords
Generated by the CFC Recordset ColdFusion MX Extension
Sun Jul 24 16:13:14 GMT-0400 (Eastern Daylight Time) 2005
--->
<cffunction name="getRecords" output="false" access="public"
returntype="query">
<cfset var recCount = "" >
<cfquery name="recCount" datasource="bag">
SELECT Count(*) as rec_Count FROM m_name
</cfquery>
<cfreturn recCount>
</cffunction>

The highlighted '3' in '(COUNT(svcRegion)*100)/3 AS percent ' above would be
replaced by variable in 'recCount'. Alas, it's not working. I'm sure that
it's something quite simple, but I'm stumped...

[MikerRoo]

First replace this line:
<!--- run the query --->
with this:
<!--- Run the query. IMPORTANT: If getRecordsRet does not return EXACTLY one
row, then an error (or bad results) will occur. --->

Then change this line:
SELECT svcRegion, (COUNT(svcRegion)*100)/3 AS percent FROM m_name GROUP BY
svcRegion
to this:
SELECT svcRegion, (COUNT(svcRegion)*100)/#getRecordsRet.rec_Count# AS percent
FROM m_name GROUP BY svcRegion

Regards,
-- MikeR

[RonKochanowski]

Thanks for the response Mike. Your suggestion was one of the things that I had
tried before posting... it didn't work. And now I'm even more confused and
frustrated. So, I will post the code that I'm putting together, both the CFC
and the CFM that calls it:



<!---
Filename: missionDb.cfc
Author: Ron Kochanowski
Purpose: component used to access the missions tables for data input and
retrieval
--->

<!--- define the CFC --->
<cfcomponent hint="Provides a means to enter, edit and retrieve data for
missions from the database">

<!---
function getRecords
Generated by the CFC Recordset ColdFusion MX Extension
Sun Jul 24 16:13:14 GMT-0400 (Eastern Daylight Time) 2005
--->
<cffunction name="getRecords" output="false" access="private"
returntype="query"
hint="a method that returns a query object containing the number of records
in the table.">

<!--- establish the variable to hold the returned info --->
<cfset var recCount = "" >

<!--- run the query --->
<cfquery name="recCount" datasource="bag">
SELECT Count(*) as rec_Count FROM m_name
</cfquery>
<!--- return the results --->
<cfreturn recCount>
</cffunction>

<!--- GridFill() method --->
<cffunction name="gridFill" returntype="query" hint="retrieve data to
populate a grid">

<!--- var scoped variables --->
<cfset var getList = "">

<!--- make call to function within this component --->
<cfinvoke method="getRecords" returnvariable="getRecordsRet"></cfinvoke>
<cfset var regionCount = getRecordsRet.rec_Count>
<cfoutput>#regionCount#</cfoutput>

<!--- run the query --->
<cfquery name="getList" datasource="bag">
SELECT svcRegion, (COUNT(svcRegion)*100)/3 AS percent FROM m_name GROUP
BY svcRegion
</cfquery>
<!--- send the results --->
<cfreturn getList>
</cffunction>

</cfcomponent>


<!--- missions_summary.cfm -- dreamweaver template info removed --->
<!--- make connection to the cfc method that works the form --->
<cfinvoke component="cf.cfc.missionDb" method="gridFill"
returnvariable="gridFillRet"></cfinvoke>

<!--- create the form --->
<cfform name="summaryForm" preloader="yes" format="flash" width="400"
height="400" skin="haloblue">
<cfformgroup type="accordion">
<cfformgroup type="page" label="Supported Missionaries by Region">
<cfgrid name="byRegion" query="gridFillRet" rowheaders="no">
<cfgridcolumn name="svcRegion" header="Region" />
<cfgridcolumn name="percent" header="Percentage" />
</cfgrid>
</cfformgroup>
<cfformgroup type="page" label="Missionary Support by Ministry">
<cfgrid name="byMinistry" rowheaders="no">
<cfgridcolumn name="ministry" header="Ministry" />
<cfgridcolumn name="percent" header="Percentage" />
</cfgrid>
</cfformgroup>
</cfformgroup>
</cfform>