How to: Customize Queries Using SQL SELECT Statements
When you create a query in the Query Designer, Visual FoxPro actually 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 query and provide more control over your query results and where the results are stored. To verify your query or add comments, you can display the SQL SELECT statement in the SQL window and modify it to further customize your query.
When you add queries to your application, you can use SQL SELECT statements to combine a variety of data sources, filter records more precisely, manipulate data, and sort the results. You can use SQL SELECT statements with queries created in the Query Designer, views in the View Designer, or in code for an event or procedure in a program (.prg) file. For more information about where you can use SQL SELECT statements and the SQL SELECT command, see How to: Create Queries (Visual FoxPro) and SELECT - SQL Command.
Viewing SQL SELECT Statements Created by Queries
You can view the SQL statement that your query builds in the SQL window at any time while you create your query. By viewing the SQL statement created by the Query Designer, you can make sure the query is properly defined.
To view the SQL SELECT statement created by a query
Create your query using the Query Designer.
On the Query menu, choose View SQL.
The SQL window opens to display the SQL SELECT statement your query 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 query in the Query Designer.
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.
Editing SQL SELECT Statements in the SQL Window
In the SQL window, you can edit the SQL SELECT statement for the query.
Note
The Query Designer parses only the first SQL SELECT statement in the SQL window, the clauses directly involved with the SELECT statement, and the first comment appearing in the query. The Query Designer disregards all other text.
To edit the SQL SELECT statement in the SQL window
Create or open a query.
Open the SQL window, and edit 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 SELECT field1, field2, ... FROM. Your preferences are preserved during the loading or generation of the query. You can also include the same field multiple times in the SELECT list.
For more information about using syntax for the SQL SELECT command, see SELECT - SQL Command.
Loading SQL SELECT Statement Changes into the Query Designer
After editing the SQL SELECT statement in the SQL window, you can reload the changes into the Query Designer.
To update the query in the Query Designer
- Close the SQL window.
You can also update the query by clicking the Query Designer to bring it to the foreground.
If the Query Designer cannot reload your changes after you make changes in the View SQL window, a dialog box appears and asks the following:
"View/Query designer is unable to reload your changes. Would you like to rebuild content from the designer?"
If you select Yes, your changes are discarded in the SQL window, and Visual FoxPro returns you to the Query Designer. If you select No, Visual FoxPro returns you to the SQL window.
To save a statement that cannot be parsed such as a call to a stored procedure, select No and click the Save button on the toolbar. Visual FoxPro saves the query. You can also click the Save button after entering the command in the SQL window to save the query. You do not need to click the Query Designer window, which causes the Query Designer to try to load and parse the query.
See Also
Tasks
How to: Collect User Input with Queries
How to: Run Queries (Visual FoxPro)