Following on from all my work with web services in the last 6 months, I have recently had cause to use ColdFusions Query of Queries a lot.
Mainly due to the fact that I am converting web service responses into query recordsets and caching them in memory for later use. Then my web service facades will pull data from these cached queries (using QofQ) instead of calling the real web services.
Now as you should all know, when using components it is best practice to locally scope all your variables so that they are local to each function.
Note that all the below is tested on ColdFusion MX 7,0,0,91690.
E.G.
<cffunction name="echo">
<cfargument name="string">
<cfset var local = StructNew()>
<cfset local.return = arguments.string>
<cfreturn local.return>
</cffunction>
But this is where the QofQ problems begin. It seems you cannot use more than one scoped query name. You also cannot use the word "LOCAL" as it is a reserved word, and will cause your query to fail with a less than helpful error. So when using QofQ within a CFC, you need to use the variables scope to store the queries so taht you do not need to use the scope in the actual query.
Here are some examples
THIS WORKS (because the query names are not scoped)
<cfquery name="foo1" dbtype="query">
select * from query1, query2
where query1.column1 = query2.column1
</cfquery>
THIS WORKS (as only one scoped query is being queried)
<cfquery name="foo1" dbtype="query">
select * from locals.query1
where column1 = '#locals.somevar#'
</cfquery>
THIS DOES NOT WORK (because 2 scoped queries are being queried)
<cfquery name="foo1" dbtype="query">
select * from locals.query1, locals.query2
where locals.query1.column1 = locals.query2.column1
</cfquery>
I also noticed this rather odd behaviour as well.
When joining 2 queries as above, lets say you wanted to select all rows that do not meet certain conditions, as I did. Well using <> or != does not work. You have to use the NOT clause instead.
E.G.
!= does not work
<cfquery name="foo1" dbtype="query">
select * from query1, query2
where query1.column1 != query2.column1
And query1.column2 = 'foo'
</cfquery>
Neither does <>
<cfquery name="foo1" dbtype="query">
select * from query1, query2
where query1.column1 != query2.column1
And query1.column2 = 'foo'
</cfquery>
This does work
<cfquery name="foo1" dbtype="query">
select * from query1, query2
where NOT (query1.column1 = query2.column1 and query2.columns2 = 'foo')
</cfquery>
What happens in the first 2 examples is that rows are returned if eaither side of the condition is true rather than BOTH.
Recent Comments