[SQL2K5]les requête recursives [Fait] [Résolu]

[Jinroh77]

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.

[ylarvor]

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

[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.

[ylarvor]

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

[Jinroh77]

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.

[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)

[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

[ylarvor]

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.
"

(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

[Jinroh77]

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)

[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 +

[Jinroh77]

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.

[Jinroh77]

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.