Dudas sobre CTE (20091013)

13/10/2009 - 10:49 por Mauricio | Informe spam
Hola a todos,
sigo con un par de dudas con respecto a las CTEs. He estado leyendo
en estos últimos días y más o menos llegué a algo parecido a lo que
necesito pero con algunos errores.
He aquí mi tabla:

CREATE TABLE [dbo].[PROYECTOS](
[IDProyecto] [int] IDENTITY(1,1) NOT NULL,
[Nombre] [varchar](50) NOT NULL,
[Comentario] [varchar](5000) NULL,
[IdPadre] [int] NOT NULL,
[Nivel] [tinyint] NULL,
[IdUsuario] [int] NULL,
[Fecha] [datetime] NULL,
[Mojon] [tinyint] NULL,
[NUMEROHISTORIA] [int] NULL,
[NoEnviarNotificacionesVCS] [tinyint] NULL,
[NoEnviarNotificacionesITS] [tinyint] NULL,
CONSTRAINT [PK_PROYECTOS] PRIMARY KEY CLUSTERED
(
[IDProyecto] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Acá algunos inserts para datos:
SET IDENTITY_INSERT [dbo].[PROYECTOS] ON;
BEGIN TRANSACTION;
INSERT INTO [dbo].[PROYECTOS]([IDProyecto], [Nombre], [Comentario],
[IdPadre], [Nivel], [IdUsuario], [Fecha], [Mojon], [NUMEROHISTORIA],
[NoEnviarNotificacionesVCS], [NoEnviarNotificacionesITS])
SELECT 1, N'People program', N'', 0, 0, 1, '20071025 22:03:49.530', 0,
NULL, NULL, NULL UNION ALL
SELECT 2, N'01- Apps', N'', 1, 1, 1, '20071025 22:04:27.930', 0, NULL,
NULL, NULL UNION ALL
SELECT 3, N'02- Dct', N'', 1, 1, 1, '20071025 22:04:38.650', 0, NULL,
NULL, NULL UNION ALL
SELECT 4, N'06- Clases', N'', 1, 1, 1, '20071025 22:04:44.180', 0,
NULL, NULL, NULL UNION ALL
SELECT 5, N'09- Scripts', N'', 1, 1, 1, '20071025 22:04:49.570', 0,
NULL, NULL, NULL UNION ALL
SELECT 6, N'07- Documents', N'', 1, 1, 1, '20071025 22:05:27.260', 0,
NULL, NULL, NULL UNION ALL
SELECT 7, N'04- Dlls and Exes', N'', 1, 1, 1, '20071025 22:12:40.100',
0, NULL, NULL, NULL UNION ALL
SELECT 8, N'03- Libs', N'', 1, 1, 1, '20071025 22:12:44.590', 0, NULL,
NULL, NULL UNION ALL
SELECT 9, N'08- Images', N'Images used in the project', 1, 1, 1,
'20071026 15:33:27.680', 0, NULL, NULL, NULL UNION ALL
SELECT 11, N'05- Data Files', N'', 1, 1, 1, '20071026 15:33:56.840', 0,
NULL, NULL, NULL UNION ALL
SELECT 12, N'School program', N'', 0, 0, 1, '20071026 15:37:35.950', 0,
NULL, NULL, NULL UNION ALL
SELECT 13, N'01- Apps', N'', 12, 1, 1, '20071026 15:37:46.000', 0,
NULL, NULL, NULL UNION ALL
SELECT 14, N'02- Dct', N'', 12, 1, 1, '20071026 15:37:52.900', 0, NULL,
NULL, NULL UNION ALL
SELECT 15, N'03- Libs', N'', 12, 1, 1, '20071026 15:37:58.420', 0,
NULL, NULL, NULL UNION ALL
SELECT 16, N'04- Dlls and Exes', N'', 12, 1, 1, '20071026
15:38:03.710', 0, NULL, NULL, NULL UNION ALL
SELECT 17, N'05- Data files', N'', 12, 1, 1, '20071026 15:38:20.210',
0, NULL, NULL, NULL UNION ALL
SELECT 18, N'06- Images', N'', 12, 1, 1, '20071026 15:38:29.340', 0,
NULL, NULL, NULL UNION ALL
SELECT 20, N'07- Help File', N'', 12, 1, 1, '20071026 15:39:07.040', 0,
NULL, NULL, NULL UNION ALL
SELECT 21, N'08- Documents', N'', 12, 1, 1, '20071026 15:41:49.510', 0,
NULL, NULL, NULL UNION ALL
SELECT 22, N'_Corporate Web Site', N'', 0, 0, 2, '20071028
19:57:49.850', 0, NULL, NULL, NULL UNION ALL
SELECT 23, N'_Source files', N'', 22, 1, 2, '20071028 19:58:16.290', 0,
NULL, NULL, NULL UNION ALL
SELECT 24, N'_Libraries', N'', 22, 1, 2, '20071028 19:58:26.890', 0,
NULL, NULL, NULL UNION ALL
SELECT 25, N'_Tools', N'Tools for development', 0, 0, 2, '20071028
19:58:37.600', 0, NULL, NULL, NULL UNION ALL
SELECT 28, N'DbgView', N'sdffsdf', 25, 1, 2, '20071028 19:59:17.870',
0, 2, NULL, NULL UNION ALL
SELECT 29, N'About Pages', N'', 22, 1, 2, '20071028 21:09:44.500', 0,
NULL, NULL, NULL UNION ALL
SELECT 30, N'Product Pages', N'', 22, 1, 2, '20071028 21:09:54.480', 0,
NULL, NULL, NULL UNION ALL
SELECT 31, N'Support Pages', N'', 22, 1, 2, '20071028 21:10:43.310', 0,
NULL, NULL, NULL UNION ALL
SELECT 32, N'Project one', N'', 0, 0, 10, '20081124 16:32:53.400', 0,
NULL, NULL, NULL UNION ALL
SELECT 35, N'Dll tutor', N'', 0, 0, 10, '20090610 11:10:47.120', 0,
999, NULL, NULL UNION ALL
SELECT 45, N'Apps', N'', 35, 1, 10, '20090612 15:20:06.120', 0, 999,
NULL, NULL UNION ALL
SELECT 46, N'Images', N'', 35, 1, 10, '20090612 18:15:30.460', 0, 999,
NULL, NULL UNION ALL
SELECT 47, N'Dlls', N'', 35, 1, 10, '20090612 18:17:25.150', 0, 999,
NULL, NULL UNION ALL
SELECT 48, N'Libs', N'', 35, 1, 10, '20090612 18:17:32.790', 0, 999,
NULL, NULL UNION ALL
SELECT 51, N'Templates Prueba', N'', 25, 1, 10, '20090614
12:21:40.540', 0, 999, NULL, NULL UNION ALL
SELECT 53, N'prueba 34', N'', 25, 1, 10, '20090614 12:42:23.170', 0,
999, NULL, NULL UNION ALL
SELECT 54, N'Documentos clarion', N'', 32, 1, 10, '20090701
08:10:50.480', 0, 999, NULL, NULL UNION ALL
SELECT 55, N'Temporary files', N'', 51, 2, 10, '20090712 16:10:22.350',
0, 999, NULL, NULL UNION ALL
SELECT 56, N'MCS', N'', 55, 3, 10, '20090712 16:10:22.450', 0, 999,
NULL, NULL UNION ALL
SELECT 57, N'TDC', N'', 55, 3, 10, '20090712 16:10:22.480', 0, 999,
NULL, NULL
COMMIT;
RAISERROR (N'[dbo].[PROYECTOS]: Insert Batch: 1.Done!', 10, 1) WITH
NOWAIT;
GO

SET IDENTITY_INSERT [dbo].[PROYECTOS] OFF;

Ahora mi CTE:

WITH
Tree (Nombre, Id, Nivel, IdTree)
AS (
SELECT Nombre, IdPadre, Nivel, IdProyecto FROM PROYECTOS
UNION ALL
SELECT P.Nombre, P.IdPadre, P.Nivel, P.IdProyecto FROM PROYECTOS P
INNER JOIN Tree T ON T.Id = P.IDProyecto
)
SELECT SPACE(Nivel*2) + Nombre as data, Id, Nivel, IdTree
FROM Tree

Esta es la que está "más o menos" bien porque da como resultado 77
líneas (cuando en realidad no tengo más de 39 filas en la tabla
PROYECTOS) y si la ejecutan verán que al final empieza a repetir muchas
líneas. Obviamente estoy haciendo algo mal pero no me doy cuenta qué.
Muchas gracias por cualquier respuesta y perdón por el largo del
post.
Saludos.

Mauricio
Copenhague, Dinamarca
 

Leer las respuestas

#1 Carlos Sacristan
13/10/2009 - 11:17 | Informe spam
Tienes un par de fallos. En primer lugar, en el miembro no recursivo no
seleccionas bien los datos, pues tienes que filtrar por aquellos proyectos
"padre", esto es, los que están arriba de la jerarquía (en tu diseño de
tabla, cuyo IdPadre = 0). Por otro lado, en el miembro recursivo no estás
haciendo bien la combinación, puesto que tienes que hacer que el registro
actual tenga un IdPadre igual a un IdTree existente (tú ahora estás diciendo
que el IdProyecto actual sea igual a un IdTree).

En definitiva, que la query correcta es algo como:

******************************************************


WITH Tree ( Nombre , Id , Nivel , IdTree )
AS ( SELECT Nombre , IdPadre , Nivel , IdProyecto
FROM PROYECTOS
WHERE IdPadre = 0
UNION ALL
SELECT P.Nombre , P.IdPadre , P.Nivel , P.IdProyecto
FROM PROYECTOS P
INNER JOIN Tree T
ON T.IdTree = P.IdPadre )
SELECT SPACE ( Nivel * 2 ) + Nombre AS DATA , Id , Nivel , IdTree
FROM Tree

******************************************************

Por cierto, yo redefiniría la tabla para permitir nulos en el campo IdPadre
y crear una restricción de clave foránea entre IdPadre e IdProyecto para de
ese modo evitar que se cuelen identificadores erróneos.

El campo nivel tampoco lo almacenaría en la tabla, puesto que podría cambiar
dependiendo del IdPadre al que perteneciera el proyecto actual, calculándolo
dentro de la CTE.

"Caminar sobre el agua y desarrollar software a partir de unas
especificaciones es fácil, si ambas están congeladas."
Edward V. Berard, ingeniero informático


"Mauricio" wrote in message
news:
Hola a todos,
sigo con un par de dudas con respecto a las CTEs. He estado leyendo en
estos últimos días y más o menos llegué a algo parecido a lo que necesito
pero con algunos errores.
He aquí mi tabla:

CREATE TABLE [dbo].[PROYECTOS](
[IDProyecto] [int] IDENTITY(1,1) NOT NULL,
[Nombre] [varchar](50) NOT NULL,
[Comentario] [varchar](5000) NULL,
[IdPadre] [int] NOT NULL,
[Nivel] [tinyint] NULL,
[IdUsuario] [int] NULL,
[Fecha] [datetime] NULL,
[Mojon] [tinyint] NULL,
[NUMEROHISTORIA] [int] NULL,
[NoEnviarNotificacionesVCS] [tinyint] NULL,
[NoEnviarNotificacionesITS] [tinyint] NULL,
CONSTRAINT [PK_PROYECTOS] PRIMARY KEY CLUSTERED
(
[IDProyecto] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Acá algunos inserts para datos:
SET IDENTITY_INSERT [dbo].[PROYECTOS] ON;
BEGIN TRANSACTION;
INSERT INTO [dbo].[PROYECTOS]([IDProyecto], [Nombre], [Comentario],
[IdPadre], [Nivel], [IdUsuario], [Fecha], [Mojon], [NUMEROHISTORIA],
[NoEnviarNotificacionesVCS], [NoEnviarNotificacionesITS])
SELECT 1, N'People program', N'', 0, 0, 1, '20071025 22:03:49.530', 0,
NULL, NULL, NULL UNION ALL
SELECT 2, N'01- Apps', N'', 1, 1, 1, '20071025 22:04:27.930', 0, NULL,
NULL, NULL UNION ALL
SELECT 3, N'02- Dct', N'', 1, 1, 1, '20071025 22:04:38.650', 0, NULL,
NULL, NULL UNION ALL
SELECT 4, N'06- Clases', N'', 1, 1, 1, '20071025 22:04:44.180', 0, NULL,
NULL, NULL UNION ALL
SELECT 5, N'09- Scripts', N'', 1, 1, 1, '20071025 22:04:49.570', 0, NULL,
NULL, NULL UNION ALL
SELECT 6, N'07- Documents', N'', 1, 1, 1, '20071025 22:05:27.260', 0,
NULL, NULL, NULL UNION ALL
SELECT 7, N'04- Dlls and Exes', N'', 1, 1, 1, '20071025 22:12:40.100', 0,
NULL, NULL, NULL UNION ALL
SELECT 8, N'03- Libs', N'', 1, 1, 1, '20071025 22:12:44.590', 0, NULL,
NULL, NULL UNION ALL
SELECT 9, N'08- Images', N'Images used in the project', 1, 1, 1, '20071026
15:33:27.680', 0, NULL, NULL, NULL UNION ALL
SELECT 11, N'05- Data Files', N'', 1, 1, 1, '20071026 15:33:56.840', 0,
NULL, NULL, NULL UNION ALL
SELECT 12, N'School program', N'', 0, 0, 1, '20071026 15:37:35.950', 0,
NULL, NULL, NULL UNION ALL
SELECT 13, N'01- Apps', N'', 12, 1, 1, '20071026 15:37:46.000', 0, NULL,
NULL, NULL UNION ALL
SELECT 14, N'02- Dct', N'', 12, 1, 1, '20071026 15:37:52.900', 0, NULL,
NULL, NULL UNION ALL
SELECT 15, N'03- Libs', N'', 12, 1, 1, '20071026 15:37:58.420', 0, NULL,
NULL, NULL UNION ALL
SELECT 16, N'04- Dlls and Exes', N'', 12, 1, 1, '20071026 15:38:03.710',
0, NULL, NULL, NULL UNION ALL
SELECT 17, N'05- Data files', N'', 12, 1, 1, '20071026 15:38:20.210', 0,
NULL, NULL, NULL UNION ALL
SELECT 18, N'06- Images', N'', 12, 1, 1, '20071026 15:38:29.340', 0, NULL,
NULL, NULL UNION ALL
SELECT 20, N'07- Help File', N'', 12, 1, 1, '20071026 15:39:07.040', 0,
NULL, NULL, NULL UNION ALL
SELECT 21, N'08- Documents', N'', 12, 1, 1, '20071026 15:41:49.510', 0,
NULL, NULL, NULL UNION ALL
SELECT 22, N'_Corporate Web Site', N'', 0, 0, 2, '20071028 19:57:49.850',
0, NULL, NULL, NULL UNION ALL
SELECT 23, N'_Source files', N'', 22, 1, 2, '20071028 19:58:16.290', 0,
NULL, NULL, NULL UNION ALL
SELECT 24, N'_Libraries', N'', 22, 1, 2, '20071028 19:58:26.890', 0, NULL,
NULL, NULL UNION ALL
SELECT 25, N'_Tools', N'Tools for development', 0, 0, 2, '20071028
19:58:37.600', 0, NULL, NULL, NULL UNION ALL
SELECT 28, N'DbgView', N'sdffsdf', 25, 1, 2, '20071028 19:59:17.870', 0,
2, NULL, NULL UNION ALL
SELECT 29, N'About Pages', N'', 22, 1, 2, '20071028 21:09:44.500', 0,
NULL, NULL, NULL UNION ALL
SELECT 30, N'Product Pages', N'', 22, 1, 2, '20071028 21:09:54.480', 0,
NULL, NULL, NULL UNION ALL
SELECT 31, N'Support Pages', N'', 22, 1, 2, '20071028 21:10:43.310', 0,
NULL, NULL, NULL UNION ALL
SELECT 32, N'Project one', N'', 0, 0, 10, '20081124 16:32:53.400', 0,
NULL, NULL, NULL UNION ALL
SELECT 35, N'Dll tutor', N'', 0, 0, 10, '20090610 11:10:47.120', 0, 999,
NULL, NULL UNION ALL
SELECT 45, N'Apps', N'', 35, 1, 10, '20090612 15:20:06.120', 0, 999, NULL,
NULL UNION ALL
SELECT 46, N'Images', N'', 35, 1, 10, '20090612 18:15:30.460', 0, 999,
NULL, NULL UNION ALL
SELECT 47, N'Dlls', N'', 35, 1, 10, '20090612 18:17:25.150', 0, 999, NULL,
NULL UNION ALL
SELECT 48, N'Libs', N'', 35, 1, 10, '20090612 18:17:32.790', 0, 999, NULL,
NULL UNION ALL
SELECT 51, N'Templates Prueba', N'', 25, 1, 10, '20090614 12:21:40.540',
0, 999, NULL, NULL UNION ALL
SELECT 53, N'prueba 34', N'', 25, 1, 10, '20090614 12:42:23.170', 0, 999,
NULL, NULL UNION ALL
SELECT 54, N'Documentos clarion', N'', 32, 1, 10, '20090701 08:10:50.480',
0, 999, NULL, NULL UNION ALL
SELECT 55, N'Temporary files', N'', 51, 2, 10, '20090712 16:10:22.350', 0,
999, NULL, NULL UNION ALL
SELECT 56, N'MCS', N'', 55, 3, 10, '20090712 16:10:22.450', 0, 999, NULL,
NULL UNION ALL
SELECT 57, N'TDC', N'', 55, 3, 10, '20090712 16:10:22.480', 0, 999, NULL,
NULL
COMMIT;
RAISERROR (N'[dbo].[PROYECTOS]: Insert Batch: 1.Done!', 10, 1) WITH
NOWAIT;
GO

SET IDENTITY_INSERT [dbo].[PROYECTOS] OFF;

Ahora mi CTE:

WITH
Tree (Nombre, Id, Nivel, IdTree)
AS (
SELECT Nombre, IdPadre, Nivel, IdProyecto FROM PROYECTOS
UNION ALL
SELECT P.Nombre, P.IdPadre, P.Nivel, P.IdProyecto FROM PROYECTOS P
INNER JOIN Tree T ON T.Id = P.IDProyecto
)
SELECT SPACE(Nivel*2) + Nombre as data, Id, Nivel, IdTree
FROM Tree

Esta es la que está "más o menos" bien porque da como resultado 77
líneas (cuando en realidad no tengo más de 39 filas en la tabla PROYECTOS)
y si la ejecutan verán que al final empieza a repetir muchas líneas.
Obviamente estoy haciendo algo mal pero no me doy cuenta qué.
Muchas gracias por cualquier respuesta y perdón por el largo del post.
Saludos.

Mauricio
Copenhague, Dinamarca


Preguntas similares