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

After posting the question I came up with a couple solutions but there might be a better one out there

• I could write dynamically named variables to the arguments scope and then reference them without their scope in query

• Create a function that accepts 2 recordsets as arguments and returns one combined recordset. This could be looped over to progressively add a recordset at a time. I'm sure this is very inefficient compared to doing all UNIONs in one query though.

Answer 2

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>

Answer 3

After a quick bit of poking around, I found this: queryConcat at CFLib.org. It uses queryaddrow/querysetcell to concatenate two queries.

I added a quick function (with no error checking, or data validation, so I wouldn't use it as-is):

<cffunction name="concatenate">
     <cfset var result = arguments[1]>
     <cfloop from="2" to="#arraylen(arguments)#" index="i">
          <cfset result=queryconcat(result, arguments[i])>
     </cfloop>
     <cfreturn result>
 </cffunction>

As a test, I threw this together:

Which does, in fact, give you fred/sammy/fred.

It's probably not the most efficient implementation, but you can always alter the insert/union code to make it faster if you wanted. Mostly, I was aiming to write as little code as possible by myself. :-)

Answer 4

all of the solutions added here should work for you, but I would also mention that depending on how much data you are working with and the database you are using, you might be better off trying to find a way to do this on the database side. With very large record sets, it might be beneficial to write the records to a temporary table and select them out again, but either way, if you can in any way rewrite the queries to let the database handle this in the first place you will be better off.