CFLib.org – Common Function Library Project

queryCompare(query1, query2)

Last updated November 4, 2005

author

Qasim Rasheed

Version: 2 | Requires: CF6 | Library: DataManipulationLib

Description:
This function will compare two queries and returns a struct with the following keys in_query1_butnotin_query2 = A query which contains records from query 1 which are different than query 2. in_query2_butnotin_query1 = A query which contains records from query 2 which are different than query 1. message = a message which may be 1. Record are indential 2. Records do not match or 3. Query 1 had different nummber of columns than query 2.

Return Values:
Returns a struct.

Example:

<cfset test = querynew("language,rating")>
<cfset queryaddrow(test,3)>
<cfset querysetcell(test,"language","ColdFusion","1")>
<cfset querysetcell(test,"language","ASP","2")>
<cfset querysetcell(test,"language","Java","3")>
<cfset querysetcell(test,"rating","10","1")>
<cfset querysetcell(test,"rating","9","2")>
<cfset querysetcell(test,"rating","8","3")>

<cfset test1 = querynew("language,rating")>
<cfset queryaddrow(test1,3)>
<cfset querysetcell(test1,"language","ColdFusion","1")>
<cfset querysetcell(test1,"language","ASP","2")>
<cfset querysetcell(test1,"language","Java","3")>
<cfset querysetcell(test1,"rating","10","1")>
<cfset querysetcell(test1,"rating","9","2")>
<cfset querysetcell(test1,"rating","7","3")>
	
<cfset temp = queryCompare(test,test1)>
<cfdump var="#temp#">

Parameters:

Name Description Required
query1 First query. Yes
query2 Second query. Yes

Full UDF Source:

<!---
 This function will compare two queries and returns a struct which shows the difference between two queries if any.
 Fix by Rob Schimp
 
 @param query1 	 First query. (Required)
 @param query2 	 Second query. (Required)
 @return Returns a struct. 
 @author Qasim Rasheed (qasimrasheed@hotmail.com) 
 @version 2, November 4, 2005 
--->
<cffunction name="queryCompare" returntype="struct" output="false">
	<cfargument name="query1" type="query" required="true" />
	<cfargument name="query2" type="query" required="true" />
	
	<cfset var rStruct = StructNew()>
	<cfset var q1 = arguments.query1>
	<cfset var q2 = arguments.query2>
	<cfset var q3 = QueryNew( q1.columnlist )>
	<cfset var q4 = QueryNew( q2.columnlist )>
	<cfset var message = "">
	<cfset var rowch = false>
	<cfset var colArray = listtoarray(q1.columnlist)>
	<cfset var thisCol = "">
	<cfset var count = 1>
	<cfset var i = "">
	<cfset var j = "">
	
	<cfloop from="1" to="#listlen(q1.columnlist)#" index="thisCol">
		<cfif listfindnocase(q2.columnlist,listgetat(q1.columnlist,thisCol)) eq 0>
			<cfset message = "Columns in query1 (#q1.columnlist#) and query2 (#q2.columnlist#) doesn't match">
		</cfif>
	</cfloop>
	<cfif not len(trim(message))>
		<cfloop from="1" to="#listlen(q2.columnlist)#" index="thisCol">
			<cfif listfindnocase(q1.columnlist,listgetat(q2.columnlist,thisCol)) eq 0>
				<cfset message = "Columns in query1 (#q1.columnlist#) and query2 (#q2.columnlist#) doesn't match">
			</cfif>
		</cfloop>
	</cfif> 
	
	<cfif not len(trim(message))>
		<cfloop from="1" to="#q1.recordcount#" index="i">
			<cfset rowch = false>
			<cfloop from="1" to="#arraylen(colArray)#" index="j">
				<cfif comparenocase(q1[colArray[j]][i],q2[colArray[j]][i])>
					<cfset rowch = true>
				</cfif>
			</cfloop>
			<cfif rowch>
				<cfset queryaddrow(q3)>
				<cfloop from="1" to="#arraylen(colArray)#" index="k">
					<cfset querysetcell( q3, colArray[k], q1[colArray[k]][count] )>
				</cfloop>
			</cfif>
			<cfset count = count + 1>
		</cfloop>
		<cfset count = 1>
		<cfloop from="1" to="#q2.recordcount#" index="i">
			<cfset rowch = false>
			<cfloop from="1" to="#arraylen(colArray)#" index="j">
				<cfif comparenocase(q1[colArray[j]][i],q2[colArray[j]][i])>
					<cfset rowch = true>
				</cfif>
			</cfloop>
			<cfif rowch>
				<cfset queryaddrow(q4)>
				<cfloop from="1" to="#arraylen(colArray)#" index="k">
					<cfset querysetcell( q4, colArray[k], q2[colArray[k]][count] )>
				</cfloop>
			</cfif>
			<cfset count = count + 1>
		</cfloop>
		<cfif q4.recordcount OR q3.recordcount>
			<cfset message = "Records do not match">
		</cfif>
	</cfif>
	<cfif len(trim(message))>
		<cfset structinsert(rStruct,"message",message)>
		<cfset structinsert(rStruct,"in_query1_butnotin_query2",q3)>
		<cfset structinsert(rStruct,"in_query2_butnotin_query1",q4)>
	<cfelse>
		<cfset structinsert(rStruct,"message","Query 1 and Query 2 are identical")>
	</cfif>
	<cfreturn rStruct />
</cffunction>
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