CTE (common table expression) -section 4

Note: T-SQL code is included for reference only not to be edited.
Edit starts here:
----------------------------------------------------------
CTE (common table expression):
CTE is like derived table expresions. It's a named table expression that is visible only to the statement that follows it. Like a query against a derived table, CTE involves three main parts:
a) Name the inner query,
b) Name the columns of the inner query (right outside the FROM clause),
c) Name the outer query.

As opposed to derived tables, CTEs are not nested. CTE_1 and CTE_2 etc. are separated by a coma. Now, CTE_2 can reference CTE_1 because it leads VTE_2. And the outer query can reference both CTE_1 and CTE_2.
;WITH <CTE_a>                    -- Single CTE syntax
AS
(
<inner query>
)
<outer query>
;                 -- Semicolon at the end terminates CTE_a operation.                 

;WITH CTE_1 AS (                 -- Multiple CTEs syntax
SELECT ...
FROM Table1),     -- Coma here allows a second CTE start next.
CTE_2 AS (
SELECT ...
FROM CTE_1)
<outer query with references to CTE_1 and/or CTE_2>
;                 -- Semicolon at the end terminates CTEs operation. 

Open in new window


Recall the example 3.1 (from inline single derived table section) above where it was returning Products with two maximum UnitPrice in each category? Here we essentially producing the same result but using with a CTE. For T-SQL to build temp table #t see http://www.experts-exchange.com/questions/28688390/How-to-query-using-derived-table-and-multi-CTEs.html

Product  ProdType                                    -- Inline single derived table subquery, example 4.1
A	      x
B	      x
B	      xx
C	      x
D	      x
D	      xx
E	      x 
-- Problem statement: Starting with above data, produce the following (ProdType =x):
   (we can do this easily with a simple WHERE clause but the following is to demo CTE use) 
ProdType  ProdType
B	      xx
D	      xx

;With d
as
(
Select row_number()  over(partition by Product order by ProdType) As rn
, Product
, ProdType
From #t)
Select d.Product, d.ProdType
From d
Where d.rn =2;

Open in new window


To have multiple CTEs, end the first with a coma and start the next CET. There is no need to repeat another WITH keyword because the first one will be is not terminated yet. For T-SQL to build temp table #t see http://www.experts-exchange.com/questions/28688390/How-to-query-using-derived-table-and-multi-CTEs.html

Product  ProdType                                    -- Inline single derived table subquery, example 4.2
A	      x
B	      x
B	      xx
C	      x
D	      x
D	      xx
E	      x 
-- Problem statement: Starting with above data, produce the following (ignore single Prod. Types):
ProdType  ProdType
B	      x
B	      xx
D	      x
D	      xx

;With d
as
(
Select row_number()  over(partition by Product order by ProdType) As rn
, Product
From #t),
e 
as
(
Select #t.Product, ProdType, d.rn 
From #t inner join d on d.Product = #t.Product
Where d.rn >=2)
Select e.Product, e.ProdType
From e;

Open in new window


Recursive CTE: “A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained.
A query is referred to as a recursive query when it references a recursive CTE. Returning hierarchical data is a common use of recursive queries, for example: Displaying employees in an organizational chart, or data in a bill of materials scenario in which a parent product has one or more components and those components may, in turn, have subcomponents or may be components of other parents.
A recursive CTE can greatly simplify the code required to run a recursive query within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.” This description is from https://technet.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx
 
In earlier versions of prior to SQL Server 2005, a recursive query use of temp tables, cursors, and additional coding was required. Here, in the example below an anchor subquery (starting subquery which fires only once) is followed by CTE body subquery with a UNION ALL operator before it. It is possible to have multiple UNION ALL + body subquery combination following the first one all separated by a coma. At least one of the body subquery is required to reference the CTE name defined in the anchor query.
 
In this example, employee 7 is returned by the anchor query before executing the body subquery repeatedly joining previous rows.
-- Problem statement: Write a query to return the management chain from EmploeeID = 7. 

;WITH BossCTE                    -- Recursive CTE, example 4.3
AS             
(
SELECT EmployeeID
     , ReportsTo
     , firstname
     , lastname
     , 0 AS distance
FROM dbo.Employees 
WHERE EmployeeID=7
UNION ALL
SELECT M.EmployeeID
     , M.ReportsTo
     , M.firstname
     , M.lastname
     , S.distance + 1 AS distance
FROM BossCTE AS S JOIN dbo.Employees AS M
ON S.ReportsTo = M.EmployeeID
) 
SELECT EmployeeID
     , ReportsTo
     , firstname
     , lastname
     , distance
FROM BossCTE
Order By distance DESC;

Open in new window


CTEs and derived tables are created and used in code, visible only in the scope of the statement that defines them. After that statement terminates, the table expression is gone thus are not, as you learned in the previous sections, derived tables and CTEs are table expressions that are visible only in the scope of the statement that defines them. Hence, derived tables and CTEs are not reusable.
What we have discussed so far is for use in the code being executed after which when it runs out of scope, it is of no use. In the following section we will see how they could be saved as database object for reusability.

Database-object (Views and Inline Table-Valued Functions): For reusability, you need to store the definition of the table expression as an object in the database, and for this you can use either views or inline table-valued functions. Because these are objects in the database, you can control access by using permissions. The main difference between views and inline table-valued functions is that the former doesn't accept input parameters and the latter does. As an example, suppose you need to persist the definition of the query with the row number computation from the examples in the previous sections. To achieve this, you create the following view.

Edit ends here:
----------------------------------------------------------
also see: http://www.experts-exchange.com/articles/18366/Subqueries-at-a-glance.html

Note, Questions on the remaining sections will be added later.
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark BullockQA EngineerCommented:
Change expresions to expressions.
Change coma to comma.
Change "versions of prior" to "versions prior"

I didn't understand this: After that statement terminates, the table expression is gone thus are not

Is there a code sample missing after this? To achieve this, you create the following view.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Hi Mark,

I change:

Change expresions to expressions.
Change coma to comma. everywhere
Change "versions of prior" to "versions prior"

I didn't understand this: After that statement terminates, the ...

to:

Non-database-objects are created and used in code, visible only in the scope of the statement that defines them. When out of scope, the table expression is gone and is not usable. See the first example under sectin 3 below.

Were these changes acceptable?

I supposed,this changes will show up after the article gets republished.

Thank you very much,

Mike
Mark BullockQA EngineerCommented:
Looks good, except change sectin 3 to section 3
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thanks. Got to be careful.

Possibly more error in "Got to be careful."

Mike
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Proofreading

From novice to tech pro — start learning today.