CTE for beginner – part 1

CTE as a Derived Table

We have a simple table Products in our database.

Select * from PRODUCTS
ProductID ProductDesc ManufacturingDate       ExpiryDate              IsSalable Price
---------------------------------------------------------------------------------------
1	  Biscuits    2011-09-01 00:00:00.000  2012-09-01 00:00:00.000 1	20.00
2	  Butter      2010-09-01 00:00:00.000  2011-09-01 00:00:00.000 1	30.00
3	  Milk	     2011-10-01 00:00:00.000 2011-11-01 00:00:00.000  1	46.00

We have created a simple ProductsCTE for displaying all the Products with Price greater than 20.00. Here CTE performs the job of acting as a simple derived table.

;WITH ProductsCTE(ProdName,Price) AS
( SELECT ProductDesc,Price
  FROM PRODUCTS
  WHERE Price>20.00
)
SELECT * FROM ProductsCTE
ProdName                                           Price
-------------------------------------------------- ---------------------
Butter                                             30.00
Milk                                               46.00

(2 row(s) affected)

Important point that needs a mention is the SELECT followed by the CTE definition, any operation SELECT,INSERT, UPDATE, DELETE or Merge can be performed immediately after the CTE and the CTE lasts only for a single such operation. When I say that, what do I mean…

It means that the below code is erroneous:

DECLARE @T INT,@I INT
SET @T = 10
SET @I = 20
;WITH ProductsCTE(ProdName,Price) AS
( SELECT ProductDesc,Price
  FROM PRODUCTS
  WHERE Price>20.00
)
SELECT @T+@I
SELECT * FROM ProductsCTE

On executing the code, it results in the below error. Which means I need to execute the ProductsCTE SELECTimmediately after the CTE definition completes.

Msg 422, Level 16, State 4, Line 10
Common table expression defined but not used.

To rectify the same… I would reinstate the order as:

DECLARE @T INT,@I INT
SET @T = 10
SET @I = 20
;WITH ProductsCTE(ProdName,Price) AS
( SELECT ProductDesc,Price
  FROM PRODUCTS
  WHERE Price>20.00
)
SELECT * FROM ProductsCTE
SELECT @T+@I
ProdName                                           Price
-------------------------------------------------- ---------------------
Butter                                             30.00
Milk                                               46.00

(2 row(s) affected)

-----------
30

(1 row(s) affected)

Let’s perform an UPDATE:

;WITH ProductsCTE(ProdName,Price) AS
( SELECT ProductDesc,Price
  FROM PRODUCTS
  WHERE Price>20.00
)
UPDATE ProductsCTE SET Price=50 WHERE ProdName='Milk'
SELECT * FROM ProductsCTE
-------------------

(1 row(s) affected)
Msg 208, Level 16, State 1, Line 7
Invalid object name 'ProductsCTE'.

The price for Milk gets duly updated to 50 but the next set of select doesn’t work. Always remember, you can hit for a result set once and hit immediately after the CTE definition. Like:

;WITH ProductsCTE(ProdName,Price) AS
( SELECT ProductDesc,Price
  FROM PRODUCTS
  WHERE Price>20.00
)
SELECT * FROM ProductsCTE
UNION
SELECT 'Bread' AS ProdName,MIN(Price) AS PRICE from ProductsCTE

ProdName                                           Price
-------------------------------------------------- ---------------------
Bread                                              30.00
Butter                                             30.00
Milk                                               50.00

(3 row(s) affected)
Calling Multiple CTEs

We can have multiple CTEs calls from one single query. Let’s have a look at the example. We have 2 tables:

Select * from Student

Select * from Teacher

Let’s implement a CTE to get all the respective class teachers for the students.

;WITH StudCTE(RollNo,StudentName,TeacherID)
AS
(
SELECT ID,Name,TID FROM Student
)
,TeacherCTE(TID,TeacherName)
AS
(
SELECT ID,Name FROM Teacher
)
SELECT RollNo,StudentName,TeacherName
FROM StudCTE SC
INNER JOIN
TeacherCTE TC
ON SC.TeacherID=TC.TID

We have called 2 CTEs from a single SELECT and based upon the inner join returned the student-teacher information. That was a simple example to show how multiple CTEs are done.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s