Solved

How to write a SQL query from 2 separate databases

Posted on 2011-02-25
7
595 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:thamilto0410
  • 3
  • 2
  • 2
7 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 34979485
assuming link is from db1 to db2...

Select t2.username, sum(t2.errors)/sum(t1.pagesprocessed)  from table1 t1, table2@db2_link t2
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
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 400 total points
ID: 34979491
if link is from db2 to db1


Select t2.username, sum(t2.errors)/sum(t1.pagesprocessed)  from table1@db1_link t1, table2 t2
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
0
 
LVL 1

Author Comment

by:thamilto0410
ID: 34984789
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34984801
What do want to be returned if this happens?
0
 
LVL 1

Author Comment

by:thamilto0410
ID: 34985082
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.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 34985215
Based on the logic provided by sdstuber see if this helps.  If there's something missing please add to the test to show the problem.
drop table tab1 purge;
create table tab1(errors number, pagesprocessed number);

insert into tab1 values(1,0);
insert into tab1 values(1,0);

-- pagesprocessed = 0, trap error
select nvl(to_char(sum(errors)/case sum(pagesprocessed) when 0 then null else sum(pagesprocessed) end),'n/a')
from tab1;

insert into tab1 values(1,1);

-- pagesprocessed = 1, show results
select nvl(to_char(sum(errors)/case sum(pagesprocessed) when 0 then null else sum(pagesprocessed) end),'n/a')
from tab1;

Open in new window

0
 
LVL 1

Author Closing Comment

by:thamilto0410
ID: 34987727
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..
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
statspack purge automate 7 30
combine an MS SQL string in Idera DM 9 31
SQL Retrieve Values 4 44
MySQL left join performance 4 14
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now