I have a process that processes an xml document. For each node in the xml a stored proc is called to update data. What I wanted to do was combine all the results into a single query return. I did some digging and found an old blog post http://www.bennadel.com/blog/114-ColdFusion-QueryAppend-qOne-qTwo-.htm - Ben Nadel that showed how to do it. However, that post was a bit old so I figured there must be a better way by now.
I asked a friend and he suggested to try using query of queries with a union to combine them. This seemed intriguing but there was an issue with this. The code I was working with was all in cfscript. I remembered that dealing with queries of queries in cfscript is, well, challenging. I dug up a ColdFusion Cookbook entry http://cookbooks.adobe.com/post_Query_of_Query_with_CFSCRIPT-16492.html on using QofQ in cfscript.
Using this I was able to fashion together a function to combine two queries into one. I am not sure how will this would work if the queries had different columns. Now, instead of dealing with about 100 independent queries, I can deal with just one.
var qry1Result = arguments.qA;
var qry2Result = arguments.qB;
var qoqResult = '';
// create new query object
var qoq = new Query();
// set attribute of new query object to be a query result with arbortrary name
qoq.setAttributes(QoQsrcTableA = qry1Result);
qoq.setAttributes(QoQsrcTableB = qry2Result);
// use previously set attribute as table name for QoQ andset dbtype = query
qoqResult = qoq.execute(sql="select * from QoQsrcTableA union select * from QoQsrcTableB", dbtype="query");
// return result
private any function combineQuery(qA, qB)
Till next time...