Coldfusion – queryOfQueries union – not elegant but effective :)


<cfset Local.currencies = arrayNew(1)>
 <cfset arrayAppend(Local.currencies, 'EUR')>
 <cfset arrayAppend(Local.currencies, 'HRK')>
 <cfset arrayAppend(Local.currencies, 'BGN')>
 <cfset arrayAppend(Local.currencies, 'RON')>

 <cfset currencyList = structNew()>

 <cfloop from="1" to="#arrayLen(Local.currencies)#" index="a">

 <cfset local.currency = structNew()>

 <cfset local.currency.currency = Local.currencies[a]>

 <cfset arguments.currency = Local.currencies[a]>

 <cfset local.currency.query = get_paiment_report_Query(argumentCollection = arguments)>

 <cfset currencyList[local.currency.currency] =  local.currency>

 </cfloop>

 <cfquery name="q_payments" dbtype="query" result="queryResult">

 <cfset var count = 0>

 <cfloop collection="#currencyList#" item="key"  >

 <cfif currencyList[key].query.recordcount neq 0>

 <cfset count = count+1>

 <cfif count gt 1 >

 UNION

 </cfif>

 SELECT * FROM currencyList.#key#.query

 </cfif>

 </cfloop>

 </cfquery>
This entry was posted on Friday, April 9th, 2021 at 12:52 pmand is filed under Coldfusion. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.

Comments are closed.