Re: distributed queries

In article 4v2_at_cougar.vut.edu.au, leng_at_cougar.vut.edu.au (Leng Kaing) writes:
>Environment: SunOs 4.1.2, SQL*Net 1.1, Oracle 6.0.36...
>
>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?
>
>Please help me out,
>Leng.

select * from emp_at_a
  UNION
select * from emp_at_b
/
Oracle7 supports UNION ALL if you want to see dupes.

Later!

Sincerely,

Martin Farber
Independent Oracle Consultant "A Jack of all trades and a slave to one."

                        |  NYNEX Science & Technology
                        |  500 Westchester Ave, Rm 1B-23
                        |  White Plains, NY  10604
                        |     -----------------
                        |  email: farber_at_nynexst.com
                        |  Voice: 914/644-2656
                        |    FAX: 914/644-2216