Discussion :

[gigigao]

Bonjour à tous,

Alors voilà mon problème, j'ai une table de 2 colonnes.

Code :

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

1
2
3
4
5
6
7
8
9
 
 ----------------------------
|   Colonne 1    |  colonne 2  |
 ----------------------------
|   A               |   1             |
|   A               |   2             |
|   B               |   1             |
|   B               |   3             |
 ----------------------------

Je veux que à chaque valeur distincte de la première colonne la colonne 2 soit transformé en ligne

Code :

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

1
2
3
4
5
6
7
 
 ----------------------------
|   Colonne 1    |  colonne 2  |
 ----------------------------
|   A               |   1;2          |
|   B               |   1;3          |
 ----------------------------

Popur celà j'ai trouvé sur internet un ensemble de fonction:
- concat_all
- sort_string

associé à 2 types
- concat_expr
- concat_all_ot

cette ensemble de fonction s'utilise de la fonction suivante.
1) concat_all = concatène toute les valeurs de la colonne2
2) sort_string = tri et élimine les doublons

dans l'exemple donné il n'y a pas de doublons mais dans le vrai cas si et je ne peux faire autrement (c'est un postulat).

bien que le code soit du chinois pour moi, il marche très bien
et je n'avais aucun souci.

Seulement je me suis trouvé à concaténer une colonne qui au cours de la concaténation retournait une variable de longueur supérieur à 4000.
Comme la fonction concat_all utilise des VARCHAR2, le programme plante avant d'avoir pu supprimer les doublons(sort_string) et insérer les valeurs dans la table.


Donc ma question est comment concaténer une colonne qui renvoie une chaine de longueur > 4000 avant la suppression des doublon?

[McM]

Il suffit de modifier la fonction concat_all pour éliminer les doublons directement.
Donne le code de CONCAT_ALL

PS : Les déclarations max de VARCHAR2
en SQL : VARCHAR2(4000)
en PL/SQL : VARCHAR2(32767)

[gigigao]

je vais aller modifier les valeurs définies pour le vachar2. je vous fais un retour.

ces fonctions s'utilisent comme ça:
concat_all(concat_expr(colonne2,';'))

alors les sources :
attention le chinois

concat_all

Code :

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

1
2
3
4
 
CREATE OR REPLACE FUNCTION Concat_All ( ctx IN concat_expr)
  RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE
  AGGREGATE USING concat_all_ot;

utilise le type concat_expr
spec:

Code :

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

1
2
3
4
5
 
CREATE OR REPLACE TYPE concat_expr AS OBJECT (
  str VARCHAR2 (4000),
  del VARCHAR2 (4000),
  MAP MEMBER FUNCTION mapping_function RETURN VARCHAR2);

body:

Code :

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

1
2
3
4
5
6
7
8
9
 
CREATE OR REPLACE TYPE BODY concat_expr AS
  MAP MEMBER FUNCTION mapping_function
  RETURN VARCHAR2
  IS
  BEGIN
    RETURN str||del;
  END mapping_function;
END;

utilise le type concat_all_ot
spec:

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
 
CREATE OR REPLACE TYPE concat_all_ot AS OBJECT (
  str VARCHAR2 (4000),
  del VARCHAR2 (4000),
 
  STATIC FUNCTION odciaggregateinitialize (
    sctx IN OUT concat_all_ot)
    RETURN NUMBER,
 
  MEMBER FUNCTION odciaggregateiterate (
    SELF IN OUT concat_all_ot,
    ctx IN concat_expr)
    RETURN NUMBER,
 
  MEMBER FUNCTION odciaggregateterminate (
    SELF IN concat_all_ot,
    returnvalue OUT VARCHAR2,
    flags IN NUMBER)
    RETURN NUMBER,
 
  MEMBER FUNCTION odciaggregatemerge (
    SELF IN OUT concat_all_ot,
    ctx2 concat_all_ot)
    RETURN NUMBER);

body:

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
38
39
40
41
42
43
44
45
46
47
48
49
 
CREATE OR REPLACE TYPE BODY concat_all_ot
AS
  STATIC FUNCTION odciaggregateinitialize (
    sctx IN OUT concat_all_ot)
    RETURN NUMBER
  IS
  BEGIN
    sctx := concat_all_ot (NULL, NULL);
    RETURN odciconst.success;
  END;
 
  MEMBER FUNCTION odciaggregateiterate (
    SELF IN OUT concat_all_ot,
    ctx IN concat_expr)
    RETURN NUMBER
  IS
  BEGIN
    IF SELF.str IS NOT NULL THEN
      SELF.str := SELF.str || ctx.del;
    END IF;
    SELF.str := TO_CHAR(SELF.str) || TO_CHAR(ctx.str);
    RETURN odciconst.success;
  END;
 
  MEMBER FUNCTION odciaggregateterminate (
    SELF IN concat_all_ot,
    returnvalue OUT VARCHAR2,
    flags IN NUMBER)
    RETURN NUMBER
  IS
  BEGIN
    returnvalue := SELF.str;
    RETURN odciconst.success;
  END;
 
  MEMBER FUNCTION odciaggregatemerge (
    SELF IN OUT concat_all_ot,
    ctx2 IN concat_all_ot)
    RETURN NUMBER
  IS
  BEGIN
    IF SELF.str IS NOT NULL THEN
      SELF.str := SELF.str || SELF.del;
    END IF;
    SELF.str := SELF.str || ctx2.str;
    RETURN odciconst.success;
  END;
END;

voili voilou

merci beaucoup

[gigigao]

ALors:

J'ai modifié la taille des VARCHAR2 dans les spec de
concat_expr et concat_all_ot

mais j'ai un problème:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
MEMBER FUNCTION odciaggregateterminate (
    SELF IN concat_all_ot,
    returnvalue OUT VARCHAR2,
    flags IN NUMBER)
    RETURN NUMBER
  IS
  BEGIN
    returnvalue := SELF.str;
    RETURN odciconst.success;
  END;

returnvalue est déclaré en varchar2 et n'accepte que 4000 caractères.
je ne sais comment changer la valeur.
A la place de varchar2 j'ai mis string mais ça n'a rien changé.

[McM]

Je t'avouerai que je ne comprend pas tout...
Bon, on peut faire ça à l'ancienne :

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
CREATE FUNCTION f_concat (p_col1 IN VARCHAR2, p_sep IN VARCHAR2)
RETURN VARCHAR2
DETERMINISTIC 
IS
	CURSOR c IS
	SELECT DISTINCT col2
	FROM MATABLE
	WHERE col1 = p_col1;
	v_retour VARCHAR2(4000);
BEGIN
	FOR r IN c
	LOOP
		v_retour := v_retour || p_sep || r.col2; 
	END LOOP;
RETURN SUBSTR(v_retour, NVL(LENGTH(p_sep),0)+1);	-- Pour supprimer le premier separateur.
END;

et tu l'appelles comme ceci

Code :

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

1
2
3
SELECT col1, f_concat(col1,';') as colonne2
FROM MATABLE
GROUP BY col1

[gigigao]

Merci pour la fonction je l'utiliserai en dernier recours
parce que là tel qu'est mon code je ne peux pas.

La concaténation ne s'appuie pas sur une table mais sur une énorme requête
et sert à faire un insert dans une autre table.

donc à mois de faire l'insert dans cette table
puis la concaténation puis la suppression des lignes ayant servi à la concaténation, je ne peux me servir de la requête.
Et j'aimerai éviter autant que possible ces étapes.

A quoi sert le paramètre deterministic??