Capitulo 4 – Lección 1: Usando técnicas de consulta adicionales

dentro de las aplicaciones que usted necesita para construir las consultas que van más allá de una declaración básica de SELECT, por ejemplo, crear totales acumulados, buscar vacios en las secuencias, atravesar una jerarquía recursiva,o datos de clasificación dentro de un grupo. En este capítulo, usted aprenderá cómo extender las técnicas de consulta que usted ha aprendido en este libro para incluir subconsultas, expresiones de tabla común (CTE), y las funciones de clasificación.

Construyendo consultas recursivas con expresiones de tabla común (CTE)
Expresiones de tabla comunes
Una CTE se define con dos partes:

• Una cláusula WITH que contiene una instrucción SELECT que genera una tabla válida
• Una declaración SELECT externa que hace referencia a la expresión de tabla

La siguiente CTE devuelve el número de empleados que tienen un cargo otorgado:

WITH EmpTitle AS
(SELECT JobTitle, count(*) numtitles
FROM HumanResources.Employee
GROUP BY JobTitle)
SELECT b.BusinessEntityID, b.JobTitle, a.numtitles
FROM EmpTitle a INNER JOIN HumanResources.Employee b ON a.JobTitle = b.JobTitle;
GO

Una CTE recursiva se amplía la definición de la expresión de tabla y consta de dos partes:

• Una consulta de anclaje, que es la fuente de la repetición, junto con una declaración UNION ALL  y una segunda consulta, que recursivamente llama a la consulta de anclaje.
• Una consulta externa, que hace referencia a la rutina y especifica el número de niveles de recursividad

Por ejemplo, la siguiente consulta le permite especificar un empleado y luego volver cada nivel de gestión por encima de los empleados de la organización a través de un máximo de 25 niveles de la organización:

DECLARE @EmployeeToGetOrgFor INT = 126;
 WITH EMP_cte(BusinessEntityID, OrganizationNode, FirstName, LastName,
      JobTitle, RecursionLevel)
 AS (SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName,
      p.LastName, e.JobTitle, 0
 FROM HumanResources.Employee e INNER JOIN Person.Person as p
      ON p.BusinessEntityID = e.BusinessEntityID
      WHERE e.BusinessEntityID = @EmployeeToGetOrgFor
UNION ALL
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);

La primera consulta en la cláusula WITH define el conjunto de resultados de anclaje.La segunda consulta se ejecuta de forma recursiva hasta el nivel máximo de recursividad en contra de la consulta de anclaje. La repetición se lleva a cabo por la combinación interna en el CTE, de la siguiente manera:

INNER JOIN EMP_cte

La consulta externa se utiliza para devolver los resultados de la operación recursiva, junto con cualquier información adicional que se necesita. La cláusula OPTION en la consulta externa especifica el número máximo de niveles de recursividad que se permiten.

Importante:

• Si la consulta iterativa no llega a la parte inferior de la jerarquía en el momento en que el  Valor MAXRECURSION se haya agotado, recibirá un mensaje de error.
• Debido a que la palabra clave WITH se utiliza en múltiples formas dentro de T-SQL,las declaraciones anteriores a la palabra clave WITH son necesarios para ser terminada con un punto y coma.

Resumen de la lección:

• Una CTE recursiva contiene dos instrucciones SELECT en la cláusula WITH,separados por la palabra clave UNION ALL. En la primera consulta se define el ancla de la repetición, y en la segunda consulta se define el conjunto de datos a través del cual se itera.
• Si el CTE se encuentra dentro de un lote, todas las declaraciones anteriores a la cláusula debe terminar con punto y coma.
• La consulta hace referencia exterior de la CTE y se especifica el máximo de recursividad.