Can I loop through the output of 2 select statements from a stored procedure based on a "order by a date" that is in the 2 different databases. For example I want to
select * from program
where cust_no = '125'Program has a field "posted"
Then
select * from history
where cust_no = '125'history has a field "posted".
Then I want to loop through the 2 selects display each line item in order by the date. I cannot use an inner join cause it will list the program for each line of history
Try a UNION ALL. It will include every record from both selects. Just make sure both selects have the same number of columns.
SELECT Posted FROM program WHERE cust_no = '125'
UNION ALL
SELECT Posted FROM history WHERE cust_no = '125'
ORDER BY (Your_Date_Field_Name)Since the table Program and History are from 2 different Database, we cannot union these tables. Try following it works.
<cfquery name="Result1" datasource="Datasource1" >
select * from program
where cust_no = '125'
</cfquery>
<cfquery name="Result2" datasource="Datasource2" >
select * from History
where cust_no = '125'
</cfquery>
<cfset myQuery = QueryNew("Posted, myDate","VarChar,Date")>
<!--- Loop over Result1 Query. Create a Row and add the values to cell --->
<cfloop query="Result1">
<cfset newRow = QueryAddRow(myQuery)>
<cfset temp = QuerySetCell(myQuery,"Posted",#Posted#)>
<cfset temp = QuerySetCell(myQuery,"myDate",#dated#)>
</cfloop>
<!--- Loop over Result2 Query. Create a Row and add the values to cell --->
<cfloop query="Result2">
<cfset newRow = QueryAddRow(myQuery)>
<cfset temp = QuerySetCell(myQuery,"Posted",#Posted#)>
<cfset temp = QuerySetCell(myQuery,"myDate",#dated#)>
</cfloop>
<!--- Order the resultant Query by the date --->
<cfquery name="FinalResult" dbtype="query">
Select Posted, myDate from myQuery order by myDate
</cfquery>
<cfoutput query="test">
#Posted# #DateFormat(myDate,'MM/DD/YYYY')#<br />
</cfoutput>Since the table Program and History are from 2 different Database, we cannot union these tables.
Possibly. But that all depends on the database and permissions.
UNION ALL should work either within a regular database query (assuming the correct permissions), or using a ColdFusion QoQ
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=using_recordsets_7.html
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.