Note:
Modify these examples for your environment. Do not try to execute them as they are written.
The following statement joins data between the Oracle database server, an IBM DB2 database, and a Sybase database:
SELECT O.CUSTNAME, P.PROJNO, E.ENAME, SUM(E.RATE*P."HOURS")
FROM ORDERS@DB2 O, EMP@ORACLE9 E, "PROJECTS"@SYBS P
WHERE O.PROJNO = P."PROJNO"
AND P."EMPNO" = E.EMPNO
GROUP BY O.CUSTNAME, P."PROJNO", E.ENAME;
Through a combination of views and synonyms, using the following SQL statements, the process of distributed queries is transparent:
CREATE SYNONYM ORDERS FOR ORDERS@DB2;
CREATE SYNONYM PROJECTS FOR "PROJECTS"@SYBS;
CREATE VIEW DETAILS (CUSTNAME,PROJNO,ENAME,SPEND)
AS
SELECT O.CUSTNAME, P."PROJNO", E.ENAME, SUM(E.RATE*P."HOURS")
SPEND
FROM ORDERS O, EMP E, PROJECTS P
WHERE O.PROJNO = P."PROJNO"
AND P."EMPNO" = E.EMPNO
GROUP BY O.CUSTNAME, P."PROJNO", E.ENAME;
Use the following SQL statement to retrieve information from the data stores in one statement:
SELECT * FROM DETAILS;
The statement retrieves the following table:
CUSTNAME PROJNO ENAME SPEND -------- ------ ----- ----- ABC Co. 1 Jones 400 ABC Co. 1 Smith 180 XYZ Inc. 2 Jones 400 XYZ Inc. 2 Smith 180