Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

What's New with Data in Visual FoxPro 9? : Page 2

The Microsoft Visual FoxPro team has a great reputation for responding to community requests and version 9 reflects many such changes, most notably changes to the data engine that enhance data access and database application development.


advertisement
Enhanced Sub-Query Support
Sub-queries have always been powerful in the SQL language. They can be used as filters by placing them on the right side of a comparison in the WHERE clause. In Visual FoxPro 9, sub-queries can also be used as part of the SELECT list (called a projection) and in the FROM clause (often called a derived table).

Many of these changes improve FoxPro's client/server capabilities by providing stronger interoperability with SQL Server.
When used as a projection, if the sub-query doesn't return any records, a NULL value is returned. Correlation is supported with projection (more on this in a minute).

A SQL statement that uses projection looks like this:

SELECT ; C.CustomerID, ; C.CompanyName, ; (SELECT YTD_Sales FROM Sales_02 WHERE ; C.CustomerID = Sales_02.CustomerID) AS Y02,; (SELECT YTD_Sales FROM Sales_03 WHERE ; C.CustomerID = Sales_03.CustomerID) AS Y03,; (SELECT YTD_Sales FROM Sales_04 WHERE ; C.CustomerID = Sales_04.CustomerID) AS Y04 ; FROM Customers C

This SELECT statement returns the customer ID and company name along with year-to-date sales from warehoused tables for the last three fiscal years.

A restriction on a projection is that the sub-query should return only one column and no more than one record for each record in the containing SELECT.

Another valuable use of a projection is when it's used as part of an expression.

SELECT ; C.customerID, ; C.companyname, ; SUM(D.quantity*D.unitprice) AS CustTotal ,; (SUM(D.quantity*D.unitprice) / ; (SELECT SUM((quantity*unitprice)-discount) ; FROM OrderDetails D2) ; )*100 AS PctTotal ; FROM Customers C ; INNER JOIN Orders O ; ON C.customerID = O.customerID ; INNER JOIN OrderDetails D ; ON O.orderid = D.orderid ; GROUP BY C.customerID, C.companyname, O.orderID ; ORDER BY pctTotal DESC

This SELECT statement returns customer ID, company name, total sales, and a percent of total sales against all customer sales.

Note that the sub-query in the SELECT list is part of a complex expression that includes an aggregate function. Now that's flexibility!

A derived table as sub-query allows you to treat the results of a sub-query as though it were its own table.

Consider the following example:



SELECT ; C.customerid, ; P.product_count AS p_count; FROM Customers C, ; (SELECT c2.customerid, ; COUNT(DISTINCT D.productID) AS p_count ; FROM Customers C2 ; INNER JOIN Orders O ; ON C2.customerid = O.customerid ; INNER JOIN OrderDetails D ; ON O.orderid = D.orderid ; GROUP BY c2.customerid) AS P ; WHERE C.customerID = p.customerID ; AND P.p_count >= ; (SELECT (COUNT(*)*.50) FROM Products) ; ORDER BY p.product_count DESC

This SELECT statement returns customer ID and product count for all customers who have purchased at least fifty percent of the product line.

Notice that the derived table has an alias of "P" that is designated the same way you would alias a column, using the AS clause (required). It's also important to note that the sub-query can be complex (in this case, joining to two other tables) and that the results from the derived table can be used as a condition of the WHERE clause and in the ORDER BY of the top-most SELECT.

Unlike a projection, the derived sub-query can return more than one column and more than one record. It cannot be correlated. All sub-selects are executed before the top-most SELECT is evaluated.

Sub-queries are also supported in the SET list of a SQL UPDATE statement. Only one sub-query is allowed in a SET clause and if there is a sub-query in the SET clause, a sub-query in the WHERE clause is not allowed.

Better Correlation Support
The SQL UPDATE and SQL DELETE commands now support correlation. A correlated statement includes a FROM clause to relate the records being affected to another table.

For example:

DELETE products ; FROM mfg ; WHERE mfg.productID = products.productID; AND mfg.discontinued = .t.

This DELETE statement deletes all products marked as discontinued from a table provided by the manufacturer.

A correlated UPDATE looks similar:

UPDATE products ; SET unitprice = mfg.msrp *.90 ; FROM mfg ; WHERE mfg.productID = products.productID

This UPDATE statement sets the unit price for a product at 90% of the manufacturer's suggested retail price.

A derived table, such as a sub-query, allows you to treat the results of a sub-query as though it were its own table.
You may be tempted to use a correlated sub-query as this is also supported. Just be aware that when using a sub-query it's like doing an outer join. For every record that is not found in the sub-query, the value returned is NULL. This may not give the desired results.

UPDATE products ; SET unitprice = ; (SELECT ( msrp *.90 ) ; FROM mfg ; WHERE mfg.productID = products.productID)

This UPDATE statement sets the unit price for a product at 90% of the manufacturer's suggested retail price for every product found in the Manufacturers table. The price for products not found in the Manufacturers table is set to NULL.

Note that this statement operates on every record in the Products table; in the previous statement, only updated records that matched in the Manufacturers table were involved.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date