Discussion :

[dede_bo]

Bonjour,

J'explique mon problème:
J'ai une table (Table1) avec un champ qui contient une liste d'ID ou l'objet est lié à différents noms (ID venant de Table2).
Cet ID est en fait n° de l'enregistrement du nom.

Table1
ID lie_a
1 | 5
2 | 8, 16
3 | 7

Table 2
ID nom
5 pomme
7 poire
8 raisin blanc
16 raisin noir

J'essaie d'extraire la liste des noms.

ID NOM
1 | pomme
2 | raisin blanc, raisin noir
3 | poire

Code :

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

1
2
3
4
5
6
 
SELECT table2.nom
   FROM table2
  WHERE table2.ID IN(SELECT table1.lie_a
                                FROM table1
                            )

Le problème est qu'une liste est une chaine de caractères et pas numérique. Donc, cela fonctionne pas.
Si je modifie la ligne suivante:

Code :

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

1
2
WHERE TO_CHAR(table2.ID) IN(SELECT table1.lie_a

Il n'y a pas d'erreur mais le résultat est vide.

J'ai cherché sur ce forum et Google.
Une discussion qui existe : https://www.developpez.net/forums/d1...on-d-variable/
L'auteur l'a clôturé sans mettre la solution.

Alors, si l'auteur se reconnait, auriez-vous la gentillesse de m'expliquer votre solution?
Autrement, quelqu'un aurais t' il une idée à proposer?

Merci
Bonne journée
André

[Waldar]

La modélisation de votre base est erronée et viole la première forme normale.
Oui, on peut jouer avec des solutions pour répondre au besoin, mais ce ne sera jamais très performant.

[Waldar]

Je ne vous ai pas proposé la bonne solution en me relisant, mais du coup voici de quoi répondre à votre besoin.
11gR2 minimum.

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
with table1 (id, lie_a) as
(
select 1, '5'     from dual union all
select 2, '8, 16' from dual union all
select 3, '7'     from dual
)
  ,  table2 (id, nom) as
(
select  5, 'pomme'        from dual union all
select  7, 'poire'        from dual union all
select  8, 'raisin blanc' from dual union all
select 16, 'raisin noir'  from dual
)
  ,  table1_rec (id, lie_a, lie_reste, lvl, nb_recurs) as
(
select id
     , substr(lie_a, 1, instr(lie_a, ',') - 1)
     , substr(lie_a, instr(lie_a, ',') + 2)
     , 1
     , length(lie_a) - length(replace(lie_a, ',', ''))
  from table1
 where instr(lie_a, ',') > 0
 union all
select id
     , case lvl
         when nb_recurs
         then substr(lie_reste, 1)
         else substr(lie_reste, 1, instr(lie_reste, ',') - 1)
       end
     , substr(lie_reste, instr(lie_reste, ',') + 2)
     , lvl + 1
     , nb_recurs
  from table1_rec
 where lvl <= nb_recurs
)
  ,  table1_finale (id, lie_a, ord) as
(
select id, to_number(lie_a), lvl
  from table1_rec
 union all
select id, to_number(lie_a), 1
  from table1
 where instr(lie_a, ',') = 0
)
    select t1.id
         , listagg(t2.nom, ', ') within group (order by t1.ord asc) as nom
      from table1_finale t1
 left join table2        t2 on t2.id = t1.lie_a
  group by t1.id;

Je vous invite toujours à reprendre la modélisation.

[escartefigue]

Je vous invite toujours à reprendre la modélisation.

+1 : à modélisation merd####, solution complexe, fiabilité douteuse et performances désastreuses

[dede_bo]

Bonjour à tous les deux,
Merci pour vos réactions. Je crains effectivement pour les performances.
Par contre, de mon côté, un collègue viens de me donner la solution (avec de bonnes performances)

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR(t.lie_a, '[^,]+', 1, lines.column_value))) AS fruitID, sl.nom
  FROM Table1 t, Table2 sl,
           TABLE (CAST (MULTISET(SELECT LEVEL FROM dual
                                                       CONNECT BY INSTR(t.lie_a, ',', 1, LEVEL - 1) > 0
                                 ) AS sys.odciNumberList 
                        ) 
                 ) lines
 WHERE sl.ID = TO_NUMBER(TRIM(REGEXP_SUBSTR(t.lie_a, '[^,]+', 1, lines.column_value)))

Je dois lui demander qu'il m'explique cet SQL mais elle fonctionne très bien.

Voilà

Bonne après-midi
André

[escartefigue]

Par contre, de mon côté, un collègue viens de me donner la solution (avec de bonnes performances)

Sur quelle volumétrie avez vous mesuré les perfs ? je doute que les perfs soient acceptables avec un volume significatif

[McM]

Pour la simplicité du code, juste avec un INSTR et une Jointure Cartésienne

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
WITH table1 (ID, lie_a) AS
(
SELECT 1, '5'     FROM dual UNION ALL
SELECT 2, '16, 5,8,7' FROM dual UNION ALL
SELECT 3, '7'     FROM dual
)
  ,  table2 (ID, nom) AS
(
SELECT  5, 'pomme'        FROM dual UNION ALL
SELECT  7, 'poire'        FROM dual UNION ALL
SELECT  8, 'raisin blanc' FROM dual UNION ALL
SELECT 16, 'raisin noir'  FROM dual
)
SELECT n.ID, listagg(l.nom, ', ') WITHIN GROUP (ORDER BY INSTR(','||REPLACE(n.lie_a, ' ') ||',', ','|| l.ID ||',')) lib
FROM table1 n, table2 l
WHERE INSTR(','||REPLACE(n.lie_a, ' ') ||',', ','|| l.ID ||',') > 0 
GROUP BY n.ID
 
ID	LIB
1	pomme
2	raisin noir, pomme, raisin blanc, poire
3	poire