CODEWITHSUNDEEP
sqlcoldfusion
Michael
Michael

Reputation: 39

ColdFusion: Get RecordCount with a variable in the name

I want to get the record count of a query that has a variable in it's name.

<cfloop query="Getteam">
    <cfquery name="GetJobs#teamstaffid#" datasource="#dataSource#" >
        SELECT  *
        FROM    Request, Hist_Req_Assign, Hist_Req_status
        WHERE   hist_req_assign.teamstaffid = '#teamstaffid#' AND
                hist_req_assign.requestid = request.requestid AND
                hist_req_status.requestid = request.requestid AND
                hist_req_status.statusid = '3'
    </cfquery>
</cfloop>

GetTeam spits out the ID of each staff member in my team.
And GetJob#teamstaffid# gets all their jobs.

MY first instinct is to do: <cfoutput>#GetJobs#teamstaffid#.RecordCount#</cfoutput>

This obviously wont work though. How can I get the record count of each team member? Thanks

Upvotes: 2

Views: 1901

Answers (5)

Keith Fosberg
Keith Fosberg

Reputation: 109

In a direct answer to the question; you can extract the data name in context (GetJobs#teamstaffid#) within each iteration and store it to a global list/array/ structure to use later but, as has been discussed, it would be better to structure the query to get everything at once.

It would be hard to provide a specific on the SQL without knowing your schema.

Upvotes: 0

KAR
KAR

Reputation: 976

Just use Coldfusion function Evaluate

<cfoutput>#Evaluate("GetJobs#teamstaffid#").RecordCount#</cfoutput>
<cfoutput>#Evaluate("GetJobs#teamstaffid#").column1#</cfoutput>
<cfoutput>#Evaluate("GetJobs#teamstaffid#").column2#</cfoutput>
.....

While you can access any column or field from the record set using Evaluate, it's better to store the returned query object first in a variable (within the loop) as a short hand access so you could easily reach other columns/fields from the query record set.

<cfloop query="Getteam">
    <cfquery name="GetJobs#Getteam.teamstaffid#" datasource="#dataSource#" >
        SELECT  *
        FROM    Request, Hist_Req_Assign, Hist_Req_status
        WHERE   hist_req_assign.teamstaffid = '#Getteam.teamstaffid#' AND
            hist_req_assign.requestid = request.requestid AND
            hist_req_status.requestid = request.requestid AND
            hist_req_status.statusid = '3'
    </cfquery>

    <cfset QGetJob = #Evaluate("GetJobs#Getteam.teamstaffid#")# />

    <cfoutput>#QGetJob.RecordCount#</cfoutput>
    <cfoutput>#QGetJob.column1#</cfoutput>
    <cfoutput>#QGetJob.column2#</cfoutput>
</cfloop>

Upvotes: 0

jawahar N
jawahar N

Reputation: 482

The cfquery tag returns some result variables in a structure. So, we use result attribute in the cfquery tag we can able to get some details of the query.

For example: 1. resultname.sql 2. resultname.recordcount

<cfloop query="Getteam">
    <cfquery name="GetJobs#teamstaffid#" datasource="#dataSource#" result="resultname">
        SELECT  *
        FROM    Request, Hist_Req_Assign, Hist_Req_status
        WHERE   hist_req_assign.teamstaffid = '#teamstaffid#' AND
                hist_req_assign.requestid = request.requestid AND
                hist_req_status.requestid = request.requestid AND
                hist_req_status.statusid = '3'
    </cfquery>
</cfloop>

<cfoutput>#resultname.recordcount#</cfoutput>

Upvotes: 0

Alex
Alex

Reputation: 7833

The name attribute in your cfquery tag is the output variable of your query. If not specified, the default scope of a variable such as GetJobs#teamstaffid# is VARIABLES. Variable scopes in ColdFusion are structs.

To access dynamic variables, use the bracket notation for structs:

<cfoutput>#VARIABLES["GetJobs#teamstaffid#"].RecordCount#</cfoutput>

Note: If your code is part of a function, use the LOCAL scope instead of the VARIABLES scope.

Upvotes: 4

Keith Fosberg
Keith Fosberg

Reputation: 109

I would probably do something along these lines:

<cfscript>
try {
    sql = "select * from Request, Hist_Req_Assign, Hist_Req_status where hist_req_assign.requestid = request.requestid and hist_req_status.requestid = request.requestid and hist_req_status.statusid = '3'";
    principalQuery = new query();
    principalQuery.setDatasource(dataSource);
    result = principalQuery.execute(sql=preserveSinglequotes(sql));
    getJobs = result.getResult();

    for(i=1;i<=listLen(teamstaffid);i++){ 
        sql = "select request, Hist_Req_Assign, Hist_Req_status from sourceQuery where hist_req_assign=#teamstaffid[i]#";
        local.queryService = new query();
        local.queryService.setName("employee");
        local.queryService.setDBType("query");
        local.queryService.setAttributes(sourceQuery=getJobs);
        local.objQueryResult = local.queryService.execute(sql=sql);
        local.queryResult = local.objQueryResult.getResult();
        writeOutput("Employee " & teamstaffid[i] & " has " & local.queryResult.recordcount & " records.");
    }
    } catch (any e){
        //whatever
    }
</cfscript>

Upvotes: 1

Related Questions