Ben Nadel © 2008.
All content is the property of Ben Nadel and Kinky Solutions.
I just helped someone on the House of Fusion CF-Talk list who was having trouble with his ColdFusion query of queries. Like some of us do, he was declaring a local variable, LOCAL, in a function and then declaring variables within it. This is done so that only one variable (LOCAL) has to be "var'ed" to enable many variables to be considered local to the function's memory space:
Launch code in new window » Download code as text file »
His problem was that he was doing a query of queries using a query stored in the LOCAL pseudo scope:
Launch code in new window » Download code as text file »
This throws the error:
Query Of Queries syntax error.
Encountered "LOCAL.
This can be frustrating because if you change the name LOCAL to something like LOCAL2, it works just fine. The problem, while frustrating, has a very simple answer: LOCAL is a reserved word in SQL and ColdFusion Query of Query SQL. The solution: use the [ ] notation:
Launch code in new window » Download code as text file »
Download Code Snippet ZIP File
Comments (10) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
ColdFusion & AJAX: Converting ColdFusion Objects to Javascript Objects
ColdFusion Error: The Column Name "3a" Is Invalid
You did NOT just save me hours of grief. :-) found your blog post on google and wanted to say thanks. I always ended up var'ing another variable to use in my QofQ. Thanks for the insight!
Tim
Posted by Tim on Oct 4, 2006 at 3:23 PM
Tim,
No worries dude. Please let me know if there IS anything I can do to save you hours of grief?
Posted by Ben Nadel on Oct 4, 2006 at 4:22 PM
Using structure in Query of queries is buggy to say the least:
Using your example as a reference.. the following fails. I can't find anyway in which to get this to work, so must be a bug
<cfscript>
var LOCAL = structnew();
LOCAL.Data = QueryNew( "id,name" );
LOCAL.Data2 = QueryNew( "id,name" );
LOCAL.Test = StructNew();
</cfscript>
<cfquery name="qTest" dbtype="query">
SELECT
id,
name
FROM
[LOCAL].Data ,[LOCAL].Data2
where
[LOCAL].Data.id =1
</cfquery>
Posted by Phil Porter on Nov 29, 2006 at 6:49 AM
Phil,
The problem is in the WHERE clause. For some reason, it cannot handle the [LOCAL].Data.id line. However, if you take that away, and just have id = 1, it will parse correctly. However, it then throws an ambiguous column name (since neither ID nor NAME are prefixed and are available in both join tables).
And since you can't alias table names, you might be out of luck with this example. Sorry.
Posted by Ben Nadel on Nov 29, 2006 at 7:43 AM
would you guys please reduce using query of queries so much, and perhaps put a bit more time into sharpenning your sql join skills.
Posted by Anonymous on Apr 10, 2008 at 6:47 PM
@Anonymous,
Can you point out a place where you feel that a JOIN would be more appropriate than a query of queries?
Posted by Ben Nadel on Apr 10, 2008 at 7:09 PM
I ran into this issue recently and decided the quick fix at the time was to simply rename my LOCAL variable to LocalScope. Renaming LOCAL to fix a QoQ seemed odd to me at the time and it has been bugging me because I have not had the time to work on it.
I was about to ask you this very question, but I tried a quick search and there was the answer on the first result. I should have tried that earlier...
It's nice to be able to keep using LOCAL without worrying about query of queries inside functions. =)
Posted by Bradley Moore on May 12, 2008 at 4:09 PM
I love the idea of scoping all variables in a function inside a single "var" struct. However, if there's an evolving standard, then using a variable name without a naming conflict makes far more sense. If the name "local" creates a conflict in QoQ, why not change the variable name, not use a workaround.
The first two that sprung to mind were "func" and "my". They're both descriptive (enough) and they're short, which is nice considering the scope is prepended to all the variables local to the function.
Any thoughts on this? I guess it just bothers me that the "accepted" practice is one that includes an unnecessary bump in the road.
Posted by Adam Thorngren on Jul 14, 2008 at 11:36 AM
@Adam,
At CFUNITED 2008, Adam Lehman actually said that in ColdFusion 9, functions would have an explicit LOCAL scope. So, it looks like they are endorsing this idea of "local" even though it causes issues in Query of Queries.
Posted by Ben Nadel on Jul 14, 2008 at 11:42 AM
What about the highly descriptive term localVar? It's only eight characters, 1 more than REQUEST, 1 less than VARIABLES and 3 less than APPLICATION
<cfset var localVar = structNew()>
Posted by Henry K on Jul 14, 2008 at 11:43 AM