Creating federated views - examples

These examples show how to create federated views to access data from several data sources. The examples show the syntax for the CREATE VIEW statement for federation.

Example: Creating a federated view that merges similar data from several data source objects

You are working with customer data on separate servers: one in Europe, one in Asia, and one in South America. The European customer data is in an Oracle table. The nickname for that table is ORA_EU_CUST. The Asian customer data is in a Sybase table. The nickname for that table is SYB_AS_CUST. The South American customer data is in an Informix® table. The nickname for that table is INFMX_SA_CUST. Each table has columns containing the customer number (CUST_NO), the customer name (CUST_NAME), the product number (PROD_NO), and the quantity ordered (QUANTITY). To create a view from these nicknames that merge this customer data, issue the following statement:

CREATE VIEW FV1 
   AS SELECT * FROM ORA_EU_CUST
   UNION 
   SELECT * FROM SYB_AS_CUST
   UNION 
   SELECT * FROM INFMX_SA_CUST

Example: Joining data to create a federated view

You are working with customer data on one server and sales data on another server. The customer data is in an Oracle table. The nickname for that table is ORA_EU_CUST. The sales data is in a Sybase table. The nickname for that table is SYB_SALES. You want to match up the customer information with the purchases that are made by those customers. Each table has a column containing the customer number (CUST_NO). To create a federated view from these nicknames that joins this data, issue the following statement:

CREATE VIEW FV4 
   AS SELECT A.CUST_NO, A.CUST_NAME, B.PROD_NO, B.QUANTITY 
   FROM ORA_EU_CUST A, SYB_SALES B
   WHERE A.CUST_NO=B.CUST_NO