Pulling Data from Oracle and SQL 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...

 

[r937]

run your two queries separately

then use query of queries to combine them in coldfusion

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[RosieGp]

I did run first 2 queries separately but how do I write the 3rd one maintaning connections to both the DBs...

 

[r937]

that's the thing -- you don't need to "maintain connections" to the databases because you've already retrieved the data that you need from them

(and besides, "maintaining connections" is something you do in other application languages, not in coldfusion)

the query results are held in coldfusion memory as long as the template which ran them is active

please see the query of queries documentation --

http://livedocs.adobe.com/coldfusion/7/htmldocs/00001263.htm#1181595

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[RosieGp]

Thanks for guiding me thru...
Now I tried the following:

<cfquery datasource="SQL" username="USER" password="PWD" name="SQLQuery">
select EmpID, EmployeeName, Salary
from Employee
</cfquery>
Oracle query:
<cfquery datasource="ORA" username="USER1" password="PWD1" name="OracleQuery">
select EID, EmpName, Date_Hired
from EmpORA
</cfquery>
<cfquery dbtype="query" name="detail">
SELECT Employee.EmployeeName, Employee.Salary, EmpORA.Date_Hired
FROM SQLQuery, OracleQuery
WHERE Employee.EmpID==EmpORA.EID
</cfquery>

But it gives me the following error:
Query Of Queries syntax error.
Encountered "OracleQuery. Incorrect Select List, Incorrect select column,

The error occurred in D:\inetpub\

http://wwwroot\Oracle.cfm:

line 11

9 : </cfquery>
10 :
11 : <cfquery dbtype="query" name="detail">
12 : SELECT Employee.EmployeeName, Employee.Salary, EmpORA.Date_Hired
13: FROM SQLQuery, OracleQuery
WHERE Employee.EmpID==EmpORA.EID
Please let me know what did I do wrong...
Thanks.

 

[RosieGp]

Just a few typos that I'm correcting here:
<cfquery dbtype="query" name="detail">
SELECT Employee.EmployeeName, Employee.Salary, EmpORA.Date_Hired
FROM SQLQuery, OracleQuery
WHERE SQLQuery.EmpID==OracleQuery.EID
</cfquery>

 

[r937]

SQLQuery has the following column names:
- EmpID
- EmployeeName
- Salary

OracleQuery has the following column names:
- EID
- EmpName
- Date_Hired

these are the only column names you can use, and furthermore, you can ~not~ make reference to the tables they came from, just the names of the queries (hence, "query of queries")

<cfquery dbtype="query" name="detail">
SELECT [blue]SQLQuery[/blue].EmployeeName
, [blue]SQLQuery[/blue].Salary
, [blue]OracleQuery[/blue].Date_Hired
FROM SQLQuery
INNER
JOIN OracleQuery
ON OracleQuery.EID = SQLQuery.EmpID
</cfquery>

make sense?



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[LarrySteele]

Getting there, but still had a few typos that needed correcting:

[tt]
<cfquery dbtype="query" name="detail">
SELECT SQLQuery.EmployeeName,
SQLQuery.Salary,
OracleQuery.Date_Hired
FROM SQLQuery,
OracleQuery
WHERE SQLQuery.EmpID=OracleQuery.EID
</cfquery>
[/tt]

HTH,
Larry

 

[LarrySteele]

Rudy, just trying to help. Doesn't surprise me that you were already on the case while I was typing

 

[RosieGp]

Thanks... really appreciate your help...