CTE – part 2

Complex Scenarios

So what is the big deal about CTE, the deal is when you need some complex queries or operations, trust me nothing goes as good as CTE. Let’s have a look at one of the most commonly encountered complex issues… Duplicates. We have a sample table (EMP) for the example.

Select * from EMP

EID         ENAME                DEPT
----------- -------------------- ----------
1           Sara                 IT
2           Rick                 HR
3           Ted                  IT
4           Sheldon              Accounts
5           Sara                 IT

(5 row(s) affected)

For removing the duplicate employee, i.e. ‘Sara’ from the table, we create a CTE:

;WITH EliminateDup(Eid,Name,Dept,RowID) AS
(
SELECT Eid,Ename,Dept, ROW_NUMBER()OVER(PARTITION BY Ename,Dept ORDER BY EID)AS RowID
FROM EMP
)
DELETE FROM EliminateDup WHERE RID>1
The query below creates a temporary result set as :
SELECT Eid,Ename,Dept, ROW_NUMBER()OVER(PARTITION BY Ename,Dept ORDER BY EID)AS RowID
FROM EMP

Eid         Ename                Dept       RowID
----------- -------------------- ---------- --------------------
2           Rick                 HR         1
1           Sara                 IT         1
5           Sara                 IT         2
4           Sheldon              Accounts   1
3           Ted                  IT         1

(5 row(s) affected)

And later, we remove the duplicate with the DELETE. Quite simple, isn’t it.

Recursion

The next and the most important feature is recursion.

With the UNION ALL, we can make the CTE recursive to formulate a final result. There is an anchor member and a recursive member which may or may not have a terminating condition. Let’s see with an example..

Suppose we have a comma separated string and we wish to extract each word from the string..

Let’s consider the string to be ‘Where,there,is,a,will,there,is,a,way’.

DECLARE @T VARCHAR(100)='Where,there,is,a,will,there,is,a,way'
SET @T =@T+','
;WITH MyCTE(Start,[End]) AS(

SELECT 1 AS Start,CHARINDEX(',',@T,1) AS [End]
UNION ALL
SELECT [End]+1 AS Start,CHARINDEX(',',@T,[End]+1)AS [End] from MyCTE where [End]<LEN(@T)
)
Select SUBSTRING(@T,Start,[End]-Start)from MyCTE;

Let’s understand what we have done here… We have an anchor in the form of SELECT 1,CHARINDEX(',',@T,1).

For the first pass, the anchor returns the values 1,6 (this value being the CHARINDEX of first comma after the word ‘Where,’) for columns Start & [End].

Next the recursive code returns [End]+1=7 as Start and 12 for CHARINDEX(',',@T,[End]+1, i.e. 7) AS [End], this code recurses unless the terminating condition is met which is [End]<LEN(@T)i.e. 37.

The UNION ALL operator unites all the start & [End], for clarity let’s take another look at the values.

DECLARE @T VARCHAR(100)='Where,there,is,a,will,there,is,a,way'
SET @T =@T+','
;WITH MyCTE(Start,[End]) AS(

SELECT 1 AS Start,CHARINDEX(',',@T,1) AS [End]
UNION ALL
SELECT [End]+1 AS Start,CHARINDEX(',',@T,[End]+1)AS [End] from MyCTE where [End]<LEN(@T)
)
Select Start,[End],SUBSTRING(@T,Start,[End]-Start)AS String from MyCTE;

Hope that makes things clearer. With CTE, we can achieve the same feats of programmability as C# or C++ with respect to generating Fibonacci series, a specific string patterns, etc. The recursion specifically finds an important use while you need a hierarchy to be reported, we will see that in a while. Currently, let’s look into the recursion option.

What if we want the first two values only out of the string?

DECLARE @T VARCHAR(100)='Where,there,is,a,will,there,is,a,way'
SET @T =@T+','
;WITH MyCTE(Start,[End]) AS(

SELECT 1 AS Start,CHARINDEX(',',@T,1) AS [End]
UNION ALL
SELECT [End]+1 AS Start,CHARINDEX(',',@T,[End]+1)AS [End] from MyCTE where [End]<LEN(@T)
)
Select Start,[End],SUBSTRING(@T,Start,[End]-Start)AS String from MyCTE
OPTION (MAXRECURSION 1);

The OPTION MAXRECURSION enables the code to recurse only once and terminates as soon as that happens.The self explanatory message flashes and the values returned out on the results pane is:

  1. initial anchor value
  2. first recursed value

MAXRECURSION value can be between 0 and 32,767. 32,767 is fine but would what a 0 return? 0 enables an infinite recursion hence if the recursive statement does not have a terminating condition, the program loops infinitely. For first hand experience, try the below code?

;WITH MyCTE(Val) AS(

SELECT 1 AS Val
UNION ALL
SELECT Val=(Val+1) FROM MyCTE
)
Select Val from MyCTE
OPTION (MAXRECURSION 0);

Fetching Hierarchy

Before we call it a day, let’s look at the final example of fetching the complete hierarchy of a particular organization. In such scenarios, CTE could outperform any complex code both in terms of simplicity and LOC (lines of code) required to derive the result.

We have a table Org as below:

Select * from Org

For fetching the bottom up hierarchy, we pass the eid and get the complete hierarchy for the concerned employee. For example, for Andy’s organizational hierarchy, we pass @T = 6 (His Eid).

DECLARE @T INT = 6
;WITH OrgCTE(Eid,Employee,SupervisorID,ReportsTo)AS
(
SELECT @T,O.Name,O2.EID,O2.Name FROM Org O
INNER JOIN Org O2 ON O.SupervisorID=O2.EID
AND O.EID=@T

UNION ALL

SELECT OC.SupervisorID,OC.ReportsTo,O2.EID,O2.Name
FROM OrgCTE OC
INNER JOIN
Org O
ON OC.SupervisorID=O.EID
INNER JOIN
Org O2
ON O.SupervisorID=O2.EID
)
SELECT * FROM OrgCTE

So we have been able to get the hierarchy for Andy.

Similarly for the top down hierarchy, we can implement the below CTE which gives the level indicating the top down org chart.

;WITH OrgCTE(Eid,SupervisorID,Employee,[Role],[Level])AS
(
SELECT EID,SupervisorID,Name,[Role],0 FROM
Org WHERE SupervisorID=0

UNION ALL

SELECT O.EID,O.SupervisorID,O.Name,O.[Role],[Level]+1
FROM Org O
INNER JOIN OrgCTE OC
ON O.SupervisorID=OC.Eid
)
SELECT * FROM OrgCTE

SQL programmers find CTE of immense use and thanks to this feature, complexities in programming life have been considerably simplified. I hope I have been able to justify CTE reasonably well in this article.

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