Advertisement
mrbusche

Dynamic Query of Query

Jan 29th, 2012
126
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <cffunction name="OuterJoin" output="no" returntype="query">
  2.     <cfargument name="QueryA" type="query" required="yes" />
  3.     <cfargument name="QueryB" type="query" required="yes" />
  4.     <cfargument name="joinColumn" type="string" required="yes" />
  5.    
  6.     <!-- QoQ doesn't like scoped queries --->
  7.     <cfset var Query1 = Arguments.QueryA />
  8.     <cfset var Query2 = Arguments.QueryB />
  9.     <cfset var JoinQuery = '' />
  10.     <cfset var Result = '' />
  11.     <cfset var IgnoreIDs = '' />
  12.    
  13.     <cfquery dbtype="query" name="joinQuery">
  14.     SELECT *
  15.     FROM Query2
  16.     WHERE Query2.#Arguments.JoinColumn# = <cfqueryparam value="-1" />
  17.     </cfquery>
  18.    
  19.     <cfset QueryAddRow( joinQuery ) />
  20.    
  21.     <!--- Value List doesn't like dynamic things --->
  22.     <cfset IgnoreIDs = '' />
  23.     <cfloop query="Arguments.QueryB">
  24.         <cfset IgnoreIDs = ListAppend( IgnoreIDs, Query2[ Arguments.JoinColumn][ Query2.CurrentRow ] ) />
  25.     </cfloop>
  26.    
  27.     <cfquery dbtype="query" name="Local.Result">
  28.     SELECT *
  29.     FROM Query1, Query2
  30.     WHERE Query1.#Arguments.JoinColumn# = Query2.#Arguments.JoinColumn#
  31.    
  32.     UNION
  33.    
  34.     SELECT Query1.*, JoinQuery.*
  35.     FROM Query1, JoinQuery
  36.     WHERE Query1.#Arguments.JoinColumn# NOT IN (<cfqueryparam value="#IgnoreIDs#" list="yes" />)
  37.     </cfquery>
  38.    
  39.     <cfreturn Result />
  40. </cffunction>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement