Pulling Data from Oracle and SQL

[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...

Thanks.

 

[simian101]

Why do you need to get rid of the timestamp?

Simi

 

[RosieGp]

as I just want to see date in Date Hire column...

 

[SQLSister]

First do these two databases share the same employee id? If they don't do you have a cross refernce table? If not how do you know which John Smith belongs to which record when you have two of them? Employee name is not a unique value and cannot be the joining field.

Next look at OPENROWSET in Books online. I think this will give you waht you need in order to put the data together once you havea field you can join on.

If you are doing this in a SQL Server query using openrowset, you should be able to use Convert to display just the date.

"NOTHING is more important in a database than integrity." ESquared

 

[RosieGp]

Yes I do have 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...