thamilto0410
asked on
How to write a SQL query from 2 separate databases
I did not search because I don't know exactly how to phrase this. I will present the scenario and ask that someone provide me with an example of what I need or a link to an example.
I have 2 separate oracle databases. I am looking to get data from one table in each database and then provide computed results to a report.
The first table in db 1 is a statistics table, the table in db2 is a corrections table. They will link on username.
The pseudo code for what I want is: Select username, sum(errors) from db2 also select username sum(pagesprocessed) from db1 and both where date_stamp between startdate and enddate provided by a webform. The results will be displayed by username as column 1 and column 2 being the sum(pagesprocessed) / sum(errors).
I have done this in a prior application but I did it by pulling 2 recordsets populating 2 arrays and looping the arrays. I would like to know : 1 is it possible to do in a single query and 2 can someone provide an example or a link to an example. And know that one of the DBs does link to the other DB through a DBLINK. Thanks.
I have 2 separate oracle databases. I am looking to get data from one table in each database and then provide computed results to a report.
The first table in db 1 is a statistics table, the table in db2 is a corrections table. They will link on username.
The pseudo code for what I want is: Select username, sum(errors) from db2 also select username sum(pagesprocessed) from db1 and both where date_stamp between startdate and enddate provided by a webform. The results will be displayed by username as column 1 and column 2 being the sum(pagesprocessed) / sum(errors).
I have done this in a prior application but I did it by pulling 2 recordsets populating 2 arrays and looping the arrays. I would like to know : 1 is it possible to do in a single query and 2 can someone provide an example or a link to an example. And know that one of the DBs does link to the other DB through a DBLINK. Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sdstuber: Sorry for the delay after I sent the question my computer at work went down I was not able to get back to you till now. Looks exactly like what I needed to see an example of. One last question suppose some of the users have pages but not errors. That would be division by zero which is not possible how do I handle that? Thanks.
What do want to be returned if this happens?
ASKER
slightwv: Thank you for offering to assist. The report will show users, errorcount, page count and then a % dividing the errors into the pages. There will always be pages. If there are no errors I would like to show 0 for the errors and something like n/a for the division. Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you both. I had not written anything like this before and the answers provided got me to a working solution with the sql offered by sdstuber and the nvl function offered by slightwv..
Select t2.username, sum(t2.errors)/sum(t1.page
where t1.date_stamp between startdate and enddate
and t2.date_stamp between startdate and edndate
and t1.username = t2.username
group by t2.username