Posts
CTE main elements:
Name of CTE, It comes after WITH keyword.
List of columns, it's optional.
Query that defines the CTE temporary result set,
The quesry sits after AS keyword and inside open and close paranteses.
It's not possible to use COMPUTE, COMPUTE BY, ORDER BY (unless TOP
is used) in a CTE.
For example, in AdventureWorksLT database sample (is available
in CodePlex web pages, you can download it from following here. Product table keeps information about products and category of the products are available in ProductCategory table. The following example provides a temporary result set in the name of CheapProducts which is derived from Product table and those products' price is less than $100 (Ooops, I know, $100 is not cheap thing, just take it easy). Then query that comes after, request for BLACK products with their names and their category.
WITH CheapProducts (ProductName, ProductColor, ProductPrice, ProductCategoryID)
AS
(
SELECT [name], [color], [listprice], [ProductCategoryID]
FROM [AdventureWorksLT].[SalesLT].[Product]
WHERE [listprice] < 100
)
SELECT chpPrd.ProductName, chpPrd.ProductPrice, prdCtg.Name as ProductCategory
FROM CheapProducts as chpPrd
JOIN [AdventureWorksLT].[SalesLT].[ProductCategory] as prdCtg
ON chpPrd.ProductCategoryID = prdCtg.ProductCategoryID
WHERE chpPrd.ProductColor = 'BLACK'
This one was a simple sample, but when we have a little more
complex queries CTE can really be a good hand to make it simple.
Next example lists the QUANTITY of PRODUCTS (and the products' CATEGORY) which are RED and their price is less than $100, ordered by companies:
WITH PrdAndCat (PrdID, PrdName, PrdColor, PrdPrice, PrdCategory)
AS
(
SELECT prd.[ProductID], prd.[name], prd.[color], prd.[listprice], prdCtg.[Name]
FROM [AdventureWorksLT].[SalesLT].[Product] as prd
JOIN [AdventureWorksLT].[SalesLT].[ProductCategory] as prdCtg
ON prd.ProductCategoryID = prdCtg.ProductCategoryID
)
SELECT cstmr.CompanyName, ordrHdr.OrderDate, ordrDtl.OrderQty,
pNc.PrdName, pNc.PrdCategory
FROM [AdventureWorksLT].[SalesLT].[SalesOrderHeader] as ordrHdr
JOIN [AdventureWorksLT].[SalesLT].[SalesOrderDetail] as ordrDtl
ON ordrHdr.SalesOrderID = ordrDtl.SalesOrderID
JOIN [AdventureWorksLT].[SalesLT].[Customer] as cstmr
ON ordrHdr.CustomerID = cstmr.CustomerID
JOIN PrdAndCat as pNc
ON pNc.[PrdID] = ordrDtl.[ProductID]
WHERE pNc.PrdColor = 'RED' AND pNc.PrdPrice
Bulk Discount Store 2004-06-01 3 Sport-100 Helmet, Red Helmets
Metropolitan Bicycle Supply 2004-06-01 1 Sport-100 Helmet, Red Helmets
Many Bikes Store 2004-06-01 2 Sport-100 Helmet, Red Helmets
Riding Cycles 2004-06-01 6 Sport-100 Helmet, Red Helmets
Action Bicycle Specialists 2004-06-01 10 Sport-100 Helmet, Red Helmets
Eastside Department Store 2004-06-01 10 Sport-100 Helmet, Red Helmets
Professional Sales and Service 2004-06-01 3 Sport-100 Helmet, Red Helmets
