Special Edition Using Visual FoxPro 6

Chapter 6

Creating Basic Queries

• What is a Query?
• Selecting Tables for a Query
  • Adding a Table or View from a Database
    
  • Adding a Free Table
• Selecting Fields to Include in the Query
• Ordering the Results
• Miscellaneous Tab Options
• Viewing the Results
• Using Multiple-Condition Filters
• Routing the Query Results
  • Output to a Cursor
  • Output to a Table
  • Output to a Graph
  • Output to the Screen
  • Output to a Report
  • Output to a Label
• Using Query Designer Versus Manually Created SQL SELECT Statements
• Grouping Records to Summarize Data
• Using Queries in Programs and Forms
• Editing the Generated SQL SELECT Statement
• Creating Complex Queries
  • Using a BETWEEN Clause to Filter a Range of Values
  • Using an IN Clause to Filter Selected Values
  • Defining Subqueries

What is a Query?

According to the American Heritage Dictionary, a query is a question, an inquiry. So when you query a database, you are asking it questions about data. Using a sales file, for example, you might ask the following questions:

• What is the total value of all sales for last week? last month? last year?
• Are sales increasing?
• Which products sell the greatest quantity?
• Which products produce the most sales dollars?
• Which products are losing sales?
• How many sales are made to repeat customers?
• How are sales distributed by state?

With a little effort, you surely can come up with infinitely more questions. Although you might write programs to answer many of those questions, you cannot anticipate them all. Both queries and views are built to order for answering these types of questions. In this chapter, you will examine the Visual FoxPro query and learn how to use it. In subsequent chapters, you will learn about Visual FoxPro views and see how they are often more powerful and full-featured than the queries.

Queries and views are similar, but the differences are important. Queries are stored as program files with the extension .QPR. The QPR program contains a SQL SELECT command. Views are stored in a database and are also stored as a SQL SELECT command. However, views have two advantages over queries. First, they can be updated, meaning that they update their source tables when the view is updated. Second, they can be parameterized. Chapter 7, "Advanced Queries and Views," will present views for you. In this chapter, the discussion is limited to queries.

Selecting Tables for a Query

Open the Query Designer by choosing File, New from the system menu or by clicking the New button in the toolbar. Choose the Query option in the New dialog box, and click the New File button, as shown in Figure 6.1.

Figure 6.1 : Use the New dialog box to start a new query.

Adding a Table or View from a Database

A query requires at least one table. However, Visual FoxPro does not assume that you want to query the table in the current work area; rather, it immediately opens a window displaying the Open Table dialog box and enables you to select the first table. Actually, VFP can open two windows, depending on whether a database is open. Figure 6.2 shows the Add Table or View dialog box, which lists tables in an open database. If more than one database is open, you can pull down the database combo list and change the current one.

Figure 6.2 : The Add Table or View dialog box appears when you create a new query, allowing you to select a table.

For this set of examples, open the Tastrade database that comes with Visual FoxPro. In the Select area, you can choose either Tables or Views to use in the query. Tastrade has both tables and some predefined views. To start a new query definition, select from the available tables. Notice that the list of tables in this dialog box includes only tables that are defined in the Tastrade database.

Adding a Free Table

To use a free table, either start the Query Designer without first opening a database, or click the Other button in the Add Table or View dialog box (see Figure 6.2). VFP displays the standard Open dialog box, shown in Figure 6.3. In this dialog box, you can select a table from any directory or drive, including network drives. If the selected table really is bound to a database, Visual FoxPro automatically opens the database, too.

Figure 6.3 : In the Open dialog box, you can select free tables or tables from databases that are not open.

To begin the first query, return to the Add Table or View dialog box, and select the PRODUCTS table from the Tastrade database. A small window in the data source area (the top half) of the Query Designer displays the table's field names. The name of the selected tables always appears in the window header. Figure 6.4 shows the Query Designer at this point.

Figure 6.4 : The Query Designer, with two tables from the Tastrade database, is open in the source data area.

When the Query Designer opens, it also opens a toolbar (see Figure 6.5). The toolbar's six buttons perform functions that are specific to queries.

Figure 6.5 : The Query Designer toolbox contains six buttons that provide fast access to some of the most common functions.

Suppose that you want to list products by suppliers. The PRODUCTS file has a supplier ID associated with every product. But the SUPPLIER table stores the details about each supplier, such as name and address. Therefore, you need to open both files. To add a second table, click the Add Table button in the query toolbar. VFP displays the View dialog box. Select the SUPPLIER table, and click Add.

In Figure 6.6, notice that when Query Designer adds the SUPPLIER table to the table view area, it also draws a line between PRODUCTS and SUPPLIER. To be more specific, it draws the line between the field PRODUCTS.Supplier_Id and SUPPLIER.Supplier_Id. This line indicates that these two files share a field that relates records from one table to another. FoxPro knew to draw this line because of the persistent relation defined between these two files in the database. VFP also displays this join condition on the Join page in the bottom half of the Query Designer window.

Figure 6.6 : The Query Designer is shown with two open tables.

Perhaps you wonder what the two buttons to the left of the field name in the Join page do. The leftmost button, which points up and down, enables you to change the order of the selection expressions (if you have more than one). The second button, which points left and right, appears only when the criteria define the link between tables and allow modification to the type of join condition. Click this second button to display the dialog box shown in Figure 6.7. You can also open this dialog box by clicking the Add Join button in the toolbar or by double-clicking the line that links the two tables in the source data area.

Figure 6.7 : Define the join criteria by using the Join Condition dialog box.

The Join page enables you to customize the join condition. You can select the type of join from the Type drop-down list, open the drop-down list for either field, and select fields to define the join relations. You can also change the connection criteria and reverse the logic by clicking the Not button. Join supports five criteria:

• Equal
• Like
• Exacly Like
• More Than
• Less Than

The Join Condition dialog box enables you to select the type of join and describes each condition. The four types of joins are as follow:

• Inner join, which includes only the records from both tables that match the join condition.
• Left outer join, which includes all records from the table on the left and records that match the join condition from the table on the right.
• Right outer join, which includes all records from the table on the right and those records that match the join condition from the table on the left.
• Full outer join, which includes all records from both tables and combines those that match the join condition into one record in the result set.

To better understand the differences among these four join types, look at some sample ddata. Tables 6.1 and 6.2 show sample data from two tables.

Notice that the Persons table has one person from whom we have never received a card, and the Cards table has one card that is not from any of the people. These two records are the ones that the various join types will affect.

The join criteria that we will use is Persons.PersonID = Cards.PersonID. Table 6.3 shows you the result of an inner join. Remember that an inner join contains only those records from the two tables that meet the join criteria.

In Table 6.3, notice that Joan doesn't show up and that no card is shown for Christmas. These two records did not meet the join criteria. Joan didn't have a matching card, and the Christmas card had no matching person.

Table 6.4 shows the left outer join.

In the left outer join, you see that Joan is included even though she has no corresponding card record. The fields for the card data are empty.

Table 6.5 is an example of the right outer join.

Notice that the Christmas card is included in the right outer join even though it has no corresponding person record.

Finally, consider the full outer join shown in Table 6.6.

Notice that both Joan and the Christmas card are included in the full outer join result.

The Equality operator performs exactly like a simple equal sign between the strings on either side of the join. Thus, whether trailing blanks are considered in the comparison depends on the current setting of SET ANSI. If SET ANSI is ON, it pads the shorter string with spaces before comparing the strings, character for character, for their entire length. If SET ANSI is OFF, the strings are compared only until the end of the shorter string is reached.

The Like criterion performs a comparison equivalent to that of SET EXACT OFF. FoxPro compares the expression on the left, character by character, with the one on the right for as many characters as the expression has.

The Exactly Like criterion creates a more restrictive comparison. It compares the two strings character for character to see whether they match, and then it compares the two strings to see whether they are the same length. As you can see, this is slightly different than SET ANSI ON. It includes only those records whose criteria match exactly.

The More Than and Less Than criteria perform simple less-than and greater-than comparisons of the expression values. You can ask for product records with product unit prices more than $10, for example. In that case, only records whose product unit price is greater than $10 appear in the result set.

Because this example compares two fields in separate tables, each representing supplier ID defined the same way, choosing Like or Exactly Like generates the same results. Additional criteria in the form of record filters, however, might use these alternative comparison criteria.

Another option when you are defining the comparison criteria is the Not button. Clicking this button reverses the criteria's logic. Click Not with Exactly Like to create Not Exactly Like; choose it with More Than to create Less Than Or Equal To.

Finally, you can ignore the case of character strings by clicking the button on the right that appears below the Case heading. You might use it in record-selection criteria to ignore inconsistent case in field values.

Selecting Fields to Include in the Query

Having defined the relation between the two tables, you next define which fields to include in the results. Clicking the Fields tab in the Query Designer displays two lists: Available Fields and Selected Fields. Initially, all fields in all selected tables appear in the Available Fields list, and the Selected Output list is empty.

To include all fields in the Selected Fields list, click the Add All button. (You can also double-click the asterisk in the table's field list or drag the asterisk to the Selected Fields list.) However, you seldom need to see all fields when viewing data. To select individual fields, click them in the Available Fields list. Each time you click an available field, the Add button becomes enabled. Click this button to move the field to the Selected Fields list. You might find double-clicking a field to be a faster way to select and move it.

Other ways to move a field from the Available Fields list to the Selected Fields list include the following:

• Drag the field from the Available list to the Selected list.
• Click the field name in the table field list in the table view of Query Designer, and drag it to the selected list.
• Double-click the field name in the table field list in the table view of Query Designer.
• Hold down the Ctrl key, click on multiple fields, and choose the Add button.
• Drag or double-click the asterisk in the table field list in the table view to include all the table's fields.

To remove all the Selected Fields, placing them back in the Available Fields list, click Remove All. You also can remove fields one at a time by double-clicking a field in the Selected Fields list or by clicking on a field and then clicking Remove.

The No Duplicates check box, in the Miscellaneous page, checks to see whether the value of every selected field remains the same from one record to the next. If so, VFP includes the record only once. If at least one value in one field differs from record to record, VFP includes the record.

Ordering the Results

To see products alphabetically by description for each supplier, click the Order By tab. This tab, shown in Figure 6.8, provides options to customize the sort order of the results.

Figure 6.8 : Define the sort order of the query results by dragging fields to the Ordering Criteria list in the order in which you want to sort the query results.

This page displays two list boxes that contain fields. The left box contains only the Selected fields. After all, you cannot sort on a field that is not included in the results. To begin the sort with the supplier's company name, click it and then click Add. As with adding fields, this action moves the selected field to the right list box. Notice that a small up arrow appears next to the field name in the Ordering Criteria list. This arrow indicates that the current sort order for this field is ascending. Change the field sort order to descending by selecting the field and then clicking the Descending option in the Order Options area. You can also select the order option before moving the fields to the Ordering Criteria list.

To see products alphabetically for each supplier, double-click Product_Name. The order in which you select fields for the ordering criteria determines their sort order. If you use the mover button to put Product_Name before Company_Name, the query sorts the records first by product name. Then, if the product has more than one supplier, the query lists them alphabetically.

Miscellaneous Tab Options

The Miscellaneous tab provides options for additional record-selection criteria:

• No Duplicates enables you to show or to exclude duplicate records from your selection.
• Cross-Tabulate outputs the result set into a cross-tabular format. It also automatically selects the Group and Order By fields.
• Top enables you to select all records, several records, or a percentage of records to be returned. If you want to show the top 10 sales items, a query ordered by the units sold and limited to the top 10 would provide your top 10 items. Changing the order option from ascending to descending provides the bottom 10 sales items.

Viewing the Results

To see the results of a query using the current query definition, click the Run button (the button with an exclamation point on it) in the toolbar. Visual FoxPro runs the query; gathers the records from the tables, based on the selection criteria; selects fields to display; and sorts the records. VFP then displays the results in Browse mode, as shown in Figure 6.9.

Figure 6.9 : Display a view of query results in Browse mode at any time by clicking the Run button in the standard toolbar.

Notice that columns in the result set appear in the order in which you selected the fields. If you do not like this order, you can change it. Click the column heading, and drag it left or right. As you move the mouse, the column heading follows. Simply release the mouse button when you are satisfied with the new position.

You also can adjust column widths to view more columns at one time. Place the mouse pointer over any vertical bar that separates two column headings. The mouse pointer changes to a thick vertical bar with a left and right arrow (see Figure 6.10). Click and drag this separator to resize the column. If you make the column smaller than necessary to display its values, VFP truncates the displayed values, not the actual data.

Figure 6.10: A split query window, with the Product Name column being resized.

Because query results appear in an actual Browse window, they have a few additional features. Perhaps you have noticed that when you press Tab or use the scrollbars to view additional columns, columns on the other side of the screen scroll out of view. Often, the first few columns include fields (such as ID or name fields) that identify the rows. Sometimes, a query has so many columns that by the time you scroll to the ones you need, you forget which row you are in. Although you could manually move these columns together (or redefine the query with the columns closer together), you might need to view so many columns that moving them would simply not be practical.

To view all the columns that you need to view, no matter how far apart they are, split the Browse window by using the browse splitter. Initially, the browse splitter appears as a thin black box in the lower-left corner of the window. To split the window, click the splitter and drag it horizontally. Immediately, the query window splits into two partitions, each with its own set of scrollbars (refer to Figure 6.10).

After you split the window, use the scrollbars to move through records or columns in either window. When you move between fields by pressing the Tab key or by using the horizontal scrollbar, the fields scroll horizontally in one window but remain unaffected in the other. Using this method, you can keep key fields displayed in one window to identify the record while you view the remaining fields in the other one.

Initially, moving between rows in one window automatically moves the corresponding record pointer in the other window and also scrolls both windows, if necessary, because Visual FoxPro maintains a link between the two windows. To break this link, deselect the Link Partitions option in the Table menu. Now VFP still moves the pointer in both windows, but it no longer scrolls the other window as you move between records. This feature helps you view records that are scattered throughout the table. If you later turn Link Partitions back on, VFP resynchronizes the two windows to match the selected record in the active partition.

You can also change the view from browse style to edit style for the entire window or just one partition. To change a partition to edit view, click the desired partition and then choose View, Edit.

For a query, you cannot modify the results in either the browse or edit style view.

To remove the partitioned window, simply drag the splitter bar back to the far-left side of the main Query window. Whatever view mode exists in the partition on the right side of the window remains in effect.

Using Multiple-Condition Filters

In this section, you return to the Query Designer and add a second condition to the filter criteria. Rather than view all products from a supplier, you can limit the view to products on order. To do so, click the Filter tab, pull down the Field Name list, and click the Units_On_Order field to select it.

To limit selected records to those that have products on order, you should find records that have a Units_On_Order value greater than zero. Therefore, select > (more than) as a Criteria and enter 0 in the Example field.

Suppose that you also want to see items that sell for less than 25 percent above cost. First, move down to a new Filter Criteria line, and click the Field Name text area. At the bottom of the drop-down field list is an item called <Expression...>. This item opens the Expression Builder dialog box, in which you can create any valid FoxPro expression. Figure 6.11 shows the Expression Builder dialog box with the completed expression for profit margin. To complete these criteria, you want records that evaluate to < .25 (less than .25) because the profit-margin expression generates a fraction, not a percentage.

Figure 6.11: Calculate the profit margin of a product by building an expression with the Expression Builder.

Because the profit-margin expression includes division by one of the fields, you should eliminate records in which Unit_Cost is zero. You cannot lose money on a product that costs nothing unless you have to pay someone to take it. But more important, you cannot divide by zero. You accomplish this task with a separate filter criterion that selects only those records in which Unit_Cost is greater than zero. To get VFP to evaluate this expression before the profit-margin expression, place it before the profit-margin expression.

Notice that VFP evaluates criteria sequentially. If a potential result record fails an intermediate criterion, VFP does not evaluate the remaining criteria. Thus, check to see whether the unit cost is zero before you use it as a divisor in a later expression.

Figure 6.12 shows the completed Filter page. This query now has one join on the Join tab, and three filter conditions on the Filter tab. The join criteria and the three filter criteria must evaluate to true before VFP will include the record in the results table.

Figure 6.12: The completed Filter tab shows several criteria for selecting records.

Running this query results in six selected records. This result means that of the items on order, six of them have profit margins of less than 25 percent. Looking at the query results, the store manager might decide to discontinue stocking these items.

But what if you want to know how many items from the entire inventory had profit margins of less than 25 percent or were on reorder? To find out, you need to connect the last two expressions with OR instead of AND. Click the Logical drop-down list for the first Field Name-Products.units_on_order-and select OR. Rerunning the query retrieves 17 records.

Remember that the Query Designer merely checks the syntax of the criteria; it has no knowledge of what makes sense. You can easily define a meaningless set of conditions or a set that eliminates all records. You control the criteria's meaning.

Finally, the Insert and Remove buttons permit you to add or remove expressions from the Join tab and Filter tab criteria lists. To add a criterion to the bottom of the list, just use the empty Field Name box provided by the Query Designer. To insert a criterion between existing criteria, select the lower criterion and click the Insert button. Insert adds a blank line above the selected criterion. Alternatively, you could always add the criterion to the bottom of the list and move it up with the mover button.

Routing the Query Results

So far, all the examples in this chapter have written the query results to a Browse or Edit window onscreen. You might want to keep some query results around a little longer, perhaps saving them or using them in a report. To change the query destination from onscreen to a file or printer, click the Query Destination button in the Query toolbar.

FoxPro displays the Query Destination dialog box (see Figure 6.13), which includes the options shown in Table 6.7.

Figure 6.13: The Query Destination dialog box provides seven ways to display the query results.

By default, the Browse button is selected. When you generate results to a Browse window, FoxPro creates a temporary table that contains the query results (usually, in memory), opens the Browse window, and displays the table. As soon as you close the Browse window, Visual FoxPro erases the temporary table. If you need only a quick view of data that matches certain conditions, sending the query results to a Browse window is fine, and it saves a tree. You don't need to define anything else with this option because it is VFP's default and needs no special parameters.

Output to a Cursor

The next output-destination option in the Query Destination dialog box creates a cursor. Cursors are also temporary files, but they remain open and active for as long as you choose. When cursors are closed, however, Visual FoxPro deletes them. Other than in the actual process of creating a cursor, you cannot write to one created as output from a query. After a cursor is created, you cannot replace values or add or delete records.

Cursors have only one additional, required attribute: Despite their temporary status, cursors must have names. The Query Destination dialog box provides a suggested cursor name, based on the query name. You can accept the name or provide your own name. The assigned name then serves as an alias, allowing you to reference it from within a program or other command lines.

Output to a Table

Sending query results to a table is similar to sending them to a cursor, with one major exception: A table, by definition, has a physical existence on your disk and, therefore, can exist after the current Visual FoxPro session. You can close and reopen tables. You can add, delete, and modify records in a table. When you select Table in the Query Destination dialog box, you can directly enter a table name or click the button containing an ellipsis (…). This button opens the Open dialog box, in which you can select tables from any directory and drive. This action causes the query results to overwrite existing tables. You might want to open the directory to ensure that the table name that you enter does not already exist.

Output to a Graph

To show the output, the query was changed (see Figure 6.14) to select only three fields from the Product file: Product_Id, Unit_Cost, and Unit_Price. Open the Query Destination dialog box, and select Graph. Finally, run the query.

Figure 6.14: This query on just the Products table extracts product information to display as a graph.

When the query runs, it opens the Graph Wizard to help define the graph. The query skips the first step of this wizard because the fields have already been selected in the query; it begins with the layout step. In this step, you assign fields to desired locations on a graph. In this example, you can use Product_Id as the axis variable. You also have two data series to plot: Unit_Cost and Unit_Price. Your Graph Wizard dialog box should look like Figure 6.15 after you make these selections.

Figure 6.15: Use the layout step of the Graph Wizard to assign fields to various graph locations.

Move to the next step by clicking Next or by choosing Step 3 from the step drop-down list. In this step, you can choose any of 12 predefined graph styles, as shown in Figure 6.16. To select a style, click the button that displays a picture of the style that you want to use.

Figure 6.16: Choose one of the 12 available graph styles.

In the last step of the Graph Wizard, you can display null values, add a legend to the graph, and preview the graph before you click Finish. The legend identifies the data series with the field name. You also can enter a title to display at the top of the graph. Figure 6.17 shows these options.

Figure 6.17: In the last step of the Graph Wizard, you can include a legend and graph title before saving the graph.

When you click Finish, the Graph Wizard prompts you for a table name under which to save the graph. After you save the graph, the Graph Wizard displays the result.

Before completing Step 4-Finish, you can click the Back button to change the features of your graph and then click the Preview option to see the results. This way, you can preview several graph styles before committing to a final one.

Output to the Screen

Figure 6.18 shows the options that are available when you output query results to the screen.

Figure 6.18: The Query Destination dialog box displays options for outputting query results to the screen.

First, you can define a secondary output destination. Why? Because FoxPro does not retain the query results after writing them to the screen. Therefore, you can use a secondary output destination to create a more permanent copy by selecting To Printer or To Text File. When you are outputting to a printer, be aware that fields within a record wrap across multiple lines if they do not all fit on one line. When you select To Text File, you must also supply a filename, using the Open dialog box. If you know the fully qualified text-file name, you can enter it directly in the text box. Notice that you cannot choose both the printer and a text file as a secondary output destination.

Additional options include the capability to Suppress Column Headings and Pause Between Screens. To view the data, be sure to check the Pause Between Screens check box; otherwise, the data scrolls past faster than you can read it.

Output to a Report

When you are outputting to a report, Visual FoxPro provides a wide selection of options, as shown in Figure 6.19.

Figure 6.19: The Query Destination dialog box lists the options for sending query results to a report.

To use an existing report definition with the new query results, click the Open Report button, and select an existing report in the Open dialog box that appears. You can also use the Open dialog box to ensure that the report name that you supply does not currently exist if you intend to create a new report. If you know the report's filename without opening the Open dialog box, enter it directly in the text box.

The button on the right side of the dialog box that looks like a magic wand opens the Report Wizard. Use this wizard to create a new report in seconds.

If you click Page Preview, VFP outputs the report in a window for review. Notice that if you select Page Preview, you cannot simultaneously output the report to a secondary destination. This feature is best used for the following purposes:

• To test reports during development
• To view reports when you need to see a few results and do not keep a hard copy
• To review reports when you don't want to walk up three flights of stairs and down four halls to retrieve the report output from the only network printer
• To preview a report before printing to see how it will look and to decide whether you want to print it
• To view reports when you do not have access to a printer, either because it temporarily does not work or is busy printing past-due notices to clients

Console On echoes the report output to the current window, and Eject Page Before Report sends a page-eject command to the printer before beginning a report to ensure that the report starts at the top of the form.

As you can see when you send output to the screen, you can define a secondary output destination. (The primary destination for a report is the screen.) Choose None, To Printer, or To Text File. When you choose the last option, Visual FoxPro displays the Open dialog box, in which you can enter or choose a file. If you select a file that already exists and SAFETY is OFF, VFP overwrites the old file automatically. When SAFETY is ON, VFP displays a warning and enables you to decide whether to overwrite the file. You can also enter a text-file name in the edit box next to the To Text File button.

In the Options area, you can choose Suppress Column Headings or create a report by choosing Summary Information Only. When you choose Summary Information Only, Visual FoxPro suppresses all detail lines and prints only Title, Group, Page, and Summary bands.

Finally, you can add a separate report heading that appears at the top of each page, in addition to any heading that is already in the Page Header band of the report. Clicking the Report Heading button opens the Expression Builder dialog box. Use this dialog box to define a heading that includes memory variables, table fields, and calculated results.

Output to a Label

Outputting options for labels are similar to those for reports, as shown in Figure 6.20.

Figure 6.20: The Query Destination dialog box lists the options for sending query results to a label.

To use an existing label definition with the new query results, click the Open Label button and select an existing label from the Open dialog box. You also can use the Open dialog box to ensure that the label name that you supply does not currently exist, if you intend to create a new label. If you know the label's filename without opening the Open dialog box, enter it in the text box.

When Page Preview is selected, Visual FoxPro outputs the label in a window for review. Notice that when you select Page Preview, you cannot simultaneously output the label to any other destination. This feature is best used for the following purposes:

• To test labels during development
• To ensure that the labels are correct before rerunning the query and sending the output to a printer

The Console On option echoes the label output to the screen when the label output is created.

As you can do with reports, you can define a secondary output destination. (The primary destination for a label is the screen.) Choose None, To Printer, or To Text File. If you choose the last option, Visual FoxPro displays the Open dialog box, in which you can enter or choose a file. If you select a file that already exists and SAFETY is OFF, VFP overwrites the old file automatically. When SAFETY is ON, VFP displays a warning and enables you to decide whether to overwrite the file. You also can enter a text-file name directly in the edit box next to the To Text File button.

Using Query Designer Versus Manually Created SQL SELECT Statements

By now, you have seen how easily you can query data by using the Query Designer. You might not realize that you have been creating SQL SELECT statements throughout this entire chapter. If you have been hesitant to write your own SQL statements because you thought they were too complex, continue reading to explore the world of SQL SELECT statements.

The simplest query grabs every field and every record from a single table and displays them in a Browse window. The following code shows this query command used in the CUSTOMER.DBF table (in the Tastrade example provided with Visual FoxPro):

USE \VFP\SAMPLES\ DATA\CUSTOMER
SELECT * FROM TASTRADE!CUSTOMER

The asterisk immediately after the SELECT keyword tells Visual FoxPro to get all fields in the table referenced by the FROM clause. If you do not want to see all the fields, list the ones that you do want to see, as in the following:

SELECT Customer_Id, Company_Name FROM TASTRADE!CUSTOMER

Notice that commas separate the field names. To see the companies that begin with the letter A, add a WHERE clause, as shown in the following command (which assumes that SET ANSI is OFF):

SELECT Customer_Id, Company_Name FROM TASTRADE!CUSTOMER ;
      WHERE Company_Name = 'A'

To perform an exact search, use the == operator.

With that introduction, let's return to the SQL SELECT statements that were generated by FoxPro while using the Query Designer that we created earlier.

Any time while you are in the Query Designer, you can view the SQL that Visual FoxPro continuously builds in the background. Simply click the SQL button in the SQL toolbar. Actually, this button alternately shows and hides the SQL window. After you click it, the button stays down until you click it again or use the Window menu to switch the active window.

When you started the first query in this chapter, you selected two tables: PRODUCTS and SUPPLIER. A persistent relation on the field Supplier_Id joined those tables. You also selected several fields to display: PRODUCTS.Product_Id, PRODUCTS.Supplier_Id, PRODUCTS.Product_Name, and SUPPLIER.Company_Name. This information defines a simple query. If you click the Run button, a Browse window pops up and displays the query results. But what SQL SELECT statement did FoxPro actually use to produce this output? If you click the SQL button in the toolbar, you should see something similar to Figure 6.21.

Figure 6.21: Here is the SQL SELECT statement in which the inner join is used to create the relationship.

SQL SELECT commands begin with the SELECT keyword. You immediately follow SELECT with a list of fields to include in the results. Notice that Visual FoxPro qualifies each field with the alias name for the table. Strictly speaking, you do not need to qualify every field-only those fields that exist in both tables, such as Supplier_Id. Qualifying every field, however, helps you document the table to which the field belongs. When you are writing a SQL SELECT that uses a single table, you do not need to qualify the fields.

Although a SQL SELECT command is one line of code, you might want the SELECT statement to show on multiple lines of text. Just remember to end each line (except the last) with a semicolon. The field order in the SQL SELECT statement determines the field output order in the results table.

The SQL SELECT statement requires the FROM clause to identify the table or tables containing the fields. Notice that when a table belongs to a database, its name is preceded by the database name, separated by an exclamation point.

You can use a WHERE clause to limit the records included in the result set. The WHERE clause of the SQL SELECT statement corresponds to the filter criteria you supplied in the Query Designer.

After you define it, use the SQL SELECT statement in the Command window, or embed it in a program. No matter where you use it, the statement creates the same results as it does when you use it from the Query Designer.

Figure 6.22 expands the SELECT statement to include more fields and conditions. First, notice the DISTINCT clause added after SELECT. This clause, which corresponds to the No Duplicates check box in the Miscellaneous tab, appears only at the beginning of the field list but applies to all fields. The clause means that if all selected fields have the same value as they did in the preceding record, the current record is not included in the results.

Figure 6.22: Add conditions to the SQL SELECT statement to filter and sort results.

A second condition is added to the WHERE clause in Figure 6.22. This expression examines the value of the field Units_On_Order in the PRODUCTS table and includes records only if this value is greater than zero. By default, Visual FoxPro connects all criteria with AND. This means that the records must pass all the criteria before they are added to the result set. In this example, VFP would create a result set of unprofitable products that are also on order. However, suppose that you want to see all the products that are on order or the unprofitable products. To do so, you need to add a logical OR operator. In other words, you want to see records for those products that have outstanding orders and for those that are unprofitable.

Finally, the ORDER BY clause defines a sort order for the results. In this case, you want to sort records first by company and then by product name. Each field in the ORDER BY clause can have a different sort direction. By default, the sort order is ascending. However, you can follow the field name with DESC to define a descending sort or ASC to ensure an ascending sort.

Now look at the SQL SELECT statement generated at the end of the Query Designer session (see Figure 6.23).

Figure 6.23: This is the final SQL SELECT created to select items with profit margins less than 25 percent.

This expression adds additional filter clauses to limit the on-order product list to products that have low profit margins. Notice that with the OR condition between the second and third condition, Visual FoxPro groups the last two expressions together, using parentheses. This result is exactly what you want because you should perform the profitability calculation only for records that have a unit cost greater than zero.

By default, VFP sends all queries to a Browse window. The INTO clause specifies where to redirect the query results. In Figure 6.23, the results are output to a cursor named UNPROFIT. However, you can select several other destinations, including the following:

• An array (INTO ARRAY <arrayname>)
• A different cursor (INTO CURSOR <cursorname>)
• A table or .DBF file (INTO TABLE <dbfname>)
• A printer (TO PRINTER)
• The screen (TO SCREEN)
• A file (TO FILE <filename>)

The array option, which is not available from the Query Designer, adds the query results to a memory variable array. VFP does not create the array if the query does not return at least one record. Therefore, before you attempt to use the array variable, test how many records the query returned by checking the value of _TALLY. This system memory variable records the number of records processed by the most recent table command. The variable also works with the following commands:

APPEND FROM	PACK
AVERAGE	REINDEX
CALCULATE	REPLACE
COPY TO	SELECT - SQL
COUNT	SORT
DELETE	SUM
INDEX	TOTAL
JOIN	UPDATE

If _TALLY equals 0, the query found no records.

When storing the query results to a cursor or table, VFP prompts you before overwriting existing tables with the same name if SET SAFETY is ON. The Alert dialog box displays the following text:

<filename>
This file already exists
Replace existing file?

Click the Yes or No button to replace or not replace the existing file. When SET SAFETY is OFF, Visual FoxPro overwrites existing files automatically. In most programming environments, unless the user specifies the filename, you probably want SET SAFETY OFF to overwrite existing files.

Specifying TO FILE together with a filename is not the same as creating a cursor or table. Sending query results to a file creates an ASCII text file, not a table. You can print this file directly to the printer or screen from DOS by using the TYPE command. You can also append the file to other documents that read text files.

Another option, NOCONSOLE, suppresses echoing the query results to the screen. This capability is especially useful when you are directing output to a file or printer. Sending query results to a cursor or table never echoes the results to the screen; therefore, this option is not needed. PLAIN removes the column headings from the output. Use this option if you intend to send the query results to a file and later use that file as input to another program.

We hope that by seeing how the Query Designer sessions result in SELECT statements, you will not think of SQL statements as being difficult. Just remember to break the SELECT into individual clauses when you try to understand it.

Grouping Records to Summarize Data

Before you leave this introductory chapter on queries and SQL, return to the Query Designer one more time to see how to group records to provide summary information.

This example requires three tables from the Tastrade database: ORDERS, ORDER_LINE_ITEMS, and PRODUCTS. Using the previously defined persistent relations, notice that ORDERS relates to ORDER_LINE_ITEMS through the Order_Id field. Similarly, ORDER_LINE_ITEMS relates to PRODUCTS through the Product_Id field. Figure 6.24 shows the inner joins and the persistent relations. A separate filter that limits the result set to orders taken in a one-year period has been set in the Filter tab.

Figure 6.24: The inner joins used in the Query Designer to join ORDERS, PRODUCTS, and ORDER_LINE_ITEMS.

This report requires as output the product name, the quantity sold, and a price analysis. In fact, you want to see only one record for each product-a record that sums the number of units sold for that product. In a report, you would group the output records by product ID or product name, sum the quantity field for each detail record, and print the resulting sum after the last record for that product.

In the Query Designer, you accomplish something similar by using options in the Group By tab. Move the Product_Id field from the Available Fields list to the Grouped Fields list. This operation tells the Query Designer to output only a single record for each product ID. Figure 6.25 shows the Group By definition in the Query Designer.

Figure 6.25: Group selected records by Product_Id, using the Group By tab of the Query Designer.

When you are looking at the field values for a group of records for the same product ID, some values change, and others remain constant. If, as in this case, the product ID identifies the group, the ID value remains constant, but so do other fields, such as description. Do not worry about these fields. In the quantity field, however, each detail record represents a different transaction. Because you need to include this field in the query, you must tell Visual FoxPro what to do with it. Otherwise, VFP outputs to the results table the values in the last record that it processes for each group.

In this query, you can sum the quantity sold in each transaction. Going to the Fields tab, click the button to the right of the Functions and Expressions field. This button displays the Expression Builder. The grouping functions are listed in the Math drop-down list; Table 6.8 defines them.

Click SUM() to calculate the total quantity of a numeric field. Visual FoxPro immediately displays "SUM(expN)" in the Expression text area. Select Order_line_items from the From Table drop-down list. Then select ORDER_LINE_ITEMS.Quantity in the Fields list (see Figure 6.26). The required expression appears in the text area. Clicking OK adds the expression to the Functions and Expressions field. Finally, select the new function and add it to the Fields list by clicking the Add button.

Figure 6.26: Open the Fields tab of the Expression Builder to define a calculation method and a field for a query.

Because the selected data spans a year, you expect that prices will change at least for some products. Thus, although Unit_Price (stored in PRODUCTS) represents the current price, it might not equal the price for previous orders. Because the program saves the Unit_Price with each order in the order details file (ORDER_LINE_ITEMS), however, you can determine the minimum, maximum, and average price throughout the year. To retrieve these other prices for the report, use the Fields page of the Expression Builder. The new calculated fields for this query include the following:

• SUM(ORDER_LINE_ITEMS.quantity)
• MIN(ORDER_LINE_ITEMS.unit_price)
• MAX(ORDER_LINE_ITEMS.unit_price)
• AVG(ORDER_LINE_ITEMS.unit_price)

Figure 6.27 shows the SQL SELECT statement for the completed query.

Figure 6.27: Here is the complete query to analyze Tastrade product prices.

Using Queries in Programs and Forms

When you are using the Query Designer to create a query that you later want to use in a program or form, remember to save the query after testing it. Visual FoxPro saves the SQL SELECT statement in a file with the extension .QPR. You can execute the SQL directly from the Command window, program, or form by using a simple DO command, such as the following:

DO SALBYPRD.QPR

Because the Query Designer does not automatically include drive or path prefixes for databases or tables, you must do one of the following things:

• Run the query from the directory that contains the databases or tables.
• Add a SET DEFAULT TO or SET PATH TO command to change to the directory that contains the data.
• Modify the query manually to contain the drive and path information.

The following SELECT statement shows a simple example:

      SELECT * ;
        FROM C:\VFP\SAMPLES\ \DATA\TASTRADE!CUSTOMER

From within a form control, simply attach a DO statement like the preceding one to the Click event. Visual FoxPro executes the query whenever someone clicks the control.

Editing the Generated SQL SELECT Statement

The .QPR file is a text file. just like a .PRG. Therefore, you can edit it with either of the following commands:

MODIFY COMMAND SELBYPRD.QPR
MODIFY FILE SELBYPRD.QPR

Both commands open an Edit window and load SELBYPRD.QPR into it. The following are some additional reasons that you might edit the SQL SELECT statement directly, as opposed to using the Query Designer:

• Faster editing of minor changes, such as adding or deleting a field or changing a condition test value.
• Creating more complex queries. After all, the Query Designer is still an end user's tool and lacks the robustness available with direct creation of SQL SELECT statements.

Creating Complex Queries

The first part of this chapter concentrated on using the Query Designer to create the SQL SELECT statements. The Query Designer has enough power for many basic queries, and it helps teach you query design in a visual environment in which you can concentrate on what you want to do, not on how to do it. If you plan to write your own SQL SELECT statements from scratch, you need to know the syntax. The following shows the complete syntax for creating SQL SELECT statements:

SELECT [ALL | DISTINCT] [TOP nExpr [PERCENT]]
 [Alias.] Select_Item [AS Column_Name]
 [, [Alias.] Select_Item [AS Column_Name] ...]
FROM [FORCE] [DatabaseName!]Table [LocalAlias]
 [[INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] JOIN]
[, [DatabaseName!]Table [Local_Alias]
 [[INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] JOIN] ...]
 [ON [DatabaseName!]Table [Local Alias] .Column_Name =
 [DatabaseName!]Table [Local_Alias] .Column_Name]

[[INTO Destination]
 | [TO FILE FileName [ADDITIVE] | TO PRINTER [PROMPT]
 | TO SCREEN]]
[PREFERENCE PreferenceName]
[NOCONSOLE]
[PLAIN]
[NOWAIT]
[WHERE FilterCondition [AND | OR FilterCondition ...]]]
[GROUP BY GroupColumn [, GroupColumn ...]]
[HAVING FilterCondition]
[UNION [ALL] SELECTCommand]
[ORDER BY Order_Item [ASC | DESC] [, Order_Item [ASC | DESC] ...]]

Many of these clauses are described earlier in this chapter. As you can quickly tell, not all of these clauses appear in the Query Designer. Two primary clauses that are not included are the use of subqueries and UNIONs. The balance of this chapter is devoted to subqueries. Chapter 7 "Advanced Queries and Views," picks up with a discussion of UNIONs.

Using a BETWEEN Clause to Filter a Range of Values

The BETWEEN clause enables you to define a value range for a field by using a single condition statement rather than two. However, you will experience very little difference in performance whether you use the BETWEEN clause or two separate conditions. Suppose that you want a list of order details for products that have product IDs between 10 and 19. You could write a two-condition SELECT, as follows:

SET ANSI ON
SELECT * ;
  FROM order_line_items ;
 WHERE ALLTRIM(Product_Id) >= '10' AND ALLTRIM(Product_Id) <= '19'

Combining these two conditions with BETWEEN results in the following statement:

SET ANSI ON
SELECT * ;
  FROM order_line_items ;
 WHERE ALLTRIM(Product_Id) BETWEEN '10' AND '19'

You will find little difference between the times that it takes these two statements to find 31,280 records out of 225,840 (in a modified version of the ORDER_LINE_ITEMS table). In fact, the first statement ran only slightly faster on the test machine, requiring 44.00 seconds, compared with 44.87 seconds for the second statement. Perhaps the difference is that the first statement eliminates some records that have only a single comparison expression. The difference is minimal, however.

Because the field used in this condition contains only digits, you could also write the condition as follows:

SET ANSI ON
SELECT * ;
  FROM products ;
 WHERE VAL(Product_Id) BETWEEN 10 AND 19

The performance of this query is significantly slower than that of the first two. Perhaps this fact reflects on the inefficiency of converting the string to a numeric value first before performing the comparison. This SELECT requires 47.35 seconds on the test system. The problem in each case is that the field criteria do not match an index definition. The index on the product ID uses no special functions. Therefore, the only way to obtain better performance from a query is to eliminate all functions used in the WHERE clauses. The following example, which carefully formats the test values as right-justified six-character fields, executes in 31.47 seconds (still a long time, but the query is retrieving 32,180 records out of 225,840):

SELECT * ;
  FROM order_line_items ;
  WHERE product_id >= '    10' AND product_id <= '    19'

This procedure also averts any potential problems caused by having SET ANSI ON or OFF.

Using an IN Clause to Filter Selected Values

The IN clause enables you to specify a discrete set of values that do not constitute an inclusive range. Suppose that you want to see only the customers from Canada, Mexico, the United Kingdom, and the United States. CUSTOMER.DBF has a Country field that you can use, but testing for each country separately would be awkward, as in the following example:

SELECT Company_Name ;
  FROM CUSTOMER ;
  WHERE UPPER(Country) = 'CANADA' OR ;
       UPPER(Country) = 'MEXICO' OR ;
       UPPER(Country) = 'UK' OR ;
       UPPER(Country) = 'USA' ;
  ORDER BY Country, Company_Name

You can perform the same query faster by using an IN clause that lists the possible values, as follows:

SELECT Company_Name ;
  FROM CUSTOMER ;
  WHERE UPPER(Country) IN ('CANADA', 'MEXICO', 'UK', 'USA') ;
  ORDER BY Country, Company_Name

This second expression required less time than the first: 4.67 seconds for the first versus 4.07 seconds for the second to find 1,540 records out of 5,005. (An inflated version of CUSTOMER was used.) The performance difference becomes even greater the more values that Visual FoxPro needs to compare. This fact implies that if you need to compare a field with several values, using the IN clause is more efficient.

Defining Subqueries

The filter conditions used in queries earlier in this chapter had this basic structure:

<field expression> <operator> <field expression or value>

You can create more complex conditions that use a subquery to limit the values. A subquery is simply a query within a query. You use subqueries primarily for two reasons:

• To define a set of values, of which a field must be a member, before including the record in the result table
• To determine whether a field value exists in another table

Suppose that you want to produce a list of customers who did not make any purchases during a given year, such as 1998. Producing a list of customers who did make purchases is easy. Simply enter the following command:

SELECT DISTINCT CUSTOMER.Company_Name ;
  FROM CUSTOMER, ORDERS ;
  WHERE CUSTOMER.Customer_Id = ORDERS.Customer_Id AND ;
       YEAR(ORDERS.Order_Date) = 1998

Notice that the DISTINCT clause prevents the company name from appearing more than one time. On a test system using Tastrade data, this query took 1.15 seconds to retrieve 87 records out of 91. To get those 87 records, the query had to examine 929 order records.

You can also write this query using a subquery. The subquery creates a list of customer IDs for customers who placed orders in 1998. The main query retrieves the company name for customer IDs in the subquery list, as follows:

SELECT Company_Name ;
  FROM CUSTOMER ;
  WHERE Customer_Id IN (SELECT Customer_Id ;
                    FROM ORDERS ;
                    WHERE YEAR(Order_Date) = 1998)

This complex query retrieves the same data as the preceding one, but it requires 5.11 seconds. Notice that a complex query takes more time than a simple one does. In almost all cases, a simple query outperforms a complex query.

The problem is that neither command answered the real question: What are the names of the customers who did not place orders? You would have trouble attempting to modify the first query to answer this request. You can modify the second query, however, by adding a single word, as follows:

SELECT Company_Name ;
  FROM CUSTOMER ;
  WHERE Customer_Id NOT IN (SELECT Customer_Id ;
                    FROM ORDERS ;
                    WHERE YEAR(Order_Date) = 1998)

Adding NOT before IN changes the meaning of the query to answer the question correctly. In case you are interested, this query ran in 5.27 seconds.

Sometimes, writing a single SQL statement is just not convenient because SQL generates a results table with duplicated fields. Suppose that you want to retrieve only orders from outside the United States. You might write the following command:

SELECT * ;
  FROM ORDERS, CUSTOMER ;
  WHERE CUSTOMER.Customer_Id = ORDERS.Customer_Id AND ;
       UPPER(CUSTOMER.Country) # 'USA'

A problem occurs when SQL sees the potential for duplicated field names; it renames both fields by appending an underscore and letter to them. You could manually list each field that you want to be prefixed by its table name, thereby averting duplicate references, but for tables that have a large number of fields, this method requires a great deal of extra work.

Another solution splits the query into two pieces. The inner query creates a list of customers who are not from the United States. The outer one retrieves records from ORDERS only if the inner query returns any records.

SELECT * ;
  FROM ORDERS ;
  WHERE EXISTS (SELECT Customer_Id FROM CUSTOMER ;
                 WHERE CUSTOMER.Customer_Id = ORDERS.Customer_Id AND ;
                       UPPER(CUSTOMER.Country) # 'USA')

The second query takes longer than the first simple one (2.20 seconds, compared with 1.65 seconds, for 931 records out of 1,080), but it has no problem with renamed fields.

This type of complex query is called an existence or correlated query. When executed, the inner query must reference a field from the outer query (ORDERS.Customer_Id, in this case). As a result, VFP performs the inner query for each record in the outer query, which slows performance.

Because EXISTS merely checks to see whether any records exist, it doesn't matter what fields it returns. EXISTS can even return all fields with an asterisk (*), although using a single field results in slightly better performance.

A better subquery than the preceding method uses IN. IN does not require re-executing the query for each row in the outer query; therefore, it is called a noncorrelated subquery. Because it uses a subquery, however, it does take longer (2.14 seconds) than a simple query.

SELECT * ;
  FROM ORDERS ;
  WHERE Customer_Id IN (SELECT Customer_Id FROM CUSTOMER ;
                 WHERE UPPER(Country) # 'USA')

The EXISTS clause compared a value in one SELECT against all values in another SELECT. Suppose that you want to find out which products, if any, have not been purchased since the last price increase posted in PRODUCTS.DBF. In this case, use an inner SQL SELECT to create a list of all product prices for orders by Product_Id. Then, using the outer SELECT, check to see whether the current unit price in PRODUCTS is greater than all values in the first list. The following query result displays the product name and ID of any such products:

SELECT DISTINCT PRODUCTS.Product_Name, PRODUCTS.Product_Id ;
   FROM PRODUCTS, ORDER_LINE_ITEMS ;
   WHERE PRODUCTS.Product_Id = ORDER_LINE_ITEMS.product_id AND ;
        PRODUCTS.Unit_Price > IN (SELECT DISTINCT Unit_Price ;
             FROM ORDER_LINE_ITEMS ;
             WHERE ORDER_LINE_ITEMS.Product_Id = PRODUCTS.product_id);
  ORDER BY PRODUCTS.Product_Id

Finally, use SOME or ANY to compare the value of a field in the outer query with at least one or more of the values selected by the inner query. Suppose that you want to know whether any products have a current price that is more than 50 percent greater than that of their first order. Modify the preceding query by using SOME, as follows:

SELECT DISTINCT PRODUCTS.Product_Name, PRODUCTS.Product_Id ;
   FROM PRODUCTS, ORDER_LINE_ITEMS ;
  WHERE PRODUCTS.Product_Id = ORDER_LINE_ITEMS.Product_Id AND ;
        PRODUCTS.Unit_Price/1.5 > SOME (SELECT DISTINCT Unit_Price ;
             FROM ORDER_LINE_ITEMS ;
            WHERE ORDER_LINE_ITEMS.Product_Id = PRODUCTS.Product_Id);
  ORDER BY PRODUCTS.Product_Id

The point of all these examples was not only to show you some of the other clauses that SQL SELECT supports, but also to show you that different ways of expressing a query can lead to different query times. A method that might provide the best performance for small tables in which the selected records are relatively concentrated might not be the best when the tables become large and the data more scattered. The best advice is to experiment with various ways of writing the SQL SELECT statement to find out which method yields the best performance.

© Copyright, Sams Publishing. All rights reserved.