ColdFusion 9 Query of Query In CFScript

When doing a Query of Query using tag context you see:

<cfquery name="local.myQuery" dbtype="query">
SELECT *
FROM [local].myOtherQuery
WHERE Col1 = 'Chris'
</cfquery>

You would expect you could just do the same in CFScript under ColdFusion 9:

local.queryService = new query();
local.queryService.setName("myQuery");
local.queryService.setDBType("query");
local.objQueryResult = local.queryService.execute(sql="SELECT [Value] FROM [local].myOtherQuery WHERE Col1='Chris'");
local.queryResult= local.objQueryResult.getResult();

However this will produce an error:
coldfusion.sql.imq.imqException: Query Of Queries runtime error.
Table named local.myOtherQuery was not found in memory. The name is misspelled or the table is not defined.

Thanks to Jared Rypka-Hauer, I found a resolution to this lame error by using the setAttributes method:

local.queryService = new query();
local.queryService.setName("myQuery");
local.queryService.setDBType("query");
local.queryService.setAttributes(sourceQuery=local.myOtherQuery);
local.objQueryResult = local.queryService.execute(sql="SELECT [Value] FROM sourceQuery WHERE Col1='Chris'");
local.queryResult = local.objQueryResult.getResult();

This must be done because local.myOtherQuery is out of context at least until they release closures for ColdFusion.

If you would like to see the inner-works of this visit:
\CustomTags\com\adobe\coldfusion\query.cfc in your ColdFusion 9 install directory.

Update 7/7/2011:
Here’s an excellent blog from Ben Nadel also describing a solution to this issue:
http://www.bennadel.com/blog/2224-Performing-Query-Of-Queries-Using-ColdFusion-9-s-Query-cfc-Component.htm

Advertisements