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

Siga el debate

5 respuestas

Tengo una respuesta

Preguntas similare

Mostrar todos los temas similares

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

Responder a este mensaje

#2 Mauricio

13/10/2009 - 12:00 | Informe spam

Gracias Carlos, quedó perfecto. Me tomé el atrevimiento de modificarlo
un poco para que quede ordenado y la verdad es que quedó bien.

WITH Tree ( Nombre , Id , Nivel , IdTree, SortKey )
AS ( SELECT Nombre , IdPadre , Nivel , IdProyecto, CAST
(IdProyecto AS VARBINARY(900))
FROM PROYECTOS
WHERE IdPadre = 0
UNION ALL
SELECT P.Nombre , P.IdPadre , P.Nivel , P.IdProyecto,
CAST (T.SortKey + CAST (P.IdProyecto AS BINARY(4)) AS
VARBINARY(900))
FROM PROYECTOS P
INNER JOIN Tree T
ON T.IdTree = P.IdPadre )
SELECT SPACE ( Nivel * 10 ) + Nombre AS DATA , Id , Nivel , IdTree,
SortKey
FROM Tree
ORDER BY SortKey

Con respecto al diseño, tienes razón pero básicamente tuve que
hacerlo así por la herramienta de desarrollo que estoy utilizando y a
esta altura ya es casi imposible cambiarlo.

Ahora solo me queda que las ramas queden ordenadas alfabéticamente
pero con tus consejos y los de Alejandro en mi post anterior creo que
podré resolverlo.
Muchas gracias nuevamente.

Carlos Sacristan a présenté l'énoncé suivant :

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.

Copenhague, Dinamarca

Mauricio
Copenhague, Dinamarca

Responder a este mensaje

#3 Mauricio

15/10/2009 - 23:21 | Informe spam

Hola Carlos,
perdón por seguir molestando pero tengo una sola duda más. Una de
tus recomendaciones es que el campo Nivel lo calcule a partir de la
CTE, algo que he probado y ha salido perfecto. Ahora lo que quiero es
indicar si un determinado registro tiene "hijos" o no.
Suponiendo que tenemos algo así:
MCS
Apps
Dlls
Imagenes
Bmps
Jpgs
Otras

En este caso solo MCS e Imagenes tendrán subniveles pero no los
otros registros. Hay alguna forma sencilla de resolver esto en la CTE
sin que la misma se torne pesada en su ejecución?
Muchas gracias por la ayuda.
Saludos.

Mauricio
Carlos Sacristan a pensé très fort :

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.

Copenhague, Dinamarca

Mauricio
Copenhague, Dinamarca

Responder a este mensaje

#4 Carlos M. Calvelo

16/10/2009 - 00:52 | Informe spam

Hola Mauricio,

On Thu, 15 Oct 2009 23:21:16 +0200, Mauricio wrote:

Hola Carlos,
perdón por seguir molestando pero tengo una sola duda más. Una de
tus recomendaciones es que el campo Nivel lo calcule a partir de la
CTE, algo que he probado y ha salido perfecto. Ahora lo que quiero es
indicar si un determinado registro tiene "hijos" o no.
Suponiendo que tenemos algo así:
MCS
Apps
Dlls
Imagenes
Bmps
Jpgs
Otras

En este caso solo MCS e Imagenes tendrán subniveles pero no los
otros registros. Hay alguna forma sencilla de resolver esto en la CTE
sin que la misma se torne pesada en su ejecución?
Muchas gracias por la ayuda.
Saludos.

En la CTE que tu mismo has puesto en el otro post pueces hacer (en la
última consulta) un left join con proyectos para determinar si un
IdProjecto en Tree es IdPadre de algún otro proyecto.

El último select en tu consulta sería entonces algo como:

SELECT SPACE ( T.Nivel * 10 ) + T.Nombre AS DATA , T.Id , T.Nivel,
T.IdTree, T.SortKey ,
CASE WHEN P.IdProjecto IS NULL 'No' ELSE 'Si' END [Tiene hijos?]
FROM Tree T left join PROJECTOS P on T.IdProjecto=P.IdPadre
ORDER BY SortKey

A ver si funciona... que yo no lo he probado.

Saludos,
Carlos

Responder a este mensaje

#5 Carlos M. Calvelo

16/10/2009 - 01:00 | Informe spam

Hola Mauricio,

SELECT SPACE ( T.Nivel * 10 ) + T.Nombre AS DATA , T.Id , T.Nivel,
T.IdTree, T.SortKey ,
CASE WHEN P.IdProjecto IS NULL 'No' ELSE 'Si' END [Tiene hijos?]
FROM Tree T left join PROJECTOS P on T.IdProjecto=P.IdPadre
ORDER BY SortKey

Entre IS NULL y 'No' tienes que poner un THEN :-)

Saludos,
Carlos

Siga el debate Respuesta

Responder a este mensaje

Ads by Google

Hacer una pregunta

Tengo una respuesta

Busqueda sugerida