Boucle sur CTE

**boutinj** · 29/10/2009, 16h50

Bonjour

Je voudrais arriver a placer une variable dans mon CTE qui soit issue d'une boucle d'un autre SELECT

ex:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Debut boucle
   SELECT @MaVariable = Prod
   FROM Produit
 
   With CTE (.......)
   as
   (
   Select .....
   from nomenclature
   where Prod=@MaVariable
   union all
   .......
   )
  Select ....
   into Table
  from CTE
 
Fin boucle

J'ai vu qu'on pouvait faire des While mais je trouve pas la syntaxe dans ce cas précis.

Je suis preneur de toutes les solutions.

Merci d'avance

**elsuket** · 29/10/2009, 18h50

Bonjour,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT @MaVariable = Prod
FROM Produit

A votre avis quelle va être la valeur de la variable @MaVariable ?
Vous ne pouvez pas vous permettre de rechercher une valeur de la sorte, sans clause WHERE, sinon vous faites cela au hasard

Les expressions de table commune sont justement là pour ne plus avoir besoin d'utiliser un WHILE ou bien un curseur.
Donnez-nous le code complet avec la définition des tables, pour que nous écrivions tout cela en une seule requête

@++

**boutinj** · 30/10/2009, 08h45

Voici mon code pour le CTE qui marche parfaitement pour l'instant

Mon but est de faire une boucle sur ma variable afin que je passe toutes les ref situé dans une autre table. Je veux eviter les curseurs.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

declare @ref char(40)
set @ref = '6121823100';

WITH DirectReports(parnt, child,psreq, psopc, niv) AS 
(
    SELECT parnt, child, psreq, psopc, 0 as niv
    FROM pspsp100
    WHERE parnt = @ref
	and psddt = '31/12/2039'
	and (psopc is null or psopc = 'P')

    UNION ALL

    SELECT e.parnt, e.child, e.psreq, e.psopc, niv+1
    FROM pspsp100 e
        INNER JOIN DirectReports d
        ON e.parnt = d.child
	Where e.psddt = '31/12/2039'
	and (e.psopc is null or e.psopc = 'P')
	
	
)

insert into test
SELECT parnt, article_1.descp as dexcp_pere, child,article.descp as descp_fils,  psreq, article.ittyp, psopc, niv
FROM DirectReports 
INNER JOIN
ARTICLE
on DirectReports.child = article.prdno
INNER Join ARTICLE as Article_1
on DirectReports.parnt = Article_1.prdno
where (psopc is null or psopc = 'P')

au lieu de fixer en dur ma variable je voudrais qu'elle soit issue d'un

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Select prdno from article

et que je passe donc chacun des article dans le CTE

Merci de votre aide

**Waldar** · 30/10/2009, 11h35

Pourquoi ne pas faire une jointure tout simplement :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
WITH DirectReports (parnt, child,psreq, psopc, niv) AS 
(
    SELECT p.parnt, p.child, p.psreq, p.psopc, 0 as niv
      FROM dbo.pspsp100 AS p
           INNER JOIN dbo.article AS a
             ON a.prdno = p.parnt
     WHERE p.psddt = cast('31/12/2039' as datetime)
       AND (p.psopc is null or p.psopc = 'P')
...

**boutinj** · 30/10/2009, 13h23

Merci
Par contre ça ne fonctionne pas à cause d'un souci

Il faudrait que j'arrive à mettre dans mon INSERT en 1ere colonne la ref sur laquelle la CTE tourne
car sinon je n'ai pas l'ensemble de ma nomemclature mais que le 1er niveau.

Si j'arrive à faire ça le système de la jointure fonctionnera impec.

Merci encore

**Waldar** · 30/10/2009, 14h19

Rajoutez-là dans la CTE directement :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH DirectReports (prdno, parnt, child, psreq, psopc, niv) AS 
(
SELECT a.prdno, p.parnt, p.child, p.psreq, p.psopc, 0
  FROM dbo.pspsp100 AS p
       INNER JOIN dbo.article AS a
         ON a.prdno = p.parnt
 WHERE p.psddt = cast('31/12/2039' AS datetime)
   AND (p.psopc IS NULL OR p.psopc = 'P')
 
UNION ALL
 
SELECT d.prdno, e.parnt, e.child, e.psreq, e.psopc, d.niv + 1
  FROM dbo.pspsp100 AS e
       INNER JOIN DirectReports AS d
         ON e.parnt = d.child
 WHERE e.psddt = cast('31/12/2039' AS datetime)
   AND (e.psopc is null or e.psopc = 'P')
)

**boutinj** · 10/11/2009, 13h59

Bonjour à tous

Je me permets de relancer mon souci car en effet aujourd'hui mon produit n'est pas invariant et je ne vois comment faire:

Création table:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
CREATE TABLE [dbo].[PRODUIT]
(
	[PROD] [nchar](10),
	[type] [nchar](10) 
) ON [PRIMARY]

Ensuite:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
 
CREATE TABLE [dbo].[nomenclature]
(
	[pere] [nchar](10) ,
	[fils] [nchar](10) ,
	[qte] [int] NULL
)

Ensuite l'insert des valeurs

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
insert into produit values ('PROD111', 'Fab')
insert into produit values ('PROD112', 'Ach')
insert into produit values ('PROD113', 'Fab')
insert into produit values ('PROD114', 'Ach')

puis des nomclatures:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
insert into nomenclature values ('PROD111','PROD112',1)
insert into nomenclature values ('PROD111','PROD113',2)
insert into nomenclature values ('PROD113','PROD114',2)

Le champ QTE ne sert à rien pour l'instant.

Avec cette CTE :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
WITH DirectReports (Prod, pere, fils, QTE) AS 
(
SELECT a.prod, p.pere, p.fils, p.qte
  FROM dbo.nomenclature AS p
       INNER JOIN dbo.produit AS a
         ON a.prod = p.pere
 
 
UNION ALL
 
SELECT d.prod, e.pere, e.fils, e.qte
FROM dbo.nomenclature AS e
       INNER JOIN DirectReports AS d
         ON e.pere = d.fils
)
 
SELECT pere, fils, type
FROM DirectReports
INNER JOIN PRODUIT
on DirectReports.fils = PRODUIT.prod
where type = 'ach'

j'obtiens
PROD111 PROD112 Ach
PROD113 PROD114 Ach
PROD113 PROD114 Ach

alors que je voudrais avoir:
PROD111 PROD112 Ach
PROD111 PROD114 Ach

Comme ça j'ai bien la liste des Produit a acheter pour fabriquer mon PROD111
et non le détail.

Merci de votre aide

**Waldar** · 10/11/2009, 14h58

Si vous regardez bien la discution, je vous ai fait rajouter un champ dans la CTE (prod).
Mais si vous ne le sélectionnez pas, évidement ça ne sert à rien.

J'ai fait une petite modification pour que vous n'ayez pas besoin de jointure supplémentaire après la CTE, et j'ai également précisé le point d'ancrage dans la CTE :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
WITH DirectReports (prod, pere, fils, typ, qte_tot) AS 
(
SELECT p.pere, p.pere, p.fils, b.type, p.qte
  FROM dbo.nomenclature AS p
       INNER JOIN dbo.produit AS b
         ON b.prod = p.fils 
WHERE p.pere = 'PROD111'
UNION ALL
SELECT d.prod, e.pere, e.fils, a.type, e.qte + d.qte_tot
  FROM dbo.nomenclature AS e
       INNER JOIN DirectReports AS d
         ON e.pere = d.fils
       INNER JOIN dbo.produit AS a
         ON e.fils = a.prod
)
SELECT prod, pere, fils, typ, qte_tot
  FROM DirectReports
 WHERE typ = 'Ach';
 
prod       pere       fils       typ        qte_tot
---------- ---------- ---------- ---------- -----------
PROD111    PROD111    PROD112    Ach        1
PROD111    PROD113    PROD114    Ach        4

**boutinj** · 10/11/2009, 15h29

Merci mais mon probleme est que mon where est fixe alors que moi je voudrais faire une boucle sur une variable ou autre.

par exemple: J'ai un table PROD_FINI avec juste un champ PROD_FINI

et là je voudrais que mon where "boucle" dessus.

Mais Ca y est je crois que j'ai trouvé, je refais une jointure sur ma table PROD_FINI et le PERE et ça a l'air de fonctionner.

Merci à vous pour votre aide. De ce pas je me fais payer une formation sur SQL avancée avec les CTE et autres diverses fonctions car je pige pas encore tout mais c'est super arrangeant.

Merci encore.