CREATE SQL VIEW Command

Displays the View Designer, allowing you to create a SQL view. 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.

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.

CREATE [SQL] VIEW [ViewName ] [REMOTE]   
[CONNECTION ConnectionName [SHARE] | CONNECTION DataSourceName]
   [AS SQLSELECTStatement]

Parameters

• SQL
  Creates a SQL view.

• VIEW 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 keyword, Visual FoxPro uses new statement handle for the shared connection, if one is available. If a shared connection is not available, Visual FoxPro creates a shared connection when the view opens, which you might be able to share with other views.

• CONNECTION DataSourceName
  Specifies an existing data source 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
  

  For additional information about parameterized views, see Creating Views.

Example

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

See Also

CREATE DATABASE | CREATE VIEW | DELETE VIEW | DISPLAY VIEWS | LIST VIEWS | MODIFY VIEW | OPEN DATABASE | RENAME VIEW | SELECT – SQL | USE | DBSETPROP( ) Function | DBGETPROP( ) Function