Query Designer and View Designer

Tamar E. Granor, Ph.D.
FoxPro Advisor

Introduction

Visual FoxPro’s Query Designer and View Designer allow you to create queries which select subsets of your data. The View Designer also lets you create updatable result sets based on both local and remote data. This session explores both the use of these tools and the SQL code they generate.

Query Designer and View Designer both create SQL queries against a data set. SQL (Structured Query Language) is a non-procedural language that lets you specify what results you want rather than how to get them. FoxPro supports a subset of the SQL language. The Designers, in turn, support a subset of that.

These notes demonstrate use of the two Designers and discuss the code they generate. Working with Query Designer or View Designer is an excellent way to become familiar with SQL.

What are Query Designer and View Designer?

Query Designer (QD) is an updated, improved version of the FoxPro 2.x RQBE (Relational Query By Example). It’s easier to use, more attractive and better organized than RQBE. It still is useful primarily for learning SQL and writing simple queries. For more complex queries, you can start with QD and modify the generated code or write by hand to begin with.

A view is an updatable collection of information originating in one or more tables. In essence, a view is an updatable query result. Views don’t exist independently; they can only be defined as part of a database. Think of a view as a virtual table formed by drawing information from other physical tables. (These views have no relationship to VUE files used to store information about open tables, relations, and so forth.)

View Designer (VD) is similar to Query Designer in appearance and functionality. However, it supports two additional features: accessing non-FoxPro tables (whether on the local system or a remove server) and updating of results. Updating can be applied to FoxPro data, as well as non-FoxPro data.

Using Query Designer and View Designer

There are several ways to start the two designers. Each can be run either by command, from the menu, or from the Project Manager.

The CREATE QUERY and MODIFY QUERY commands let you open new and existing queries. CREATE VIEW lets you open new views. (In order to save a view created with no database open however, you have to specify and open a database.) MODIFY VIEW opens existing views, but can only be used when a database is open.

In the Project Manager, QD and VD are accessed from the Data tab. The Local Views and Remote Views items access View Designer, while the Queries item accesses Query Designer.

Figure 1 shows Query Designer as it opens for a new query with no tables specified. Figure 2 shows QD with tables and join conditions specified.

Figure 1—Query Designer with no tables

Figure 2—Query Designer with tables and relations

Types of Data

Visual FoxPro can handle two kinds of data. Local data is FoxPro data whether it’s stored on the workstation or on a file server. Remote data is data accessed via ODBC.

Local (FoxPro) data can be accessed through either Query Designer or through local views created in View Designer. Remote data is accessed through remote views created with View Designer.

The Query/View Toolbar

When QD or VD opens, by default, a corresponding toolbar (which can be docked to either side, top or bottom) opens as well. The toolbar provides quick access to some of the most common options used in building a query or view. Figure 3 shows the Query toolbar undocked, meaning it can be moved around on the desktop. The toolbar may be docked to any of the four sides, in which it reshapes itself into a single line.

Figure 3—The Query Toolbar

The View toolbar is identical to the Query toolbar except for the last item (the one with glasses) which lets you specify the destination for a query. Views automatically go to a cursor, so no destination option is needed.

The first row of items in the toolbar relate to the tables used in the query or view. The button with the plus sign adds a table or view, the button with the X removes a table or view, and the third button sets join conditions.

The second row of buttons relate to the configuration of the Designer itself. The SQL button shows or hides a window containing the query code. The button with the screen icon enlarges or reduces the top pane of the Designer to show more or less of the tables used and their relations.

Watching Your Query Grow

The SQL window accessed from the Show SQL button on the toolbar or from View SQL on the Query menu shows you the query under construction. If you have enough room to do so, keeping this window visible as you work will help you to learn what each option does and to learn to write your own queries.

Adding Comments

The Comments item in the Query menu lets you write a comment that is placed at the beginning of the generated query. This lets you add a description of the query, including whatever information you consider important. Use this option to enter enough information to let you identify the query or view later.

Building Queries

Both designers build a query—an SQL-SELECT statement that constructs a result set. The four tabs common to both Designers (Selection Criteria, Fields, Order By and Group By) specify most of the clauses of the query. The FROM clause of the query, specifying the tables used, lists all tables shown in the top pane. The destination of a query result is specified either from the menu or from the toolbar. Views always store results in a cursor.

Adding Tables to a Query

When you begin a new query or view, you’re prompted for the first table to include. For a query or local view, tables in the open database are shown with an option to use an existing view or other tables. For a remote view, you can select a connection existing in the database or an available ODBC datasource. Once the datasource is chosen, you’re prompted with a list of tables available in that datasource.

Tables can be added in several ways. The Query menu contains an Add Table item, as does the toolbar. In addition, tables can be dragged into a query from the Project Manager.

A query containing more than one table needs join conditions between the tables. The join condition indicates which records in the tables should be paired to form the query result. Without join conditions, results from multi-tables queries can be extremely large and quite meaningless. Query Designer prompts for a join condition when you add a table after the first to a query.

When tables that belong to a database are added to a query, Query Designer checks for a persistent relation between them. If one exists, it’s used as the default join condition between the tables.

Specifying Fields

A query needs a list of columns (fields) of the result. The Fields tab contains a two-column mover listing all fields from all tables in the query. Any field can be moved to the selected list either from the field list or from the top pane showing the tables and relations. Double-clicking on a field either in the top pane or in the field list moves it to the selected list. In fact, fields can usually be added to the field list by double-clicking in the top pane. (When the Order By tab is topmost, double-clicking in the top pane adds the field to both the field list and the list of ordering criteria. When the Group By tab is on top, double-clicking in the top pane adds the field to the grouping criteria without adding it to the field list. See below for descriptions of ordering and grouping criteria.) Fields can also be dragged and dropped from the top pane or the field list.

Any field from any table in the query can be included. In addition, expressions can be used to build more complex fields. In a query, any valid FoxPro expression can be used, though you need to be careful with UDFs. Both queries and views can use the aggregate functions which provide composite results. Aggregate functions are discussed in the “Grouping Data” section below.

Figure 4 shows a field list including an expression, in this case, the total cost of an item in an order.

Figure 4—Field list including expression

The query generated for Figure 4 is:

SELECT Customer.company, Products.prod_name,;
  Orditems.quantity*Orditems.unit_price;
 FROM nwind!Customer, nwind!Orders, nwind!Orditems, nwind!Products;
 WHERE Customer.cust_id = Orders.cust_id;
   AND Orders.order_id = Orditems.order_id;
   AND Orditems.product_id = Products.product_id;
   AND UPPER(Customer.country) = "FRANCE"

You can change the order of the fields in the result by moving the fields in the Selected Output list (shown on both the Fields tab and the Selection Criteria tab). The order of fields does not affect the order of the records in the result, simply the order in which particular data items within a record appear. In most cases, there’s no reason to put fields in a specific order.

The Selection Criteria Tab

The Selection Criteria tab serves two purposes in creating a query. It contains both join conditions and filter conditions. The information in the Selection Criteria tab goes into the WHERE clause of the generated query.

Join Conditions

Join conditions indicate how records from different tables should be paired in the query. Normally, the join condition between two tables is the same as the relation between those tables. Without a join condition between two tables, every record in the first table is paired with each record in the second table, a situation known as a “Cartesian Join”. Cartesian Joins are generally quite large and are rarely the desired result. In most cases, there is some relationship between specific records in the two tables and these are the ones that should be paired.

Join conditions are normally added when you add a table to the query. If there’s a persistent relation between two tables, that relation is automatically added as the join condition. If no such relation exists, you’re prompted when adding a table to join it to at least one other table already in the query.

Join conditions are displayed in the Selection Criteria tab with a horizontal double-headed arrow to indicate that the condition is a join condition. (See Figure 2.) While you can remove join conditions, it’s not a good idea. Clicking on the join arrow brings up the Join Condition dialog, allowing you to modify an existing join condition. You can also add a join condition by clicking the Add Join Condition button on the toolbar.

Filter Conditions

Filter conditions determine which records appear in the result. They’re used to include or exclude records based on content. For example, you may want to see all customers in France or all orders in November.

To specify a filter condition, choose a field or enter an expression in the left-hand column (labeled Field Name). All fields of all tables in the query are listed. To enter an expression, scroll to the very bottom of the list and choose the <Expression...> item. That brings up the Expression Builder where you can construct the expression.

The center portion of Selection Criteria indicates the type of comparison you want. The default is LIKE, which (for strings) is sensitive to the setting of SET ANSI. With SET ANSI OFF, LIKE compares until the shorter string is exhausted. If the strings match to that point, the comparison is true. With SET ANSI ON, strings must be the same length to match with LIKE.

Other comparisons are EXACTLY LIKE (which uses ==) MORE THAN (>), LESS THAN (<), BETWEEN, and IN. BETWEEN lets you specify two values and tests whether the field is greater than or equal to the first and less than or equal to the second. IN lets you specify a list of values and checks whether the field is in the list.

The button to the left of the operator reverses the comparison when checked. It’s equivalent to putting NOT in front of the comparison. It’s especially useful with MORE THAN and LESS THAN, since NOT MORE THAN is the same as <=, and NOT LESS THAN is equivalent to >=.

In the right-hand column (Example), put the value or values to be compared. This value can be a constant (“FRANCE”), a variable (m.country), or an expression (UPPER(m.country)). It’s not necessary to surround a character string with quotes in this column.

You can drag fields from the top pane or the Selected Output list into both the Field Name and Example columns.

The final button lets you indicate whether the comparison is case-sensitive. Checking it makes the comparison case-insensitive.

Figure 5—Selection Criteria with a filter condition

Figure 5 shows the Selection Criteria tab with a filter condition restricting results to customers in France. Note that the Case button is checked to ensure all French customers are found regardless of whether they were entered as “FRANCE”, “France”, “france” or some other variation. You may not be able to use the Case button in some views, since the datasource may not support the UPPER() function, which is how case-insensitivity is implemented.

Combining Conditions

Ordinarily, all conditions in the Selection Criteria tab (whether join conditions or filter conditions) are combined with AND. So the WHERE clause generated by the choices in Figure 5 would look like this:

WHERE Customer.cust_id = Orders.cust_id;
   AND Orders.order_id = Orditems.order_id;
   AND Orditems.product_id = Products.product_id;
   AND UPPER(Customer.country) = "FRANCE"

For complex conditions involving multiple ORs and ANDs, it’s generally better to write the query by hand than to use the Query Designer.

Ordering Results

The Order By tab lets you indicate the order in which records should appear in the query result. It allows you to order based on any fields in the query. For example, you may want to see customers alphabetically. If you’re showing customer orders, you may want them from most recent to oldest for each customer.

The Order By tab uses a two-column mover like the one in the Fields tab. In this case, the left-hand list contains all the fields in the query result. To order data based on a field, move it to the right-hand list (Ordering Criteria), either by double-clicking or by highlighting it and clicking Add or by dragging and dropping.

You can indicate, for each field in the Ordering Criteria, whether it should use ascending or descending order. Each field in the list can make a different choice. An arrow appears next to each item indicating which order it uses.

When more than one field is included in Ordering Criteria, they’re applied in the order shown. So, data is first ordered on the first field shown. If any records have the same value for that field, those records are then ordered based on the second field listed, and so forth.

Figure 6 shows the Query Designer set up to show customers, order dates and order totals, in customer order. For each customer, orders are shown from newest to oldest.

Figure 6—Specifying Order of Results

Fields specified in the Order By tab are listed in the ORDER BY clause of the generated query. The query shown in Figure 6 generates the following query:

SELECT Customer.company, Orders.order_date, Orders.order_amt;
 FROM nwind!Customer, nwind!Orders;
 WHERE Customer.cust_id = Orders.cust_id;
 ORDER BY Customer.company, Orders.order_date DESCENDING

The Group By tab lets you indicate how result records can be combined. It’s used to generate aggregate results, such as the total of the orders for each customer, or the number of customers in each country. Normally, when you specify grouping, you also use one or more of the aggregate functions listed in the Functions/Expressions list in the field tab. (Actually, this goes both ways. Usually, you only use those functions when you specify grouping.)

Note that grouping in a query is quite different from grouping in a report. In a query, grouping actually combines multiple records into a single result. In a report, grouping indicates where to put visual breaks and subtotals.

Like the Fields and Order By tabs, the Group By tab contains a two-column mover. In this case, the left-hand column lists all fields from tables in the query. Queries and local views may be grouped on a field not included in the selected fields.

The right-hand list shows the fields chosen for grouping. If only one field is listed, all records which match in that field are combined into a single record in the result. For example, Figure 7 shows a query grouped on customer id (cust_id). The result contains one record for each customer.

Figure 7—Consolidating Results with the Group By tab

If multiple fields are listed, records matching in all fields listed are combined. For example, listing state, then city in Group By Fields results in one record for each city-state combination.

The fields chosen in the GROUP BY tab appear in the GROUP BY clause of the generated query. The query created in Figure 7 is shown here:

SELECT Customer.company, SUM(Orders.order_amt);
 FROM nwind!Customer, nwind!Orders;
 WHERE Customer.cust_id = Orders.cust_id;
 GROUP BY Customer.cust_id

FoxPro’s SQL includes five functions used for consolidating data. They are COUNT(), SUM(), AVG(), MAX() and MIN(). All five are listed in the Functions/Expressions combo box in the Fields tab. When combined with grouping, these functions compute aggregate results, for example, the number of customers in each country, the total of each customer’s orders, the maximum unit cost of a product, and so forth.

To specify an aggregate function, choose it from the combo box. Then choose the field to which to apply the function. Click Add to move the function to the Selected Fields list or drag and drop.

Several special forms of the aggregate functions are available. The COUNT() function can take a special “field” of “*”, indicating that it should count the number of records in the group. Listing a specific field in COUNT() counts the number of records in the group where that field is not .NULL.

COUNT(), SUM() and AVERAGE() can also include the DISTINCT keyword indicating that a given value should be included only once in the result.

Filtering Consolidated Results

The Having button on the Group By tab lets you filter based on the results of consolidation. You can do things such as include only those customers with total orders above $10,000 or those countries with fewer than 10 customers.

When you press Having, a dialog similar to the Selection Criteria tab appears. You enter the additional filter conditions in the dialog. Figure 8 shows the Having dialog set up to include only customers with total orders greater than $10,000.

Figure 8—The Having dialog

Only conditions based on the results of grouping should be entered in the Having dialog. Other filter conditions belong in the Selection Criteria tab. The conditions in the Having dialog appear in the Having clause of the query. The query generated by the set up shown in Figure 7 and Figure 8 is:

SELECT Customer.company, SUM(Orders.order_amt);
 FROM nwind!Customer, nwind!Orders;
 WHERE Customer.cust_id = Orders.cust_id;
 GROUP BY Customer.cust_id;
 HAVING SUM(Orders.order_amt) > 10000

Working with Results

At this point, Query Designer and View Designer part company. Almost all of the discussion above applies equally to both. Once the query criteria are specified, though, the two Designers have very different features. Query Designer lets you indicate where to put the results and what to do with them. Views are always stored in a cursor, but View Designer lets you indicate which of the data, if any, can be updated and under what conditions.

Cursors

The term “cursor” stands for “CURrent Set Of Records.” A cursor is a temporary table that exists until you close it. When closed, it is deleted.

Cursors created by a query are read-only. Cursors created by views may be updatable. (The CREATE CURSOR command used to create empty temporary tables which you can populate produces read-write cursors.)

Cursors are particularly useful in multi-user applications. The name assigned to a cursor is an alias, not a filename and therefore does not need to be unique across sessions. Visual FoxPro ensures that the underlying filename for the cursor is unique.

Destinations

Query results from native FoxPro data can be routed many different ways. The default, if no destination is specified, is to put them in a cursor and Browse the cursor. While this is a reasonable approach when working interactively, it’s generally not desirable in applications.

Other destinations are specified by pressing the Query Destination button on the Query toolbar or choosing Query Destination from the Query menu. Either brings up the dialog shown in Figure 9.

Figure 9—Query Destination dialog

Storing Results in a cursor or table

The Cursor and Table buttons allow you to store query results in FoxPro’s table format.

Choosing Cursor stores the results in a cursor, without issuing Browse. You specify the name of the cursor. (With Browse, the cursor gets a default name the same as the name of the query.)

The Table button indicates that a real FoxPro table (DBF) should be created. Unlike a cursor, the table continues to exist after it’s closed. Use Table for data you want to keep, rather than just work with. When you specify Table, you must be sure the table name you provide is unique or you risk overwriting existing data. SYS(3) and SYS(2015) are useful for creating unique file names.

Graphing Results

Query Designer provides an interface to Microsoft Graph through the Graph button in the Destination dialog. Choosing Graph brings up Graph Wizard to help you design the desired graph.

Displaying Results

The last three options in the Destination dialog, Screen, Report and Label, provide methods for displaying or printing results without saving them. Pressing any of these buttons expands the Destination dialog to include additional options.

Screen displays the results in the main Visual FoxPro window. The dialog also provides options to print the results or save them in a text file. Output produced this way looks like that from the DISPLAY or LIST commands, with one column for each field. A checkbox in the dialog lets you eliminate the column headers.

The Report button lets you send results to a report created with Report Designer. You can choose an existing report or use the Report Wizard to create one. The report can be shown in Preview mode, printed, sent to a text file or displayed in the main FoxPro window. Additional options in the Destination dialog let you add optional clauses to the REPORT FORM command generated.

The Labels button sends results to a label created with Label Designer. You specify the name of an existing label. As with reports, you indicate where to send the labels (preview, printer, file or screen).

Updates

Views give you the option of updating the back-end data on which the view is based, as long as the ODBC driver specified supports updates. The Update Criteria tab contains the various options for updating back-end data. Figure 10 shows the Update Criteria tab for a query of the sample Access data provided in the ODBC SDK.

Figure 10—Update Criteria

Updating of data matches records in the view with the original records using one or more “key fields.” Generally, the key field for a table is the primary key. The center of the Update Criteria tab contains the list of fields in the view plus two special columns. The left-hand column (with the “key” above) indicates the key fields for each displayed table. The right-hand column (with the “pencil”) indicates which fields may be updated. Only fields with a checkmark in the pencil column are updated. Changes to other fields are discarded. (However, all fields in the cursor can be updated. Changes to unchecked fields are not propagated to the back-end.)

The Tables dropdown indicates whether the field list displays all fields in the view or only those from a particular table. It does not affect the updatability of any item.

Once key fields have been marked and at least one updatable field indicated, the Send SQL Updates checkbox is enabled. Checking this box is the key to updating. When checked, updated view data is sent to the source upon closing the view. When this box is unchecked, changes are lost when closing the view.

The right-hand side of the Update Criteria tab indicates the method to be used for the updates. There are two sets of choices. Updates can be performed using the SQL Update command or by deleting the changed record and inserting a new one. It may not be possible to use the Delete then Insert method with some data, since the deletions may violate the referential integrity of the original data.

The upper set of option buttons controls the conditions for updates. When performing the update, the ODBC driver can check whether the original data has been changed since the view was generated. The last three radio buttons use variations of this technique. The top button in this set indicates that updates should be applied as long as the matching record can be found. The second button (Key and Updatable Fields) indicates that updates should be canceled if any of the fields which are allowed to be updated have changed. Choosing the third button (Key and Modified Fields) means that the update is canceled if any of the data to be updated has already been changed. The final option (Key and Timestamp) can be used only with servers that timestamp records and aborts the update if the timestamp of the record has changed since the view was generated.

Connection Parameters

You can change some of the parameters used to connect to a back-end server through the Advanced Options item that appears on the Query menu when working in View Designer. These options specify items such as the number of records to fetch at one time, the point at which character data should be stored in a memo field, and whether to retrieve memo fields initially or wait until they’re needed.

Creating an Updatable View of FoxPro Data

One of the most frustrating aspects of queries in FoxPro 2.x was that neither the original data nor the query results could be updated. Local views change all that. To create an updatable result set or to have the original data on which a result set is based be updated, use View Designer rather than Query Designer to specify the query.

Fill in the Update Criteria tab just as you would with remote data. If you check Send SQL Updates, the original data is updated. With that checkbox unchecked, you can update the result set without affecting original data.

Parameterized Views

In View Designer, you can create parameterized views. A parameterized view uses one or more variables in the filter conditions or Having clause. The variables are evaluated before FoxPro sends the query to the back-end datasource. You refer to a parameter by preceding it with a colon. Figure 11 shows a parameterized view of the Access sample data from the ODBC SDK. In this example, cCountry is a parameter.

Figure 11—A parameterized view

The View Parameters dialog is used to define the parameters for a view. This dialog lets you specify the names and data types of the parameters without actually creating the variables.

When you run the view, you have two choices for providing values for the parameters. You can create and initialize the variables prior to running the view. If the variables do not exist when you run the view, a dialog prompts you to provide values for them.

You can refresh a parameterized view after a parameter has changed by issuing the REQUERY() function.

Combining FoxPro data with other data

Queries and local views can draw data both from FoxPro tables and from other existing views. The Add Table or View dialog that appears both when you begin a new query or local view and when you choose the Add Table button or menu item uses an option button set to choose between FoxPro tables and views in the database. You can include both in the same query.

This ability means that a single query can include data originating from many different sources. As long as appropriate join conditions can be specified, any available data may be used.

For example, you might join the customer tables of the Northwinds database provided with Access to the Tasmanian Traders database supplied with FoxPro to look for customers in common. You do this by creating a view of the Northwinds database including customer information. Then, create a query joining that view to the Customer table from the Tasmanian Traders database.