Referencing a cfquery of dbtype="query" in a cffunction

I have the following in file1.cfm:
query1 - uses a datasource
query2 - uses dbtype="query" and uses "from query1" in the select statement.

I have a functions.cfm file.

In it I have the following:

<cffunction name=x returntype="string">
<cfargument name=tn type="numeric">

<cfquery name="query4" dbtype="query">
select * from query2 where z = "#tn#"
</cfquery>
.
.
More stuff to do calculations and return a value down here
.
</cffunction>

From file1, if I call the function with #x(query2.tn)#, it seems that query2 is not being recognized in the cffunction x.

What do I need to do to make this type of scenerio work?
dmarnloAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jtreherCommented:
you could pass the query in as an argument to your function. Just name the arguement something other than query2 to keep them all straight. You can then perform operations using the query local to the function.
jtreherCommented:
Just further clarify -

You are calling a functon and passing it the value of 'query2.tn'. The scope of your query is local to the calling page, so the function doesn't know about it.

The only variable your function recognizes is the column value tn. An easy fix will be to pass both the tn and query2 in comma delims.

<cffunction name=x returntype="string">
<cfargument name="theQuery" type="query">
<cfargument name=tn type="numeric">

</cffunction>

#x(query2.tn,query2)#

From Macromedia:
"Other variable scopes

A CFC (or external function file) shares the Form, URL, Request, CGI, Cookie, Client, Session, Application, Server, and Flash scopes with the calling page. Variables in these scopes are also available to all pages that are included by a CFC. These variables do not have any behavior that is specific to CFCs."

dmarnloAuthor Commented:
Still have an error:

Here is my query in the main .cfm

<cfquery name="stand" dbtype="query" >
SELECT (Sum(win)*2 + Sum(tie)) / ((Sum(win)+ Sum(loss) + Sum(tie)) * 2) as wpercent,
Sum(rf)  AS srf, Sum(ra) AS sra,
Sum(points) AS spoints,  
Sum(win) AS swin, Sum(loss) AS sloss, Sum(tie) AS stie ,

division,tm,TeamNumber

FROM ustand
GROUP BY division, tm, TeamNumber
ORDER BY division, spoints desc, wpercent desc, sra
</cfquery>


Here is the call to the function:

#streak(stand.TeamNumber,stand)#

Here is the function in function.cfm

<cffunction name=streak returntype="string">
<cfargument name=tn type="numeric">
<cfargument name=theQuery type="query">


<cfquery name="stkdata"  dbtype="query">
SELECT (case when Points = 2 then 'W'
        when Points = 1 then 'T'
            when Points = 0 then 'L' end) as Results from theQuery where TeamNumber = #tn#
order by GameDate Desc
</cfquery>

<cfset cnt = 0>
<cfset wlt = "#stkdata.Results[1]#">

<cfloop query="stkdata">
<cfif wlt eq "#stkdata.Results#">
<cfset cnt = cnt + 1>
<cfset wlt =  "#stkdata.Results#">
<cfelse>
<cfreturn "#wlt# " &  "#cnt#">
<cfabort>
</cfif>
</cfloop>
<cfreturn "#wlt# " &  "#cnt#">
</cffunction>

Here is the error I get:

Error Executing Database Query.  

Query Of Queries syntax error.
Encountered "case" at line 0, column 0. Incorrect Select List,  
 
The error occurred in C:\Inetpub\wwwroot\CFIDE\functions.cfm: line 18
Called from C:\Inetpub\wwwroot\CFIDE\TMPeibfjbibz.cfm: line 109
Called from C:\Inetpub\wwwroot\CFIDE\functions.cfm: line 18
Called from C:\Inetpub\wwwroot\CFIDE\TMPeibfjbibz.cfm: line 109
 
16 : SELECT (case when Points = 2 then 'W'
17 :         when Points = 1 then 'T'
18 :             when Points = 0 then 'L' end) as Results from theQuery where TeamNumber = #tn#
19 : order by GameDate Desc
20 : </cfquery>

 

jtreherCommented:
Ahhhh, you can't run 'case' on a query of queries.

Macromedia limits you to what query of queries can do, it's definitley not a roboust SQL system.

http://livedocs.macromedia.com/coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm see that for 6.1 and go to the next page for some archaic syntax

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jtreherCommented:
I myself am actually battling this at the moment because I realized that something isn't working correctly with my query and the function I need is outside the scope of QoQ, so it's back to the SQL server.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.