loop through two select statements

[freshfitz]

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

[arrgh]

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)

[priya.r]

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# &nbsp;&nbsp; #DateFormat(myDate,'MM/DD/YYYY')#<br />
</cfoutput>

[arrgh]

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