comment traduire des clefs étrangeres ',1,2,3,4' => 'texte1,text2,text3,texte4' dans un champ

**jo_la_poisse** · 20/01/2010, 22h51

Bonsoir,

Dans le cadre d'un changement de cms je dois mettre à plat des données de type collections d'attributs qui font référence à son propre référentiels de données pour les transposer dans un autre systeme.

c'est à dire je dois modifier mes valeurs de champ ',1,2,3,11,13,' par leur contenu métier 'label1,label2,label3,label11,label13,'

J'ai chercher une solution à base de decode de prior de translate et de replace mais je n'y arrive pas et il est tard ... !!

Peut être pouvez vous m'aider ?

Voici les données du probleme

ma table :

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
CREATE TABLE mytable (
 clef integer NOT NULL, 
liste_data as VARCHAR2(90));
 
/* --- mes données ---
Mon produit : 1 data: ,1,2,5,9,13,
Mon produit : 2 data: ,1,2,3,
Mon produit : 3 data: ,1,5,13,
Mon produit : 4 data: ,1,11,13,
Mon produit : 5 data: null
 
----------------------------*/
INSERT INTO  mytable (
 clef ,liste_data ) values ( 1 , ',1,2,5,9,13,') ;
 
INSERT INTO  mytable (
 clef ,liste_data ) values ( 2 , ',1,2,3,') ;
 
INSERT INTO  mytable (
 clef ,liste_data ) values ( 3 , ',1,5,13,') ;
 
 
INSERT INTO  mytable (
 clef ,liste_data ) values ( 4 , ',1,11,13,') ;
 
INSERT INTO  mytable (
 clef ,liste_data ) values ( 5 , null) ;
 
COMMIT;
 
/* ---- Mon referntiel ----
Ma table de reference 
id valeur
1 label1
2 label2
3 label3
4 label4
5 label5
9 label9
10 label10
11 label11
12 label12
13 label13
-----------------------------*/
 
CREATE TABLE myreferentiel ( id interger NOT NULL , valeur VARCHAR2(20));
 
INSERT INTO  myreferentiel (
 id ,valeur) values ( 1 , 'label1') ;
 
INSERT INTO  myreferentiel (
 id ,valeur) values ( 2 , 'label2') ;
 
INSERT INTO  myreferentiel (
 id ,valeur) values ( 3 , 'label3') ;
 
INSERT INTO  myreferentiel (
 id ,valeur) values ( 4 , 'label4') ;
 
 
INSERT INTO  myreferentiel (
 id ,valeur) values ( 5 , 'label5') ;
 
INSERT INTO  myreferentiel (
 id ,valeur) values ( 6 , 'label6') ;
 
INSERT INTO  myreferentiel (
 id ,valeur) values ( 7 , 'label7') ;
 
INSERT INTO  myreferentiel (
 id ,valeur) values ( 8 , 'label8') ;
 
 
INSERT INTO  myreferentiel (
 id ,valeur) values ( 9 , 'label9') ;
 
INSERT INTO  myreferentiel (
 id ,valeur) values ( 10 , 'label10') ;
 
INSERT INTO  myreferentiel (
 id ,valeur) values ( 11 , 'label11') ;
 
INSERT INTO  myreferentiel (
 id ,valeur) values ( 12 , 'label12') ;
 
INSERT INTO  myreferentiel (
 id ,valeur) values ( 13 , 'label13') ;
 
INSERT INTO  myreferentiel (
 id ,valeur) values ( 14 , 'label14') ;
 
COMMIT;
--------------------------------------------------------

Ce que je veux avoir c'est une copy de mytable :

Code :

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

1
2
3
4
5
6
 mytable_refactored :
 mon produit : 1  data_refactored : ',label1,label2,label5,label9,label13,'
 mon produit : 2  data_refactored : ',label1,label2,label3,'
 mon produit : 3  data_refactored : ',1,5,13,'
 mon produit : 4  data_refactored : ',1,11,13,'
 mon produit : 5  data_refactored :  null

Or la seule chose que j'ai trouvé c'est de faire un replace dans une procedure stockée

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
50
51
52
53
54
55
56
57
CREATE OR REPLACE PROCEDURE "TEST" (
   monchampatraiter              IN VARCHAR2  ,
   v_object_refrentiel           IN VARCHAR2  ,
   v_clef_referentiel            IN   VARCHAR2 ,
   v_valeur_referentiel          IN VARCHAR2
                                                   )
IS
  nb_valeur_ds_champ INTEGER ;
  car VARCHAR2(50);
  car2 VARCHAR2(50);
  v_label VARCHAR2(255);
  DDL$OrdreSql VARCHAR2(255);
  monchamp_traiter VARCHAR2(255);
 
BEGIN
 DBMS_OUTPUT.PUT_LINE( 'debut  ') ; 
 
    IF LENGTH(monchampatraiter) IS NOT NULL THEN
	 monchamp_traiter:=  monchampatraiter;
     FOR i IN 1 .. LENGTH (monchampatraiter)
       LOOP
	    DBMS_OUTPUT.PUT_LINE( 'debut for '||monchampatraiter) ; 
            IF LENGTH (monchampatraiter) > 1 THEN
                    /* on recupere ',1,2,' par exemple*/
 DBMS_OUTPUT.PUT_LINE( 'debut for if ') ; 					
                        car :=  SUBSTR (monchampatraiter, INSTR(monchampatraiter,',',1,i)+1 , INSTR(monchampatraiter,',',1,i+1) - 
 
INSTR(monchampatraiter,',',1,i) ) ;
						 DBMS_OUTPUT.PUT_LINE( 'debut for if car'||car) ; 					
 
            /* on recupere '1' */
               car2 := SUBSTR(car, 1, INSTR(car,',',1,1)-1);
 DBMS_OUTPUT.PUT_LINE( 'debut for if car 2') ; 
               IF car2 IS NOT NULL  THEN
               /* on remplace par sa valeur de la table de référence  */
			   DDL$OrdreSql :=' SELECT  '||v_valeur_referentiel  ||' FROM '||v_object_refrentiel||' WHERE '||v_clef_referentiel ||'= 
 
'||car2;
                 ---------------------------------
               EXECUTE IMMEDIATE DDL$OrdreSql INTO v_label;
 
	      monchamp_traiter:=  REPLACE  (  monchamp_traiter, car2  ,v_label  ) ;
               END IF;
             END IF;
         END LOOP;
   END IF;
   DBMS_OUTPUT.PUT_LINE( 'fin on a ' ||monchamp_traiter);
 
  EXCEPTION
 
    WHEN OTHERS THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE( 'Code    erreur : ' || TO_CHAR( SQLCODE )) ;
 
RETURN ;
END TEST;
/

Probleme : le 13 est compris comme un 1 dans lors du replace !!

Code :

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

 EXEC TEST (',2,1,13,7,4,' ,  myreferentiel, id ,valeur) ;

Help

**SheikYerbouti** · 21/01/2010, 09h08

Dans ce cas, ordonnez vos nombre en ordre décroissant 13,8,2

**jo_la_poisse** · 21/01/2010, 14h44

Envoyé par SheikYerbouti

Dans ce cas, ordonnez vos nombre en ordre décroissant 13,8,2

Exact... c'est simple et ça repond au probleme

Merci beaucoup.

Voici la procestock en découpant la variable de droite à gauche.

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
50
51
52
53
54
 
CREATE OR REPLACE PROCEDURE "TEST" (
   monchampatraiter              IN VARCHAR2  ,
   v_object_refrentiel           IN VARCHAR2  ,
   v_clef_referentiel            IN   VARCHAR2 ,
   v_valeur_referentiel          IN VARCHAR2
                                                   )
IS
  nb_valeur_ds_champ INTEGER ;
  car VARCHAR2(50);
  car2 VARCHAR2(50);
  v_label VARCHAR2(255);
  DDL$OrdreSql VARCHAR2(255);
  monchamp_traiter VARCHAR2(255);
 
BEGIN
 DBMS_OUTPUT.PUT_LINE( 'debut  ') ; 
 
    IF LENGTH(monchampatraiter) IS NOT NULL THEN
	 monchamp_traiter:=  monchampatraiter;
     FOR i IN 1 .. LENGTH (monchampatraiter) 
       LOOP
	    DBMS_OUTPUT.PUT_LINE( 'debut for '||monchampatraiter) ; 
            IF LENGTH (monchampatraiter) > 1 THEN
                    /* on recupere ',1,2,' par exemple*/
 DBMS_OUTPUT.PUT_LINE( 'debut for if ') ; 					
/*   MODIF suite remarque SheikYerbouti   car :=  SUBSTR (monchampatraiter, INSTR(monchampatraiter,',',1,i)+1 , INSTR(monchampatraiter,',',1,i+1) - INSTR(monchampatraiter,',',1,i) ) ;*/
                         car :=  SUBSTR (monchampatraiter, INSTR(monchampatraiter,',',-1,i+1)+1 , INSTR(monchampatraiter,',',-1,i) - INSTR(monchampatraiter,',',-1,i+1) ) ;
						 DBMS_OUTPUT.PUT_LINE( 'debut for if car'||car) ; 					
 
            /* on recupere '1' */
               car2 := SUBSTR(car, 1, INSTR(car,',',1,1)-1);
 DBMS_OUTPUT.PUT_LINE( 'debut for if car 2') ; 
               IF car2 IS NOT NULL  THEN
               /* on remplace par sa valeur de la table de référence  */
			   DDL$OrdreSql :=' SELECT  '||v_valeur_referentiel  ||' FROM '||v_object_refrentiel||' WHERE '||v_clef_referentiel ||'= '||car2;
                 ---------------------------------
               EXECUTE IMMEDIATE DDL$OrdreSql INTO v_label;
 
			      monchamp_traiter:=  REPLACE  (  monchamp_traiter, car2  ,v_label  ) ;
               END IF;
             END IF;
         END LOOP;
   END IF;
   DBMS_OUTPUT.PUT_LINE( 'fin on a ' ||monchamp_traiter);
 
  EXCEPTION
 
    WHEN OTHERS THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE( 'Code    erreur : ' || TO_CHAR( SQLCODE )) ;
 
RETURN ;
END TEST;

**Waldar** · 21/01/2010, 19h32

Ou sinon en pur SQL :

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
  SELECT mt.CLEF, 
         NULLIF(REPLACE(REPLACE(XMLAgg(XMLElement("x", mr.VALEUR) ORDER BY instr(mt.LISTE_DATA, ',' || mr.id || ',') asc), '<x>', ','), '</x>', '') || ',', ',,') as res
    FROM MYTABLE mt
         LEFT OUTER JOIN MYREFERENTIEL mr
           ON instr(mt.LISTE_DATA, ',' || mr.id || ',') <> 0
GROUP BY mt.CLEF
ORDER BY mt.CLEF ASC;
 
 
CLEF 	 RES  
------- -------------------------------------
1	,label1,label2,label5,label9,label13,
2	,label1,label2,label3,
3	,label1,label5,label13,
4	,label1,label11,label13,
5	null

**jo_la_poisse** · 25/01/2010, 09h20

Envoyé par Waldar

Ou sinon en pur SQL :

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
  SELECT mt.CLEF, 
         NULLIF(REPLACE(REPLACE(XMLAgg(XMLElement("x", mr.VALEUR) ORDER BY instr(mt.LISTE_DATA, ',' || mr.id || ',') asc), '<x>', ','), '</x>', '') || ',', ',,') as res
    FROM MYTABLE mt
         LEFT OUTER JOIN MYREFERENTIEL mr
           ON instr(mt.LISTE_DATA, ',' || mr.id || ',') <> 0
GROUP BY mt.CLEF
ORDER BY mt.CLEF ASC;
 
 
CLEF 	 RES  
------- -------------------------------------
1	,label1,label2,label5,label9,label13,
2	,label1,label2,label3,
3	,label1,label5,label13,
4	,label1,label11,label13,
5	null

impec !! .... je vais re travailler un peu mon SQL....

comment traduire des clefs étrangeres ',1,2,3,4' => 'texte1,text2,text3,texte4' dans un champ

PL/SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager