Pulling data form oracle and sql in single query 1

[RosieGp]

I have 2 different DBs one in orcale and other one in SQl that I need to pull data from...
I need 3 output columns: Employee Name Date Hire Salary
The problem is that Date Hire is in Oracle and salary is in SQL and Employee Name is in Both DBS.
SQL query:
<cfquery datasource="SQL" username="USER" password="PWD" name="SQLQuery">
select EmployeeName, Salary
from Employee
</cfquery>
Oracle query:
<cfquery datasource="ORA" username="USER1" password="PWD1" name="OracleQuery">
select EmpName, Date_Hired
from EmpORA
</cfquery>

Is there a way to make it work with a query... and the Date Column has time stamp with date (2009-05-27 11:01:57.0). How do I get rid of the time stamp...

Yes I do have unique Employee ID in both the tables...
As I stated above, my connection source is Cold Fusion...
For each query I have to provide DataSource, UID, PWD to pull the data from servers...

Can you examplify how can I do a join on Employee ID, include connection strings from both Data sources using Openrowset

I tried to do the following:
<cfquery datasource="SQL" username="USER" password="PWD" name="SQLORAQuery">
select EmpName, Date_Hired
from openrowset('MSDAORA.1', 'User ID=USER1'; 'Password=PWD1'; 'datasource=ORA', EmpORA)
where ORAEMPID == SQLEMPID
</cfquery>

Is there anything wrong with my query that it is not working...
Thanks...

 

[ChrisHunt]

I don't speak Cold Fusion, so I can't be sure, but it doesn't sound like something you'd be able to do. A question asked on forum232 would get you a more authoratative answer, and maybe the one you want to hear.

Is it possible to establish a database link from one database to the other? Then you could create views on (say) the Oracle database that would point at the tables on the SQL database. Then your cf script would only have one database to connect to.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd