A CONNX View is a logical CONNX Data Dictionary table that is in essence a SQL statement. Views enhance the usability of CONNX by end-users through field aliases ('friendly' field names) and limiting the quantity of fields to only those that the end-user is concerned with. Views can specify filters for the returned result set. Views can define relationships to data from multiple tables, even multiple databases and other CONNX Views. Views are used to specify record-level security. A user may be granted access to a view without being granted access to the underlying table(s).
Important: CONNX Views are updateable through front-end applications that use ADO, RDO, or DAO. However, they are not updateable through Microsoft Access unless you use a Microsoft Access passthrough query to construct the view. If you are going to be using Microsoft Access and want to be able to update CONNX Views, you must create a Microsoft Access passthrough query. Consult your Microsoft Access documentation for more information.
NOTE: CONNX Views support standard SQL functionality with the exception of aggregate functions (Group by, Sum, Avg, etc.), distinct queries, Union, and sub-queries. Insert, Update, and Delete statements are only permitted against CONNX views that are composed of a single table.
The following is a sample CONNX View. Notice that SQL comments have been added to document the view. The view logic is as follows:
The CONNX sample ORDER and CUSTOMER tables are joined together, their relationship is defined in the where clause. Only specific fields from each table are chosen for display, including a new computed field with the alias "Ext Price". Only order records for customers whose CUSTOMERSTATE field contains an abbreviation for a state in the Northwest Territory --WA, OR, MT, ID, or CA -- are selected.
/*This view was requested by Johnathon Jones on 3/1/2001.
He executes this view daily to see orders for the Northwest Territory.
*/
SELECT
ORDERS_RMS.orderid as 'Order' /* Order Number */,
ORDERS_RMS.customerid as 'Cust Id' /* Customer Identification
*/,
CUSTOMERS_RMS.customername as 'Name' /* Name of Customer*/,
CUSTOMERS_RMS.customerstate as 'ST' /* State Ordered by
*/,
ORDERS_RMS.orderdate as 'Ord Date' /* Date Ordered */,
ORDERS_RMS.productid as 'Product' /* Product number */,
PRODUCTS_RMS.productname as 'Description' /* Product Description
*/,
ORDERS_RMS.productquantity as 'Qty' /* order quantity */,
PRODUCTS_RMS.productprice as 'Price' /* price per unit
*/,
(ORDERS_RMS.productquantity * PRODUCTS_RMS.productprice)
as 'Ext Price' /* Calculate extended price) */
FROM ORDERS_RMS,
CUSTOMERS_RMS, PRODUCTS_RMS /* Tables included in view */
WHERE ORDERS_RMS.customerid=CUSTOMERS_RMS.customerid AND
ORDERS_RMS.productid=PRODUCTS_RMS.productid and CUSTOMERS_RMS.customerstate
in ('WA', 'OR', 'MT', 'ID', 'CA') /* Join tables together and select only
Northwest states */
To create a CONNX View manually
in the CONNX Data Dictionary Manager