Customizing Views Using SQL SELECT Statements

When you create a view in the View Designer, Visual FoxPro uses a SQL SELECT statement to retrieve the information from the fields in the tables. SQL SELECT statements offer you a more powerful way to manipulate your view and provide more control over the results. To verify your view or add comments, you can display the SQL SELECT statement in the SQL window and add comments.

You can use SQL SELECT statements with views in the View Designer, queries created in the Query designer, or in code for an event or procedure in a program (.prg) file. For more information about using the SQL SELECT command, see SELECT - SQL Command.

Viewing SQL SELECT Statements Created by Views

You can view the SQL statement that your view builds in the SQL window at any time while you create your view. By viewing the SQL statement created by the View Designer, you can make sure the view is properly defined.

To view the SQL SELECT statement created by a view

1. Create your query using the View Designer.
2. On the Query menu, choose View SQL.

The SQL window opens to display the SQL SELECT statement your view created. The first statement is the SQL SELECT statement. For example, you can select all the records from the Customer table in the TasTrade database where the country field contains the value "Canada":

SELECT *;
   FROM tastrade!customer;
   WHERE Customer.country = Canada

The script for the SQL SELECT statement appearing in the SQL window is automatically generated when loading or modifying a view in the View Designer and contains view properties set by DBSETPROP( ) function calls that you can edit.

To run the SQL SELECT statement immediately, enter the statement in the Command window. If you want each clause to appear on a separate line in the window, end each line except the last one with a semicolon (;) so that Visual FoxPro processes the command only after the last line.

To include the SELECT statement in code, copy the SQL SELECT statement into a .prg file.

Creating Views with Stored SQL SELECT Statements

You can use macro substitution to store the SQL SELECT statement into a variable that you use with the AS clause in the CREATE SQL VIEW command. For example, the following code stores a SQL SELECT statement into the variable emp_cust_sql, which is then used to create a new view:

emp_cust_sql = "SELECT employee.emp_id, ;
   customer.cust_id, customer.emp_id, ;
   customer.contact, customer.company ;
   FROM employee, customer ;
   WHERE employee.emp_id = customer.emp_id"
CREATE SQL VIEW emp_cust_view AS &emp_cust_sql

Editing SQL SELECT Statements in the SQL Window

You can edit the SQL SELECT statement and properties, which are set by DBSETPROP( ) function calls, created for the view. For more details about view properties, see DBSETPROP( ) Function, DBGETPROP( ) Function, or CURSORGETPROP( ) Function.

To edit the SQL SELECT statement in the SQL window

• Open the SQL window, and modify the SQL SELECT statement.

You can specify an asterisk, table.*, or a comma-delimited list of fields for the SELECT statement in the SQL window. For example, you can specify the SQL statement as SELECT * FROM or SELECTfield1, field2, ... FROM. Your preferences are preserved during the loading or generation of the view. You can also include the same field multiple times in the SELECT list.

For more information the syntax for the SQL SELECT command, see SELECT - SQL Command.

Loading SQL SELECT Statement Changes into the View Designer

After editing the SQL SELECT statement in the SQL window, you can reload the changes into the View Designer.

To update the view in the View Designer

• Close the SQL window.

You can also update the view by clicking the View Designer to bring it to the foreground. If Visual FoxPro cannot reload the view in the View Designer, the SQL window reappears for you to modify the SQL SELECT statement.

See Also

Working with Views | Creating Local Views | Creating a Multitable View | Customizing Views