[SQL2K5]les requête recursives

Version imprimable

Bonjour à tous,
Suite à la lecture, d'un post, j'ai voulu m'essayer à la rêquete récursive pour récupérer des infos.

J'ai bien lu le tuto mais je n'arrive pas à appliquer.

J'ai une table

Code:

T_DIM_GRANDES_COMPTES (GrandesComptes_Id, EntiteClienteMere_Id, EntiteClienteFille_Id)

et voici mon essai de la requête récursive
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 WITH tree (EntiteClienteMere_Id, level, EntiteClienteFille_Id, GrandesComptes_Id, pathstr) AS (SELECT EntiteClienteMere_Id , 0 , EntiteClienteFille_Id , GrandesComptes_Id , CAST('' AS VARCHAR(MAX)) FROM T_DIM_GRANDES_COMPTES WHERE EntiteClienteMere_Id = 110532 --248372 UNION ALL SELECT V.EntiteClienteMere_Id , t.level + 1 , V.EntiteClienteFille_Id , V.GrandesComptes_Id , t.pathstr + CAST(V.EntiteClienteMere_Id AS VARCHAR(MAX)) FROM T_DIM_GRANDES_COMPTES V INNER JOIN tree t ON t.EntiteClienteFille_Id= V.EntiteClienteMere_Id ) SELECT SPACE(level) + EntiteClienteMere_Id AS EntiteClienteMere_Id , level , EntiteClienteFille_Id , GrandesComptes_Id , pathstr FROM tree ORDER BY pathstr, EntiteClienteMere_Id
Si j'utilise un EntiteClienteMere_Id qui n'a que des filles et pas de petites-filles, la requête me retourne bien les filles avec un level = 0

Par contre si j'execute avec un EntiteClienteMere_Id ayant des petites-filles je me retrouve avec l'erreur suivante :
"The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

Or j'ai vérifié avec des requêtes toute simple que la récursion est bonne.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 DROP TABLE #tmpGC1 SELECT GrandesComptes_Id, EntiteClienteMere_Id, EntiteClienteFille_Id into #tmpGC1 FROM T_DIM_GRANDES_COMPTES WHERE EntiteClienteMere_Id = 110532 --248372 select * from T_DIM_GRANDES_COMPTES where EntiteClienteMere_Id in (select EntiteClienteFille_Id from #tmpGC1) AND GrandesComptes_Id not in (select GrandesComptes_Id from #tmpGC1) select * from T_DIM_GRANDES_COMPTES where EntiteClienteMere_Id in (select EntiteClienteFille_Id from #tmpGC2) AND GrandesComptes_Id not in (select GrandesComptes_Id from #tmpGC2)
Dans le cas de mon EntiteClienteMere_Id, la dernière requêtes ne retourne rien, le nombre de niveau n'est pas donc infini.

Merci à vous pour vos éclaircissements.

je n'ai pas beaucoup de temps pour regarder mais pour depasser 100 recursion, il faut mettre une option.
Code:

1 2 3 4 5 6 7 8 9 10 WITH CTETemps AS ( SELECT cast('1990-01-01' AS datetime) Date UNION ALL SELECT Date + 1 FROM CTETemps WHERE Date + 1 < '2031-01-01' ) SELECT top 10 * FROM CTETEMPS ORDER BY Date desc OPTION (MAXRECURSION 0);
pour en savoir plus : http://blog.developpez.com/index.php...ation_de_table

15/05/2008, 16h54
Jinroh77

Le truc est que j'ai maximum 3 niveau dans mes Entites.
C'est pour cela que je cherche le problème dans ma requête récursive.

Merci.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
WITH 
tree (EntiteClienteMere_Id,level, EntiteClienteFille_Id, GrandesComptes_Id, pathstr)
AS(SELECT 
EntiteClienteMere_Id
, 0
, EntiteClienteFille_Id
, GrandesComptes_Id
,CAST('' AS VARCHAR(MAX)) FROM T_DIM_GRANDES_COMPTES
WHERE EntiteClientefille_Id isnull
UNION ALL
SELECT 
V.EntiteClienteMere_Id
, t.level + 1
, V.EntiteClienteFille_Id
, V.GrandesComptes_Id
, t.pathstr +CAST(V.EntiteClienteMere_Id ASVARCHAR(MAX)) 
 FROM T_DIM_GRANDES_COMPTES V
INNER JOIN tree t
ON t.EntiteClienteMere_Id= V.GrandesComptes_Id )
SELECT 
EntiteClienteMere_Id AS EntiteClienteMere_Id
,level
, EntiteClienteFille_Id
, GrandesComptes_Id
, pathstr
FROM tree
ORDER  BY level,pathstr, EntiteClienteMere_Id

Bonjour, Merci pour l'idée mais cela ne fonctionne pas.
J'ai oublier une explication.

Cette table Grandes_Comptes n'est qu'un table de référence entre EntiteFille_Id et Mere_Id.
ces Id se retrouvent dans une autre table EntiteCliente dont j'aurais ensuite besoin de récupérer des infos.
Le GrandesComptes_Id ne sert strictement à rien, je l'affichais juste pour avoir quelque chose à mettre.
De plus, tu ne spécifiais plus nul part l'IdMere de laquelle partir.

J'ai donc essayer le modifier ton script de la manière suivante :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 WITH tree (EntiteClienteMere_Id,level, EntiteClienteFille_Id, pathstr) AS(SELECT EntiteClienteMere_Id , 0 , EntiteClienteFille_Id ,CAST('' AS VARCHAR(MAX)) FROM T_DIM_GRANDES_COMPTES WHERE EntiteClienteMere_Id = 110532 UNION ALL SELECT V.EntiteClienteMere_Id , t.level + 1 , V.EntiteClienteFille_Id , t.pathstr +CAST(V.EntiteClienteMere_Id AS VARCHAR(MAX)) FROM T_DIM_GRANDES_COMPTES V INNER JOIN tree t ON V.EntiteClienteMere_Id= t.EntiteClienteFille_Id WHERE t.EntiteClienteFille_Id IS NULL ) SELECT EntiteClienteMere_Id AS EntiteClienteMere_Id ,level , EntiteClienteFille_Id , pathstr FROM tree ORDER BY level,pathstr, EntiteClienteMere_Id SELECT * FROM T_DIM_GRANDES_COMPTES
.

Le premier SELECT retourne bien les filles de l'EntiteClienteMere_Id donné dans le where, mais ensuite je n'ai pas de récursivité.

Merci pour ton aide.

16/05/2008, 12h53
ylarvor

Le script que je t'ai fourni fonctionne pour une table

T_DIM_GRANDES_COMPTES (GrandesComptes_Id, EntiteClienteMere_Id, EntiteClienteFille_Id)

avec les données suivantes

(1,2,null)
(2,3,1)
(3,null,2)
(4,null,null)
(5,6,null)
(6,null,5)
16/05/2008, 14h11
Jinroh77

Ok, je suis bien d'accord mais le problème est que ma table ne foncitonne aps comme cela. Si on prend une table
T_DIM_GRANDESCOMPTES (GrandesComptes_Id, EntiteClienteMere_Id, EntiteClienteFille_Id).
Le champ GrandesComptes_Id ne m'interesse pas.
Depuis la table T_DIM_ENTITECLIENTE(EntiteCliente_Id, Libelle), je choisis un EntiteCliente_Id.
A partir de celui-ci, dans la table T_DIM_GRANDESCOMPTES je restreint le EntiteClienteMere_Id avec EntiteCliente_Id sélectionné.
Ensuite je récupère tout les EntiteClienteFille_Id. Et pour chaque EntiteClienteFille_Id, je l'utilise en tant que EntiteClienteMere_Id dans la même table GRANDESCOMPTES et je cherche tout les EntiteClienteFille_Id etc...

J'ai donc une table T_DIM_GRANDESCOMPTES avec les valeurs suivantes :
(1,100,100)
(2,100,101)
(3,100,102)
(4,101,105)
(5,101,106)
(6,105,110)
(6,105,111)

Je veux alors obtenir la hiérarchie
(EntiteClienteMere_Id, EntiteClienteFille_Id, NiveauFille)
100, 100, 0
100, 101, 1
100, 102, 1
100, 105, 2
100, 106, 2
100, 110, 3
100, 111, 3

Merci

solution

j'ai observe le tutorial de sql pro et il faut une racine à null comme ci dessous. tu peux creer une super racine à l'ensemble de tes branches...

si tu remplace null par 100 et que tu met = 100 dans la requete, ca t'indique "
L'instruction a été terminée. La récursivité maximale 100 a été épuisée avant la fin de l'instruction.
"

Citation:

(1,null,100)
(2,null,101)
(3,null,102)
(4,101,105)
(5,101,106)
(6,105,110)
(6,105,111)
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 WITH tree (EntiteClienteMere_Id,level, EntiteClienteFille_Id, pathstr) AS(SELECT EntiteClienteMere_Id , 0 , EntiteClienteFille_Id ,CAST(''ASVARCHAR(MAX))FROM T_DIM_GRANDES_COMPTES WHERE EntiteClienteMere_Id isnull UNION ALL SELECT V.EntiteClienteMere_Id , t.level + 1 , V.EntiteClienteFille_Id , t.pathstr +CAST(V.EntiteClienteMere_Id ASVARCHAR(MAX))FROM T_DIM_GRANDES_COMPTES V INNERJOIN tree t ON V.EntiteClienteMere_Id= t.EntiteClienteFille_Id ) SELECT EntiteClienteMere_Id AS EntiteClienteMere_Id ,level , EntiteClienteFille_Id , pathstr FROM tree ORDERBYlevel,pathstr, EntiteClienteMere_Id

Bonjour,
je viens de re-vérifier les infos du tuto, et d'après ce que je viens de comprendre, il faut partir du bas de la hiérarchie ??
Si c'est bien le cas, c'est pour cela que l'on ne se comprend pas.
Ce que je souhaite est partir du haut de ma hiérarchie et trouver tout le fils, petit-fils etc.. à partir de la mère.

Un peu comme dans cette suite de requêtes très manuelles,
Code:

1 2 3 4 5 6 7 8 9 10 11 12 SELECT GrandesComptes_Id, EntiteClienteMere_Id, EntiteClienteFille_Id into #tmpGC1 FROM T_DIM_GRANDES_COMPTES WHERE EntiteClienteMere_Id = 110532 select * INTO #tmpGC2 from T_DIM_GRANDES_COMPTES where EntiteClienteMere_Id in (select EntiteClienteFille_Id from #tmpGC1) AND GrandesComptes_Id not in (select GrandesComptes_Id from #tmpGC1) select * from T_DIM_GRANDES_COMPTES where EntiteClienteMere_Id in (select EntiteClienteFille_Id from #tmpGC2) AND GrandesComptes_Id not in (select GrandesComptes_Id from #tmpGC2)

20/05/2008, 17h39
SQLpro

J'ai vu votre sujet. Il est malheureusement impossible pour moi d'intervenir longuement : je suis en déplacement pour une formation et ais des RV tard le soir cette semaine. Néanmoins, si vous n'êtes pas pressé, postez les DDL de vos tables, ainsi qu'un jeu d'essais et je traite votre cas de remonté dans les ancêtres ce week end, après mon cours au CNAM de samedi matin...

A +

Bonjour,
Merci beaucoup pour votre aide et merci d'y passer du temps quand vous le pouvez.

Voici les tables

Code:

1
2
3
4
5
6
7
8
9
10
CREATE TABLE [dbo].[T_DIM_GRANDES_COMPTES](
	[GrandesComptes_Id] [int] NOT NULL,
	[EntiteClienteFille_Id] [int] NOT NULL,
	[EntiteClienteMere_Id] [int] NOT NULL,
)
 
CREATE TABLE [dbo].[T_DIM_ENTITES_CLIENTES](
	[EntiteCliente_Id] [int] NOT NULL,
	[RaisonSociale] [nvarchar](100) NOT NULL,
)

Comme données, on peut utiliser :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
INSERT INTO T_DIM_GRANDES_COMPTES VALUES(1, 100, 100)
INSERT INTO T_DIM_GRANDES_COMPTES VALUES(2, 101, 100)
INSERT INTO T_DIM_GRANDES_COMPTES VALUES(3, 102, 100)
INSERT INTO T_DIM_GRANDES_COMPTES VALUES(4, 103, 101)
INSERT INTO T_DIM_GRANDES_COMPTES VALUES(5, 104, 101)
INSERT INTO T_DIM_GRANDES_COMPTES VALUES(6, 105, 102)
INSERT INTO T_DIM_GRANDES_COMPTES VALUES(7, 106, 102)
INSERT INTO T_DIM_GRANDES_COMPTES VALUES(10, 110, 130)
 
INSERT INTO T_DIM_ENTITES_CLIENTES VALUES(100, 'client100')
INSERT INTO T_DIM_ENTITES_CLIENTES VALUES(101, 'client101')
INSERT INTO T_DIM_ENTITES_CLIENTES VALUES(102, 'client102')
INSERT INTO T_DIM_ENTITES_CLIENTES VALUES(103, 'client103')
INSERT INTO T_DIM_ENTITES_CLIENTES VALUES(104, 'client104')
INSERT INTO T_DIM_ENTITES_CLIENTES VALUES(105, 'client105')
INSERT INTO T_DIM_ENTITES_CLIENTES VALUES(106, 'client106')
INSERT INTO T_DIM_ENTITES_CLIENTES VALUES(110, 'client110')

Ce que je souhaites obtenir avec le paramètre EntiteClienteMere_Id = 100 :
EntiteMere, EntiteFille, Niveau
100, 100, 0
100, 101, 1
100, 102, 1
100, 103, 2
100, 104, 2
100, 105, 2
100, 106, 2

je souhaites donc obtenir l'ensemble des EntiteClienteFille_Id qui sont en dessous de EntiteClienteMere_Id que j'aurais donné en paramètre.
L'entiteCliente_Id = 110 ne doit donc pas apparaitre.

Merci pour votre aide.

Bonjour à tous, il semble que j'ai trouvé la solution à ma requête avec le code suivant

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
DECLARE @IdClient VARCHAR(MAX)
SET @IdClient = '177069, 331919';
 
WITH Tree (
	EntiteClienteMere_Id
	, EntiteCliente_Id)                   
AS (
	SELECT 
		cl.Item --gc.EntiteClienteMere_Id
		,ec.EntiteCliente_Id
	FROM 
		T_NRM_ENTITES_CLIENTES ec WITH (NOLOCK)
		 JOIN T_NRM_GRANDES_COMPTES gc WITH (NOLOCK) 
			ON ec.EntiteCliente_Id = gc.EntiteClienteFille_Id 
		 JOIN (SELECT Item FROM fctSplitToVarChar(@IdClient, ',')) cl
			ON cl.Item = gc.EntiteClienteMere_Id
 
	UNION ALL
 
	SELECT 
		cl.Item --gc.EntiteClienteMere_Id
		, ec.EntiteCliente_Id
	FROM 
		T_NRM_ENTITES_CLIENTES ec WITH (NOLOCK)		 
		 JOIN T_NRM_GRANDES_COMPTES gc WITH (NOLOCK) 
			ON ec.EntiteCliente_Id = gc.EntiteClienteFille_Id 
			AND gc.DateSuppression IS NULL
		 JOIN (SELECT Item FROM fctSplitToVarChar(@IdClient, ',')) cl
			ON cl.Item != gc.EntiteClienteMere_Id
		 JOIN Tree t ON t.EntiteCliente_Id = gc.EntiteClienteMere_Id
	WHERE 
		gc.EntiteClienteMere_Id <> gc.EntiteClienteFille_Id
)
 
 
SELECT 
	t.EntiteClienteMere_Id
	, t.EntiteCliente_Id
FROM Tree t

La fonction "fctSplitToVarChar" permettant de gérer le cas de l'envoi de plusieurs @IdClient dans la variable.

Merci à tous pour votre aide.