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