CTE's Recursivas

Hola alex,

On 9 okt, 03:46, "alex" wrote:

Hola , tengo una duda con respecto a las CTE's recursivas. El ejemplo que
sale en books online al ejecutarlo los resultados comienzan ordenados por la
columna LEVEL de menor a mayor 0 1 2 3 4 pero en cierto punto comienza a
descender como 0 1 2 3 4 3 3 2, o sea no estan ordenados de menor a mayor
todo el tiempo que es como se supone que trabaja haciendo UNION ALL a todos
los resultados parciales. Alguien me puede ayudar a comprender esto?
El ejemplo de books online (el ultimo WHERE lo elimine para que salgan todos
los resultados:

USE AdventureWorks;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
0 AS Level
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
Level + 1
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports

El "como se supone que trabaja" para entender o explicar QUE
significa una operación y el "como lo hace realmente" son cosas
distintas. Lo importante es que el resultado sea el mismo para
como nosotros lo explicamos y para como se ejecuta realmente.
Todas las posibles ordenaciones del resultado son el mismo
resultado. O sea que el "QUE hace" es lo mismo aunque el "COMO"
pueda ser distinto.

En todo caso el "como lo hace" tiene que ver con la ejecución
recursiva del CTE.
Mira este ejemplo. Fíjate en el patrón que sigue la enumeración
en la columa 'nombre' y el orden en se presentan los ParentID con
respecto a los IDŽs que ya estaban en el resultado.
Después nos cuentas que patrón descubres y que deduces de eso en
cuanto a "como" lo hace.


declare @T table(ID int, ParentID int, Nombre varchar(20))
;
insert into @T (ID, ParentID, Nombre)
select 1, null, 'Director 1'
union select 2, null, 'Director 2'
union select 3, 1, 'Jefe 1/1'
union select 4, 1, 'Jefe 1/2'
union select 5, 2, 'Jefe 2/1'
union select 6, 2, 'Jefe 2/2'
union select 7, 3, 'Pepito 1/1/1'
union select 8, 3, 'Pepito 1/1/2'
union select 9, 4, 'Pepito 1/2/1'
union select 10, 4, 'Pepito 1/2/2'
union select 11, 5, 'Pepito 2/1/1'
union select 12, 5, 'Pepito 2/1/2'
union select 13, 6, 'Pepito 2/2/1'
union select 14, 6, 'Pepito 2/2/2'
;
with A as
(
select ParentID, ID, Nombre, 1 as Nivel
from @T
where ParentID is null
union all
select T.ParentID, T.ID, T.Nombre, A.Nivel + 1
from @T T join A on T.ParentID = A.ID
)
select * from A
;


Saludos,
Carlos

Alex,

La unica forma de garantizar un resultado ordenado es usando la clausula
"order by" en la sentencia "select" que produce el resultado.

SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports

Si no estas usando esta clausula, como esperas que ocurra el ordenamiento de
el resultado?

SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
ORDER BY Level;

La sentencia anterior, aunque sea posible que no devuelva lo que tu esperas,
si ordena por la columna [Level]. Para las filas de un mismo nivel, el orden
no ha sido especificado, asi que no podemos esperar un orden especifico para
estas filas dentro del nivel donde se encuentran, al menos que usemos otra
columna en la clusula "order by".

SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
ORDER BY Level, EmployeeID;


AMB


"alex" wrote:

Hola , tengo una duda con respecto a las CTE's recursivas. El ejemplo que
sale en books online al ejecutarlo los resultados comienzan ordenados por la
columna LEVEL de menor a mayor 0 1 2 3 4 pero en cierto punto comienza a
descender como 0 1 2 3 4 3 3 2, o sea no estan ordenados de menor a mayor
todo el tiempo que es como se supone que trabaja haciendo UNION ALL a todos
los resultados parciales. Alguien me puede ayudar a comprender esto?
El ejemplo de books online (el ultimo WHERE lo elimine para que salgan todos
los resultados:

USE AdventureWorks;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
0 AS Level
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
Level + 1
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports

pero cada vez que se ejecuta la CTE recursiva se le asigna LEVEL + 1 a los
siguientes por lo tanto siempre debe ir ascendiendo, y nunca se hace LEVEL -
1, o sea por eso es que no comprendo como es que comienza ascendiendo y
luego desciende. segun entiendo van a salir ordenados por LEVEL
automaticamente porque siempre es LEVEL + 1

"Alejandro Mesa" wrote in message
news:

Alex,

La unica forma de garantizar un resultado ordenado es usando la clausula
"order by" en la sentencia "select" que produce el resultado.

SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports

Si no estas usando esta clausula, como esperas que ocurra el ordenamiento
de
el resultado?

SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
ORDER BY Level;

La sentencia anterior, aunque sea posible que no devuelva lo que tu
esperas,
si ordena por la columna [Level]. Para las filas de un mismo nivel, el
orden
no ha sido especificado, asi que no podemos esperar un orden especifico
para
estas filas dentro del nivel donde se encuentran, al menos que usemos otra
columna en la clusula "order by".

SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
ORDER BY Level, EmployeeID;


AMB


"alex" wrote:

Hola , tengo una duda con respecto a las CTE's recursivas. El ejemplo que
sale en books online al ejecutarlo los resultados comienzan ordenados por
la
columna LEVEL de menor a mayor 0 1 2 3 4 pero en cierto punto
comienza a
descender como 0 1 2 3 4 3 3 2, o sea no estan ordenados de menor a mayor
todo el tiempo que es como se supone que trabaja haciendo UNION ALL a
todos
los resultados parciales. Alguien me puede ayudar a comprender esto?
El ejemplo de books online (el ultimo WHERE lo elimine para que salgan
todos
los resultados:

USE AdventureWorks;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
0 AS Level
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
Level + 1
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports

hola carlos, gracias por responder y entiendo lo que dices de que lo
principal es lo que hace y no como lo hace. el problema es que el ejemplo de
books online viene con descripcion detallada de como lo hace pero la
explicacion no esta completa porque usan un WHERE dp.GroupName = N'Research
and Development' OR Level = 0; y esto filtra los resultados por GroupName y
entonces sale todo ordenado del LEVEL 0 al LEVEl 4.
Mi gran duda es que el como lo hace al parecer es simple pues el INNER JOIN
recursivo automaticamente me saca TODOS los hijos de todos los PADRES del
resultado anterior, o sea el inner join de la tabla de
HumanResources.Employee con la CTE me va a devolver TODOS los empleados cuyo
manager esta en la CTE y luego recursivamente hace lo mismo con los nuevos
resultados.
Entonces como es posible que por ejemplo todos los LEVEL 3 no salgan juntos
pues obligatoriamente tienen que tener un manager en LEVEL 2, o sea deberian
salir todos los level 3 juntos y todos los level 4 juntos y asi
sucesivamente.

gracias,
Alejandro.


"Carlos M. Calvelo" wrote in message
news:
Hola alex,

On 9 okt, 03:46, "alex" wrote:

Hola , tengo una duda con respecto a las CTE's recursivas. El ejemplo que
sale en books online al ejecutarlo los resultados comienzan ordenados por
la
columna LEVEL de menor a mayor 0 1 2 3 4 pero en cierto punto comienza
a
descender como 0 1 2 3 4 3 3 2, o sea no estan ordenados de menor a mayor
todo el tiempo que es como se supone que trabaja haciendo UNION ALL a
todos
los resultados parciales. Alguien me puede ayudar a comprender esto?
El ejemplo de books online (el ultimo WHERE lo elimine para que salgan
todos
los resultados:

USE AdventureWorks;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
0 AS Level
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
Level + 1
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports

El "como se supone que trabaja" para entender o explicar QUE
significa una operación y el "como lo hace realmente" son cosas
distintas. Lo importante es que el resultado sea el mismo para
como nosotros lo explicamos y para como se ejecuta realmente.
Todas las posibles ordenaciones del resultado son el mismo
resultado. O sea que el "QUE hace" es lo mismo aunque el "COMO"
pueda ser distinto.

En todo caso el "como lo hace" tiene que ver con la ejecución
recursiva del CTE.
Mira este ejemplo. Fíjate en el patrón que sigue la enumeración
en la columa 'nombre' y el orden en se presentan los ParentID con
respecto a los IDŽs que ya estaban en el resultado.
Después nos cuentas que patrón descubres y que deduces de eso en
cuanto a "como" lo hace.


declare @T table(ID int, ParentID int, Nombre varchar(20))
;
insert into @T (ID, ParentID, Nombre)
select 1, null, 'Director 1'
union select 2, null, 'Director 2'
union select 3, 1, 'Jefe 1/1'
union select 4, 1, 'Jefe 1/2'
union select 5, 2, 'Jefe 2/1'
union select 6, 2, 'Jefe 2/2'
union select 7, 3, 'Pepito 1/1/1'
union select 8, 3, 'Pepito 1/1/2'
union select 9, 4, 'Pepito 1/2/1'
union select 10, 4, 'Pepito 1/2/2'
union select 11, 5, 'Pepito 2/1/1'
union select 12, 5, 'Pepito 2/1/2'
union select 13, 6, 'Pepito 2/2/1'
union select 14, 6, 'Pepito 2/2/2'
;
with A as
(
select ParentID, ID, Nombre, 1 as Nivel
from @T
where ParentID is null
union all
select T.ParentID, T.ID, T.Nombre, A.Nivel + 1
from @T T join A on T.ParentID = A.ID
)
select * from A
;


Saludos,
Carlos

Hola Alejandro,

On Fri, 9 Oct 2009 15:57:14 -0400, alex wrote:

hola carlos, gracias por responder y entiendo lo que dices de que lo
principal es lo que hace y no como lo hace. el problema es que el ejemplo de
books online viene con descripcion detallada de como lo hace pero la
explicacion no esta completa porque usan un WHERE dp.GroupName = N'Research
and Development' OR Level = 0; y esto filtra los resultados por GroupName y
entonces sale todo ordenado del LEVEL 0 al LEVEl 4.
Mi gran duda es que el como lo hace al parecer es simple pues el INNER JOIN
recursivo automaticamente me saca TODOS los hijos de todos los PADRES del
resultado anterior, o sea el inner join de la tabla de
HumanResources.Employee con la CTE me va a devolver TODOS los empleados cuyo
manager esta en la CTE y luego recursivamente hace lo mismo con los nuevos
resultados.
Entonces como es posible que por ejemplo todos los LEVEL 3 no salgan juntos
pues obligatoriamente tienen que tener un manager en LEVEL 2, o sea deberian
salir todos los level 3 juntos y todos los level 4 juntos y asi
sucesivamente.

Si miras el ejemplo que te he puesto, en la tercer y cuarta línea del
resultado saca los descendientes directos del último director, no de todos
los directores. En las linaas 5 y 6 están los descendientes directos del
último jefe del último director. Las líneas 7 y 8 son los descendientes
directos del penúltimo jefe del último director.
Las líneas 8 y 9 son los descendientes directos del penúltimo director
(aquí vuelve a reaparecer el nivel 2!), etc, etc.

Pero tu asumes que "el INNER JOIN recursivo automaticamente me saca TODOS
los hijos de todos los PADRES del resultado anterior", y eso no es verdad.
Por eso te dije que analizaras bien el ejemplo que te puse, que es
mas sencillo que el que estas estudiando ahora.

Saludos,
Carlos