Views

Advantage SQL Engine

A view is a "virtual table" in a database whose contents are defined by an SQL SELECT statement. To the database user, the view appears just like a real table, with a set of named columns and rows of data. In reality, a VIEW is simply an SQL statement that is dynamically executed by the Advantage SQL engine when a view is being utilized, and the result set of the query is treated as a table. All users in the database can have permissions to open a view or execute queries against views but only the data dictionary administrator can create or drop views.

Note Parameters are not allowed in the definition of a view.

Views can be created and dropped from an Advantage Data Dictionary in three ways (Note that each of the Advantage products and their corresponding Help files are installed separately):

A view SELECT statement can be any valid Advantage SQL statement, including joins or unions. If the view SELECT statement would result in a live cursor when executed on its own, the view will be a live (updateable) view. If the view SELECT statement would result in a static cursor when executed on its own (for example, a join), the view will be a static (read only) view. (See Live versus Static Cursors and Cursor Types for more information about cursors.)

Examples of live cursors based on VIEWs:

CREATE VIEW GoodCust AS SELECT * FROM customers WHERE credit_limit > 50000

SELECT * FROM GoodCust ORDER BY customer_name ; live result set

SELECT customer_name FROM GoodCust WHERE sales_rep = 169 ; live result set

Examples of static cursors based on VIEWs:

CREATE VIEW GoodCust AS SELECT * FROM customers WHERE credit_limit > 50000

SELECT customer_name, rep_name

FROM GoodCust, salesreps

WHERE GoodCust.sales_rep = salesreps.rep_id ; static cursor based on live view

 

CREATE VIEW CustAndReps AS SELECT customer_name, rep_name

FROM customers, salesreps

WHERE customers.sales_rep = salesreps.rep_id

SELECT * FROM CustAndReps ; static cursor, because CustAndReps is a static view