1

A few times in my functions I have stuff like this:

<cffunction name="mergeData">
<cfquery name="myQuery">
SELECT columnName FROM tableName
</cfquery>

<cfquery dbtype="query" name="myOtherQuery">
SELECT columnName FROM myQuery
</cfquery>
</cffunction>

<cfset resulta = mergeData(queryA) />
<cfset resultb = mergeData(queryB) />
<cfset resultc = mergeData(queryC) />

Occasionally then I get the error The select column reference [myQuery.columnname] is not found in table [myQuery].

So what could be causing this. How can I diagnose. I was thinking it could be a scoping issue, so I'm going to add <cfquery name="local.myQuery"> just to make sure things are contained in the function (I should be doing that anyway probably). But when something only happens sometimes I have a hard time figuring out how to diagnose.

EDIT: Added some clarity on why it's most likely a scoping issue. My thought is that myQuery is poossibly being referenced in other calls. I mean, it's not like it's running multiple threads on the data, but is it possible that that could be the cause? Are there other causes? This isn't always the case when I get the error. I also get it on a page where it function is only running once.

Leeish
  • 5,203
  • 2
  • 17
  • 45
  • I locally scoped everything, made the variable names unique to each function and the errors went away. (at least in the last 5 hours.) I hope that means that it was the issue. – Leeish May 19 '16 at 20:27

2 Answers2

3

In the query of queries, use brackets around the local scope prefix.

<cffunction name="mergeData">
    <cfquery name="local.myQuery">
        SELECT columnName FROM tableName
    </cfquery>

    <cfquery dbtype="query" name="local.myOtherQuery">
        SELECT columnName FROM [local].myQuery
    </cfquery>
</cffunction>

<cfset resulta = mergeData(queryA) />
<cfset resultb = mergeData(queryB) />
<cfset resultc = mergeData(queryC) />
Adrian J. Moreno
  • 14,350
  • 1
  • 37
  • 44
  • Thanks, I'll try that, but still, any thoughts on the errors? – Leeish May 18 '16 at 18:59
  • Also, `SELECT [local].myQuery.columnName` doesn't seem to work. So if you do what you said, how do you reference columns. For example in my where clause I have `myQuery.columnName`. Can't get it to work with `local` no matter how I try to format it. – Leeish May 18 '16 at 19:07
  • @Leeish - A simple `SELECT columnName FROM [local].myQuery` should work fine. Unless your real code is doing something more complex than what you posted. If so, could you update your question to elaborate? – Leigh May 18 '16 at 22:27
1

I've never gotten LOCAL to work in query of queries in a function.

So I do this....

<cffunction>
<cfquery name="VARIABLES.myQuery">
SELECT columnName FROM tableName
</cfquery>

<cfquery dbtype="query" name="myOtherQuery">
SELECT columnName FROM VARIABLES.myQuery
</cfquery>
<cffunction>

I strongly suggest using a more explicit name for your query, especially in query of queries.

Evik James
  • 10,335
  • 18
  • 71
  • 122
  • I was just wondering the same, because when I try to use local I get an error at the `local.` part. – Leeish May 18 '16 at 18:39
  • Much of the time, I am forced to use SELECT * in query of queries. I think there are some real limitations with ColdFusion's QofQ. – Evik James May 18 '16 at 18:43
  • What do you mean by explicit name? – Leeish May 18 '16 at 18:45
  • Also, `variables` seems to be accessible anywhere in the component. That doesn't seem good and isn't the same as function scope. I think using `variables` is about as good as what I'm doing now but declaring nothing. – Leeish May 18 '16 at 18:52
  • Instead of myQuery use EmployeeBasicRegistrationInfoQuery or CurrentlyActiveEmployeesQuery. That's what I mean by more explicit. Perhaps your example used general names. – Evik James May 18 '16 at 19:06
  • Just an example yes, but I'm not sure just the name would fix the issue, but I'll see about it. I'm not using name explicit from variables in other functions so worth a try. However, in this particular instance, I don't think I'm calling functions in the `cfc` that use those same variable names. I still think it might be related to scoping, but... – Leeish May 18 '16 at 19:08
  • Why are you not `var`-ing your myQuery and myOtherQuery variables? – Dan Bracuk May 18 '16 at 19:43
  • Well, you can't do `` can you. That's the point of `local` isn't it. I can admit my bad habit of for some reason not doing it in the query names. However, how could `myQuery` exists, let alone be a query without having those columns in it? – Leeish May 18 '16 at 19:46
  • You can do ``. – Dan Bracuk May 18 '16 at 19:48
  • And then do ` – Leeish May 18 '16 at 19:56
  • I'm going to try to `local` scope everything and use unique names and see how that goes. If the problem remains I'll be really confused. – Leeish May 18 '16 at 20:31
  • 2
    @EvikJames *I've never gotten LOCAL to work in query of queries in a function* - Its bcoz `local` is a reserved word in CF QoQ. So you need to escape it using square brackets i.e., `[local].varName`. – Abhishekh Gupta May 19 '16 at 05:35