Chapter 8
SQL Changes

After leaving the SQL portion of VFP virtually unchanged for a long time, Microsoft began to introduce additional features and increased compliance with the ANSI-92 standard with VFP 8. VFP 9 continues that process, offering additional functionality for SELECT, INSERT, UPDATE, and DELETE, as well as a number of other improvements.

VFP’s SQL sublanguage lets you manipulate data without worrying about record pointers or work areas. VFP 9 includes a number of enhancements to this part of the language. Many of the limits related to queries have been removed, the use of subqueries has been expanded, and the UPDATE and DELETE commands accept join conditions to specify the records to modify or remove. VFP 9 also offers some performance improvements, an easier way to test performance, and the ability to query against a buffered table.

VFP 9 has no limits

The most basic change to queries in VFP 9 is the elimination of a number of restrictions. In earlier versions, for example, the total number of joins and subqueries in a single query was limited to nine; in VFP 9, there’s no limit. Table 1 shows the limits related to queries removed or raised in VFP 9.

Table 1. SQL queries were limited in a number of ways in earlier versions of Visual FoxPro. Many of those limits were lifted in VFP 9.

Description

Limit in VFP 8 (and earlier)

Limit in VFP 9

Total number of joins and subqueries

9

No limit

Number of UNIONs

9

No limit

Number of tables and aliases referenced

30

No limit

Number of items listed in IN clause

24

Based on SYS(3055) setting

Nesting level for subqueries

1

No limit

 

For everyday queries, most developers never run into these limits, but each of them can pose problems in certain situations.

Lots of tables

With fully normalized data, the old limits on joins, as well as those on the number of tables and aliases referenced, can make it difficult to pull together all the information for a particular entity. For example, consider the Northwind database that comes with VFP 8
and 9. Collecting all the data related to each order, including customer information, shipper information, supplier information and so forth, involves 11 tables. Listing 1 shows such
a query.


Text Box: "

The Developer Downloads for this chapter, available from www.hentzenwerke.com, include the query in Listing 1 as ManyTables.PRG.

Listing 1. Flattening a normalized database can involve many joins and tables.

SELECT ProductName, CategoryName, OrderDate, ;

       Customers.CompanyName AS CustomerName, ;

       OrderDetails.Quantity, ;

       OrderDetails.UnitPrice, ;

       Suppliers.CompanyName AS SupplierName, ;

       Employees.LastName, Employees.FirstName, ;

       Territories.TerritoryDescription, ;

       Region.RegionDescription, ;

       Shippers.CompanyName AS ShipperName;

  FROM Orders ;

    LEFT JOIN Customers ;

      ON Orders.CustomerID = Customers.CustomerID ;

    LEFT JOIN OrderDetails ;

      ON Orders.OrderID = OrderDetails.OrderID ;

    LEFT JOIN Products ;

      ON OrderDetails.ProductID = Products.ProductID ;

    LEFT JOIN Categories ;

      ON Products.CategoryID = Categories.CategoryID ;

    LEFT JOIN Suppliers ;

      ON Products.SupplierID = Suppliers.SupplierID ;

    LEFT JOIN Shippers ;

      ON Orders.ShipVia = Shippers.ShipperID ;

    LEFT JOIN Employees ;

      ON Orders.EmployeeID = Employees.EmployeeID ;

    LEFT JOIN EmployeeTerritories ;

      ON Employees.EmployeeID = EmployeeTerritories.EmployeeID ;

    LEFT JOIN Territories ;

      ON EmployeeTerritories.TerritoryID = Territories.TerritoryID ;

    LEFT JOIN Region ;

      ON Territories.RegionID = Region.RegionID ;

  ORDER BY Products.ProductID, Orders.OrderDate ;    

  INTO CURSOR Unfolded

This query lists 11 different aliases and performs 10 joins; VFP 8 and earlier versions can’t run it because it generates error 1805, “SQL: Too many subqueries.” VFP 9 executes it without a problem. (While this particular example is a little contrived, flattening a normalized database is a common problem and can involve many, many joins.)

Nearly unlimited IN list

In earlier versions of VFP, the IN (list of items) operator was limited to 24 items in the list. While VFP 9 doesn’t entirely remove the limit, it does give you control over it through the SYS(3055) function. Even without manipulating SYS(3055), the limit is significantly higher than in earlier versions. In our tests, we could include 154 items before we had to raise SYS(3055).


Text Box: ¥

The IN operator can be used both to filter based on a list of items and to filter based on subquery results. The change here affects only the use of IN with a specific list of items.

When you control the query yourself, the limit on the IN clause isn’t generally a problem. You can usually find another approach to avoid a large IN clause. One solution is to store the list of values to a cursor and do a join with that cursor. For example, this query:

SELECT cFirst, cLast ;

   FROM Person ;

   WHERE UPPER(cLast) IN ("BLACK", "BROWN", "GREEN", "SILVER", "WHITE")

could be replaced with:

CREATE CURSOR Names (cName C(25))

INSERT INTO Names VALUES ("BLACK")

INSERT INTO Names VALUES ("BROWN")

INSERT INTO Names VALUES ("GREEN")

INSERT INTO Names VALUES ("SILVER")

INSERT INTO Names VALUES ("WHITE")

 

SELECT cFirst, cLast ;

   FROM Names ;

     JOIN Person ;

       ON UPPER(cLast) = RTRIM(cName)

However, you don’t always have the chance to write this kind of code. In particular, other applications that access VFP data through OLE DB may generate queries that use the IN clause and offer no chance to code around it.

SYS(3055) was added in VFP 6 to solve a problem in updating views. It controls the allocation of space for evaluating FOR and WHERE clauses. The default value is 320, and it can be increased in increments of 8. In our tests, each increase of 8 in the SYS(3055) value allowed 4 more items in the list for IN.

Nested Subqueries

The ability to use subqueries (a query within a query) makes it possible to get some results with a single query that would otherwise require multiple queries. Perhaps the most common query involving a subquery is finding all the records in one table that are not in another. For example, this query (using the TasTrade database that comes with VFP) gets a list of companies from the Customer table that placed no orders:

SELECT Company_Name ;

   FROM Customer ;

   WHERE Customer_ID NOT IN ;

      (SELECT Customer_ID FROM Orders) ;

   INTO CURSOR NoSales

Another common use for a subquery is to perform aggregation and then allow the main query to retrieve additional information about the aggregate results. For example, you can use a subquery to get a list of the products included in each customer's most recent order, as in Listing 2. The subquery in this example is correlated, meaning it uses a field from a table  not listed in the subquery itself, only in the main query. In the example, OrdLast.Customer_ID is used in the WHERE clause of the subquery, but OrdLast is an alias for Orders defined in the main query.

Text Box: "

The Developer Downloads for this chapter, available from www.hentzenwerke.com, include the query in Listing 2 as CustProducts.PRG and the query in Listing 3 as ProductFirstNotLast.PRG.

Listing 2. The subquery here finds each customer’s most recent order, and then uses those results to get the list of products included in that order.

SELECT CustLast.Customer_ID, Product_ID ;

   FROM Order_Line_Items OLILast;

     JOIN Orders OrdLast;

       ON OLILast.Order_ID = OrdLast.Order_ID ;

     JOIN Customer CustLast;

       ON OrdLast.Customer_ID = ;

          CustLast.Customer_ID ;

   WHERE OrdLast.Order_Date = ;

     (SELECT MAX(Order_Date) ;

        FROM Orders Ord ;

        WHERE OrdLast.Customer_ID = ;

              Ord.Customer_ID ) ;

   INTO CURSOR CustProducts

In VFP 8 and earlier, subqueries cannot be nested. That is, the WHERE clause of a subquery can’t use another subquery. VFP 9 allows nested subqueries, increasing the number of questions you can answer with a single query.

Suppose you want to find out what products a company included in its first order, but not its most recent. While you could use the cursor created in Listing 2 in another query, in VFP 9, you can do the whole job with a single query, shown in Listing 3.

Listing 3. Nesting subqueries lets you solve more problems with a single query rather than a series of queries.

SELECT Customer.Company_Name, Product_ID ;

  FROM Order_Line_Items ;

    JOIN Orders ;

      ON Order_Line_Items.Order_ID = Orders.Order_ID ;

    JOIN Customer ;

      ON Orders.Customer_ID = Customer.Customer_ID ;

    WHERE Orders.Order_Date = ;

      (SELECT MIN(Order_Date) ;

         FROM Orders Ord ;

          WHERE Orders.Customer_ID=Ord.Customer_ID );

      AND Product_ID NOT IN ;

        (SELECT Product_ID ;

           FROM Order_Line_Items OLILast;

             JOIN Orders OrdLast;

               ON OLILast.Order_ID = OrdLast.Order_ID ;

             JOIN Customer CustLast;

               ON OrdLast.Customer_ID = ;

                  CustLast.Customer_ID ;

           WHERE OrdLast.Order_Date = ;

             (SELECT MAX(Order_Date) ;

                FROM Orders Ord ;

                WHERE OrdLast.Customer_ID = ;

                      Ord.Customer_ID ) );

  INTO CURSOR FirstNotLast

More flexible subqueries

The previous section looked at one way subqueries have improved in VFP 9. In fact, the VFP team did a lot of work with subqueries for this version. In VFP 8 and earlier, subqueries could be used only in the WHERE clause of a SQL command, that is, only in filtering data. In addition to removing the limit on nesting, VFP 9 allows you to use subqueries in the field list and the FROM clause of a query and in the SET clause of the SQL UPDATE command. VFP 9 also allows the GROUP BY clause in correlated subqueries (subqueries that refer to one or more fields from tables in the main query, like the subquery in Listing 2). Finally, VFP 9 allows you to use the TOP clause in subqueries, as long as those subqueries are not correlated.

Derived tables—Subqueries in the FROM clause

The result of a subquery in the FROM clause is called a derived table. One use for a derived table is retrieving additional data when you use an aggregate function.

Continuing to use the TasTrade database that comes with VFP, consider the problem of finding out about each customer’s most recent order. If all you want is the customer id and the order date, it’s easy:

SELECT Customer_ID, MAX(Order_Date) ;

  FROM Orders ;

  GROUP BY 1 ;

  INTO CURSOR MostRecentOrder

This query is simple and works in every version of VFP. Suppose, though, you want not just the customer id, but additional information about the order, such as the company name, the contact person for the company, and the name of the shipper. Prior to VFP 9, you need to use a correlated subquery, two queries in sequence, or a very ugly subquery. Listing 4 shows the solution using a correlated subquery, Listing 5 shows the two queries in sequence technique, and Listing 6 shows the ugly subquery approach. What makes the subquery in Listing 6 so ugly is the need to combine the customer id and the most recent date into a single field for comparison.

Text Box: "

The queries shown in Listing 4, Listing 5, and Listing 6 are included
in the Developer Downloads for this chapter, available from www.hentzenwerke.com, as MostRecentCorrelated.PRG, MostRecentTwoQueries.PRG, and MostRecentSubquery, respectively.

Listing 4. Finding data associated with an aggregate is one reason to use a correlated subquery.

SELECT Orders.Order_ID, Customer.Company_Name as Cust_Name, ;

       Shippers.Company_Name AS Ship_Name, Orders.Order_Date ;

  FROM Orders ;

    JOIN Customer ;

      ON Orders.Customer_ID = Customer.Customer_ID ;

    JOIN Shippers ;

      ON Orders.Shipper_ID = shippers.Shipper_ID ;

  WHERE Orders.Order_Date = ;

    (SELECT MAX(Order_Date) ;

       FROM Orders Ord WHERE Orders.Customer_ID=Ord.Customer_ID );

  ORDER BY Cust_Name ;

  INTO CURSOR MostRecentOrders

Listing 5. Another way to find the data associated with an aggregate result is to use two queries in sequence. The first query does the grouping, and then the second query joins with that result.

SELECT Customer_ID, MAX(Order_Date) as Order_Date ;

  FROM Orders ;

  GROUP BY 1 ;

  INTO CURSOR RecentOrder

 

SELECT Orders.Order_ID, Customer.Company_Name as Cust_Name, ;

       Shippers.Company_Name AS Ship_Name, Orders.Order_Date ;

  FROM Orders ;

    JOIN RecentOrder ;

      ON Orders.Customer_ID = RecentOrder.Customer_ID ;

      AND Orders.Order_Date = RecentOrder.Order_Date ;

    JOIN Customer ;

      ON Orders.Customer_ID = Customer.Customer_ID ;

    JOIN Shippers ;

      ON Orders.Shipper_ID = shippers.Shipper_ID ;

  ORDER BY Cust_Name ;

  INTO CURSOR MostRecentOrders

Listing 6. Yet another way to find data associated with an aggregate result is to use a subquery in the WHERE clause. The resulting query is hard to maintain because of the strange expression used to match data to the subquery result.

SELECT Orders.Order_ID, Customer.Company_Name as Cust_Name, ;

       Shippers.Company_Name AS Ship_Name, Orders.Order_Date ;

  FROM Orders ;

    JOIN Customer ;

      ON Orders.Customer_ID = Customer.Customer_ID ;

    JOIN Shippers ;

      ON Orders.Shipper_ID = shippers.Shipper_ID ;

  WHERE Orders.Customer_ID + DTOS(Orders.Order_Date) IN ;

    (SELECT Customer_ID + DTOS(MAX(Order_Date)) ;

       FROM Orders GROUP BY Customer_ID );

  ORDER BY Cust_Name ;

  INTO CURSOR MostRecentOrders

VFP 9 gives you a better alternative. You can perform the subquery on the fly right in the join clause. Putting the subquery into the join clause means it doesn’t have to be correlated; in some cases, that means it will run faster. The query in Listing 7 uses this approach. Here, the subquery has moved into the JOIN clause, and doesn’t need to combine the customer id and most recent date. Instead, the ON portion of the JOIN compares the two fields separately, much like the two query solution in Listing 5.

Text Box: "

The query in Listing 7 is included in the Developer Downloads for this chapter, available from www.hentzenwerke.com, as MostRecentOrderDetails.PRG.

Listing 7. This query uses a subquery in the FROM clause (a derived table) to solve the problem of finding data associated with aggregates.

SELECT Orders.Order_ID, Customer.Company_Name as Cust_Name, ;

       Shippers.Company_Name AS Ship_Name, Orders.Order_Date ;

  FROM Orders ;

    JOIN (SELECT Customer_ID, MAX(Order_Date) as Order_Date ;

        FROM Orders CheckOrderDate ;

        GROUP BY 1) RecentOrder ;

      ON Orders.Customer_ID = RecentOrder.Customer_ID ;

      AND Orders.Order_Date = RecentOrder.Order_Date ;

    JOIN Customer ;

      ON Orders.Customer_ID = Customer.Customer_ID ;

    JOIN Shippers ;

      ON Orders.Shipper_ID = shippers.Shipper_ID ;

  ORDER BY Cust_Name ;

  INTO CURSOR MostRecentOrders

Be aware that a subquery in the FROM clause can’t be correlated, which means it may not refer to fields of tables used in the main query, only to fields of the tables it lists. This is because all subqueries in the FROM clause are computed before the joins are performed, thus it’s not yet clear what records are in the result.

Computing fields with a subquery

In addition to supporting derived tables, VFP 9 lets you put subqueries in the field list of a query. That is, you can use a subquery to compute a field to appear in the result. A subquery used this way must return a single field and no more than a single record. If there are no records in the result for a particular record, that field is assigned the null value.

Why would you do this? Why not include the expression in the main query and add any necessary tables? As with derived tables, this approach is handy when grouping is involved. Suppose you want to find the total value of the orders placed for each customer in a particular year. Along with that, you want a great deal of customer information, including the address, phone number, and fax number.

Clearly to compute the total value of orders for a customer, you need to group data from Order_Line_Items by customer. You can extract multiple fields from Customer along the way as long as you add them to the GROUP BY clause. (Prior to VFP 8, you could include additional fields, even without putting them in the GROUP BY clause. In VFP 8, you can do so by issuing SET ENGINEBEHAVIOR 70.) Listing 8 shows a query that retrieves the customer id, company name, address, phone, and fax information along with the total value of that customer’s orders.

Text Box: "

The query in Listing 8 is included in the Developer Downloads for
this chapter, available from www.hentzenwerke.com, as CustomerTotalGrouped.PRG.

Listing 8. In a query aggregating a field or fields of a child table, you can add fields from a parent table as long as you add them to the GROUP BY clause. Don’t forget to define nYear before running this query.

SELECT Customer.Customer_ID, Customer.Company_Name, ;

       Customer.Address, Customer.City, Customer.Region, ;

       Customer.Postal_Code, Customer.Phone, Customer.Fax, ;

       SUM(quantity*unit_price) AS yTotal ;

FROM Customer ;

  LEFT JOIN Orders ;

    JOIN Order_Line_Items;

      ON Orders.Order_ID = Order_Line_Items.Order_ID ;

    ON Customer.Customer_ID = Orders.Customer_ID ;

    AND BETWEEN(Order_Date,DATE(m.nYear,1,1),DATE(m.nYear,12,31)) ;

  GROUP BY 1, 2, 3, 4, 5, 6, 7, 8;

INTO CURSOR CustomerTotal

However, adding so many fields to the GROUP BY clause slows the query down. One alternative that works in this situation is to remove the fields from the GROUP BY clause and wrap them in MAX() or MIN(). As with grouping, because these fields are the same for all records in the group, using MAX() or MIN() doesn’t change the results. This version, shown in Listing 9, is somewhat faster than listing all the fields in the GROUP BY clause.

Text Box: "

The query in Listing 9 is included as CustomerTotalMax.PRG in the Developer Downloads for this chapter, available from www.hentzenwerke.com.

Listing 9. Another approach to retrieving parent data in a grouped query is to wrap the extra fields in MAX() or MIN(). Again, don’t forget to define nYear before running this query.

SELECT Customer.Customer_ID, MAX(Customer.Company_Name), ;

       MAX(Customer.Address), MAX(Customer.City), MAX(Customer.Region), ;

       MAX(Customer.Postal_Code), MAX(Customer.Phone), MAX(Customer.Fax), ;

       SUM(quantity*unit_price) AS yTotal ;    

FROM Customer ;

  LEFT JOIN Orders ;

    JOIN Order_Line_Items;

      ON Orders.Order_ID = Order_Line_Items.Order_ID ;

    ON Customer.Customer_ID = Orders.Customer_ID ;

    AND BETWEEN(Order_Date,DATE(m.nYear,1,1),DATE(m.nYear,12,31)) ;

  GROUP BY 1;

  INTO CURSOR CustomerTotal

However, the ability to use a subquery in the field list provides an even simpler and more efficient solution to this problem. You can compute the total value of the orders in a subquery, allowing the main query to refer only to the parent table. Not only do you eliminate the extra fields in GROUP BY and the extra calls to aggregate functions, but you can also eliminate the outer join. Listing 10 shows the query.

Text Box: "

The query in Listing 10 is included in the Developer Downloads for this chapter, available from www.hentzenwerke.com, as CustomerTotal.PRG.

Listing 10. Using a subquery in the field list simplifies the problem of showing additional fields from the parent table when data is aggregated based on a child table. Be sure to give nYear a value before running this query.

SELECT Customer.Customer_ID, Customer.Company_Name, ;

       Customer.Address, Customer.City, Customer.Region, ;

       Customer.Postal_Code, Customer.Phone, Customer.Fax, ;

       (SELECT SUM(quantity*unit_price) ;

          FROM Orders ;

            JOIN Order_Line_Items;

              ON Orders.Order_ID = Order_Line_Items.Order_ID ;

          WHERE BETWEEN(Order_Date,DATE(m.nYear,1,1),DATE(m.nYear,12,31)) ;

            AND Customer.Customer_ID=Orders.Customer_ID ) as yTotal ;

  FROM Customer ;

  INTO CURSOR CustomerTotal

In our tests, using the queries shown, the subquery version (Listing 10) was about 12% faster than the version with calls to MAX() (Listing 9), which in turn was about 12% faster than listing all the parent fields in the GROUP BY clause (Listing 8). The more fields from the parent in the field list, the greater the advantage of the subquery version.

Computing replacements in UPDATE

The third new place you can use subqueries is the SET clause of the UPDATE command. That is, you can use a subquery to compute the value to which a field is to be set. However, when you use this approach, the UPDATE command cannot include a subquery in the WHERE clause. In addition, you’re limited to a single subquery in the SET clause, so you can’t use this approach to compute the values of multiple fields.

For this example, imagine that you have a data warehouse (SalesByProduct) for TasTrade data that tells you how many of each product were sold and the dollar amount of those sales. It’s designed to hold data for a single month and you want to update it at the end of the month.

To update the data, use the following UPDATE commands. Set nMonth and nYear to the month and year of the data you’re collecting before running the code in Listing 11. (“Correlated Updates,” later in this chapter, provides a better solution to this problem.)

Text Box: "

Code to create the data warehouse as a cursor is included in the Developer Downloads for this chapter, available from www.hentzenwerke.com, as CreateWarehouse.PRG. The code in Listing 11 is SubqueryInSet.PRG.

Listing 11. You can use a subquery in the SET portion of the SQL UPDATE command to calculate the new field values on the fly.

UPDATE SalesByProduct ;

  SET TotalSales = (;

    SELECT NVL(SUM(quantity*unit_price),$0) ;

      FROM Order_Line_Items ;

        JOIN Orders ;

          ON Order_Line_Items.Order_ID = Orders.Order_ID ;

      WHERE MONTH(Order_Date) = nMonth AND YEAR(Order_Date) = nYear;

        AND Order_Line_Items.Product_ID = SalesByProduct.Product_ID)

   

UPDATE SalesByProduct ;

  SET UnitsSold = (;

    SELECT CAST(NVL(SUM(quantity),0) AS N(12)) ;

      FROM Order_Line_Items ;

        JOIN Orders ;

          ON Order_Line_Items.Order_ID = Orders.Order_ID ;

      WHERE MONTH(Order_Date) = nMonth AND YEAR(Order_Date) = nYear;

        AND Order_Line_Items.Product_ID = SalesByProduct.Product_ID)

The second UPDATE in the example uses the new CAST() function that lets you change data types on the fly. See Chapter 14, “Language Improvements,” for details.

Correlated subqueries and grouping

A correlated subquery is one that refers to one or more fields of the main query, generally in the subquery’s WHERE clause. VFP 8 prohibits the GROUP BY clause in correlated subqueries. Because correlation can give you the same effect as grouping in many cases (see Listing 4, for example), you may not have run into this limit.

But there are a few situations where the ability to use GROUP BY in a correlated subquery makes it easier to get the desired results. Fortunately, VFP 9 permits grouping in a correlated subquery.

For example, consider the case where you want a list of customers who placed at least one order totaling more than a specified value and had that order shipped somewhere other than their address of record. (You might be checking into suspicious transactions.) In VFP 8 and earlier, extracting this information requires two queries in sequence: the first collects information about orders over the specified amount, while the second compares the shipping address of those records to the customer address and extracts customer information. Listing 12 shows one way to do this, with the threshold value set to $4000.

Text Box: "

The code in Listing 12 is included in the Developer Downloads
for this chapter, available from www.hentzenwerke.com, as SuspiciousTwoQueries.PRG. The query in Listing 13 is called SuspiciousOrders.PRG.

Listing 12. In VFP 8 and earlier, it takes two queries to get a list of large orders shipped somewhere other than their home office, along with customer information.

SELECT Orders.Customer_ID, Orders.Ship_to_Address, ;

       Orders.Order_ID, Orders.Order_Date ;

  FROM Orders ;

    JOIN Order_line_items ;

      ON Orders.Order_ID=Order_Line_Items.Order_ID ;    

  GROUP BY 1, 2, 3, 4 ;

  HAVING SUM(Quantity*Unit_Price)> 4000 ;

  INTO CURSOR BigOrders

 

SELECT Company_Name, Order_ID, Order_Date ;

  FROM Customer ;

    JOIN BigOrders ;

      ON Customer.Customer_ID = BigOrders.Customer_ID ;

  WHERE BigOrders.Ship_to_Address <> Customer.Address ;

  INTO CURSOR Suspicions

In VFP 9, the ability to group in correlated subqueries means you can find this result with one, more readable query. Listing 13 shows the one-query version.

Listing 13. Using GROUP BY in a correlated subquery in VFP makes it possible to find customers with suspicious orders in a single query.

SELECT Company_Name, Ord.Order_ID, Ord.Order_Date ;

  FROM Customer ;

   JOIN Orders Ord;

     ON Customer.Customer_ID = Ord.Customer_ID ;

  WHERE Ord.Order_ID in (;

    SELECT Orders.Order_ID;

    FROM Orders ;

      JOIN Order_Line_Items ;

        ON Orders.Order_ID=Order_Line_Items.Order_ID ;   

        AND Orders.Customer_ID=Customer.Customer_ID ;

        AND Orders.Ship_to_Address <> Customer.Address ;

    GROUP BY Orders.Order_ID ;

    HAVING SUM(Quantity*Unit_Price)> 4000 ) ;

  INTO CURSOR Suspicions

As with a number of other situations where a single query replaces two queries, the version in Listing 13 is faster than the one in Listing 12.

Using TOP n in subqueries

The TOP n clause of SELECT lets you return only the first n records (or first n% of the records) of the result set. While the MIN() and MAX() functions let you choose the single smallest or largest value in a given field, TOP n lets you choose multiple items. You can use it to see things like the 10 most recent orders or the 30 most expensive products. For example, this query finds the 10% of products with the fewest overall sales.

SELECT TOP 10 PERCENT Product_ID, ;

       SUM(Quantity) AS nSales ;

   FROM Order_Line_Items ;

   GROUP BY Product_ID ;

   ORDER BY nSales ;

   INTO CURSOR LowSales

In VFP 8 and earlier, you can’t use the TOP n clause in subqueries. VFP 9 changes that, permitting TOP n in subqueries unless the subquery is correlated. When you use TOP n in a subquery, you must include an ORDER BY clause in that subquery.

Imagine that you’re considering discontinuing the items extracted by the query above (the lowest-selling items). You might want to contact customers who have purchased those items to make sure it won’t be a problem for them. The query in Listing 14 uses the previous query as a subquery to create that list of customers.

Text Box: "

The code in Listing 14 is included in the Developer Downloads for this chapter, available from www.hentzenwerke.com, as Discontinue.PRG.

Listing 14. The subquery here finds the lowest-selling 10% of products. The main query uses that information to get a list of customers who purchased those products.

SELECT DISTINCT Company_Name, English_Name ;

   FROM Customer ;

     JOIN Orders ;

       ON Customer.Customer_ID = Orders.Customer_ID ;

     JOIN Order_Line_Items ;

       ON Orders.Order_ID = Order_Line_Items.Order_ID ;

     JOIN ( ;

      SELECT TOP 10 PERCENT Product_ID, ;

             SUM(Quantity) AS nSales ;

         FROM Order_Line_Items ;

         GROUP BY Product_ID ;

         ORDER BY nSales );

         AS LowSales ;

      ON Order_Line_Items.Product_ID = ;

         LowSales.Product_ID ;

     JOIN Products ;

       ON LowSales.Product_ID = Products.Product_ID ;

   ORDER BY English_Name, Company_Name ;

   INTO CURSOR BoughtLowSellers

Correlated updates

In addition to supporting subqueries in the SET clause, the SQL UPDATE command in VFP 9 has a new FROM clause that allows you to draw the update data from another table. This gives what you might call correlated updates.

The example in Listing 11 has one serious drawback. You have to use a separate UPDATE command for each field you want to change. Using the FROM clause, you can achieve the same result with a query followed by an UPDATE command. The code in Listing 15 computes the new values, stores them in a cursor, and then references that cursor in the UPDATE command.

Text Box: "

The code in Listing 15 is included in the Developer Downloads for this chapter, available from www.hentzenwerke.com, as CorrelatedUpdate.PRG. The code in Listing 16 is CorrelatedUpdateSubquery.PRG in the Developer Downloads.

Listing 15. The new FROM clause in SQL UPDATE lets you draw replacement values from another table.

SELECT Order_Line_Items.Product_ID, ;

       SUM(Quantity*Order_Line_Items.Unit_Price) as TotalSales, ;

       SUM(Quantity) AS UnitsSold ;

  FROM Order_Line_Items ;

    JOIN Orders ;

      ON Order_Line_Items.Order_ID = Orders.Order_ID ;

      AND MONTH(Order_Date) = nMonth AND YEAR(Order_Date) = nYear ;

  GROUP BY 1 ;

  INTO CURSOR MonthlySales

 

UPDATE SalesByProduct ;

  SET SalesByProduct.TotalSales = NVL(MonthlySales.TotalSales, $0), ;

      SalesByProduct.UnitsSold = NVL(MonthlySales.UnitsSold, 0) ;

  FROM SalesByProduct ;

    LEFT JOIN MonthlySales ;

      ON SalesByProduct.Product_ID = MonthlySales.Product_ID

Along with specifying that the values come from another table, you can actually perform joins in the FROM clause to put together the list of values. In Listing 15, the outer join ensures that the records for products not sold in the specified month are set to 0.

In fact, the FROM clause of UPDATE supports subqueries (derived tables), so you can do this entire operation in a single UPDATE command, as shown in Listing 16.

Listing 16. Instead of running a query ahead of time to compute the results, you can use a derived table in the FROM clause of an UPDATE command.

UPDATE SalesByProduct ;

  SET SalesByProduct.TotalSales = NVL(MonthlySales.TotalSales, $0), ;

      SalesByProduct.UnitsSold = NVL(MonthlySales.UnitsSold, 0) ;

  FROM SalesByProduct ;

    LEFT JOIN (;

      SELECT Order_Line_Items.Product_ID, ;

             SUM(Quantity*Order_Line_Items.Unit_Price) as TotalSales, ;

             SUM(Quantity) AS UnitsSold ;

        FROM Order_Line_Items ;

          JOIN Orders ;