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

Question

I would like to be able to do a query of a query to UNION an unknown number of recordset. However when doing a query-of-query dots or brackets are not allowed in record set names.

For example this fails:

<cfquery name="allRecs" dbtype="query">
    SELECT * FROM recordset[1]
    UNION
    SELECT * FROM recordset[2]
</cfquery>

Using dynamic variable names such as "recordset1" work but this is in a function and needs to be var-scoped so I can't build up the variable names dynamically without producing memory leaks in a persisted object.

Any other ideas?

Answer 1

Difficult task. I could imagine a solution with a nested loop based on GetColumnNames(), using QueryAddRow() and QuerySetCell(). It won't be the most efficient one, but it is not really slow. Depends on the size of the task, of course.

Your "create a function that combines two recordsets" could be made much more efficient when you create it to accept, say, ten arguments. Modify the SQL on the fly:

<cfset var local = StructNew()>

<cfquery name="local.union" dbtype="query">
  SELECT * FROM argument1
  <cfloop from="2" to="#ArrayLen(arguments)#" index="local.i">
    <cfif IsQuery(arguments[local.i])>
      UNION
      SELECT * FROM argument#local.i#
    </cfif>
  </cfloop>
</cfquery>

<cfreturn local.union>