Re: distributed queries

From: Roderick Manalac <rmanalac_at_oracle.com>
Date: 27 Sep 1994 05:04:04 GMT
Message-ID: <368944$chg_at_dcsun4.us.oracle.com>


leng_at_cougar.vut.edu.au (Leng Kaing) writes:
|> Ok, I've been able to setup my clients and servers to communicate
|> over SQL*Net. I've also created some database links. I've also got the
|> same tables on many databases. For example, table "emp" resides on both
|> database A and database B. So how do I see all the records in all the
|> emp table on all the databases at once?
|>
|> eg: select * from emp_at_A, emp_at_B;
|> *
|> I'm expecting an output of some sort - either emp_at_A followed by emp_at_B;
|> or a cartesian product of all of the two tables. But what I get is:
|> ORA-00918: column ambiguous defined.
|>
|> Could someone please explain what I'm doing wrong? How do I write just
|> one SQL statement to see the records on all the databases with a given
|> table name?

Your original query is doing a cartesian product which may not be what you really want to see, but to get around the ORA-918 error, you would say
SELECT a.*, b.* FROM emp_at_A a, emp_at_B b

The query that will return all records from all emp tables would be as follows:
SELECT * FROM emp_at_A
UNION ALL
SELECT * FROM emp_at_B

Note that Oracle V6 only had UNION but not the UNION ALL operator and would result in duplicates getting tossed out. One kludgy workaround in that case would be to add a pseudo-column to each table (e.g.

SELECT a.*, 'A' FROM emp_at_A a
UNION
SELECT b.*, 'B' FROM emp_at_B b

and create a VIEW around the SELECT that omits the last column.

Hope this helps.
Roderick Manalac
Oracle Corporation Received on Tue Sep 27 1994 - 06:04:04 CET

Original text of this message