Displays the View Designer, allowing you to create a SQL view.
Syntax
CREATE SQL VIEW [ViewName ] [REMOTE]
[CONNECTION ConnectionName [SHARE]
| CONNECTION DataSourceName]
[AS SQLSELECTStatement]
Arguments
ViewName
Specifies the name of the view to create.
REMOTE
Specifies that a remote view using remote tables is created. If you omit REMOTE, you can create a view using local tables.
CONNECTION ConnectionName [SHARE]
Specifies a named connection to establish when the view is opened. If you include the SHARE clause, Microsoft Visual FoxPro will use a shared connection if one is available. If a shared connection isn't available, a unique connection is created when the view is opened and cannot be shared with other views.
CONNECTION DataSourceName
Specifies an existing datasource to which a connection is established.
AS SQLSELECTStatement
Specifies the view definition. SQLSELECTStatement must be a valid SQL SELECT statement, and should not be enclosed in quotation marks. For local views, preface the view or table name with the name of the database and an exclamation point (!). For example, the following command creates a SQL view called mysqlview that selects all fields in the orders table in the customer database:
CREATE SQL VIEW mysqlview AS SELECT * FROM customer!orders
For additional information about SQL SELECT statements, see SELECT – SQL.
You can limit the scope of a view without creating a separate view for each subset of records by creating a parameterized view. A parameterized view uses a WHERE clause that limits the records downloaded to only those records by supplying a value as a parameter.
For example, you can create a SQL view that allows you to download records for any country, simply by supplying the country's name when you use the view.
The parameter you supply is evaluated as a Visual FoxPro expression. If the evaluation fails, Visual FoxPro prompts for the parameter value. For example, if the customer table from the Testdata database is located on a remote server, the following example creates a parameterized remote view that limits the view to those customers whose country matches the value supplied for the ?cCountry parameter:
OPEN DATABASE testdata
CREATE SQL VIEW customer_remote_view CONNECTION remote_01 ;
AS SELECT * FROM customer WHERE customer.country = ?cCountry
Tip If your parameter is an expression, enclose the parameter expression in parentheses. This allows the entire expression to be evaluated as part of the parameter.
For additional information about parameterized views, see Chapter 8, Creating Views, in the Programmer's Guide.
Remarks
A SQL view allows you to extract specific fields and records from one or more related tables, and treat the resulting data set as a table that you can update. Views are a powerful means of providing customized access to selected portions of your database, combining the flexibility of a query with the ability to update data.