This is addressed easily enough. Sorry, OCD kicking in and I'm feeling
helpful to boot. Consider the following not-so-pseudo code:
<cffunction name="mergeQueries"... returntype="query">
<cfargument name="queries" type="struct" required="true">
<!--- assume arguments.queries contains these three struct keys, which
themselves are queries :
query1: thedate, name, flavor, size
query2: thedate, origin, dimension
query3: thedate, name, destination
--->
<cfset var columns = ""><!--- master column list, will be used to
create the master merge query --->
<cfset var q = "">
<cfset var c = "">
<cfset var merged = "">
<cfset var temp = "">
<cftry>
<!--- add each query's columns which haven't been added to the
master list --->
<cfloop collection="#arguments.queries#" item="q">
<cfif isquery(arguments.queries[q])><!--- ensure its a query
--->
<cfloop list="#arguments.queries[q].columnlist#"
index="c"><!--- loops the columns in the current query --->
<cfif not listfindnocase(columns,c)><!--- column isn't
in the master columns list --->
<cfset columns = listappend(columns,c)>
</cfif>
</cfloop>
</cfif>
</cfloop>
<!--- create the master query --->
<cfset merged = querynew(columns)>
<!--- loop the queries and add their data to the master query --->
<cfloop collection="#arguments.queries#" item="q">
<cfif isquery(arguments.queries[q]) and
arguments.queries[q].recordcount><!--- ensure it's a query and make sure it
has data --->
<cfset temp = arguments.queries[q]><!--- easier reference
to the query --->
<cfloop query="temp">
<cfset queryaddrow(merged,1)>
<cfloop list="#temp.columnlist#" index="c"><!---loop
the columns in the current query, only setting those values in the master,
others will be 'null' --->
<cfset
querysetcell(merged,c,temp[c][currentrow])><!---by default is setting
values for the last row --->
</cfloop>
</cfloop>
</cfif>
</cfloop>
<cfquery dbtype="query" name="q">
SELECT *
FROM merged
ORDER BY thedate ASC
</cfquery>
<cfreturn q><!--- the returned query will be a merge of all queries
in the struct, and for each row, those columns which didn't exist in a
given query will simply be an empty string --->
<cfcatch>
<cfrethrow>
</cfcatch>
</cftry>
</cffunction>
On Tue, Jul 10, 2012 at 8:12 PM, Matt C <[email protected]> wrote:
> Ah, my problem there was that the queries could contain different fields
> and different numbers of fields. The only definites are the Date and
> a record number unique to the query. So one big master query was
> technically out of the question. I did however end up using a query of
> queries to pull and sort just the unique number, the date, and a custom
> "whichQuery" field from each query. Then when outputing that QofQ, I could
> run another QofQ using the unique number and "whichQuery" to get the rest
> of the data for that record from the associated original query.
>
>
> On Tuesday, July 10, 2012 1:02:50 PM UTC-4, websolete wrote:
>
>> Not to beat a dead horse, since you seem to be happy with your solution,
>> but if elegant is a goal I would have gone with merging all the queries
>> into a single query inside the cfc, then queryOfQuery it for the sort you
>> wanted, returning the merged master query as a single entity, as that's
>> about as simple as you can get to work with for output.
>>
> --
> online documentation: http://openbd.org/manual/
> http://groups.google.com/group/openbd?hl=en
>
--
online documentation: http://openbd.org/manual/
http://groups.google.com/group/openbd?hl=en
