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.

view plain print about
1private any function combineQuery(qA, qB)
2 {
3 var qry1Result = arguments.qA;
4 var qry2Result = arguments.qB;
5 var qoqResult = '';
6        
7 // create new query object
8 var qoq = new Query();
9
10 // set attribute of new query object to be a query result with arbortrary name
11
12 qoq.setAttributes(QoQsrcTableA = qry1Result);
13 qoq.setAttributes(QoQsrcTableB = qry2Result);
14
15 // use previously set attribute as table name for QoQ andset dbtype = query
16 qoqResult = qoq.execute(sql="select * from QoQsrcTableA union select * from QoQsrcTableB", dbtype="query");
17
18 // return result
19 return qoqResult.getResult();
20
21 }
22    
23}

Till next time...

--Dave