Common Table Expressions

Someone I work with turned me on to SQL Server 2005 Common Table Expressions (CTEs) a few months ago and I'm really starting to get into them.

CTEs are ways to create a derived set of data; sort of like a view but it doesn't persist in the database after being used. Here's a simple example:

WITH ProductSales (ProductID, ProductName, TotalSales)
(
SELECT product.ProductID,product.ProductName,
TotalSales = (SELECT SUM(Quantity)
FROM salesdetail
WHERE salesdetail.ProductID = product.ProductID)

FROM product
)

You then use SELECT * FROM ProductSales to get the results. Actually, you have to immediately query the results before doing any other non-CTE SQL.

I wonder if this is something we should have thought of for Visual FoxPro 9. Probably would have if Gene Goldhammer hadn't retired since he tended to think about new data features.