Creating federated views - examples

This topic provides examples of creating federated views.

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

You are working with customer data on three separate servers, one in Europe, one in Asia, and one in South America. The Europe customer data is in an Oracle table. The nickname for that table is ORA_EU_CUST. The Asia customer data is in a Sybase table. The nickname for that table is SYB_AS_CUST. The South America customer data resides 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). The syntax to create a view from these three nicknames that merges this customer data is:

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 made by those customers. Each table has a column containing the customer number (CUST_NO). The syntax to create a federated view from these two nicknames that joins this data is:

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