how to generate and print barcode in c# windows application Lesson 1: Building Recursive Queries with CTEs in C#

Draw Denso QR Bar Code in C# Lesson 1: Building Recursive Queries with CTEs

SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 FROM HumanResources.Employee e INNER JOIN EMP_cte ON e.OrganizationNode = EMP_cte.OrganizationNode.GetAncestor(1) INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID)

SELECT EMP_cte.RecursionLevel, EMP_cte.BusinessEntityID, EMP_cte.FirstName, EMP_cte.LastName, EMP_cte.OrganizationNode.ToString() AS OrganizationNode, p.FirstName AS 'ManagerFirstName', p.LastName AS 'ManagerLastName' FROM EMP_cte INNER JOIN HumanResources.Employee e ON EMP_cte.OrganizationNode.GetAncestor(1) = e.OrganizationNode INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID ORDER BY RecursionLevel, EMP_cte.OrganizationNode.ToString() OPTION (MAXRECURSION 25);

The first query within the WITH clause defines the anchor result set. The second query is executed recursively up to the maximum recursion level against the anchor query. The recursion is accomplished by the inner join on the CTE, as follows:

The outer query is then used to return the results of the recursive operation along with any additional data that is needed. The OPTION clause in the outer query specifies the maximum number of recursion levels that are allowed.

If the iterative query does not reach the bottom of the hierarchy by the time the MAXRECURSION value has been exhausted, you receive an error message.

Because the WITH keyword is used in multiple ways within T-SQL, any statements preceding the WITH keyword are required to be terminated with a semicolon.

1. A CTE has a WITH clause that contains a SELECT statement, which defines a table,

with a UNION ALL statement and a second query, which recurses across the anchor query; and an outer query, which references the CTE and specifies the maximum recursion levels.

In this practice, you use a recursive CTE to expand the bill of materials for a component within the AdventureWorks database.

In this exercise, you use a recursive CTE to expand the bill of materials for a component within the AdventureWorks database.

Open a new query window, type and execute the following query:

DECLARE @date @productassembly date = '4/18/2000', int = 749;

WITH BOM(ProductAssemblyID, ComponentID, AssemblyDescription, PerAssemblyQty, ComponentCost, ListPrice, BOMLevel, RecursionLevel) AS (SELECT b.ProductAssemblyID, b.ComponentID, p.Name, b.PerAssemblyQty, p.StandardCost, p.ListPrice, b.BOMLevel, 0 FROM Production.BillOfMaterials b INNER JOIN Production.Product p ON b.ComponentID = p.ProductID WHERE b.ProductAssemblyID = @productassembly AND @date >= b.StartDate AND @date <= ISNULL(b.EndDate, @date) UNION ALL SELECT b.ProductAssemblyID, b.ComponentID, p.Name, b.PerAssemblyQty, p.StandardCost, p.ListPrice, b.BOMLevel, RecursionLevel + 1 FROM BOM cte INNER JOIN Production.BillOfMaterials b ON b.ProductAssemblyID = cte.ComponentID INNER JOIN Production.Product p ON b.ComponentID = p.ProductID WHERE @date >= b.StartDate AND @date <= ISNULL(b.EndDate, @date))

SELECT b.ProductAssemblyID, b.ComponentID, b.AssemblyDescription, SUM(b.PerAssemblyQty) AS ComponentQty , b.ComponentCost, b.ListPrice, b.BOMLevel, b.RecursionLevel FROM BOM b GROUP BY b.ComponentID, b.AssemblyDescription, b.ProductAssemblyID, b.BOMLevel, b.RecursionLevel, b.ComponentCost, b.ListPrice ORDER BY b.BOMLevel, b.ProductAssemblyID, b.ComponentID OPTION (MAXRECURSION 25) GO

2. 3.

Rerun the query with the date changed to 10/16/2000 and observe the results. Inspect the contents of the Production.BillOfMaterials table and observe the changes to components over time to see why the two queries return such different results.

A recursive CTE contains two SELECT statements within the WITH clause, separated by the UNION ALL keyword. The first query defines the anchor for the recursion, and the second query defines the data set that is to be iterated across. If a CTE is contained within a batch, all statements preceding the WITH clause must be terminated with a semicolon. The outer query references the CTE and specifies the maximum recursion.

Subqueries allow you to nest one query within another to build complex routines, as well as retrieve data sets that would be impossible to construct without resorting to a multistep process that writes intermediate results out to temporary objects. You can construct queries with two types of subqueries: correlated and noncorrelated. A noncorrelated subquery is independent of the outer query within which it is contained. A correlated subquery depends upon and references columns from the outer query. Either type of subquery can return a scalar or multiple values. Scalar-valued subqueries can be placed anywhere within a SELECT statement where one or zero values are expected. A multivalued subquery can be used anywhere a set of values is expected. In this lesson, you learn how to implement correlated and noncorrelated subqueries.

After this lesson, you will be able to: