What is CONNX Views
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 by limiting the quantity of fields to only those with which the end-user is concerned. 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).
Note:
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.
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.
In the following example, 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.
SELECT
ORDERS_RMS.orderid as order,
ORDERS_RMS.customerid as customerID,
CUSTOMERS_RMS.customername as name,
CUSTOMERS_RMS.customerstate as state,
ORDERS_RMS.orderdate as date ordered,
ORDERS_RMS.productid as product number,
PRODUCTS_RMS.productname as description,
ORDERS_RMS.productquantity as order quantity,
PRODUCTS_RMS.productprice as price per unit,
(ORDERS_RMS.productquantity * PRODUCTS_RMS.productprice)
as 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 */