Discussion :

[juju05]

Bonjour,

Je travaille sur de la GPAO. Je souhaite connaître la position de mon opération sur ma gamme opératoire.
Une gamme opératoire est constituée de plusieurs opérations.

J'ai donc une table des gammes et une table des opérations.
La table des gammes a pour clé unique id_gamme.
La table des opérations a pour clé unique l'id_operation et pour clé étrangère l'id_gamme. Il y a également un champ qui correspond au rang.
Je veux créer une requête sql qui pour chaque opération d'une gamme me restitue la position dans la gamme (1,2,3, ...).
Je suis sur Oracle 10g et je souhaite placer cette requête dans une vue pour l'utiliser sur CR XI.

Je vous remercie d'avance.
Bon week-end.
Julien.

[punkoff]

bonjour,

et qu'avez-vous essayé comme requête ? où bloquez-vous ?

[juju05]

Je souhaiterais que pour chaque opération sur laquelle il reste du "à travailler", les opérations suivantes apparaissent sur la même ligne.
Le raisonnement n'est pas très bon puisque potentiellement le nombre d'opérations sur la gamme opératoire est illimité et cela pourrait "déborder" de mon report.
Je ne sais pas si cela est faisable.
Ma requête SQL est la suivante :

Code :

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

1
2
3
4
5
6
7
select wo.id_wo, wo.seq,  tmp.seq
from WO_OP wo
left outer join
(select wos.id_wo, wos.seq  
from WO_OP wos) tmp
on wo.id_wo = tmp.id_wo
where wo.seq < tmp.seq

où id_wo est l'identifiant de l'ordre de fabrication, seq la séquence (position dans la gamme opératoire).
La requête ramène plusieurs lignes pour la même gamme opératoire. Je souhaiterais qu'elle ramène une ligne par opération.
Exemple : sur ma gamme, j'ai 3 phases : la 10, la 20 et la 30.
Je voudrais l'id_wo et 10, 20, 30
l'id_wo et 20,30
l'id_wo et 30.

Merci d'avance.

[punkoff]

Je ne comprend pas.

Vous utilisez des termes dans tous les sens, sans les réutiliser ni les modéliser.

Donc :

Vos deux tables :
gamme (gam_id, ...)
opérations (ope_id, gam_id, rang, ...) <= gam_id ne fait-il pas aussi partie de votre clef primaire ?


A quoi correspond le rang par rapport à "position dans la gamme" et "position dans la gamme opératoire"


A quoi correspond une "phase" par rapport aux tables décrites ?


Que viens faire "l'ordre de fabrication" là-dedans ? vous ne le mentionnez nulle part dans la description de vos tables ?

Bref, faites une présentation correcte de votre modélisation, mettez un exemple parlant de vos données en base et du résultat souhaité car là je nage complètement.

Enfin pour ceci :

La requête ramêne plusieurs ligne pour la même gamme opératoire. Je souhaiterai qu'elle ramène une ligne par opération.

Il y a des chances que vous vouliez faire un "pivot" (vous pouvez regardez en recherchant sur ce forum) et en natif sql ça n'est pas vraiment supporté sur Oracle.


A creuser donc.

[juju05]

Effectivement je présuppose que mon interlocuteur y connaisse un minimum en GPAO. Je vais essayer d'être plus clair.

Mes tables :

Ordres de fabrication : WO dont la clé primaire unique est id_wo,

Opérations (ou phases) de la gamme opératoire : WO_OP dont la clé primaire unique est id_wo_op. Elle possède une clé étrangère non-unique qui est id_wo et un champ seq qui est la séquence dans la gamme opératoire (ce qui est habituellement nommé le rang). Dans la mesure où celui-ci est modifiable, je ne peux utiliser la clé de WO_OP pour avoir les phases suivantes.

Je travaille sur Oracle 10g.

[punkoff]

On reprend,

Vous avez donc deux tables :
WO (id_wo,...)
WO_OP (id_wo_op, id_wo, seq, ...)

La relation entre les deux est
WO (0,n) ---------- (0,1) WO_OP

c'est ça ?

niveau exemple de donnée, que j'invente :

Code :

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

1
2
3
4
5
6
7
8
 
WO_OP
------------------------
id_wo_op     id_wo    seq
1                1          10
2                1          20
3                1          30
4                1          40

c'est ça ?

que voulez-vous en sortie ? ceci ?

Code :

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

1
2
3
 
id_wo          String
1                10, 20, 30, 40

[juju05]

On reprend,

Vous avez donc deux tables :
WO (id_wo,...)
WO_OP (id_wo_op, id_wo, seq, ...)

La relation entre les deux est
WO (0,n) ---------- (0,1) WO_OP

c'est ça ?

niveau exemple de donnée, que j'invente :

Code :

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

1
2
3
4
5
6
7
8
 
WO_OP
------------------------
id_wo_op     id_wo    seq
1                1          10
2                1          20
3                1          30
4                1          40

c'est ça ?

que voulez-vous en sortie ? ceci ?

Code :

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

1
2
3
 
id_wo          String
1                10, 20, 30, 40

C'est cela.
En sortie, je souhaiterai avoir

Code :

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

1
2
3
4
5
6
 
id_wo          String
1                10, 20, 30, 40
1                20, 30, 40
1                30, 40
1                40

[punkoff]

Concernant votre colonne "seq" quelles sont les valeurs possible dedans ?
est-ce qu'il y a toujours une incrémzentation de 10 ?

Est-ce celà commence toujours à 10 ?

[juju05]

La séquence peut être modifiée par l'utilisateur et l'ERP ne la recalcule pas.

[punkoff]

Le problème étant que vous cherchez à faire une concaténation de chaine sur un principe un peu spécial, et que seule une requête récursive permettra de le faire.

Maintenant si votre séquence n'a aucune suite logique sur laquelle s'appuyer, on peut s'appuyer sur 2 vue pour ce faire (peut être qu'avec une seule requête ça passe mais je n'ai pas Oracle ici).

1ere vue afin d'ordonner correctement votre séquence :

Code :

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

1
2
3
create view v_wo_op as (
select wo_id, seq, row_number() over(partition by wo_id order by seq) as rnk
from wo_op)

Ensuite requête récursive pour former le résultat :

Code :

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

1
2
3
4
5
6
7
8
9
with tmp as (
select wo_id, seq, rnk
from v_wo_op
where rnk = 1
union all
select b.wo_id, a.seq || ', ' || b.seq, b.rnk
from tmp a
inner join v_wo_op b on a.wo_id = b.wo_id and a.rnk + 1 = b.rnk)
select * from tmp

Je ne trouve pas plus simple personnellement.

Peut-être qu'une autre personne aura une idée.

[pacmann]

Salut,

C'est de la concaténation de chaîne de caractères.
Bien tenté Punkoff, mais si je ne m'abuse le WITH récursif n'est pas implémenté par Oracle avant la 11 (et je suis pas sûr de la release 1 ou 2).

En attendant, tu peux te dépatouiller avec les méthodes existantes, par exemple en liant tes lignes en requête hiérarchique :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
WITH t AS (
SELECT	1	id_wo_op,	1	id_wo, 	10	seq FROM DUAL UNION ALL
SELECT	2	id_wo_op,	1	id_wo, 	20	FROM DUAL UNION ALL
SELECT	3	id_wo_op,	1	id_wo, 	30	FROM DUAL UNION ALL
SELECT	4	id_wo_op,	1	id_wo, 	40	FROM DUAL)
SELECT id_wo, substr(sys_connect_by_path(seq, ', '), 2) 
FROM t
CONNECT BY prior id_wo = id_wo AND prior id_wo_op = id_wo_op - 1
START WITH id_wo_op = 1
ORDER BY id_wo, id_wo_op desc

Code :

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

1
2
3
4
5
6
7
8
9
10
 
     ID_WO RES                                               
---------- --------------------------------------------------
         1  10, 20, 30, 40                                   
         1  10, 20, 30                                       
         1  10, 20                                           
         1  10                                               
 
 
4 rows selected.

=> Si tu veux d'autres méthodes, tu peux chercher les tutos sur la concaténation de chaînes (d'ailleurs, je veux me faire un mémo parce que je galère toujours à retrouver les syntaxes )

[EDIT] tiens, en relisant l'énoncé, je me suis vautré sur le sens du connect... à corriger

[punkoff]

Je ne penses pas que calà va marcher.

Code :

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

1
2
 
CONNECT BY prior id_wo = id_wo AND prior id_wo_op = id_wo_op - 1

id_wo_op est une PK, ceci étant dit en reprenant la vue de mon exemple + ton code ca devrait le faire

[skuatamad]

Salut Pacman,

(d'ailleurs, je veux me faire un mémo parce que je galère toujours à retrouver les syntaxes )

Il existe déjà :
String Aggregation Techniques

Il manque juste la technique du XML.

Pour info, le WITH recursif c'est en 11GR2, et pour cette version je préfère LISTAGG qui est je trouve le plus simple.

[pacmann]

J'ai connement pris l'exemple donné sans essayer de comprendre, désolé

Sinon pour avoir les bonnes lignes, il faut garder celles dont la seq est max :

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
 
WITH t AS (
SELECT	1	id_wo_op,	1	id_wo, 	10	seq FROM DUAL UNION ALL
SELECT	2	id_wo_op,	1	id_wo, 	20	FROM DUAL UNION ALL
SELECT	3	id_wo_op,	1	id_wo, 	30	FROM DUAL UNION ALL
SELECT	4	id_wo_op,	1	id_wo, 	40	FROM DUAL)
, u AS(
SELECT id_wo, substr(sys_connect_by_path(seq, ', '), 2) res, seq  
FROM t
CONNECT BY prior id_wo = id_wo AND prior id_wo_op = id_wo_op - 1)
SELECT u.* 
FROM u 
  JOIN (SELECT id_wo, max(seq) seq
        FROM t
        GROUP BY id_wo) v ON u.id_wo = v.id_wo AND u.seq = v.seq
ORDER BY u.id_wo, res

Code :

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

1
2
3
4
5
6
7
8
9
10
 
     ID_WO RES                                                       SEQ
---------- -------------------------------------------------- ----------
         1  10, 20, 30, 40                                            40
         1  20, 30, 40                                                40
         1  30, 40                                                    40
         1  40                                                        40
 
 
4 rows selected.

Je laisse juju rajouter le row_number et connecter dessus...

[pacmann]

Hey salut Skuatamad !

Il manque juste la technique du XML.

Justement, c'est sur celle-ci que j'étais parti... et c'est un peu galère
(Par contre, j'étais très déçu de m'apercevoir que ce n'est pas compatible à l'analytique... du coup chuis parti sur autre chose)

[juju05]

J'avoue être un peu perdu.
J'ai remplacé le terme DUAL par le nom de ma table.

Que dois-je modifier d'autre ?

Comment utiliser cette requête ? Dois-je créer une vue sur SQLDEVELOPPER ?.

Merci d'avance.

[pacmann]

La vue, c'est comme tu le sens (soit tu appelles la vues ensuite, soit tu retapes à chaque fois la requête).

Concernant cette partie :

Code :

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

1
2
3
4
5
6
 
WITH t AS (
SELECT	1	id_wo_op,	1	id_wo, 	10	seq FROM DUAL UNION ALL
SELECT	2	id_wo_op,	1	id_wo, 	20	FROM DUAL UNION ALL
SELECT	3	id_wo_op,	1	id_wo, 	30	FROM DUAL UNION ALL
SELECT	4	id_wo_op,	1	id_wo, 	40	FROM DUAL)

Je l'ai constituée à partir du jeu de données que tu as fourni... si je ne me trompe pas, il résulte d'une jointure entre tes tables : à substituer.

Code :

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

1
2
3
4
5
 
u AS(
SELECT id_wo, substr(sys_connect_by_path(seq, ', '), 2) res, seq  
FROM t
CONNECT BY prior id_wo = id_wo AND prior id_wo_op = id_wo_op - 1)

Cette partie lie tes lignes entre elles : même id_wo, id_wo_op consécutifs.
Note la remarque de punkoff, et essaie d'y intégrer le ROW_NUMBER sur lequel tu pourras faire le CONNECT BY (à la place de id_wo_op)

Code :

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

1
2
3
4
 
  JOIN (SELECT id_wo, max(seq) seq
        FROM t
        GROUP BY id_wo) v ON u.id_wo = v.id_wo AND u.seq = v.seq

Ici, j'ai ajouté la jointure sur le max seq pour chaque id_wo.
En fait, tu peux tenter de lancer sans, et tu verras que toutes les arborescences possibles sont construites, alors que selon ton exemple, seulement celles contenant la dernière opération t'intéressent.