coldfusion - How can I use query-of-query UNION on n-recordsets when var scoping is needed? -

I would like to be able to inquire about a query, an unknown number of UNIX records. However, when a query-of-query dots or brackets record set names are not allowed.

For example, this fails:

  & lt; Cfquery name = "allRecs" dbtype = "query" & gt; Select from the records [1] Select the union * From the records [2] & lt; / Cfquery & gt;  

Using dynamic variable names such as "RecordSet 1" work, but it is in a function and it needs to be var-scoped so that I design memory leaks without dynamically You can have a permanent object.

Any other ideas?

The hard work I get GetColumnNames () , QueryAddRow () and QuerySetCell () could imagine a solution with a nested loop. It will not be the most efficient, but it does not really slow down, definitely depends on the size of the work.

Your "Connect two record sets adds a function" can be made more efficient when you make it to accept, say, modify the SQL when you fly ten logic:

  & lt; Cfset var local = StructNew () & gt; & Lt; Cfquery name = "local.union" dbtype = "query" & gt; SELECT * FROM argument1 & lt; Cfloop = "2" = "#Arrayen (argument) #" index = "local.i"> gt; & Lt; Cfif isQuery (argument [local.i]) & gt; Select Union * Logic # local.i # & lt; / Cfif> & Lt; / Cfloop & gt; & Lt; / Cfquery & gt; & Lt; Cfreturn local.union & gt;