Parcours de nomenclature et calcul des qtés

**coyote90** · 22/03/2007, 11h16

Bonjour,

j'utilise la requête suivante pour parcourir une nomenclature.

SELECT LPAD (To_Char (Level), 3, '0') || '.' || LPAD(' ', 2*Level) || Composant , quantite
FROM Nomenclatures
WHERE COMPOSANT LIKE '%'
CONNECT BY (PRIOR Composant = Compose)
START WITH Compose = '&code_article_complet';

comment faire pour afficher la qté totale en prenant en compte les qté de chaque niveau ?

exemple :
niv 1 - qté niv = 1 - qte besoin = 1
niv 2 - qté niv = 2 - qte besoin = 2
niv 3 - qté niv = 1 - qte besoin = 2 << je veux la quantité du besoin

merci d'avance.

**laurentschneider** · 22/03/2007, 11h44

la question n'est pas très claire, dans un exemple de valeur et surtout n'oublie pas de préciser la version

**laurentschneider** · 22/03/2007, 11h44

Code :

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

WHERE COMPOSANT LIKE '%'

c'est sensé faire quoi?

**coyote90** · 22/03/2007, 11h52

sur la requête initiale, je filtre sur un type d'article...
pour le débug, j'ai laissé % pour afficher toute la nomenclature...

Oracle 8i release 8.1.7.0.0
SQL Plus : 8.0.6.0.0

**McM** · 22/03/2007, 12h15

Il nous faudrait un pt'i jeu d'essai stp.

**coyote90** · 22/03/2007, 13h17

voici un exemple avec le niveau, le code article et la qté du niveau:

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
000 PRODUIT_FINI_001   Qté niv = 1
001   T08686  Qté niv =  .93 
001   T08692  Qté niv =  .31 
001   O#O39  Qté niv =  1 
002     90-401_#P499  Qté niv =  1 
002     90-401_#P500  Qté niv =  1 
002     E01-110/85.5  Qté niv =  4 
003       110_00_6000  Qté niv =  .0154 
002     E01-92/43  Qté niv =  4 
003       92_00_6700  Qté niv =  .0073 
002     E63-20/10  Qté niv =  4 
003       20_00_6700  Qté niv =  .00229 
002     EXE001  Qté niv =  8 
002     GOU88EP  Qté niv =  16 
002     SPE-B1  Qté niv =  1 
002     SPE-B1X  Qté niv =  1 
002     SPE-E1  Qté niv =  2 
001   R#R117  Qté niv =  2               
002     422_#P42  Qté niv =  2        qté besoin devra être = 4 (qté produit fini * qté R#R117 * Qté 422_#P42 )
002     422_#P44  Qté niv =  2        qté besoin devra être = 4
001   R#R122  Qté niv =  4 
002     IVR100  Qté niv =  .154575 
002     P110  Qté niv =  5            qté besoin devra être = 20
002     IP171  Qté niv =  1           qté besoin devra être = 4 
002     VIS4.8*13  Qté niv =  12      qté besoin devra être = 48


TABLE "NOMENCLATURE":
COMPOSANT    NOT NULL VARCHAR2(40)
COMPOSE      NOT NULL VARCHAR2(40)
QUANTITE     NUMBER

**salim11** · 22/03/2007, 13h52

Bonjour,

Tu peux nous donner le contenu de ta table Nomenclatures
?

**McM** · 22/03/2007, 14h04

Si j'ai bien compris :

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
CREATE TABLE wmc_NOMENCLATURE
(COMPOSANT    VARCHAR2(40),
COMPOSE      VARCHAR2(40),
QUANTITE     NUMBER
)
 
 
INSERT INTO wmc_Nomenclature(COMPOSANT, COMPOSE, QUANTITE)
VALUES ('PRODUIT_FINI_001', 'ART1', 1);
 
INSERT INTO wmc_Nomenclature(COMPOSANT, COMPOSE, QUANTITE)
VALUES ('R122', 'PRODUIT_FINI_001', 4);
 
INSERT INTO wmc_Nomenclature(COMPOSANT, COMPOSE, QUANTITE)
VALUES ('P110', 'R122', 4);
 
INSERT INTO wmc_Nomenclature(COMPOSANT, COMPOSE, QUANTITE)
VALUES ('IP171', 'R122', 5);
 
INSERT INTO wmc_Nomenclature(COMPOSANT, COMPOSE, QUANTITE)
VALUES ('VIS4', 'R122', 12);
 
INSERT INTO wmc_Nomenclature(COMPOSANT, COMPOSE, QUANTITE)
VALUES ('R000', 'PRODUIT_FINI_001', 1);
 
INSERT INTO wmc_Nomenclature(COMPOSANT, COMPOSE, QUANTITE)
VALUES ('P110', 'R000', 4);
 
INSERT INTO wmc_Nomenclature(COMPOSANT, COMPOSE, QUANTITE)
VALUES ('IP171', 'R000', 5);
 
INSERT INTO wmc_Nomenclature(COMPOSANT, COMPOSE, QUANTITE)
VALUES ('VIS4', 'R000', 12);

Code :

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

1
2
3
4
5
6
7
	SELECT 	LEVEL AS niv, 
			LPAD(' ', 2*LEVEL, '+ ') || Composant AS compo, 
			quantite
	FROM WMC_NOMENCLATURE
	WHERE COMPOSANT LIKE '%' 
	CONNECT BY (PRIOR Composant = Compose)
	START WITH Compose = 'ART1'

NIV COMPO QUANTITE [souhait]
1 + PRODUIT_FINI_001 1 [ 1 ]
2 + + R122 4 [ 1 x 4 (qte lev-1 x qte lev)]
3 + + + P110 4 [ 1 x 4 x 4 = 16]
3 + + + IP171 5 [ 1 x 4 x 5 = 20]
3 + + + VIS4 12 [ 1 x 4 x 12 = 48 ]
2 + + R000 1 [ 1 x 1 ]
3 + + + P110 4 [ 1 x 1 x 4 = 4]
3 + + + IP171 5 [ 1 x 1 x 5 = 5]
3 + + + VIS4 12 [ 1 x 1 x 12 = 12]

Donc faire une Somme des qte des level pour arriver à ce point.
Sans curseur j'aurais du mal, j'ai regardé avec les fcts analytiques.. sans succès

**laurentschneider** · 22/03/2007, 14h07

dans Oracle8, il te faudra écrire une fonction en plsql qui remonte les noeuds

éventuellement (non testé en 8i)

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
 
alter session set "_new_connect_by_enabled"=TRUE; 
 
create or replace function eval(s varchar2) 
    return number is i number;
    begin execute immediate 'select '||s||' from dual' into i;
    return i; end;
/
 
select to_char(level,'FM009'),ENAME,1||sys_connect_by_path(sal,'*') p, 
  eval(1||sys_connect_by_path(sal,'*')) besoin 
from emp 
connect by prior empno = mgr 
start with ename='KING';
 
TO_C ENAME      P                                  BESOIN
---- ---------- ------------------------- ---------------
001  KING       1*5000                               5000
002  JONES      1*5000*2975                      14875000
003  SCOTT      1*5000*2975*3000              44625000000
004  ADAMS      1*5000*2975*3000*1100      49087500000000
003  FORD       1*5000*2975*3000              44625000000
004  SMITH      1*5000*2975*3000*800       35700000000000
002  BLAKE      1*5000*2850                      14250000
003  ALLEN      1*5000*2850*1600              22800000000
003  WARD       1*5000*2850*1250              17812500000
003  MARTIN     1*5000*2850*1250              17812500000
003  TURNER     1*5000*2850*1500              21375000000
003  JAMES      1*5000*2850*950               13537500000
002  CLARK      1*5000*2450                      12250000
003  MILLER     1*5000*2450*1300              15925000000

En 10g, c'est plus simple avec l'opérateur CONNECT_BY_ROOT

**laurentschneider** · 22/03/2007, 14h14

avec le create table de mcm ça donne

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
 
SQL> r
  1  select to_char(level,'FM009') niveau,
  2  LPAD(' ', 2*LEVEL)||Composant compos,
  3  quantite,
  4  eval(1||sys_connect_by_path(quantite,'*')) besoin
  5  FROM WMC_NOMENCLATURE
  6  CONNECT BY PRIOR Composant = Compose
  7* START WITH Compose = 'ART1'
NIVE COMPOS                 QUANTITE          BESOIN
---- -------------------- ---------- ---------------
001    PRODUIT_FINI_001            1               1
002      R000                      1               1
003        IP171                   5               5
003        P110                    4               4
003        VIS4                   12              12
002      R122                      4               4
003        IP171                   5              20
003        P110                    4              16
003        VIS4                   12              48

**coyote90** · 22/03/2007, 15h49

Merci pour votre aide, je vais tester...

**salim11** · 22/03/2007, 17h13

Bonjour,

Voici la solution au problème sans utiliser la foction

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
35
 
/* Formatted on 2007/03/22 12:11 (Formatter Plus v4.8.8) */
SELECT     *
      FROM (SELECT niv,
                   LPAD (' ', 2 * niv, '+ ') || nomenclature.composant compos,
                   nomenclature.composant, nomenclature.COMPOSE,
                   nomenclature.quantite * quantite_sup besoin_qte
              FROM (SELECT DISTINCT LEVEL niv, composant, COMPOSE, quantite
                               FROM nomenclature
                              WHERE composant LIKE '%'
                         CONNECT BY PRIOR composant = COMPOSE
                         START WITH COMPOSE = 'ART1') nomenclature,
                   (SELECT DISTINCT LEVEL niv1, quantite quantite_sup,
                                    composant, COMPOSE
                               FROM nomenclature
                              WHERE composant LIKE '%'
                                AND COMPOSE = nomenclature.COMPOSE
                         CONNECT BY (PRIOR nomenclature.COMPOSE =
                                                        nomenclature.composant
                                    )
                         START WITH nomenclature.composant = composant) nom
             WHERE nom.composant = nomenclature.COMPOSE
               AND nom.composant LIKE '%'
            UNION
            SELECT     LEVEL niv,
                          LPAD (' ', 2 * LEVEL, '+ ')
                       || nomenclature.composant compos,
                       composant, COMPOSE, quantite
                  FROM nomenclature
                 WHERE composant LIKE '%' AND LEVEL = 1
            CONNECT BY PRIOR composant = COMPOSE
            START WITH COMPOSE = 'ART1')
     WHERE composant LIKE '%'
CONNECT BY PRIOR composant = COMPOSE
START WITH COMPOSE = 'ART1'

**salim11** · 22/03/2007, 17h19

Envoyé par salim11

Bonjour,

Voici la solution au problème sans utiliser la foction

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
35
36
37
 
/* Formatted on 2007/03/22 12:11 (Formatter Plus v4.8.8) */
SELECT     *
      FROM /* selectionner l'aroborescence descendante père vers le fils */
                  (SELECT niv,
                   LPAD (' ', 2 * niv, '+ ') || nomenclature.composant compos,
                   nomenclature.composant, nomenclature.COMPOSE,
                   nomenclature.quantite * quantite_sup besoin_qte
              FROM (SELECT DISTINCT LEVEL niv, composant, COMPOSE, quantite
                               FROM nomenclature
                              WHERE composant LIKE '%'
                         CONNECT BY PRIOR composant = COMPOSE
                         START WITH COMPOSE = 'ART1') nomenclature,
/* selectionner l'aroborescence ascendante des fils vers le père*/
                   (SELECT DISTINCT LEVEL niv1, quantite quantite_sup,
                                    composant, COMPOSE
                               FROM nomenclature
                              WHERE composant LIKE '%'
                                AND COMPOSE = nomenclature.COMPOSE
                         CONNECT BY (PRIOR nomenclature.COMPOSE =
                                                        nomenclature.composant
                                    )
                         START WITH nomenclature.composant = composant) nom
             WHERE nom.composant = nomenclature.COMPOSE
               AND nom.composant LIKE '%'
            UNION/* pour afficher le père */
            SELECT     LEVEL niv,
                          LPAD (' ', 2 * LEVEL, '+ ')
                       || nomenclature.composant compos,
                       composant, COMPOSE, quantite
                  FROM nomenclature
                 WHERE composant LIKE '%' AND LEVEL = 1
            CONNECT BY PRIOR composant = COMPOSE
            START WITH COMPOSE = 'ART1')
     WHERE composant LIKE '%'
CONNECT BY PRIOR composant = COMPOSE
START WITH COMPOSE = 'ART1'