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