CREATE SQL VIEW Command
Displays the View Designer, allowing you to create a SQL view.
CREATE SQL VIEW [ViewName ] [REMOTE] [CONNECTION ConnectionName [SHARE] | CONNECTION DataSourceName] [AS SQLSELECTStatement]
- Specifies the name of the view to create.
- 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
mysqlviewthat selects all fields in the
orderstable in the
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
customertable 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
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 Creating Views.
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.
The following example opens the
testdata database. CREATE SQL VIEW is used to create a local SQL view named
myview which is created from a SELECT – SQL statement that selects all records from the
customer table. The View Designer is displayed, allowing you to modify the SQL view. After the View Designer is closed, the SQL view is erased.
CLOSE DATABASES * Open testdata database OPEN DATABASE (HOME(2) + 'data\testdata') * Create view with initial select criteria from customer table CREATE SQL VIEW myview AS SELECT * FROM testdata!customer; WHERE country="Mexico" * Activate View Designer to modify or run query MODIFY VIEW myview &&Activates View Designer * Delete view after View Designer closes DELETE VIEW myview