I had an issue where I needed to join data from two different datasets with out having an ODBC connection created by the admin.
I ran the two queries in thier respective databases then used query of queries to join them. This should work with differing types of databases like Oracle vs SQL Server.
Example (without brackets):
// query dataset 1
cfquery name="q1" dataset="ds1"
select emp_id, first name, last name
from tbl
/cfquery
//query dataset 2
cfquery name="q2" dataset="ds2"
select emp_id, salary
from tbl
/cfquery
// now use query of queries to merge the results
cfquery name="q3" dbtype="query"
select q1.emp_id, q1.first_name, q1.lastname, q2.salary
from q1, q2
where q1.emp_id = q2.emp_id
/cfquery
//output results
cfdump var="#q3#"
