Query Recursivo
renemoreno
tiene mareado.
Tengo dos campos con numero de codigo [DOCTO] y [DOCTOCOMP], un codigo
en DOCTO tiene un DOCTOCOMP, este codigo de DOCTOCOMP taambien existe
en el campo DOCTO y tambien tiene un DOCTOCOMP, esto se repite hasta
que DOCTO y DOCTOCOMP son iguales, ahi termina la secuencia. Por
ejemplo:
DOCTO - DOCTOCOMP
11111 - 22222
22222 - 33333
33333 - 44444
44444 - 44444
Estoy tratando de obtener el DOCTO incial y el DOCTOCOMP final, para
el ejemplo anterior seria 11111 - 44444.
Espero haber sido claro y de antemano agradezco cualquier ayuda que me
puedan dar.
Saludos
Rene Moreno
Leonardo Azpurua
"renemoreno" <jrenem...@gmail.com> escribió en el mensaje
news:9fc76a8f-51eb-405f...@t42g2000hsg.googlegroups.com...
Hola, Rene:
Seguramente habrá una manera mejor de hacerlo, pero esto funciona:
CREATE FUNCTION findDoctocomp(@p AS INT) RETURNS INT
AS
BEGIN
DECLARE @f AS INT
SET @f = (SELECT d2 FROM Test WHERE d1 = @p)
IF @f IS NULL RETURN @f
If @f = @p RETURN @f
RETURN dbo.findDoctocomp(@f)
END
Los nombres de las columnas están cambiados para no escribir tanto :)
Salud!
Alfredo Novoa
Hola,
El Sat, 11 Oct 2008 21:39:24 -0700 (PDT), renemoreno escribió:
> Estoy tratando de obtener el DOCTO incial y el DOCTOCOMP final, para
> el ejemplo anterior seria 11111 - 44444.
>
> Espero haber sido claro y de antemano agradezco cualquier ayuda que me
> puedan dar.
Es muy fácil. Solo tienes que usar una CTE (Common Table Expression).
Este tipo de consulta se llama "cierre transitivo". Si quieres ejemplos
puedes buscar: "transitive closure"+CTE
Saludos
Alfredo Novoa
Hola Leonardo,
El Sun, 12 Oct 2008 00:51:28 -0430, Leonardo Azpurua escribió:
> Seguramente habrá una manera mejor de hacerlo, pero esto funciona:
Pues si, con una CTE tiene que ir mucho más rápido.
Saludos
Leonardo Azpurua
"Alfredo Novoa" <alfr...@gmail.com> escribió en el mensaje
news:1htbmsch8ho3j.1...@40tude.net...
Hola, Alfredo:
En los BOL del 2000 aparecen sólo dos instancias de "transitive closure",
ambas relacionadas con otra cosa, y ni un resultado para CTE. Busqué en
Google, pero me aburrí antes de encontrar nada util (aparece un articulo de
SQL Journal, o como quiera que se llame, pero es solo para suscriptores, lo
único que se puede acceder es el código, que es un poco coñazo).
Puedes sugerir algún vínculo que explique -como para que lo entienda un
desarrollador de aplicaciones de negocios- de que van las CTE?
Salud!
Pedro
"Leonardo Azpurua" <l e o n a r d o [arroba] m v p s [punto] o r g> escribió
en el mensaje news:uZ0skxHL...@TK2MSFTNGP03.phx.gbl...
Alfredo Novoa
Hola Leonardo,
El Sun, 12 Oct 2008 10:44:48 -0430, Leonardo Azpurua escribió:
> En los BOL del 2000 aparecen sólo dos instancias de "transitive closure",
> ambas relacionadas con otra cosa, y ni un resultado para CTE.
Es que las CTE como dice Pedro las incluyeron en el 2005.
> Busqué en
> Google, pero me aburrí antes de encontrar nada util (aparece un articulo de
> SQL Journal, o como quiera que se llame, pero es solo para suscriptores, lo
> único que se puede acceder es el código, que es un poco coñazo).
Pues que más quieres :-). Si miras el siguiente enlace también hay otro
ejemplo.
http://www.eggheadcafe.com/forumarchives/SQLServerprogramming/Oct2005/post24683455.asp
> Puedes sugerir algún vínculo que explique -como para que lo entienda un
> desarrollador de aplicaciones de negocios- de que van las CTE?
Si buscas CTE + "SQL Server" te saldrán cerca de 60.000 enlaces. También
puedes buscar "recursive queries".
Y como me has pillado de buen humor ya te lo doy todo hecho :-)
with T as
(
select DOCTO, DOCTOCOMP from LaTabla
union all
select e.DOCTO, t.DOCTOCOMP
from LaTabla e, T
where e.DOCTOCOMP = t.DOCTO
)
select docto,doctocomp from t where docto not in (select doctocomp from t)
and doctocomp not in (select docto from t)
Saludos
Alfredo
Alfredo Novoa
campos son iguales, que no hacen más que molestar.
Entonces quedaría así:
with x as (select * from latabla where docto<>doctocomp), y as (select
docto, doctocomp from x union all select x.docto, y.doctocomp from x, y
where x.doctocomp = y.docto) select docto, doctocomp from y where docto not
in (select doctocomp from y) and doctocomp not in (select docto from y)
Aunque esas filas sería mejor borrarlas de la tabla.
Saludos
Leonardo Azpurua
Yo igual me quedé sin entender nada :)
De todas maneras, si algún día la necesidad aparece, ya al menos sé por
donde buscar.
Salud!
Alfredo Novoa
>> with x as (select * from latabla where docto<>doctocomp), y as (select
>> docto, doctocomp from x union all select x.docto, y.doctocomp from x, y
>> where x.doctocomp = y.docto) select docto, doctocomp from y where docto
>> not
>> in (select doctocomp from y) and doctocomp not in (select docto from y)
>>
>> Aunque esas filas sería mejor borrarlas de la tabla.
>
> Yo igual me quedé sin entender nada :)
Es muy fácil.
Las CTE son simplemente expresiones "with".
with x as (select * from latabla where docto<>doctocomp)
Con esto simplemente creamos una variable temporal llamada x que es igual
al resultado de la consulta entre parentesis.
Podemos crear más variables de esas poniendo comas
with x as (...), y as (...), z as ...
Y podemos usar esas variables en las siguientes variables que definamos e
incluso en la misma variable que estamos definiendo, y esto es lo que
permite las consultas recursivas.
y as
(
select docto, doctocomp from x
union all
select x.docto, y.doctocomp from x, y
where x.doctocomp = y.docto
)
Esta subexpresión es la que calcula el cierre transitivo que nos daría este
resultado:
DOCTO - DOCTOCOMP
11111 - 22222
11111 - 33333
11111 - 44444
22222 - 33333
22222 - 44444
33333 - 44444
Así explicado para andar por casa, el cierre transitivo es simplemente una
lista de los elementos asociados por una relación transitiva. Relación
transitiva quiere decir que si A está relacionado con B y B está
relacionado con C entonces A está relacionado con C.
O también se podría representar como una lista de los nodos conectados
directa o indirectamente de un grafo dirigido acíclico. Todo esto viene en
cualquier introducción a la matemática discreta como la de 1º de la
carrera.
Aquí explican como funcionan las consultas recursivas:
http://msdn.microsoft.com/en-us/library/ms186243.aspx
La expresión de debajo del union all es la expresión recursiva, y la de
encima es el enganche.
Y la expresión del "where" simplemente sirve para eliminar los registros
intermedios que nos sobran.
Si un código aparece en la columna DOCTOCOMP entonces quiere decir que ese
código no es el inicio de un grafo, y si no aparece en DOCTO quiere decir
que no es el final de un grafo. Si quitamos todo lo que no son ni inicios
ni finales entonces nos quedan los inicios y los finales que es lo que
queremos :-)
Las filas donde los dos campos son iguales hay que eliminarlas por que
crean un ciclo y por lo tanto recursividad infinita. Aunque SQL Server solo
permite 100 niveles de recursividad, por lo menos tal y como lo tengo yo
configurado, y no se si se puede cambiar.
En resumen, que esta es una de las consultas recursivas más sencillitas que
pueda haber :-)
> De todas maneras, si algún día la necesidad aparece, ya al menos sé por
> donde buscar.
Las consultas recursivas son muy útiles para calcular escandallos y cosas
así, y las CTE son utilísimas para casi todo.
Saludos
Alfredo Novoa
> El Sun, 12 Oct 2008 17:34:11 -0430, Leonardo Azpurua escribió:
> Y la expresión del "where" simplemente sirve para eliminar los registros
> intermedios que nos sobran.
Aquí me refiero a esta:
where docto not in (select doctocomp from y) and doctocomp not in
(select docto from y)
> Si un código aparece en la columna DOCTOCOMP entonces quiere decir que ese
> código no es el inicio de un grafo, y si no aparece en DOCTO
> que no es el final de un grafo.
Esto último está al reves :(
Si un código aparece en DOCTO quiere decir que no es el final de un
grafo.
Saludos
Leonardo Azpurua
> El Sun, 12 Oct 2008 17:34:11 -0430, Leonardo Azpurua escribió:
>> Yo igual me quedé sin entender nada :)
>
> Es muy fácil.
Pues sí.
A la carpeta de las explicaciones que vale la pena guardar.
Muchisimas gracias, Alfredo.
Salud!
Alejandro Mesa
Aqui tienes otra version, pero el principio sigue siendo el mismo, una CTE
recursiva.
; WITH r_cte
AS
(
SELECT
docto, doctocomp, 1 AS lvl
FROM
dbo.t
WHERE
docto = doctocomp
UNION ALL
SELECT
c.docto, c.doctocomp, p.lvl + 1 AS lvl
FROM
dbo.t AS c
INNER JOIN
r_cte AS p
ON p.docto = c.doctocomp
AND (c.docto <> c.doctocomp)
),
r_set
AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY lvl DESC) AS rn
FROM r_cte
)
SELECT
MAX(CASE WHEN rn = 1 THEN docto END) AS c1,
MAX(CASE WHEN lvl = 1 THEN doctocomp END) AS c2
FROM
r_set
GO
AMB
Alfredo Novoa
Hola Alejandro,
El Mon, 13 Oct 2008 17:01:00 -0700, Alejandro Mesa escribió:
> Aqui tienes otra version, pero el principio sigue siendo el mismo, una CTE
> recursiva.
Lo malo de usar MAX es que si en la tabla hay varias jerarquías solo sacas
el principio y final de una de ellas.
Saludos
Alfredo
Alejandro Mesa
Asi es, y en cuyo caso podriamos usar:
; WITH r_cte
AS
(
SELECT
docto, doctocomp, docto AS grp, 1 AS lvl
FROM
dbo.t
WHERE
docto = doctocomp
UNION ALL
SELECT
c.docto, c.doctocomp, p.grp, p.lvl + 1 AS lvl
FROM
dbo.t AS c
INNER JOIN
r_cte AS p
ON p.docto = c.doctocomp
AND (c.docto <> c.doctocomp)
),
r_set
AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY lvl DESC) AS rn
FROM r_cte
)
SELECT
MAX(CASE WHEN rn = 1 THEN docto END) AS c1,
MAX(CASE WHEN lvl = 1 THEN doctocomp END) AS c2
FROM
r_set
GROUP BY
grp
GO
AMB
Alfredo Novoa
Hola Alejandro,
El Tue, 14 Oct 2008 06:11:01 -0700, Alejandro Mesa escribió:
> Asi es, y en cuyo caso podriamos usar:
>
> ; WITH r_cte
> AS ...
Debe de haber algo mal por que acabo de probar y me sigue sacando solo
una
fila.
Los datos con los que he probado son estos:
00000 00000
11111 22222
22222 33333
33333 44444
44444 55555
77777 88888
88888 99999
99999 00000
Tendría que devolver esto:
11111 55555
77777 00000
Y me devuelve
77777 00000
Saludos
renemoreno
Señores, mis agradecimientos y mis respetos a todos ustedes, aun estoy
liandome un poco con la CTE pero sus explicaciones han sido de
muchisima ayuda. Se los agradezco.
Saludos
Rene Moreno
Carlos M. Calvelo
On 14 okt, 15:52, Alfredo Novoa <alfred...@gmail.com> wrote:
> Hola Alejandro,
>
> El Tue, 14 Oct 2008 06:11:01 -0700, Alejandro Mesa escribió:
>
> > Asi es, y en cuyo caso podriamos usar:
>
> > ; WITH r_cte
> > AS ...
>
> Debe de haber algo mal por que acabo de probar y me sigue sacando solo
> una
> fila.
>
> Los datos con los que he probado son estos:
>
> 00000 00000
> 11111 22222
> 22222 33333
> 33333 44444
> 44444 55555
> 77777 88888
> 88888 99999
> 99999 00000
Deberías probar con esto:
00000 00000
11111 22222
22222 33333
33333 44444
44444 55555
55555 55555 <<=
77777 88888
88888 99999
99999 00000
>
> Tendría que devolver esto:
>
> 11111 55555
> 77777 00000
y devolverá
11111 55555
77777 00000
La consulta de Alejandro necesita el registro final de la serie
con docto = doctocomp.
Saludos,
Carlos
Alfredo Novoa
> La consulta de Alejandro necesita el registro final de la serie
> con docto = doctocomp.
Ah, vale, era eso. Gracias.
Saludos
Alejandro Mesa
Gracias por la ayuda. Estuve ocupado y ahora es que pude regresar al hilo.
AMB