Objets avec les mêmes composants [Résolu]

[meryazer]

Bonjour tout le monde,

j'ai une table nommée "type_piece" :

type  piece
----- -----
4012  p1
4012  p2
4013  p2
4111  p1
4111  p2

La requête suivante m'affiche les types qui ont des pièces communes avec le "4012", alors que je souhaite récupérer par exemple tous les types qui ont exactement les mêmes pièces que le type "4012" : (càd 4012 et 4111)

Code :

1
2
3
4
5
6
7
8
9
10
11
  SELECT type.TEV_C_EVOP
       , type.TEV_L_EVOP
    FROM P_TYPE_EVOP_TC type
       , P_PIECES_EVOP_TC piece
   WHERE type.TEV_C_EVOP = piece.TEV_TEV_C_EVOP
     AND piece.TDO_TDO_C_TYP_DOC IN (SELECT TDO_TDO_C_TYP_DOC
                                       FROM P_PIECES_EVOP_TC
                                      WHERE TEV_TEV_C_EVOP = 4012)
     AND type.PROC_PROC_CODE = 3
GROUP BY type.TEV_C_EVOP
       , type.TEV_L_EVOP;

Merci d'avance.

[laurentschneider]

enfantin

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH type_piece AS (
SELECT 4012 type,  'p1' piece FROM dual union ALL
SELECT 4012,  'p2' FROM dual union ALL
SELECT 4013,  'p2' FROM dual union ALL
SELECT 4111,  'p1' FROM dual union ALL
SELECT 4111,  'p2' FROM dual)
SELECT type,collect(piece) pieces
FROM type_piece
GROUP BY type
HAVING collect(piece) = (SELECT collect(piece) FROM type_piece WHERE type=4012)
 
 TYPE PIECES
----- ------------------------------------------------------------
 4012 SYSTPoxbnTMPboOTgQ59n0o2g5A==('p1', 'p2')
 4111 SYSTPoxbnTMPboOTgQ59n0o2g5A==('p1', 'p2')

[meryazer]

Merci laurent pour ta réponse rapide
je sais que c'est enfantin , mais je viens tout juste de commencer

j'ai donné un simple exemple de données pour me faire comprendre, ma table est déjà remplie,
c'est quoi "collect"?

merci

[mnitu]

Sur la même idée avec ListAgg

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
mni@DIANA> r
  1  WITH type_piece AS (
  2  SELECT 4012 type,  'p1' piece FROM dual union ALL
  3  SELECT 4012,  'p2' FROM dual union ALL
  4  SELECT 4013,  'p2' FROM dual union ALL
  5  SELECT 4111,  'p1' FROM dual union ALL
  6  SELECT 4111,  'p2' FROM dual)
  7  SELECT type, LISTAGG(piece, ';') WITHIN GROUP (ORDER BY piece) pieces
  8  FROM type_piece
  9  GROUP BY type
 10  HAVING LISTAGG(piece, ';') WITHIN GROUP (ORDER BY piece)
 11    = (SELECT LISTAGG(piece, ';') WITHIN GROUP (ORDER BY piece) pieces
 12         FROM type_piece
 13*       WHERE type=4012)
 
      TYPE PIECES
---------- ------------------------------
      4012 p1;p2
      4111 p1;p2

[laurentschneider]

Envoyé par meryazer

c'est enfantin
...
c'est quoi "collect"?

c'était ironique, COLLECT est quasi inconnu et extrêment rarement employé (je ne l'ai encore jamais vu). C'est aussi apparu en 10g.

c'est du code objet, ça retourne une collection d'un type temporaire. Il existe un certain nombre de fonctions / opérateurs / conditions obscures, style IS A SET, MEMBER OF, SUBMULTISET, etc...

COLLECT a pas mal de bugs et n'est pas trop rapide. Mais ton exemple est vraiment un des très rares cas où COLLECT pourrait se révéler utile :-) En fait c'est exactement l'exemple que j'avais choisi dans mon livre, mais finalement je ne l'ai pas publié car je n'ai pas couvert les TYPES Oracle.

Sinon il faut faire des NOT EXISTS pour être sûr qu'aucune pièce n'existe que dans un des set.

[pacmann]

Ho bien joué, ça compare vraiment des ensembles !

J'avais fait un truc comme ça avec GROUP_CONCAT sous MySQL (comme la solution LISTAGG de Mnitu) et ça avait divisé le temps d'exécution par 100... mais conceptuellement ça me dérangeait un peu à cause des trucs du genre JAM BON et JAMBON... (même si le risque est limité par le fait qu'on puisse choisir un délimiteur qui ait peu de chances d'apparaître dans les valeurs).
(Du coup je pensais ajouter la comparaison de la concaténation des longueurs de chaine )

[meryazer]

voici l'erreur que ça donne :

Error at line 1
ORA-00904: "COLLECT" : identificateur non valide

même chose pour "LISTAGG", je précise que je suis sur Oracle 9i,
ces fonctions sont supportées par 10g et 11g
une solution pour 9i?

merci

[Pomalaix]

Envoyé par laurentschneider

ça retourne une collection d'un type temporaire.

C'est très séduisant ce COLLECT, et effectivement d'une syntaxe simplissime, mais c'est quand même frustrant d'être obligé de bricoler pour se débarrasser du nom de constructeur qui fait partie du résultat.
C'est un peu une verrue sur le visage de Miss France !

La doc Oracle préconise de faire un CAST pour avoir un résultat fiable, donc il faut créer un type, mais l'inconvénient cosmétique persiste : il y a toujours le nom du type dans le résultat, dont il faut se débarrasser.

[skuatamad]

Peut être comme ça :

Code :

1
2
3
4
5
6
SELECT type,
       count(*) AS nb
  FROM type_piece
 WHERE piece IN (SELECT piece FROM type_piece WHERE type = 4012)
 GROUP BY type
HAVING count(*) = (SELECT count(*) FROM type_piece WHERE type = 4012)

Sinon en restant sur l'idée de base, regarde les autres techniques de String Aggregation

[pacmann]

Tu as l'horrible XMLAGG entre autres

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
WITH type_piece AS (
SELECT 4012 type,  'p1' piece FROM dual union ALL
SELECT 4012,  'p2' FROM dual union ALL
SELECT 4013,  'p2' FROM dual union ALL
SELECT 4111,  'p1' FROM dual union ALL
SELECT 4111,  'p2' FROM dual)
SELECT type, RTRIM(REPLACE(REPLACE(XMLAgg(XMLElement("x", piece ) ORDER BY piece), '<x>', ' '), '</x>', ','), ',') pieces
FROM type_piece
GROUP BY type
HAVING RTRIM(REPLACE(REPLACE(XMLAgg(XMLElement("x", piece ) ORDER BY piece), '<x>', ' '), '</x>', ','), ',') 
= (SELECT RTRIM(REPLACE(REPLACE(XMLAgg(XMLElement("x", piece ) ORDER BY piece), '<x>', ' '), '</x>', ','), ',') pieces
FROM type_piece
WHERE type=4012)

[laurentschneider]

Envoyé par Pomalaix

C'est très séduisant ce COLLECT, et effectivement d'une syntaxe simplissime, mais c'est quand même frustrant d'être obligé de bricoler pour se débarrasser du nom de constructeur qui fait partie du résultat.
C'est un peu une verrue sur le visage de Miss France !

La doc Oracle préconise de faire un CAST pour avoir un résultat fiable, donc il faut créer un type, mais l'inconvénient cosmétique persiste : il y a toujours le nom du type dans le résultat, dont il faut se débarrasser.

Pour la verrue, on peut toujours utiliser une fonction TABLE...

Code :

1
2
3
4
CREATE type piece_t AS object(piece varchar2(5))
/
CREATE type piece_coll AS TABLE of piece_t
/

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH type_piece AS (
SELECT 4012 type,  'p1' piece FROM dual union ALL
SELECT 4012,  'p2' FROM dual union ALL
SELECT 4013,  'p2' FROM dual union ALL
SELECT 4111,  'p1' FROM dual union ALL
SELECT 4111,  'p2' FROM dual),
t AS (
SELECT type,cast(collect(piece_t(piece)) AS piece_coll) p
FROM type_piece
GROUP BY type
HAVING collect(piece) = (SELECT collect(piece) FROM type_piece WHERE type=4012)
)
SELECT type, piece FROM t,TABLE(t.p)

Code :

1
2
3
4
5
6
      TYPE PIECE
---------- -----
      4012 p1   
      4012 p2   
      4111 p1   
      4111 p2

[laurentschneider]

Envoyé par meryazer

voici l'erreur que ça donne :




même chose pour "LISTAGG", je précise que je suis sur Oracle 9i,
ces fonctions sont supportées par 10g et 11g
une solution pour 9i?

merci

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT * FROM type_piece t1
 WHERE     NOT EXISTS
              (SELECT *
                 FROM type_piece t2
                WHERE     t2.TYPE = t1.TYPE
                      AND t2.piece NOT IN (SELECT t3.piece
                                             FROM type_piece t3
                                            WHERE t3.TYPE = 4012))
       AND NOT EXISTS
              (SELECT *
                 FROM type_piece t3
                WHERE     t3.TYPE = 4012
                      AND t3.piece NOT IN (SELECT t2.piece
                                             FROM type_piece t2
                                            WHERE t2.TYPE = t1.TYPE))

[meryazer]

Merci beaucoup