CFLib.org – Common Function Library Project

queryMerge(querysource, queryoutput, keyColumn[, mergeList])

Last updated July 21, 2004

author

Alain Blais

Version: 1 | Requires: CF5 | Library: DataManipulationLib

Description:
Merge the columns from a source query into a second query. The merge is base on the value of the primary key identified by the parameter "KeyColumn". For each match of the primary the values contained in the source queury will be added to the output query; creating a merging effect.

Return Values:
Returns a query.

Example:

<cfscript>
cols = "sName,sAge";
heads = "First Name,Age";
sourcequery = queryNew(cols);
queryAddRow(sourcequery,2);
querySetCell(sourcequery,"sName","Joe",1);
querySetCell(sourcequery,"sAge","25",1);
querySetCell(sourcequery,"sName","John",2);
querySetCell(sourcequery,"sAge","30",2);
</cfscript>
<cfscript>
cols = "sName,sClass,sGrade";
heads = "First Name,Class,Grade";
outputquery = queryNew(cols);
queryAddRow(outputquery,4);
querySetCell(outputquery,"sName","Joe",1);
querySetCell(outputquery,"sClass","Math",1);
querySetCell(outputquery,"sGrade","A",1);
querySetCell(outputquery,"sName","Joe",2);
querySetCell(outputquery,"sClass","Gym",2);
querySetCell(outputquery,"sGrade","C",2);
querySetCell(outputquery,"sName","John",3);
querySetCell(outputquery,"sClass","Gym",3);
querySetCell(outputquery,"sGrade","C",3);
</cfscript>
<cfdump var="#sourcequery#">
<cfdump var="#outputquery#">
<br><br>
Resulting query
<br>
<cfset temp = querymerge(sourcequery, outputquery, 'sName')>
<cfdump var="#outputquery#">

Parameters:

Name Description Required
querysource Source query. Yes
queryoutput Destination query. Yes
keyColumn Column (that exists in both queries) to merge on. Yes
mergeList List of columns from source query to add to destination query. Defaults to all of them. No

Full UDF Source:

/**
 * Merge two queries.
 * 
 * @param querysource 	 Source query. (Required)
 * @param queryoutput 	 Destination query. (Required)
 * @param keyColumn 	 Column (that exists in both queries) to merge on. (Required)
 * @param mergeList 	 List of columns from source query to add to destination query. Defaults to all of them. (Optional)
 * @return Returns a query. 
 * @author Alain Blais (Alain_blais@hotmail.com) 
 * @version 1, July 21, 2004 
 */
function querymerge(querysource,queryoutput,keyColumn){
	var mergeColumn = querysource.columnlist;
	var valueArray = arrayNew(1);
	// define counters
	var i = 1;
	var iRow = 1;
	var jRow = 1;
	//if there is a 4th argument, use that as the mergeColumn
	if(arrayLen(arguments) GT 3) mergeColumn = arguments[4];	
	//loop through the merge column
	for(i=1; i lte listLen(mergeColumn,','); i=i+1) {
		if (listFindNoCase(queryoutput.columnlist,listGetAt(mergeColumn,i,','),',') eq 0) {
		    // loop through each row of queryoutput and add information from querysource
			found = listGetAt(mergeColumn,i,',');
		    for (iRow=1; iRow lte queryoutput.recordcount; iRow=iRow+1) {
			    // find the row in querysource that matches the value in keycolumn from queryoutput  
				jRow = 1;
				while (jRow lt querysource.recordcount and querysource[keyColumn][jRow] neq queryoutput[keycolumn][iRow]) {
				    jRow = jRow + 1;
				}
				if (querysource[keyColumn][jRow] eq queryoutput[keycolumn][iRow]) {
				    valueArray[iRow] = querysource[listGetAt(mergeColumn,i,',')][jRow];
				}
			}
		    // add the columnm
			queryaddcolumn(queryoutput,listGetAt(mergeColumn,i,','),valueArray);
		}
	}
	return queryoutput;
}
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson